SQL problem

Classic Servoy
1

Need your help. This code works, just fine. It presents all the records in the array.

var array = new Array(3);
array[0] = "3005";
array[1] = "3015";
array[2] = "3020";
var length = array.length;
var maxReturedRows = 10;
var query = "select companiesid from companies where companiesid in (?,?,?)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, array, maxReturedRows);
controller.loadRecords(dataset);

I need an efficient way to populate as many question marks separated by commas as in the variable “length”. It won’t always be just three. My grasp of JS isn’t sufficiently robust to grasp a workable solution.

2

Try to solve in a way as in Oracle

select companiesid from companies where companiesid in ( select companiesid from companiesid a) ;

I am not sure. I haven’t tried. Pls try

3

rkrservoy:
Try to solve in a way as in Oracle

select companiesid from companies where companiesid in ( select companiesid from companiesid a) ;

I am not sure. I haven’t tried. Pls try

Sorry, I don’t understand. The method that prompted this thread was recommended by Jan Block and Johan Compagner.

4

To get a string with the correct amount of question marks (that is your question right?) you could use a function.

In Servoy you could use a global method for this.

Lets say we call this method getQuestionmarks.
The code of this method could be like the following:

var theArray = arguments[0]; // the given array
var resultString ='';

for ( var i = 0 ; i < theArray.length ; i++ )
{
	resultString += '?,';
}

return resultString.substr(0, resultString.length-1 ); // return the string minus the last comma

Then in your method where you do your query you use the following syntax:

var array = new Array(3); 
array[0] = "3005"; 
array[1] = "3015"; 
array[2] = "3020"; 
var length = array.length; 
var maxReturedRows = 10; 
var query = 'select companiesid from companies where companiesid in ('+ globals.getQuestionmarks(array) +');'; 
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, array, maxReturedRows); 
controller.loadRecords(dataset);

Hope this helps.

5

If you let me add this: if you this kind of hassle, you can fill in the IN-statement yourself…

6

Hi Morley,

I am probably misunderstanding something here, but if you are hardocding the array elements then don’t you always know how many ‘?’ characters need to go into the SQL query string !?

Cheers
Harry

7

Harry Catharell:
I am probably misunderstanding something here, but if you are hardocding the array elements then don’t you always know how many ‘?’ characters need to go into the SQL query string !?

The hard code was just for testing/learning. Now that I’ve got the routine to work I was looking for a little magic to generate the string of question marks. Bob Ivens’ simple loop is all that’s really required. I can often miss the obvious.

8

And so can I !!!