databaseManager.getDataSetByQuery and Servoy 2019.12

Discuss all problems you have with Servoy here. It might help to mention the Servoy version and Operating System version you are using

databaseManager.getDataSetByQuery and Servoy 2019.12

Postby swingman » Wed Mar 11, 2020 4:48 pm

Hi all,

not sure if this is an issue in Servoy or with the driver or whatever;

If you have a query where you have an unnamed column, caused by simply using a constant as a placeholder, databaseManager.getDataSetByQuery may not return the result.
Code: Select all
SELECT person_id, 0 FROM people WHERE person_name ILIKE ?


fails even if there are people records that satisfy the criteria. Changing it to
Code: Select all
SELECT person_id, 0 as zero FROM people WHERE person_name ILIKE ?


works. Both version work when executed outside of Servoy, directly in PostgreSQL 12.
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby rvanderburg » Wed Mar 11, 2020 5:02 pm

is zero not a reserver keyword?
rvanderburg
Site Admin
 
Posts: 78
Joined: Wed May 04, 2011 10:28 am

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby kwpsd » Wed Mar 11, 2020 6:54 pm

Ron...are you inferring that 'zero' is a Servoy reserved keyword? If so, it's not in the Servoy wiki.

https://wiki.servoy.com/display/DOCS/Reserved+keywords

Perhaps, you could elaborate on your response. I could not find it as an SQL keyword either.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby jcompagner » Wed Mar 11, 2020 7:06 pm

what does it return?

getDataSetByQuery() just gives that string to make a PreparedStatement, we don't really touch it its all just the jdbc driver and the database itself.

So how do you test this outside of servoy?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby swingman » Tue Mar 17, 2020 12:45 am

I construct a query in Servoy as a string. Pass it together with an array of parameters to getDataSetByQuery();
When it does not work, I output the query string using application.output() and paste it into DataGrip where it works.

Maybe the problem is that
Code: Select all
SELECT some_field, 0 FROM some_table WHERE some_thing

causes Servoy assume the column is called 0 which may not be allowed because it starts with a number.

Why do I do this kind of thing? Set operations (UNION, UNION ALL, INTERSECT etc) in PostgreSQL combining or slicing data from tables that have little in common.
The data can be used as in-memory data sources... very powerful...

Anyway, the workaround (0 as something) is very simple. I just thought I'd mention it in case someone else hits the same problem.
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby jcompagner » Tue Mar 17, 2020 10:34 am

i just did a very quick test:

Code: Select all
    var ds = databaseManager.getDataSetByQuery("example_data","select node_id, 0 from book_nodes where node_id = ?",[1],100);
    application.output(ds);

    ds = databaseManager.getDataSetByQuery("example_data","select node_id, 0 as zero from book_nodes where node_id = ?",[1],100);
    application.output(ds);


and that is working fine.

Code: Select all
  BufferedDataSet {Columnnames [node_id, ?column?]}
  row_1=[1, 0]
  BufferedDataSet {Columnnames [node_id, zero]}
  row_1=[1, 0]
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: databaseManager.getDataSetByQuery and Servoy 2019.12

Postby swingman » Tue Mar 17, 2020 3:29 pm

Hi Johan, I can confirm your sample code works.

There must have been something else interfering in my query. Sorry for having wasted valuable time on this. :oops:
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London


Return to Discuss possible Issues and Bugs

Who is online

Users browsing this forum: Google [Bot] and 9 guests