Invalid Parameter in getDataSetByQuery??

Classic Servoy
1

Its pretty obviousy a formatting problem here but I’m a bit snow blind with it.

This code fails with the Error Invalid parameter index 2.

Regardless of the number of entries inthe array?? Tried this will lots of different formattings but I still get the error on the getDataSetByQuery.

var recordsToView = foundset.getSelectedIndexes()
var args = new Array()
for(var i=0; i<=recordsToView.length-1; i++) //length - 1 removed
{
  foundset.setSelectedIndex(recordsToView[i])
  args[i] = foundset.fc_id
}
 application.output('New Entry ;' +recordsToView+ ' Args ;'+ args + ' Loop Count ;' + i) 
 application.output('Args 0 :' +args[0]+ " ; " + 'Args 1 :' +args[1])

 var sqlFabric = "SELECT * FROM fabric_condition WHERE fc_id = ?" 
 var fabricDataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),sqlFabric,args,10)

The data in the array is correct and it does not appear to have any empty or undefined entries!
Is this error related to the SQL Criteria or the Array Index??

Not sure what I have missed here but any feedback will be welcomed.

2

Hi Ian,

your SQL is:

var sqlFabric = "SELECT * FROM fabric_condition WHERE fc_id = ?"

So it will only accept 1 argument.

Based on your foundset the args-array can contain ‘any’ number of arguments.
This is where things go wrong.

2 things to be altered:

  1. insert the number of questionmarks matching the length of your array:
var _argString = args.map(function() {return '?'}).join(',');
  1. alter the sql:
var sqlFabric = "SELECT * FROM fabric_condition WHERE fc_id IN (" + _argString + ")"

This should solve your problem.
1 thing to keep in mind: the number of accepted arguments depends on you SQL server.
So: depending on this limit, big foundsets can give you some more trouble…

Hope this helps.

3

Wow Marc - exactly what I needed - though I had not thought of the map and function! Good one!

Mean time I’d built a very inelegant string with concatenating the array contents and OR’s, it works but not really very sophisticated!

Cheers

Ian