getDataSetByQuery throws exception while Sql doesn't

Classic Servoy
1

Hi,

Does anyone know why this fails when used as query in the databaseManager.getDataSetByQuery-method? :

if not object_id('article') is null SELECT TOP 1 article_code FROM article else select top 1 article_code from article

(I know this check on object_id returns the same result but this is only as a test and even this fails. Also tried with NOT IS NULL instead of NOT … IS NULL)
This query results in one row with one column when executed as Sql statement in any Sql-client program which is correct.
When executed in Servoy, I just get a JavaException.DataException with no additional info.

The table article and field article_code exist and a getDataSetByQuery with this succeeds :

SELECT TOP 1 article_code FROM article

Thx

2

It may be a difference in version between your database client and the driver that Servoy is using to connect to your database.

I believe the proper not null syntax would be:

if object_id('article') is not null

That may be what the driver is complaining about.

3

Nope, same result.
Then I thought, maybe the getDataSetByQuery fails when the table exists but contains no records so I tried this :

if object_id('temp_contracts') is not null SELECT count(*) as cnt FROM temp_contracts else select 0 as cnt

since this will always return one row and one column but sadly enough, same error.

4

10,

Servoy requires custom queries to start with one of the keywords ‘select’, ‘call’, ‘with’ or ‘declare’.

Something went wrong in logging this, that is why you did not get a proper error message, unfortunately.

Rob

5

Hi Rob,

That was indeed the case.
it’s working now just by adding :

declare @dummy as int;

at the start of the query-string.
Thx

Chris