dynamic sql EXEC SQL EXECUTE

[Arti Potnis]

I have some problems with dynamic SQL statements in DB2

in a c file we have the following statements in sequence:-

1)EXEC SQL DECLARE my_stmt STATEMENT ;
2)EXEC SQL PREPARE my_stmt FROM :SQL_STMT;

here the variable SQL_STMT contains " INSERT INTO MY_INS_TABLE (
CUSTOMER_ID
,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT :v1, current date, current
date,NAME from MY_LOAD_TABLE where FSI=:fsi and FCI=:fci)"

3)EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

the SQL_ERROR= -727 and error message is in an encrypted format.

SQLERRM: 2ÿ-418ÿ42610ÿ
SQLERRCODE:-727

Is there any way to decrypt an encrypted message thrown by DB2.
OR is there any known problem using Dynamic SQL Statements (with bind
variables) along with 'EXEC SQL EXECUTE' in DB2.

[Mark A]

"Arti Potnis" <ar******@yahoo.com> wrote in message
news:ec**************************@posting.google.c om...

I have some problems with dynamic SQL statements in DB2

in a c file we have the following statements in sequence:-

1)EXEC SQL DECLARE my_stmt STATEMENT ;
2)EXEC SQL PREPARE my_stmt FROM :SQL_STMT;

here the variable SQL_STMT contains " INSERT INTO MY_INS_TABLE (
CUSTOMER_ID
,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT :v1, current date, current
date,NAME from MY_LOAD_TABLE where FSI=:fsi and FCI=:fci)"

3)EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

the SQL_ERROR= -727 and error message is in an encrypted format.

SQLERRM: 2ÿ-418ÿ42610ÿ
SQLERRCODE:-727

Is there any way to decrypt an encrypted message thrown by DB2.
OR is there any known problem using Dynamic SQL Statements (with bind
variables) along with 'EXEC SQL EXECUTE' in DB2.

Did you check out the Messages and Codes manual? All manuals can be
downloaded from the IBM website in PDF format. The SQLERRCODE messages are
in volume 2.

[Serge Rielau]

db2 => ? SQL0418;
SQL0418N A statement contains a use of a parameter marker that
is not valid.

Actually I think your ptoblem is the reverse: You try to use
Hostvariables where you should use parameter markers.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Arti Potnis]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...

db2 => ? SQL0418;
SQL0418N A statement contains a use of a parameter marker that
is not valid.

Actually I think your ptoblem is the reverse: You try to use
Hostvariables where you should use parameter markers.

Cheers
Serge

I have tried with various combinations like:-
1)my_stmt is :-
INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT ?, current date, current
date,NAME from MY_LOAD_TABLE where FSI=? and FCI=?)"

EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

ERROR:SQL0418

2)my_stmt is :-
INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT :v1, current date, current
date,NAME from MY_LOAD_TABLE where FSI=:v2 and FCI=:v3)"

EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

ERROR: SQL0312

[Philip Sherman]

Check the "Programming Client Applications" manual. It has a great
section (with examples) on dynamic SQL.

Your SQL statement raises questions about why you are trying to use
dynamic SQL. Dynamic SQL incurs additional run-time costs for the
prepare. There is also potential for additional contention on the
catalog during prepare. Using Parameter markers looses the greatest
benefit (optimization) of dynamic SQL over static SQL.

From the manual:
"The recommendation is to use static SQL with host variables or dynamic
SQL without parameter markers as the most efficient options."

Phil Sherman
Arti Potnis wrote:

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...

db2 => ? SQL0418;
SQL0418N A statement contains a use of a parameter marker that
is not valid.

Actually I think your ptoblem is the reverse: You try to use
Hostvariables where you should use parameter markers.

Cheers
Serge

I have tried with various combinations like:-
1)my_stmt is :-
INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT ?, current date, current
date,NAME from MY_LOAD_TABLE where FSI=? and FCI=?)"

EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

ERROR:SQL0418

2)my_stmt is :-
INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME) (SELECT :v1, current date, current
date,NAME from MY_LOAD_TABLE where FSI=:v2 and FCI=:v3)"

EXEC SQL EXECUTE my_stmt
using :CUST_ID ,:fsi ,:fci;

ERROR: SQL0312

[Serge Rielau]

INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME)
(SELECT
?, -- <-- TROUBLE HERE
current date, current
date,NAME from MY_LOAD_TABLE where FSI=? and FCI=?)

You need to cast(? as <datatype>)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Arti Potnis]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...

INSERT INTO MY_INS_TABLE (
CUSTOMER_ID,CREATEDATE,LASTUPDATEDATE , NAME)
(SELECT
?, -- <-- TROUBLE HERE
current date, current
date,NAME from MY_LOAD_TABLE where FSI=? and FCI=?)

You need to cast(? as <datatype>)

Cheers
Serge

-----------------------------
Thanks a lot. It worked.

-Arti.