# Coercing parameters for databaseManager.getDataSetByQuery

**URL:** <https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995>\
**Category:** Classic Servoy\
**Created:** [April 9, 2008, 9:19pm UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995 "2008-04-09T21:19:11Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![richh](https://avatars.discourse-cdn.com/v4/letter/r/73ab20/32.png) [@richh](https://forum.servoy.com/u/richh)\
**Post date:** [April 9, 2008, 9:19pm UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/1 "2008-04-09T21:19:11Z")

</div>

Hi all: I am still fairly new to Servoy and running into as yet unexplored territory. Servoy Talk generally gives me an answer: congrats to all who contribute. But this one has me really stumped.

I have the following line in a method:  
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), sqlstring, arguments, 9999)

Notice that I have two variables: ‘sqlstring’ and ‘arguments’.

The variable sqlstring in a test example is:  
select id\_person from people where present\_location\_country like ? or surname like ? or first\_name like ? order by surname asc, first\_name asc

I have a problem with setting the variable ‘arguments’ correctly. Reading Servoy Talk and the Servoy Reference Guide I can see there are issues over arrays and objects.

My example has arguments in an array of 3 elements: [‘%Marten%’,‘%Marten%’,‘%Marten%’]. In essence, I am looking for records containing the string ‘Marten’ in one of a number of fields.

CASE 1: Forget about setting arguments and specify the array directly:  
var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), sqlstring, [‘%Marten%’,‘%Marten%’,‘%Marten%’], 9999)  
Result: OK – a correct foundset of 9 records in my sample database of 45,000 records.

CASE 2: Set var arguments = [‘%Marten%’,‘%Marten%’,‘%Marten%’] before executing the original vDataset declaration.  
Result: OK – same foundset.

CASE 3: Declare arguments = new Array. Set argument[0] = ‘%Marten%’ and so on for [1] and [2].  
Result: A foundset of 0 records.

In my scenario I need to set the arguments programmatically (as in case 3). Fixed arguments as it cases 1 and 2 will not do. However, I have yet to come up with a way of doing this that works.

Does anyone have any thoughts on how this might be done?

Richard Hinder  
London

Development environment:  
Servoy Developer  
Version 3.5.5-build 518  
Java version 10.0-b19 (Windows XP)  
Sybase for repository  
PostgreSQL 8.3 for sample data

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [April 9, 2008, 9:23pm UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/2 "2008-04-09T21:23:49Z")

</div>

> CASE 3: Declare arguments = new Array. Set argument[0] = ‘%Marten%’ and so on for [1] and [2].  
> Result: A foundset of 0 records.

Please share your code so we can see what it is that you exactly do…

---

<div class="post-metadata">

**Author:** ![richh](https://avatars.discourse-cdn.com/v4/letter/r/73ab20/32.png) [@richh](https://forum.servoy.com/u/richh)\
**Post date:** [April 9, 2008, 9:56pm UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/3 "2008-04-09T21:56:37Z")

</div>

Marcel

We are talking here about Case 3:

args = new Array  
args[0] = ‘%Marten%’  
args[1] = ‘%Marten%’  
args[2] = ‘%Marten%’

var vDataset = databaseManager.getDataSetByQuery(controller.getServerName(), sqlstring, args, 9999)

This fails with an exception:  
‘java.lang.IllegalArgumentException: Query must start with ‘SELECT’’

Yet the sqlstring does start with ‘select’. And it does work OK in the other cases.

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [April 9, 2008, 9:58pm UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/4 "2008-04-09T21:58:45Z")

</div>

````auto
args = new Array 
```Really this?
Make it new Array();
````

---

<div class="post-metadata">

**Author:** ![richh](https://avatars.discourse-cdn.com/v4/letter/r/73ab20/32.png) [@richh](https://forum.servoy.com/u/richh)\
**Post date:** [April 10, 2008, 9:13am UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/5 "2008-04-10T09:13:02Z")

</div>

You are so right!

I made a transcription error on compiling the example, which failed for the reason you mentioned. I do apologise for this.

With the correct syntax the query in Case 3 runs without error, but returns a foundset of zero records in place of the expected 9 records of cases 1 and 2.

Using application.output() I have traced the cause of the problem.  
The arguments arrays were output as follows:  
Case 2 (Correct): [%Marten%,%Marten%,%Marten%]  
Case 3 (Wrong): [‘%Marten%’,‘%Marten%’,‘%Marten%’]

LESSON: When constructing SQL parameters programmatically, be careful with string delimiters! Check your array using application.output(). Ensure that there are no string delimiters present.

Many thanks Marcel for stimulating me to find this.

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [April 10, 2008, 9:47am UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/6 "2008-04-10T09:47:01Z")

</div>

> LESSON: When constructing SQL parameters programmatically, be careful with string delimiters! Check your array using application.output(). Ensure that there are no string delimiters present.

LESSON: making a mistake is sooooooo easy, finding the mistake is sooooooooooo annoying ![:)]( "Smile")

---

<div class="post-metadata">

**Author:** ![hansayton](https://avatars.discourse-cdn.com/v4/letter/h/f6c823/32.png) [@hansayton](https://forum.servoy.com/u/hansayton)\
**Post date:** [April 11, 2008, 6:50am UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/7 "2008-04-11T06:50:10Z")

</div>

sort of related:  
Query must start with ‘SELECT’’ :  
this restriction was a problem as it prevented all recursive queries and all queries with common table exprssions , as these begin with “with”, not with “select”  
f.e:

“with temp as (  
select bla bla bla from bla bla bla  
)  
select bla, bla  
from temp  
where bla bla  
group by bla bla”

There is a patch I believe for 3.5 now from Servoy that does away with this restriction. Also one of my collegues (Danny Richardson) has written a little plugin “rawds.jar” that gets a dataset by query, that does not demand the query to start with “select”, therefore allows all valid queries to work.

Such common table expressions are especially usefull when having to use M$SQL2005, (instead of f.i. Sybase) as column-correlation names in it are not exposed names, therefore may not appear in where-clauses or group by’s. When selected from such a common table expression within the query they are, and may be “grouped by” or appear in a “where” clause. When a column is a complex calculation or expression this eliminates the need to repeat the whole expression.

Also recursive queries may be useful, these always begin with “with”, as they recursively query the common table expression (until some condition is met) after which you select from that result, may save a lot of loops in your Servoy-code, and shift the work to the backend. (both Sybase 9 and M$SQL2005 support these, using only slightly different syntax)

---

<div class="post-metadata">

**Author:** ![IT2Be](https://avatars.discourse-cdn.com/v4/letter/i/dc4da7/32.png) [@IT2Be](https://forum.servoy.com/u/IT2Be)\
**Post date:** [April 11, 2008, 7:00am UTC](https://forum.servoy.com/t/coercing-parameters-for-databasemanager-getdatasetbyquery/8995/8 "2008-04-11T07:00:52Z")

</div>

> sort of related:  
> Query must start with ‘SELECT’’ :  
> this restriction was a problem as it prevented all recursive queries and all queries with common table exprssions , as these begin with “with”, not with “select”

In this case the query started with Select.  
The issue was that the query should also include a where clause.  
When the clause is absent it will throw the (rather confusing) error about the missing select.  
The reason that the where clause was missing, in this case, was the issue with the input array…