databaseManager.getDataSetByQuery and Servoy 2019.12

Feedback
1

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.

 SELECT person_id, 0 FROM people WHERE person_name ILIKE ?

fails even if there are people records that satisfy the criteria. Changing it to

 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.

2

is zero not a reserver keyword?

3

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.

4

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?

5

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

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.

6

i just did a very quick test:

	 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.

  BufferedDataSet {Columnnames [node_id, ?column?]} 
  row_1=[1, 0]
  BufferedDataSet {Columnnames [node_id, zero]} 
  row_1=[1, 0]
7

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: