SELECT statement executing for only the foundset

Classic Servoy
1

Hello All

I need to find names beeing the same for the first n characters. I got that code with the example of Maarten, which works as I wish, but unfortunatly on all records in the table. How can I limit that SELECT statement to the actual foundset? Any hint is appreciated.

Here is my code:

// Ask for how many characters (from left) in the attribute name in table WAYPOINTS to compare
var nameToCompareLength = plugins.dialogs.showInputDialog(‘Waypoint to Compare’,‘Length of waypoint name to compare?’, ‘10’);

// Query to get the number of equal names for the first n characters
var query =
“SELECT id” +
" FROM waypoints" +
" WHERE SUBSTRING(name,1,“+nameToCompareLength+”) IN" +
“(SELECT SUBSTRING(name,1,”+nameToCompareLength+“) FROM waypoints GROUP BY SUBSTRING(name,1,”+nameToCompareLength+“)” +
" HAVING count (name) > 1 )";

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000)
controller.loadRecords(dataset);

Best regards, Robert

2

Robert Huber:
How can I limit that SELECT statement to the actual foundset? Any hint is appreciated.

Get all the PKs of the foundset in an array and then add a WHERE condition to the select statement like “WHERE id IN …”. Look at getDataSetByQuery for sample code.

3

Hello Nicola

Thanks for the hint, up to now I did not find in this context useful example code, but I am going to try to find a solution.

Best regards, Robert

ngervasi:

Robert Huber:
How can I limit that SELECT statement to the actual foundset? Any hint is appreciated.

Get all the PKs of the foundset in an array and then add a WHERE condition to the select statement like “WHERE id IN …”. Look at getDataSetByQuery for sample code.

4

Hi All

I implemented the following code and it gives me an error because the dataset ist empty (error below, the poblem is the SELECT statement which doesn’t work, see red font parts):

===
TypeError: java.sql.SQLException: JZ0SB: Parameter index out of range: 2. JZ0SB: Parameter index out of range: 2. is not a function. (cambridgeExport, line 39)

=== code not working ===
…
if ( controller.getMaxRecordIndex() > 0 )
{
var args = new Array();
var count = controller.getMaxRecordIndex()
for ( var i = 1 ; i <= count ; i++ )
{
controller.setSelectedIndex(i);
args[i-1] = id;
}
}

var query =
‘SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)’ +
’ FROM waypoints’ +
’ WHERE id IN ?’ +
’ ORDER BY name’;

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

//var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(‘,’,‘\r\n’,‘’,false); // column separator ‘,’, row separator CR and LF (DOS), no value delimiter, no column names
…

But when I have i program it (for testing reasons) with only one id as a result (for loop is executed only once) and using id = ? in the WHERE clause it works. Seems to look like a problem wit the WHERE id IN ? clause. Has anyone a solution for that?

=== code working (but not useful) ===
…
if ( controller.getMaxRecordIndex() > 0 )
{
var args = new Array();
var count = controller.getMaxRecordIndex()
for ( var i = 1 ; i <= 1 ; i++ )
{
controller.setSelectedIndex(i);
args[i-1] = id;
}
}

var query =
‘SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)’ +
’ FROM waypoints’ +
’ WHERE id = ?’ +
’ ORDER BY name’;

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

//var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(‘,’,‘\r\n’,‘’,false); // column separator ‘,’, row separator CR and LF (DOS), no value delimiter, no column names
…

Best regards, Robert

5

I recall when having had a similar problem that this is mainly due to the fact that there is no clear spec for this type of situations in neither the JDBC nor the SQL-standard. Some JDBC drivers do allow it, others don’t. One solution would be to dynamically add OR’s to your sql statement, or even simpler to compute the IN bit of your statement.

6

Hello Jan

Nice to hear from you again so early after Christmas .-) Hope you had some relaxing Christmas days!

jaleman:
I recall when having had a similar problem that this is mainly due to the fact that there is no clear spec for this type of situations in neither the JDBC nor the SQL-standard. Some JDBC drivers do allow it, others don’t.

I use the standard installation with iAnywhere, so this JDBC driver seems to NOT support the WHERE x IN y clause. Is there another JDBC driver for the standard installation I could try? Or is there even a better/easier way to access and in my case export a foundset as the way I did it?

jaleman:
One solution would be to dynamically add OR’s to your sql statement, or even simpler to compute the IN bit of your statement.

As I don’t know exactly what you mean by … to compute the IN bit … could you point me to an example?

Thanks in advance, Robert

7

iAnywhere does not support it at all as far as I know. Here is how you compute the in bit:

var query = 
'SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)' 
query += ' FROM waypoints' + 
query += ' WHERE id IN (' + 
for ( var i = 1 ; i <= controller.getMaxRecordIndex() ; i++ ) 
{ 
	controller.setSelectedIndex(i); 
	query += id + ','; 
} 
//at the end of the loop you will have one , too much, strip it out
query=query.substring(query,0,query.length-1)
//add closing parenthesis
query += ')'
query +=' ORDER BY name'; 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);

as you can see you don’t use a prepared statement anymore but a complete computed SQL statement.
NOTE 1: Before you run the query you might want to add an if to see if there are any records at all in the current controller.
NOTE 2: I assumed id is a number, if it is a string add ’ where necessary

8

Hello Jan

Thanks for the code. I see now what you mean by computeing the IN part. Unfortunatly, I get a syntax error with your code on the (beginning) of the for ( var i =1 …) loop, and I can’t figure out how to embed the for loop correctly. Have you got any idea?

Thanks again, Robert

PS: Yes, the id is a number (artificial key).

jaleman:
iAnywhere does not support it at all as far as I know. Here is how you compute the in bit:

var query =

‘SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)’
query += ’ FROM waypoints’ +
query += ’ WHERE id IN (’ +
for ( var i = 1 ; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.setSelectedIndex(i);
query += id + ‘,’;
}
//at the end of the loop you will have one , too much, strip it out
query=query.substring(query,0,query.length-1)
//add closing parenthesis
query += ‘)’
query +=’ ORDER BY name’;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);



as you can see you don't use a prepared statement anymore but a complete computed SQL statement. 
NOTE 1: Before you run the query you might want to add an if to see if there are any records at all in the current controller.
NOTE 2: I assumed id is a number, if it is a string add ' where necessary
9

my mistake, try removing the + at the end of the line above it.

10

Hi Jan

I tried that already, but then the dataset is empty (again, as with the .. IN … clause) and on the next statement the error: TypeError: null-value is not a function, hmmm.

Have you got another idea I could try?

Best regards, Robert

jaleman:
my mistake, try removing the + at the end of the line above it.

11

turn on debugger and step through the method watching the contents of each variable closely.

12

Hello Robert,

I can’t really see what the problem is, but you could use a shortcut to Jan’s suggestion:

var idArray = databaseManager.getFoundSetDataProviderAsArray(foundset, 'id');
var query =
'SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)'
query += ' FROM waypoints' +
query += ' WHERE id IN (' + idArray.join(', ') + ')'
query +=' ORDER BY name';
application.output(query);
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);

This avoids the loop and having to take care of the extra comma. To narrow your problem you should look at the output and see if that SQL statement is what you need in your situation…

13

Maybe we could use this thread to reactivate a feature request of mine, that has been forgotten somehow:

While I have managed to figure out the prepared statement myself in a plugin, I was not able to get hold of the arguments of that statement. Since we use SQL for our searches to overcome some shortcomings in the area of joining over schemas, we are currently unable to offer a search-in-search, because we don’t know how the current foundset has been created.

It’d be great if Servoy could take another look at that for 3.0!

Thanks
Patrick

14

patrick:
Maybe we could use this thread to reactivate a feature request of mine, that has been forgotten somehow:

databaseManager.getFoundSetQuery(foundset) - Classic Servoy - Servoy Community

It’d be great if Servoy could take another look at that for 3.0!

Thanks
Patrick

Is on the todo for 3.0
*NOTE: as usually this is not a 100% guarantee but in general this means it will get in

15

Hi Jan

I have it always on, but now I finally look at the right expression :-) The problem is the query=query.substring(query,0,query.length-1); line because the query get’s deleted with that statement. I did not find in any docu this substring command/property. But with the stringLeft it works.

jaleman:
turn on debugger and step through the method watching the contents of each variable closely.

It works now with the following code:

===
var query =
‘SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)’ +
’ FROM waypoints’ +
’ WHERE id IN (’
for ( var i = 1 ; i <= controller.getMaxRecordIndex() ; i++ )
{
controller.setSelectedIndex(i);
query += id + ‘,’;
}

query = utils.stringLeft(query, query.length-1) // at the end of the loop there is one , too much (after last id), to be stripped it out
//add closing parenthesis
query += ‘)’ +
’ ORDER BY name’;

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);

//var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
var csv = dataset.getAsText(‘,’,‘\r\n’,‘’,false); // column separator ‘,’, row separator CR and LF (DOS), no value delimiter, no column names

Best regards and thanks a lot, Robert

16

substring is a standard javascript function on the string object. I looks like I used the wrong syntax. Something like:

query=query.substring(query,0,query.length-1);

should work much better.

this is way any examples I provide should never be copy - pasted but only used for inspiration :wink:

17

Hello Patrick

I like your suggestion very much and it works! It’s very elegant! Where did you get the .join property from? Now as I know from you I found in the whole Developer Reference guide one example with that. Is there somewhere more documentation or is it learned knowledge by doing it?

The problem was the substring property.

patrick:
Hello Robert,

I can’t really see what the problem is, but you could use a shortcut to Jan’s suggestion:

var idArray = databaseManager.getFoundSetDataProviderAsArray(foundset, 'id');

var query =
‘SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16)’
query += ’ FROM waypoints’ +
query += ’ WHERE id IN (’ + idArray.join(‘, ‘) + ‘)’
query +=’ ORDER BY name’;
application.output(query);
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);




This avoids the loop and having to take care of the extra comma. To narrow your problem you should look at the output and see if that SQL statement is what you need in your situation...

As already said, it works like a charm (without the + after waypoints of course .-)

Best regards and thanks, Robert

18

You could even write it in one line of code:

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16) FROM waypoints WHERE id IN (' + databaseManager.getFoundSetDataProviderAsArray(foundset, 'id').join(', ') + ') ORDER BY name' , null, maxReturnedRows)

Make sure to buy a REALLY wide screen though

19

Hi Jan

Thanks for the tip, I learned a lot today!
I do not want to write code in one line, I still think it should be as readable as possible, and yes, I did quite a lot of C programming and there are also guys who like to write it as short as possible. That’s not my goal :-)

Thanks a lot for your help, I appreciate it very much! Robert

jaleman:
You could even write it in one line of code:

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), 'SELECT ROW_NUMBER () OVER (ORDER BY name ASC), id, latitude, longitude, elevation, attributes_trimmed, SUBSTR(name,1,16), SUBSTR(remark,1,16) FROM waypoints WHERE id IN (' + databaseManager.getFoundSetDataProviderAsArray(foundset, 'id').join(', ') + ') ORDER BY name' , null, maxReturnedRows)

Make sure to buy a REALLY wide screen though