controller.loadRecords with a query

Classic Servoy
1

In 3.5.5 you are no longer able to controller.loadRecords() with a query if the query does not contain a WHERE clause.

For example

“SELECT usr_job.jodid FROM usr_job ORDER BY usr_job.jobid”

will not work, whereas

“SELECT usr_job.jodid FROM usr_job WHERE 1=1 ORDER BY usr_job.jobid”

will work.

2

Hi,

This is nothing new, servoy < 3.5 you always needed a where-keyword.
In servoy 3.5, you can try without the order by, in that case you have less restrictions on your custom sql.

Rob

3

Hi Rob,

If I understand James correctly he wants to use a query without a WHERE clause. The ORDER BY is not in question.
Also he wants to use this approach with the latest 3.5.5 but can’t for some reason.

I can reproduce the problem by using this query format:```
SELECT usr_job.jodid FROM usr_job ORDER BY usr_job.jobid

However when I use the following query format it does work```
SELECT jodid FROM usr_job ORDER BY jobid

I tested it on 3.5.3, 3.5.4 and 3.5.5. They all show the same issue.
James’s valid SQL doesn’t work with the loadRecords() function.

4

Here are some more observations.
Using a semicolon in your query also have different effects.

SELECT jodid FROM usr_job ORDER BY jobid;

Works fine.

SELECT jodid FROM usr_job;

Gives an error ‘syntax error at or near “;”’
When removing the semicolon from the query it does work.

SELECT jodid FROM usr_job

Ending a SQL statement with a semicolon is perfectly valid SQL.

5

From the developer reference guide:

NOTE: As of Servoy 3.5 (and higher), any standard SQL statement can be used for
custom queries if ORDER BY is not included; otherwise qualified SQL SELECT
statement that:

  • Must start with ‘SELECT’.
  • Must contain ‘FROM’ and ‘ORDER BY’ keywords.
  • Must at least select from the specified forms’s table.
  • Can contain ‘?’ which are replaced with values from the array
    supplied to parameters function argument.
  • Cannot contain ‘GROUP BY’ or ‘HAVING’.
  • All columns must be fully qualified like ‘orders.order_id’.

There are 2 ways for servoy to use the custom sql.

If you meet all requirements listed above (which are the pre-3.5 requirements for custom sql statements), servoy will use that sql and add its own stuff (added conditions, joins, order by clauses, etc).

If you use any other sql, servoy uses subselect to add its own stuff giving the developer more freedom for the sql.
Unfortunately, in that case, the order-by clause is lost.

So for the examples:

SELECT usr_job.jodid FROM usr_job ORDER BY usr_job.jobid

is treated as a pre-3.5 query and requires a where-condition.
I agree that requiring a where-clause is strange, I will have a look at that.

SELECT jodid FROM usr_job ORDER BY jobid

is treated as a new type query and is executed as ```
select pk from tab where pk in (SELECT jodid FROM usr_job)


Rob
6

Some more observations:

SELECT jobid FROM usr_job ORDER BY jobid

will fail since there is a space in front of the word ‘SELECT’.

(SELECT jobid FROM usr_job) UNION (someotherquery...)

will fail since there is a ‘(’ in front of the word ‘SELECT’…

When I upgraded to 3.5.x I had to take out all qualifiers from my queries to avoid them being sent through the old SQL engine otherwise things like

loadRecords(query,args) would fail.

This was confusing, but I understand now…

7

rgansevles:
This is nothing new, servoy < 3.5 you always needed a where-keyword.
In servoy 3.5, you can try without the order by, in that case you have less restrictions on your custom sql.

This may be nothing new to the 3.5.x series, I’ve just noticed it recently though (having just gotten into 3.5.x). I do know however this was not a restriction in 2.2.x, we have a handful of methods that use a query with no WHERE clause (namely data export methods) to load records.

Obviously I can hack up these methods with a statement that checks for the existence of a WHERE in the provided SQL and inserts a simple “WHERE 1=1” when needed, but this isn’t the most optimal solution. If a WHERE clause is going to be a required segment of SQL for loading records I might recommend putting this in the sample code instructions (being that it isn’t there now).

8

In servoy 3.5.6 custom queries without where-clause will be allowed.

Rob