Coercing parameters for databaseManager.getDataSetByQuery

Classic Servoy
1

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

2

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…

3

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.

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

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.

6

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 :)

7

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)

8

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…