Variables not getting substitued when I dont supply value to them

, ,
1

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,
KS

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

2

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,
SriniK

3

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

KS

4

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.

5

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

Thanks,

6

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