Help with difference between 5 & 6

Questions, tips and tricks and techniques for scripting in Servoy

Help with difference between 5 & 6

Postby MacSince85 » Fri Jan 27, 2012 9:31 pm

Hi All

I am having a difficult time converting a method from version 5 to version 6. Following is the code:
Code: Select all
var uniT = unit;
   var arrive = utils.dateFormat(arr_date,'MM/dd/yyyy');
   var depart = utils.dateFormat(dep_date,'MM/dd/yyyy');
   var resNum = res_num;
   var query = 'SELECT res_num FROM reservations WHERE res_num= ? AND status= ? AND unit = ? AND \
         ((arr_date<=? AND dep_date > ? AND dep_date <= ?) OR \
         (arr_date >= ? AND arr_date< ? AND dep_date >= ?) OR \
         (arr_date <= ? AND dep_date >= ?) OR (arr_date >= ? AND dep_date <= ?))';
   var args = new Array(); args[0] = resNum;  args[1] = 'Active';  args[2] = uniT;  args[3] = arrive;
   args[4] = 'arrive'; args[5] = depart; args[6] = arrive; args[7] =depart; args[8] =depart;
   args[9] = arrive; args[10] = depart; args[11] = arrive; args[12] =depart;
   var dataset = databaseManager.getDataSetByQuery(globals.server,query,args,-1);

The error is:
ERROR: operator does not exist: timestamp without time zone > character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 116
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: operator does not exist: timestamp without time zone > character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 116 (/Users/TPG/servoy_workspace/TWF_Database/forms/Reservations.js#1565)

Right now I am not understanding the why or how of type casts.
Thanks for the help.
Mac OS X 10.9
Java 1.6.0_65, 64-Bit Server VM
Servoy version7.4.0 -build 2026
MacSince85
 
Posts: 14
Joined: Wed May 04, 2011 12:20 am

Re: Help with difference between 5 & 6

Postby jasantana » Fri Jan 27, 2012 10:06 pm

The problem is not Servoy. It´s a DB problem, you are sending "arrive" in argument 4. It´s a string not a variable name
Best regards,
Juan Antonio Santana Medina
jasantana@nephos-solutions.co.uk
Servoy MVP 2015
Servoy 6.x - Servoy 7.x - Servoy 8.x - MySQL - PostgreSQL - Visual Foxpro 9
User avatar
jasantana
 
Posts: 555
Joined: Tue Aug 10, 2010 11:40 am
Location: Leeds - West Yorkshire - United Kingdom

Re: Help with difference between 5 & 6

Postby ROCLASI » Fri Jan 27, 2012 10:41 pm

jasantana wrote:The problem is not Servoy. It´s a DB problem, you are sending "arrive" in argument 4. It´s a string not a variable name


Well I would call that a programming error. Not a DB problem ;)

But that is not the only issue with the code. PostgreSQL, the db used behind this code, doesn't auto-cast string dates to dates for you (anymore). So you need to pass real date objects like so:
Code: Select all
// Use date objects with the time component set to 00:00:00
var uniT = unit,
    arrive = new Date(arr_date.getFullYear(), arr_date.getMonth(), arr_date.getDate(), 0, 0, 0),
    depart = new Date(dep_date.getFullYear(), dep_date.getMonth(), dep_date.getDate(), 0, 0, 0),
    resNum = res_num,
    query = "SELECT res_num FROM reservations WHERE res_num= ? AND status= ? AND unit = ? AND \
        ((arr_date<=? AND dep_date > ? AND dep_date <= ?) OR \
        (arr_date >= ? AND arr_date< ? AND dep_date >= ?) OR \
        (arr_date <= ? AND dep_date >= ?) OR (arr_date >= ? AND dep_date <= ?))",
    args = [resNum, 'Active', uniT, arrive, arrive, depart, arrive, depart, depart, arrive, depart, arrive, depart],
    dataset = databaseManager.getDataSetByQuery(globals.server,query,args,-1);

// rest of your code


Or you can pass the string dates to a SQL function that makes it a real timestamp:
Code: Select all
// Use to_timestamp() function in SQL to cast the string dates to a timestamp
var uniT = unit,
    arrive = utils.dateFormat(arr_date,'MM/dd/yyyy'),
    depart = utils.dateFormat(dep_date,'MM/dd/yyyy'),
    resNum = res_num,
    query = "SELECT res_num FROM reservations WHERE res_num= ? AND status= ? AND unit = ? AND \
        ((arr_date<=to_timestamp(?,'MM/DD/YYYY') AND dep_date > to_timestamp(?,'MM/DD/YYYY') \
        AND dep_date <= to_timestamp(?,'MM/DD/YYYY')) OR \
        (arr_date >= to_timestamp(?,'MM/DD/YYYY') AND arr_date< to_timestamp(?,'MM/DD/YYYY') \
        AND dep_date >= to_timestamp(?,'MM/DD/YYYY')) OR \
        (arr_date <= to_timestamp(?,'MM/DD/YYYY') AND dep_date >= to_timestamp(?,'MM/DD/YYYY')) \
        OR (arr_date >= to_timestamp(?,'MM/DD/YYYY') AND dep_date <= to_timestamp(?,'MM/DD/YYYY')))",
    args = [resNum, 'Active', uniT, arrive, arrive, depart, arrive, depart, depart, arrive, depart, arrive, depart],
    dataset = databaseManager.getDataSetByQuery(globals.server,query,args,-1);

// rest of your code

See http://www.postgresql.org/docs/9.1/stat ... tting.html for more on the SQL function used.

The above code also shows how you can declare AND fill an array in one command instead of filling it one value at a time.
(and declaring a bunch of variables in one var statement)

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5342
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Help with difference between 5 & 6

Postby MacSince85 » Sun Jan 29, 2012 6:16 pm

Thanks Guys. Great Help.
Mac OS X 10.9
Java 1.6.0_65, 64-Bit Server VM
Servoy version7.4.0 -build 2026
MacSince85
 
Posts: 14
Joined: Wed May 04, 2011 12:20 am


Return to Methods

Who is online

Users browsing this forum: No registered users and 2 guests