Null Values are not getting substituted while calling a proc from the SQL datasource using custom SQL tag.

[Isomorphic]

Hi,

We are trying to call a proc using the SQL Datasource Custom SQL tag.
The parameters that have NULL value in the record do not get substituted in the proc call giving the below error :

java.sql.SQLSyntaxErrorException: (conn=3194936) Unknown table '$values' in field list at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153) at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274) at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363) at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:612) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

Below is the call as printed in the logs :

CALL PROC_NAME(3369,'TPPPPP','TPPPPP',$values.param1,$values.param2,'false',
-1005,$values.param5,2,$values.param3,0,$values.param4,@`_OutputId`,
@message, @`projectId`)

Seems the values were not replaced for the null parameters.

Parameters as passed in the request :

data:{
xxx:"TPPPPP",
yyy:"TPPPPP",
zzz:3369,
aaa:2,
para1m:null,
bbb:0,
para2:null,
para3:null,
ccc:-1005,
para4:null,
ddd:false,
param5:null
},


Please suggest.

"originally posted by devashish.kaushik "

[Isomorphic]

Hi devashish.kaushik,

Converting a null value to the String "null" might be correct in your case, but certainly not everywhere.
See here for options: https://cwiki.apache.org/confluence/...heckingForNull
I personally use the https://cwiki.apache.org/confluence/...OCITY/NullTool mentioned there and inject it via request.addToTemplateContext("null", new NullTool()) in my SQLDataSource-subclass.


Best regards
Blama

"originally posted by Blama "