getDataSetByQuery with multiple arguments

Classic Servoy
1

With a prepared SQL statement, is it possible to have more than one ? please see code below. Do I instead need to do something like:

var Query = ‘SELECT start_time, end_time FROM calendar where user_id = ? AND ((start_time >= /’ + end + ’ AND end_time <= /’ + end + ')…etc

var Query = 'SELECT start_time, end_time FROM calendar where user_id = ? AND ((start_time >= ? AND end_time <= ?) OR (start_time >=  AND ? <= end_time))';
	var dayArgs = new Array();
	dayArgs[0] = userID;
	dayArgs[1] = end;
	dayArgs[2] = end;
	dayArgs[3] = start;
	dayArgs[4] = start;
2

Hi Phil,

Yes it’s possible to have more arguments in your prepared statement. You pass the arguments in an array in the same order as the position of the placeholder in de query.
So like you were doing in your example code.

By the way you can assign the array variable in one statement like so:

var Query = 'SELECT start_time, end_time FROM calendar where user_id = ? AND ((start_time >= ? AND end_time <= ?) OR (start_time >=  AND ? <= end_time))';
var dayArgs = [userID,end,end,start,start];

Hope this helps.

3

Thank you Robert, that was a very quick response.

I have another question relating to prepared statements and dates: What is the best way to compare dates in a prepared statement?

I am getting errors trying to compare a Date() object to a value in a DateTime dataprovider with a prepared statement, (SQL Anywhere Error -131: Syntax error near ‘Sep’ on line 1 ‘Sep’ being September) a date string doesn’t seem to work, I found DateCompare() on the internet but that doesn’t seem to be what I am after, I even tried storing the milliseconds since 1970 in an integer column but that didn’t work (paramter out of bounds, I guess a trillion or so is a bit over the top), it’s 3am :shock: and this is ‘doing my head in’! I use an online query validator, so the syntax should be OK, it’s Sybase having a fit with the date/time format. I even tried cutting and pasting the date time/string direct from the raw data in Sybase Central and it still told me where I could stick my prepared statement wth multiple parameters!

I have seen examples that use a basic date like ‘12/5/2009’ but I need at least minute accuracy. Any ideas would be much appreciated!

//SELECT start_time, end_time FROM calendar where user_id = ? AND ((start_time >= ? AND end_time <= ?) OR (start_time >= ? AND ? <= end_time)) doesn’t play ball.

4

Hi Phil,

What if you pass the date argument using utils.dateFormat(yourDateValue, ‘yyyy-MM-dd HH:mm:ss’) ?

5

Doesn’t work either. I think I need to get the components of the date I need to compare(days, hours etc), then compare them with a constant (dataprovider). For my purposes, I am going to try and store the date as an integer of minutes since base line 1970. Thanks for your help Robert.