Variables not getting substitued when I dont supply value to them

I have an execSQL that has statement like insert into TB (field1, field2) values (’%document/firstname%’,’%document/lastname%’)

When I dont enter value to last name (and enter first name as ‘test’)for the document in pipeline-in, the insert gets translated as

insert into TB (field1,field2) values (‘test’,’%document/lastname%’);
causing a data truncation error as the column in table cannot accomodate the raw %document/lastname% literal!!

I would have expected a ‘’ in that place instead of ‘%document/lastname’

Strange enough, when I enter value for lastname, it substitues okay adn insert succeeds,
Advise please,

For those variables/fields where I dont enter any value in the pipeline-in, I would expect it to

Hi KS,

  Place  cursor press space bar at runtime in lastName field(when ever at run time) then 

it will work if you call nvl function in execSQL service.


Thanks But I am calling this flow service from a WebPage, and the values are supplied from the form fields!!


You should not use variable substitutions in execSQL statments. Use a map step to map document/firstname and document/lastname to variables called firstname and lastname. In the next step invoke execSQL and set the $dbSQL to

insert into TB (field1, field2) values (?, ?)

Also set the input list $dbParamNames to firstname,lastname. And then set the input list $dbParamTypes to VARCHAR, VARCHAR.

This will fix the problem with nulls.

Thanks Rupinder but for numeric values if I dont enter anything I get this??
java.lang.NumberFormatException: Zero length BigDecimal


Never mind… I de-checked the ‘overwrite pipeline varuiable’ box for the numeric variables and it worked…