I'm trying to EXECUTE a prepared statement that has a combination of INTEGER and VARCHAR values and am getting an error. Can you help.. thanks!


PID and PVAL are INTEGERS and PNAME is VARCHAR(24).
I have SET name = 'Warehouse' for example --

SET insertStatement = 'INSERT INTO ' || insertTable
|| ' (PID, PNAME, PVAL)'
|| ' VALUES ('|| CHAR(100) || ', ' || name || ', ' || CHAR(100) || ')';

PREPARE s1 FROM insertStatement;

I get this error :

SQL0206N "Warehouse" is not valid in the context where it is used.
SQLSTATE=42703



Btw, it works just fine when name is an INTEGER type and I execute this --

For example, SET name = 12;

SET insertStatement = 'INSERT INTO ' || insertTable
|| ' (PID, PNAME, PVAL)'
|| ' VALUES ('|| CHAR(100) || ', ' || CHAR(name) || ', ' || CHAR(100) || ')';

PREPARE s1 FROM insertStatement;

Also, the column type of PNAME in the insertTable is VARCHAR(24) in the first case and INTEGER in the second case.

on the host (mainframe),
PNAME would have to be a group item consisting of:
2 bytes of binary providing the length of the varchar - warehouse - 9 in this case
and
a string item to contain the actual value 'warehouse'.

Hi, not sure I understand what you mean.. I think I have a syntax error in the 1st INSERT statement.. but don't know how to fix it.

Update -- the only way I could get it to work is by setting it up this way :

PID and PVAL are INTEGERS and PNAME is VARCHAR(24).
I have SET name = 'Warehouse' for example --

SET insertStatement = 'INSERT INTO ' || insertTable
|| ' (PID, PNAME, PVAL)'
|| ' VALUES ('|| CHAR(100) || ', ? , ' || CHAR(100) || ')';

PREPARE s1 FROM insertStatement;
EXECUTE s2 USING name;


But there must be a way to have 'name' directly in the insertStatement variable.. any thoughts?

This must be a common mistake when using dynamic SQL.

Suppose, the value of insertStatement after SET statement.
If, the value of name was 'Warehouse',
insertStatement would be...
(where <insertTable> is the value of insertTable.)

So, you should include quotation marks around name, like...
SET insertStatement = 'INSERT INTO ' || insertTable
|| ' (PID, PNAME, PVAL)'
|| ' VALUES ('|| CHAR(100) || ', ''' || name || ''', ' || CHAR(100) || ')';

Thank you! Actually I put two extra single quotes on each end ( not double quotes) and it works.. thanks much!