loadRecords(sql, []) fails with 'sysdate-1' as param

Questions, tips and tricks and techniques for scripting in Servoy

loadRecords(sql, []) fails with 'sysdate-1' as param

Postby eKelman » Sat Aug 20, 2011 12:41 am

Does anyone know what I can do to load a foundset via controller.loadRecords(sqlstring, params); where the parameter is a date field and we want the dates to be say the last 24 hours, 3 days, 1 week or 30 days?

our code passes something like 'sysdate-1' as the parameter.

This worked in 5.2.7 but now fails in both 5.2.8 and 5.2.9 versions of servoy


Server Information
Servoy version 5.2.9 -build 1020
Port used by RMI Registry: 1099
Repository version 38
Firefox 3.6.16
Tested O/S: Windows 7 and XP and linux application servers
Ernest
Kelman Technologies
eKelman
 
Posts: 52
Joined: Tue May 12, 2009 9:09 pm

Re: loadRecords(sql, []) fails with 'sysdate-1' as param

Postby Hans Nieuwenhuis » Sat Aug 20, 2011 9:00 am

sysdate -1 still has a time component.
You should remove that or use a from datetime1 to datetime2

Regards,
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: loadRecords(sql, []) fails with 'sysdate-1' as param

Postby rgansevles » Mon Aug 22, 2011 3:49 pm

Ernest

When you call foundset.loadrecord(sql, parms) the question marks in the sql are replaced with the values passed in, a value 'sysdate-30' will be interpreted as string, not as a special function of the db.
This has never worked before.

You can call db-specific functions with custom sql like this:
foundset.loadrecords('select order_id from orders where priority <> ? and row_created_date >= sysdate-30 and row_created_by = ?', ['5', 'ernest'])

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: loadRecords(sql, []) fails with 'sysdate-1' as param

Postby eKelman » Mon Aug 22, 2011 7:35 pm

Rob,

Thanks I will take a look at the code and remove it as a parameter being passed.

Ernest
Ernest
Kelman Technologies
eKelman
 
Posts: 52
Joined: Tue May 12, 2009 9:09 pm


Return to Methods

Who is online

Users browsing this forum: No registered users and 10 guests

cron