Servoy Forum

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.

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

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.

Thanks Guys. Great Help.