db2 for i : passing a varchar containing comma separated string in an sql procedure in in clause

Sara D

db2 for i : passing a varchar containing comma separated string in an sql procedure in in clause

i have a string containing comma separated strings in java.. which i m passing to a sql procedure being called in java ... here is example of java string:

    String codeString = "'232/232','34fd/34//'";

code in sql db2 for i:

    create procedure history (in id varchar (3), in code varchar (2000))
   ......
   ......
   begin
   insert into table1
   select date_from, date_to, code
   from table2 
   where table2.serial= id
   and table2.status not in (code); 
   end

this sql procedure is inserting same string in table1.code but it is not excluding table2.status in in clause..

value being inserted in table1.code is '232/232','34fd/34//' (including all single quotes and commas)

I just wonder that when i insert code value in table1..it works fine even no issue with any single quote,comma.. but when i pas it to not in clause its creating problem

James Campbell

db2 for i : passing a varchar containing comma separated string in an sql procedure in in clause
(in response to Sara D)
Problem? What problem? What is not happening as you expect?

Are you expecting
> and table2.status not in (
> code);
to be treated as
and table2.status not in (
'232/232',
'34fd/34//'
)
?

Doesn't work like that. It is treating the entire string "'232/232','34fd/34//'" as a single value.

If you want the component parts of code to be treated as separate values you will have to
either

- use dynamic SQL to build the insert statement. Beware of SQL injection if you do so
- break the components into separate variables - which are listed in the NOT IN list.

James Campbell


On 12 May 2018 at 18:53, Sara D wrote:

>
> i have a string containing comma separated strings in java.. which i m passing to a sql procedure
> being called in java ... here is example of java string:
>
> String codeString = "'232/232','34fd/34//'";
>
> code in sql db2 for i:
>
> create procedure history (in id varchar (3), in code varchar (2000))
> ......
> ......
> begin
> insert into table1
> select date_from, date_to, code
> from table2
> where table2.serial= id
> and table2.status not in (
> code);
> end
>
> this sql procedure is inserting same string in table1.code but it is not excluding table2.status in in
> clause..
> value being inserted in table1.code is '232/232','34fd/34//' (including all single quotes and
> commas)
> I just wonder that when i insert code value in table1..it works fine even no issue with any single
> quote,comma.. but when i pas it to not in clause its creating problem
>


---
This email has been checked for viruses by AVG.
http://www.avg.com