Re: Dynamic SQL, Creating Table and insert into table

>In article <C9vIB1.AKv_at_ms.uky.edu> rama_at_ms.uky.edu (Sivakumar Ramamoorthy) writes:
 

>> i.e. I need something like
>> EXEC SQL CREATE TABLE:X AS SELECT * FROM ANOTHERTABLE;
>> Which of the four modes of dynamic SQL
>> namely
>> 1. EXECUTE IMMEDIATE
>> 2. PREPARE AND EXECUTE
>> 3. PREPARE AND FETCH
>> 4. PREPARE, DESCRIBE AND FETCH
>>
>> should I use.
 

>Any of them. The solution is to construct the sql string using C runtime
>functions, such as strcpy() and strcat(). You cannot use a bind variable
>for a table or column name in any SQL.
>--
>Dave Mausner / Sr Consultant / Datalogics division of Frame Technology Inc
>441 W Huron / Chicago IL 60610 / +1-312-266-4450 / "Just show me the code"

Be careful with this! Our application also creates dynamic tables (with the name determined at run time), inserts some data, then drops the table when finished. We have found that when you use the PREPARE/EXECUTE construct, the table gets created at the PREPARE statement, not the EXECUTE statement! It appears that DDL statements (e.g., create table, drop table) take effect after the PREPARE, but DML statements (e.g., insert, update, delete) take effect after the EXECUTE. Since this is a problem for our application, we have changed the PREPARE/EXECUTEs to EXECUTE IMMEDIATEs.

Has anyone else seen this? Is this a feature or a bug? We are using RDBMS V6.0.36.7.1 and Pro*C 1.4.11.2.1.

Marianne G. Pittorino Received on Tue Jul 13 1993 - 17:38:53 CEST