Conditional Value List

Classic Servoy
1

Hello All

I would like to set a conditional states value list depending on the country. The selection of the country in a popup should set the correct states in the following states popup.

Here is my code:

var maxReturnedRows = 100;
var query =
‘SELECT name, id’ +
’ FROM states’ +
’ WHERE country_code = ’ + globals.countryCode + ‘’ +
’ ORDER BY name’;

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
application.setValueListItems(‘statesInCountry’, dataset);

This gives me a java.lang.NullPointerException.

The query without the WHERE clause works, but of course sets the popup with all states in it. Just for testing reasons I tried with the following SELECT statement (WHERE clause), which also works:

‘SELECT name, id’ +
’ FROM states’ +
’ WHERE id IN (6, 7)’ +
’ ORDER BY name’;

Can anyone give me a hint what’s wrong with my WHERE clause?

Thanks in advance, Robert

2

is ‘globals.countryCode’ a string? maybe it needs to be in quotes?

TIP: ALWAYS use the query syntax using the ‘?’ for each parameter and put you params into an array see sample code for databaseManager.getDataSetByQuery. Servoy will take care of any data conversions for you :slight_smile: saves a lot of time.

If this does not solve the problem, try, setting a breakpoint and use the evaluate pane in the debugger to capture the contents of ‘query’. Try executing the query in your favourite SQL front end. If you don’t have one try Aqua Data Studio… works with most DBs.

3

Hi Christian

swingman:
is ‘globals.countryCode’ a string? maybe it needs to be in quotes?

TIP: ALWAYS use the query syntax using the ‘?’ for each parameter and put you params into an array see sample code for databaseManager.getDataSetByQuery. Servoy will take care of any data conversions for you :-) saves a lot of time.

Thanks for the tip! I will look at it. I don’t understand the ‘?’ at the moment and I also don’t undrestand the setValueListItem example code :-(

swingman:
If this does not solve the problem, try, setting a breakpoint and use the evaluate pane in the debugger to capture the contents of ‘query’. Try executing the query in your favourite SQL front end. If you don’t have one try Aqua Data Studio… works with most DBs.

The select name, id from states where country_code = ‘CH’; statement work in my SQL front end (SQL 4X Manager J). If I use select name, id from states where country_code = “CH”; I get the error: ASA Error -143: Column ‘CH’ not found. I am irritated. Up to now I thought one could use either " or ', but doesn’t seem so.

Just before hitting the Submit button, I found a working statement:

var query =
‘SELECT name, id’ +
’ FROM states’ +
’ WHERE country_code = ’ + “'” + globals.countryCode + “'” +
’ ORDER BY name’;

although I don’t exactly why it has to be so!?

Thanks and best regards, Robert

4

Sounds like sybase is not liking your quotes. Servoy already knows which type of quotes to use :slight_smile: Avoid the whole problem this way:

var query = 'SELECT name, id FROM states WHERE country_code = ? ORDER BY name'; 
var args = new Array();
args[0] = globals.countryCode;
var maxReturnedRows = 100; //you may need to change this one...
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows); 
application.setValueListItems('statesInCountry', dataset);
5

Another advantage is that the “?” allows the database to optimize the query for reuse… it will run faster…

6

Hello Christian

Thanks for your code, I like it!

Best regards, Robert

PS: I noticed that when the SQL query returns 0 records found, the value list keeps the previous values. Shouldn’t it empty the value list or is this to be done programmatically?

swingman:
Sounds like sybase is not liking your quotes. Servoy already knows which type of quotes to use :-) Avoid the whole problem this way:

var query = 'SELECT name, id FROM states WHERE country_code = ? ORDER BY name';

var args = new Array();
args[0] = globals.countryCode;
var maxReturnedRows = 100; //you may need to change this one…
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);
application.setValueListItems(‘statesInCountry’, dataset);

7

Hi Robert,

I guess you could create an empty array if the dataset is empty. Something like this may work:

if(dataset.getMaxRowIndex()) {
//do whatever you are doing now
} else {
var args = new Array();
application.setValueListItems(‘statesInCountry’, args);
}

8

Hi Christian

Thanks, your suggestion works!

Thanks and best regards, Robert

swingman:
Hi Robert,

I guess you could create an empty array if the dataset is empty. Something like this may work:

if(dataset.getMaxRowIndex()) {
//do whatever you are doing now
} else {
var args = new Array();
application.setValueListItems(‘statesInCountry’, args);
}