how to use the SQL 'IN' operator with an array?

Classic Servoy
1

I hope anyone can help me out on this:

I have some values in an array and want to use them in a query, using the ‘IN’ operator.
I’ve been struggling around and ended up converting the array to literal text and add this to my query string:

function getRecordsWorking()
{
var $array = new Array(globals.idA ,globals.idB)
var $param = '(\'' + $array.join('\',\'') + '\')';
var $query = '';
$query += 'SELECT *' + ' ';
$query += 'FROM dummy' + ' ';
$query += 'WHERE k_owner_id IN ' + $param;
var $server = 'myserver';
var $max = -1;

var $dataset = databaseManager.getDataSetByQuery($server, $query, null, $max);
var $countRec = $dataset.getMaxRowIndex();
}

Isn’t there anything like this that can be used???:

function getRecordsNotWorking()
{
var $array = new Array(globals.idA ,globals.idB)

var $query = '';
$query += 'SELECT *' + ' ';
$query += 'FROM dummy' + ' ';
$query += 'WHERE k_owner_id IN (?)';
var $server = 'myserver';
var $max = -1;
var $args = new Array();
$args[0] = $array;

var $dataset = databaseManager.getDataSetByQuery($server, $query, $args, $max);
var $countRec = $dataset.getMaxRowIndex();
}

Maybe another easier syntax than the one I use now???

Thnkx a lot!

2

Best is to supply the values in your array as an arguments array.

But, then you need to insert the proper amount of questionmarks into your SQL statement to match the number of arguments.

Quickest way I found to get a string with the right number of question marks is this:

$array.map(function() {return "?"}).join(",")

Source: http://www.javascriptkit.com/jsref/arrays.shtml
Requires Servoy 4.0.0 or higher

Paul

3

Thnkx Paul! This is definitely shorter /more readable than what I figured out…

4

I dare people to come up with an even shorter notation to get the same thing done :D

Paul

5

pbakker:
I dare people to come up with an even shorter notation to get the same thing done :D

I’m just starting to get my head around some of the JavaScript contortions Servoy 4 allows us to utilize. Thanks for a REALLY cool example.