Servoy 3.5.1 : Problem: Can't load records when date in args

Classic Servoy
1

Hi, I’m trying to work out if I’m doing something stupid or there is a bug:

I have a query

SELECT document_id FROM acc_documents WHERE date_trunc('day',document_date) = ? ORDER BY document_number DESC

And a date argument in the args array

[Tue Jan 09 2007 00:00:00 GMT-0000 (GMT)]

If I try

currentcontroller.loadRecords(query,args);

I get an error

Error loading primary key data, Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.
com.servoy.j2db.dataprocessing.DataException: Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.
2

What happens when you perform the query outside of Servoy?

3

Can you show us, how you really create the arguments array for the query? Your quote looks like it is a string.

4

Hi, the argument is created using new Date(). The same query and args work when used in

databaseManager.getDataSetByQuery().

try this code (my table is called test2, with two fields, test2_id and a_date)

//CREATE SOME SAMPLE DATA
controller.loadAllRecords();
if(controller.getMaxRecordIndex() == 0) {
	for ( var i = 1 ; i < 10 ; i++ )
	{
		controller.newRecord();
		a_date = new Date();
		//MY TREE DOES NOT CONTAIN controller.saveData() !!!!
	}
}

//CODE WHICH FAILS
var query = "SELECT test2_id FROM test2 WHERE a_date = ? ORDER BY a_date";
var d = new Date();
var args = new Array();
args.push(d);
controller.loadRecords(query,args);

I have now created a case for this.

5

Have you tried to use

controller.loadRecords(query, [d]);

Maybe something goes wrong with push?

6

Hi Patrick,

tried your idea but I get the same error.

push is a fairly basic javascript function so I would have been surprised if it was the culprit.

The error-message I get is from java…

7

I think I have seen your error before. Have you reported this one (with sample)? Dates are one of the main reasons for using a prepared statement, because you never know what the database expects…

8

Yep. I produced a sample solution which fails!

9

This issue has been solved in Servoy 3.5.3.

It was specific to Date objects in custom queries.

Rob