Conversion error on solution import with MS SQL Server 2005

Classic Servoy
1

Hi everybody,

We made a solution export with data from a PostgreSQL 8.4.2 database. Then we have imported it onto a server using MS SQL Server 2005. This second database was blank.
During the import the tables have been properly created but the data import didn’t work for some tables and we got the following error:

[info] Created table ‘tbl_expenses_currency’ in server ‘epic’.
[info] Added column with name ‘fld_id’ to table ‘tbl_expenses_currency’ in server ‘epic’.
[info] Added column with name ‘fld_currency’ to table ‘tbl_expenses_currency’ in server ‘epic’.
[info] Added column with name ‘fld_symbol’ to table ‘tbl_expenses_currency’ in server ‘epic’.
[info] Added column with name ‘fld_exchange_rate’ to table ‘tbl_expenses_currency’ in server ‘epic’.
…
[warning] Sample data import failed for table tbl_expenses_currencyin server epic ,exception com.servoy.j2db.dataprocessing.DataException: Error converting data type nvarchar to decimal.

2011-03-14 14:24 http-8080-1 ERROR com.servoy.j2db.persistence.XMLInMemoryImportHandlerVersions11AndHigher com.servoy.j2db.dataprocessing.DataException: Error converting data type nvarchar to decimal.
2011-03-14 14:24 http-8080-1 ERROR com.servoy.j2db.util.Debug Error executing sql: insert into tbl_expenses_currency (fld_id, fld_currency, fld_symbol, fld_exchange_rate) values (?, ?, ?, ?) with params: [5 ,type: java.lang.Integer, ‘EUR - Euros’ ,type: java.lang.String, ‘?’ ,type: java.lang.String, 1.57 ,type: java.lang.Double]
com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
at sun.reflect.GeneratedMethodAccessor225.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.Za.Za.Zf.invoke(Zf.java:3)
at $Proxy0.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
…

We got this kind of error for 11 other tables :(

Here is the list of columns datatypes of the table tbl_expenses_currency (the error example)

 fld_id integer NOT NULL,
  fld_currency character varying(50) DEFAULT NULL::character varying,
  fld_symbol character varying(5) DEFAULT NULL::character varying,
  fld_exchange_rate numeric(10,2) DEFAULT NULL::numeric

In MS SQL Server 2005 here is the list of columns datatypes (after import):

fld (PK, int, not null)
fld_currency (nvarchar(50), null)
fld_symbol (nvarchar(5), null)
fld_exchange_rate(numeric(19,2), null)

And in Servoy the list of columns datatypes is:

fld_id INTEGER (servoy sequence)
fld_currency TEXT length:50
fld_symbol TEXT length:5
fld_exchange_rate NUMBER length:10,2

By the way, I don’t understand why after the import the column fld_exchange_rate went from numeric(10,2) to numeric(19,2) :shock:

Cheers.

2

Should I create a case for it? :roll:

3

Foobrother,

Yes, please create a case.
Create a new sample solution with just 1 form on the tbl_expenses_currency table in your postgres dev server, export with sample data and attach the .servoy file to the case.
We will try to import that into our sqlserver db.

Rob

4

Case 365299 created with the sample solution you requested.

5

Foobrother:
By the way, I don’t understand why after the import the column fld_exchange_rate went from numeric(10,2) to numeric(19,2) :shock:

I noticed similar problems. My case 362633 was already checked but no error was found.
Maybe Servoy can find the problem when they look at both my case and your case

6

FooBrother,

I imported the sample solution in our sql server 2008 database.
I got the same column types, but I did not get the error, the sample data was imported correctly.

Please try with the latest drivers or,if possible, with the latest sql server.

The reason that the data type changed is that we use a mapping (as defined by the Hibernate library) for java types to the db specific ddl sql.
For sql server this maps different then for postgres.

Another option could be to create the table outside of Servoy and (re) start Servoy.
Servoy will always work with existing tables as they are.

Rob