SQL "IN" runtime exception

Classic Servoy
1

What is wrong with my code? userid column definition is integer, allownull

var tableSQL = "SELECT * FROM address WHERE userid IN(?,?,?)"
var arguments=[null, 11,12]
var tableDataSet = databaseManager.getDataSetByQuery(dbServerName, tableSQL, arguments,-1)

exception… “Error during evaluation:Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: No value specified for parameter 3.”

2

You can’t use ‘null’ as an argument.
This is seen as ‘nothing at all’ and the parameters just move one place (that’s why your error is referring to 3)

Instead, if you need to pass an argument choose a extreme value which your sure of it doesn’t exist.

Generally it’s better if you just leave out those arguments as every argument takes time to query (although in this case a minimum :) )

3

Rogel,

Try this

var tableSQL = "SELECT * FROM address WHERE userid is null or userid IN(?,?)"
var arguments=[11,12]
var tableDataSet = databaseManager.getDataSetByQuery(dbServerName, tableSQL, arguments,-1)

Rob

4

rgansevles:
Rogel,

Try this

var tableSQL = "SELECT * FROM address WHERE userid is null or userid IN(?,?)"

var arguments=[11,12]
var tableDataSet = databaseManager.getDataSetByQuery(dbServerName, tableSQL, arguments,-1)




Rob

Thanks Rob,

I was just wondering because if I change it to something like…

var tableSQL = "SELECT * FROM address WHERE userid =? or userid=? or userid = ?"
var arguments=[11,12, null]

then the query will work. i guess? :)

5

Hi Rogel,

Like Marc and Rob already explained you can’t pass a null as an argument to a prepared statement like that.
Also in SQL you can’t use operators with a NULL since you can only use an operator with a value. NULL is not a value or more specific it’s the absence of a value.
So userid=NULL won’t work. The proper syntax is userid IS NULL.

Hope this helps.

6

ROCLASI:
Hi Rogel,

Like Marc and Rob already explained you can’t pass a null as an argument to a prepared statement like that.
Also in SQL you can’t use operators with a NULL since you can only use an operator with a value. NULL is not a value or more specific it’s the absence of a value.
So userid=NULL won’t work. The proper syntax is userid IS NULL.

Hope this helps.

Thanks Robert.