Hi,
Is there a method or recommended way of constructing a complete SQL query from these 2 methods ?? rather than having seperate sql and parameters. I would like to pass the whole query including parameters to Jasper reports ??
databaseManager.getSQL(foundset)
databaseManager.getSQLParameters(foundset)
Many Thanks
I created a method for that some time ago:
function sqlParse()
{
//Combines query and argument array into 1 string, mostly useful for testing
var _query = arguments[0];
var _args = arguments[1];
if (_args.length != utils.stringPatternCount(_query, "?")) { //number of params and number of ?'s doesn't match
return "-ERROR- args: " + _args.length + "; query: " + utils.stringPatternCount(_query, "?") + ";";
}
var _val;
//Loop through array and replace question marks by values
for (var i=0; i < _args.length; i++) {
switch(typeof _args[i]) {
case "string":
_val = "'" + _args[i] + "'";
break;
case "object": //date
_val = "'" + utils.dateFormat(_args[i], "yyyy-MM-dd HH:mm:ss.SSS") + "'";
break;
default: //number, integer
_val = _args[i];
}
_query = _query.replace(/\?{1}/, _val);
}
//Format the query a little to please the eye
_query = _query.replace(/[\t\n]/g, "").replace(/(WHERE|AND|OR|ORDER|GROUP)/g, "\n$1");
return _query;
}
The first argument is the sql-string, and the second the parameters-array.
Thanks Joas,
Should it wirk in servoy 3.5 OK. I seem to be getting some errors, it stops at the switch ??
switch(typeof _args*)*
*```*
*Heres the error*
*org.mozilla.javascript.EvaluatorException: Invalid JavaScript value of type java.sql.Timestamp (gSqlParse, line 14)*I haven’t really used it since 3.1, but I don’t know a reason why it wouldn’t work in 3.5.
What value is in _args when you get the error?
HI Joas,
Joas:
I haven’t really used it since 3.1, but I don’t know a reason why it wouldn’t work in 3.5.What value is in _args when you get the error?[/quote]
It is a date value of 2009-04-01 00:00:00.0
all other datatypes seem to be fine
I tried to reproduce this using Sybase, but I don’t get the error. What database do you use?
I am using Mysql
Not sure if that causes the problem, but I suggest you create a case in the support system with a small solution that demonstrates the problem. Make sure to mention your database version.
OK,
Just out of interest though, what date format does sybase return, as it seems to me that at this stage of the method that we are dealing with vars not database values.
prj311:
it seems to me that at this stage of the method that we are dealing with vars not database values.
The value I get is a javascript date variable, but for some reason your value isn’t a javascript var, but a java.sql.Timestamp. That’s what causes the problem.
It is coming from a global would that make a difference? I could change the format if required ?
Does it make a difference if you do: new Date(globals.yourvar) instead of just: ```
globals.yourvar
No, That does not seem to help, but I have noticed that after you ```
databaseManager.getSQLParameters(foundset)
Fri May 01 20:11:52 EST 2009
2009-05-01 20:11:52.028
is this correct ?I’m pretty sure if you drop the milliseconds off it will work. So:
utils.dateFormat(_args*, "yyyy-MM-dd HH:mm:ss.SSS") + "'";*
*```*
*becomes:*
*```*
<em>utils.dateFormat(_args*, "yyyy-MM-dd HH:mm:ss") + "'";*</em>
_*```*_
_*Alternately, drop the time formatting off entirely and mysql will assume 00:00:00:*_
_*```*_
<em><em>utils.dateFormat(_args*, "yyyy-MM-dd") + "'";*</em></em>
<em>_*```*_</em>
<em>_*Also, if you want the "pretty" formatting to work consistently (nice trick Joas!), make the regexp case insensitive by adding an "i" to the last regexp filter:*_</em>
<em>_*```*_</em>
<em><em>*_query = _query.replace(/[\t\n]/g, "").replace(/(WHERE|AND|OR|ORDER|GROUP)/gi, "\n$1");*</em></em>
<em>_*```*_</em>david:
I’m pretty sure if you drop the milliseconds off it will work.
I actually tested it with a (java) query tool directly on a date and datetime column in MySQL and it works just fine with the whole date plus time AND milliseconds.
So it should just work fine in Servoy.
Hi David,
Thanks for your help, but the method is failing at the beginning of the loop, it seems that JS ‘typeof’ is the problem. I assume that your suggestions for reformating the date is for further in the loop??
//Loop through array and replace question marks by values
for (var i=0; i < _args.length; i++) {
switch(typeof _args[i]) {
regards
Could have to do with version and/or engine type? There is an outstanding millisecond bug:
So even if the value _args is being populated from a global or a var and not from the database nor is it being written back to the database is it still possible to be a mysql bug???
No, not a mysql issue.
Are you sure it’s stopping on the “typeof” line?
I’m noticing the method will choke on:
if (_args.length != utils.stringPatternCount(_query, "?"))
if there isn’t actually a search performed on your form (because _args variable is null and so _args.length is invalid).
prj311:
So even if the value _args is being populated from a global or a var and not from the database nor is it being written back to the database is it still possible to be a mysql bug???[/quote]
Be sure that variable “_args” is populated by:
* *databaseManager.getSQLParameters(foundset)* *