getDataSetByQuery throws exception while Sql doesn't

Questions and answers regarding general SQL and backend databases

getDataSetByQuery throws exception while Sql doesn't

Postby developers10 » Wed Jun 14, 2017 9:15 am

Hi,

Does anyone know why this fails when used as query in the databaseManager.getDataSetByQuery-method? :
Code: Select all
   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 :
Code: Select all
   SELECT TOP 1 article_code FROM article


Thx
developers10
 
Posts: 31
Joined: Thu Feb 23, 2017 5:33 pm

Re: getDataSetByQuery throws exception while Sql doesn't

Postby pbromwell » Wed Jun 14, 2017 2:04 pm

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:

Code: Select all
if object_id('article') is not null


That may be what the driver is complaining about.
pbromwell
 
Posts: 6
Joined: Mon May 19, 2014 7:59 pm

Re: getDataSetByQuery throws exception while Sql doesn't

Postby developers10 » Wed Jun 14, 2017 2:32 pm

Nope, same result.
Then I thought, maybe the getDataSetByQuery fails when the table exists but contains no records so I tried this :
Code: Select all
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.
developers10
 
Posts: 31
Joined: Thu Feb 23, 2017 5:33 pm

Re: getDataSetByQuery throws exception while Sql doesn't

Postby rgansevles » Wed Jun 14, 2017 9:24 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1841
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: getDataSetByQuery throws exception while Sql doesn't

Postby developers10 » Thu Jun 15, 2017 8:37 am

Hi Rob,

That was indeed the case.
it's working now just by adding :
Code: Select all
declare @dummy as int;

at the start of the query-string.
Thx

Chris
developers10
 
Posts: 31
Joined: Thu Feb 23, 2017 5:33 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 2 guests