Two queries - different?

Classic Servoy
1

I’m not getting the results I expect when I perform a Servoy-style search.

Is there a difference between this,

queryText = "SELECT std_student_id FROM student";
queryText += " JOIN transport_action ON (trn_student_id = std_student_id)";
queryText += " WHERE trn_action_category = \'request\' AND";
queryText += " trn_req_start_d IS NOT NULL AND";
queryText += " trn_req_start_d::date >= \'" + utils.dateFormat(transRequestSince_D,"yyyy-MM-dd") + "\'";		
fs.loadRecords(queryText, null);

and this,

fs.find();
 fs.student_to_transport_action.trn_action_category = 'request';
fs.student_to_transport_action.trn_req_start_d = "!^";  // is not null
fs.student_to_transport_action.trn_req_start_d = ">=" + utils.dateFormat(transRequestSince_D,"yyyy-MM-dd");
fs.search();

Thank you,
Don

2

Why are you escaping the single quotes? You can also use the \ (backslash) to get a cleaner code like this:

queryText = "SELECT std_student_id FROM student \
 JOIN transport_action ON (trn_student_id = std_student_id) \
 WHERE trn_action_category = 'request' AND \
 trn_req_start_d IS NOT NULL AND \
 trn_req_start_d::date >= '" + utils.dateFormat(transRequestSince_D,"yyyy-MM-dd") + ' "  

fs.loadRecords(queryText, null);

Beside that they should generate the same query.

Another hint, don’t hardcode parameters inside the query, use prepared statements:

queryText = "SELECT std_student_id FROM student \
 JOIN transport_action ON (trn_student_id = std_student_id) \
 WHERE trn_action_category = ? AND \
 trn_req_start_d IS NOT NULL AND \
 trn_req_start_d::date >= ? "  

fs.loadRecords(queryText, ['request',utils.dateFormat(transRequestSince_D,"yyyy-MM-dd")]);
3

Hi Nicola,

The sql is generating the results that I expect to get. The Servoy statement does not. Is there something about the way the Servoy statement is constructed that causes it to fail?

Thanks for the hints on the sql statements.

Don

4

Is trn_req_start_d a datetime column? Is transRequestSince_D a datetime object?

Doing like this:

fs.student_to_transport_action.trn_req_start_d = "!^";  // is not null
fs.student_to_transport_action.trn_req_start_d = ">=" + utils.dateFormat(transRequestSince_D,"yyyy-MM-dd");

You are overwriting the search request so basically the first line of code is useless, altough I guess it’s not important since any date is greater than null.

What do you actually store in the trn_action_category colum? ‘request’ or request (with or without the single quotes)?

5

Woops, I noticed only now an error in your code, when searching over a datetime column you have to do like this (notice the last string with the pipe char):

fs.student_to_transport_action.trn_req_start_d = '>='+utils.dateFormat(transRequestSince_D,'yyyy-MM-dd')+'|yyyy-MM-dd'

Probably that’s the reason of your code failing.

6

Both the column and the variable are datetime. But you hit the nail on the head with the pipe character. I knew at one time to do that, but forgot.

Thank you, Nicola! - Don