LoadRecords with Query and Parameter question

Classic Servoy
1

I would like to add to my Query an array with the in operator.
My problem is that I don’t know how to pass the array _posnr in to the _params array for the Query that it works.

In the SQL it would look like this.

select *
from my_table 
where (jahr > 2011 or (jahr = 2011 and monat >= 2)) and (jahr < 2011 or (jahr = 2011 and monat <= 2))
		and posnr in ('510','511','500','545')

In Servoy something like that:

var _fs = databaseManager.getFoundSet(globals.nav_db_framework, "my_table");
var _query = "select uuid from my_table \
	where (jahr > ? or (jahr = ? and monat >= ?)) and (jahr < ? or (jahr = ? and monat <= ?))\
		and posnr in ?"
var _params = new Array(_vonJahr, _vonJahr, _vonMonat, _bisJahr, _bisJahr, _bisMonat, _posnr);
_fs.loadRecords(_query, _params);

Right now my _params looks like this:

_params = [2011.0,2011.0,2.0,2011.0,2011.0,2.0,('510','511','500','545')]

But it doesn’t works.

2

Try to pass the INs as a string, it should work:

_params = [2011.0,2011.0,2.0,2011.0,2011.0,2.0,"('510','511','500','545')"]
3

Thanks for your suggestion.
But it doesn’t work, I get the following error.

Conversion failed when converting the nvarchar value ‘(‘511’,’ 510’,’ 500’,’ 545’)’ to data type int.
Wrapped com.servoy.j2db.dataprocessing.DataException: Conversion failed when converting the nvarchar value ‘(‘511’,’ 510’,’ 500’,’ 545’)’ to data type int.

4

is “posnr” an INT?
Then you need to pass it like this:

_params = [2011.0,2011.0,2.0,2011.0,2011.0,2.0,"(510,511,500,545)"]
5

If the above does not work I’m afraid you’ll have to do like this:

var _query = "select uuid from my_table \
   where (jahr > ? or (jahr = ? and monat >= ?)) and (jahr < ? or (jahr = ? and monat <= ?))\
      and posnr in (?,?,?,?)"

and:

_params = [2011.0,2011.0,2.0,2011.0,2011.0,2.0,510,511,500,545]

It’s a bit of an hassle to build the params array like this but I don’t see any other option.

6

My problem is that I don’t know how many “?marks” I have to add.
But I found another way.
I add a Foundset Filter param with an IN clouse and the array of my posnr.
This works fine for me.

var _fs = databaseManager.getFoundSet(globals.nav_db_framework, "my_table");
_fs.addFoundSetFilterParam('posnr','in',_posnr, 'posnrFilter')
var _query = "select uuid from my_table \
	where (jahr > ? or (jahr = ? and monat >= ?)) and (jahr < ? or (jahr = ? and monat <= ?))"
var _params = new Array(_vonJahr, _vonJahr, _vonMonat, _bisJahr, _bisJahr, _bisMonat);
_fs.loadRecords(_query, _params);

Thanks for four help

7

Just for the sake of completeness and for future viewers of this thread, this is the correct way to handle it:

var _query = "SELECT DISTINCT magpro \
					FROM nprevd1l \
					WHERE codpre = ? AND magpro NOT IN (SELECT magpro FROM notacc1l WHERE codpre = ?) \
					ORDER BY magpro ASC"
	var _ds = databaseManager.getDataSetByQuery('lfspa',_query,[codpre,codpre],-1)
        // prepare a string with the correct number of ?, ex: (?,?,?,?,?)
	var _inClause = '('
	for (var i = 0; i < _ds.getColumnAsArray(1).length; i++) {
		_inClause += '?,'	 
	}
	_inClause = utils.stringLeft(_inClause,_inClause.length-1) // strip the last comma
	_inClause += ')'
	
        var _sql = "SELECT DISTINCT nprevd1l.cdparp \
		FROM nprevd1l JOIN notarp1l \
		ON nprevd1l.cdparp = notarp1l.pcdpar \
		WHERE nprevd1l.codpre = ? AND notarp1l.catvar = ? AND notarp1l.dcdpar = ? AND nprevd1l.magpro IN " + _inClause // append the IN clause string
	
	var _regulars = databaseManager.getDataSetByQuery('lfspa',_sql,[codpre,'A',''].concat(_ds.getColumnAsArray(1)),-1) // concatenate the params array with the INs values array
8

There is an easier way to do this.
Although I can’t find it anymore I believe it was Paul Bakker who posted this (a long time ago) on this forum.

 var _aParam = [value1, value2, value3],
    _sQuery = "SELECT id FROM table WHERE id IN (" + _aParam.map(function() {return "?"}).join(",") + ")"