# Combining SQL and Params

**URL:** <https://forum.servoy.com/t/combining-sql-and-params/10623>\
**Category:** Classic Servoy\
**Created:** [May 7, 2009, 9:23am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623 "2009-05-07T09:23:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 7, 2009, 9:23am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/1 "2009-05-07T09:23:25Z")

</div>

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 ??

```auto
databaseManager.getSQL(foundset)
databaseManager.getSQLParameters(foundset)

```

Many Thanks

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 7, 2009, 3:12pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/2 "2009-05-07T15:12:59Z")

</div>

I created a method for that some time ago:

```auto
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.

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 7, 2009, 10:33pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/3 "2009-05-07T22:33:15Z")

</div>

Thanks Joas,

Should it wirk in servoy 3.5 OK. I seem to be getting some errors, it stops at the switch ??

````auto
switch(typeof _args*)* 
*```*
*Heres the error*
*org.mozilla.javascript.EvaluatorException: Invalid JavaScript value of type java.sql.Timestamp (gSqlParse, line 14)*
````

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 8, 2009, 7:23am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/4 "2009-05-08T07:23:40Z")

</div>

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?_

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 7:58am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/5 "2009-05-08T07:58:15Z")

</div>

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_

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 8, 2009, 8:05am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/6 "2009-05-08T08:05:22Z")

</div>

I tried to reproduce this using Sybase, but I don’t get the error. What database do you use?

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 9:17am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/7 "2009-05-08T09:17:08Z")

</div>

I am using Mysql

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 8, 2009, 9:20am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/8 "2009-05-08T09:20:24Z")

</div>

Not sure if that causes the problem, but I suggest you create a case in the [support system](http://www.servoy.com/s) with a small solution that demonstrates the problem. Make sure to mention your database version.

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 9:34am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/9 "2009-05-08T09:34:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 8, 2009, 9:52am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/10 "2009-05-08T09:52:02Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 9:54am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/11 "2009-05-08T09:54:44Z")

</div>

It is coming from a global would that make a difference? I could change the format if required ?

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [May 8, 2009, 10:00am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/12 "2009-05-08T10:00:59Z")

</div>

Does it make a difference if you do: `new Date(globals.yourvar)` instead of just: ```  
globals.yourvar

```auto

```

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 10:26am UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/13 "2009-05-08T10:26:50Z")

</div>

No, That does not seem to help, but I have noticed that after you ```  
databaseManager.getSQLParameters(foundset)

```the

Fri May 01 20:11:52 EST 2009 

2009-05-01 20:11:52.028

is this correct ?
```

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [May 8, 2009, 12:47pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/14 "2009-05-08T12:47:06Z")

</div>

I’m pretty sure if you drop the milliseconds off it will work. So:

````auto
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>
````

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [May 8, 2009, 12:59pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/15 "2009-05-08T12:59:20Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 1:05pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/16 "2009-05-08T13:05:47Z")

</div>

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??

```auto
//Loop through array and replace question marks by values
   for (var i=0; i < _args.length; i++) {
      switch(typeof _args[i]) {

```

regards

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [May 8, 2009, 1:07pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/17 "2009-05-08T13:07:09Z")

</div>

Could have to do with version and/or engine type? There is an outstanding millisecond bug:

[http://bugs.mysql.com/bug.php?id=8523](http://bugs.mysql.com/bug.php?id=8523)

---

<div class="post-metadata">

**Author:** ![prj311](https://avatars.discourse-cdn.com/v4/letter/p/85f322/32.png) [@prj311](https://forum.servoy.com/u/prj311)\
**Post date:** [May 8, 2009, 1:16pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/18 "2009-05-08T13:16:46Z")

</div>

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???_

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [May 8, 2009, 1:22pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/19 "2009-05-08T13:22:56Z")

</div>

No, not a mysql issue.

Are you sure it’s stopping on the “typeof” line?

I’m noticing the method will choke on:

```auto
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).

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [May 8, 2009, 1:28pm UTC](https://forum.servoy.com/t/combining-sql-and-params/10623/20 "2009-05-08T13:28:09Z")

</div>

> 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)* *`_

[Next page](https://forum.servoy.com/t/combining-sql-and-params/10623.md?page=2)