Can SQL queries include variables?

Classic Servoy
1

Is this possible?

var a1target = a1[0];
var maxReturnedRows = 10;
var query = 'select catitemid from catitem where catkey = a1target;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
2

Try

var a1target = a1[0];
var maxReturnedRows = 10;
var query = 'select catitemid from catitem where catkey = ' + a1target;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
3

Argh!! It’s telling me it can’t find the column ‘L1C1’!

var a1 = new Array(3);
a1[0] = 'L1C1';
a1[1] = 'L1C2';
a1[2] = 'L1C2L2C1';
var a1target = a1[0];
var maxReturedRows = 10; 
var query = 'select catitemid from catitem where catkey = ' + a1target;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
controller.loadRecords(dataset);
4

Morley:
Argh!! It’s telling me it can’t find the column ‘L1C1’!

var a1 = new Array(3);

a1[0] = ‘L1C1’;
a1[1] = ‘L1C2’;
a1[2] = ‘L1C2L2C1’;
var a1target = a1[0];
var maxReturedRows = 10;
var query = 'select catitemid from catitem where catkey = ’ + a1target;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
controller.loadRecords(dataset);

In your query you use L1C1 as a column value, not a columnname.
You are selecting any value from the column catitemid from the table catitem where the column catkey contains the string L1C1.
Is catkey a (var)char? Does any record indeed contain L1C1 ?

5

Morely,

you have a string concatenating problem. Your database is absolutely right in telling you that there is no such column. You are doing

select catitemid from catitem where catkey = L1C1

if there is no column L1C1 you have a problem

what you want is

select catitemid from catitem where catkey = 'L1C1'

and you have to write that for example as

var query = “select catitemid from catitem where catkey = '” + a1target + “'”;

Instead of using " you can also use the escape character. I think I told you exactly this story before…

6

To overcome fromatting issues in SQL use the arguments array,like:

var array = new Array(3); 
array[0] = "L1C1"; 
array[1] = new Date(); 

var maxReturedRows = 10; 
var query = "select catitemid from catitem where catkey = ? and date_col = ?" 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, array, maxReturedRows);