invalid character value for cast

Classic Servoy
1

I am trying to create a data source from a data set obtained by a select query:

var _bDataSet = databaseManager.getDataSetByQuery(_bDatabase, _bQuery, _bPars, 5)
var _bDataSource = _bDataSet.createDataSource(‘_bDataSource’)

The dataset is correctly populated but the next step of creation gives me the following exception:

Exception Object: com.servoy.j2db.dataprocessing.DataException: data exception: invalid character value for cast
MSG: data exception: invalid character value for cast
is a ServoyException
Errorcode: 100
data exception: invalid character value for cast
> java.lang.reflect.InvocationTargetException

I’ve tried to change input parameters and to specify columns type but I still get the same error.
Here’s what I get in the dataset : [1.0, A, 8.30-12.30 13.30-17.30, 6.0, 2.0]
Maybe it doesn’t like the dots?!? Any idea?

2

What is the datatype of the column that you query? And which Servoy version do you use?

3

They are in order : numeric, varchar, varchar, varchar, numeric obtained from a sqlserver database
I’m using Servoy 6

4

do you have a full stacktrace of that error in the log?

5

I have the same error.

2012-06-11 19:09:45,231 ERROR [Debug command reader] com.servoy.j2db.util.Debug - Error executing sql: insert into "TEMP_31_F666A762-D3F6-43C5-9849-32AA166DF137" (description, notes, status) values (?, ?, ?) with params: ['task' ,type: java.lang.String, 'added from context' ,type: java.lang.String, 'A' ,type: java.lang.String]
java.sql.SQLException: data exception: invalid character value for cast
	at org.hsqldb.jdbc.Util.throwError(Util.java:81)
	at org.hsqldb.jdbc.JDBCPreparedStatement.setParameter(JDBCPreparedStatement.java:4186)
	at org.hsqldb.jdbc.JDBCPreparedStatement.setString(JDBCPreparedStatement.java:587)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.servoy.j2db.Za.Zg.invoke(Zg.java:14)
	at $Proxy6.setString(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:135)
	at com.servoy.j2db.dataprocessing.Zv.Za(Zv.java:769)
	at com.servoy.j2db.dataprocessing.Zv.Za(Zv.java:899)
	at com.servoy.j2db.dataprocessing.Zv.performUpdates(Zv.java:465)
	at com.servoy.j2db.dataprocessing.Zv.insertDataSet(Zv.java:182)
	at sun.reflect.GeneratedMethodAccessor469.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.servoy.j2db.debug.SerializingDataserverProxy.invoke(SerializingDataserverProxy.java:91)
	at $Proxy7.insertDataSet(Unknown Source)
	at com.servoy.j2db.debug.ProfileDataServer.insertDataSet(ProfileDataServer.java:639)
	at com.servoy.j2db.dataprocessing.DataServerProxy.insertDataSet(DataServerProxy.java:242)
	at com.servoy.j2db.dataprocessing.FoundSetManager.createDataSourceFromDataSet(FoundSetManager.java:2023)
	at com.servoy.j2db.dataprocessing.JSDataSet.js_createDataSource(JSDataSet.java:547)
	at com.servoy.j2db.dataprocessing.JSDataSet.execIdCall(JSDataSet.java:1680)
	at org.mozilla.javascript.IdFunctionObject.call(IdFunctionObject.java:127)
	at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:65)
	at org.mozilla.javascript.gen.c294._c0(eval:0)
	at org.mozilla.javascript.gen.c294.call(eval)
	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:387)
	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3134)
	at org.mozilla.javascript.gen.c294.call(eval)
	at org.mozilla.javascript.gen.c294.exec(eval)
	at org.mozilla.javascript.Context.evaluateString(Context.java:1233)
	at org.eclipse.dltk.rhino.dbgp.DBGPDebugFrame.eval(DBGPDebugFrame.java:139)
	at org.eclipse.dltk.rhino.dbgp.EvalCommand.parseAndExecute(EvalCommand.java:45)
	at org.eclipse.dltk.rhino.dbgp.DBGPDebugger.run(DBGPDebugger.java:549)

Server Information
Servoy version 6.0.6 i2-build 1234
Port used by RMI Registry: 1099
Repository version 40

6

The database engine is MS SQL Server:

CREATE TABLE [dbo].[ab_user_task](
	[description] [nvarchar](100) NOT NULL,
	[status] [char](1) NOT NULL,
	[notes] [ntext] NULL,
        ....
)

I receive the error only when there is a database column of type “ntext”. Here the column “notes” is of type “ntext”.

Servoy pseudo code:

dataset = getDataSetByQuery('select description, notes, status from ab_user_task', ...);
dataset.createDataSource(...) // this line throws the error
7

Vasil.

The error occurs when the query result is stored in an inmemory (hsql) database.

When you create a datasource this way, Servoy will deduct the column types from the query result and use those as types in the hsql db.
Something goes wrong with the combination you use here, please create a case in our support system for that.

As a workaround you may pass in the types in the createDataSource call like this:

createDataSource('mydata', [JSColumn.TEXT], JSColumn.TEXT, JSColumn.TEXT]);

Rob

8

Yes, it works if I pass in the types in the createDataSource.

In my scenario the code is executed against different database tables and columns at run time. I do not know in advance what are the column types.
I need a away to infer the types at run time or to have this done automatically by Servoy.

9

Vasil,

This should be done automatically by Servoy.
If you create a case in our support system we will look into this.

Rob

10

I have created case.
https://support.servoy.com/browse/SVY-2435

11

I encountered the same bug today with a Postgres db and also trying to build a datasource with createDataSource after a sql query with getDataSetByQuery.

Confirmed with Servoy 7.4.1 and 7.4.4.

Is there a technical solution other than specifying all column datatypes ? (which is not really easy in my case) ?

Philippe