Re: Dynamic Pro*C SQL Requiring mult. PREPAREs. WHY?

In article <321E29D1.3895_at_unixmail.ks.boeing.com>, "John says...
>
>We are trying to code a Pro*C (V1.6.8.0.0) program to loop thru a file and
>insert multiple records into a (variably named) table using dynamic INSERTs.
>
>What we want to do is something like:
>strcpy(string, "INSERT INTO table (....) VALUES (:x, :y)");
>EXEC SQL PREPARE statement_name FROM :string;
>while (records exist in the file) {
> set the host variables
> EXEC SQL EXECUTE statement_name USING :hv1, :hv2; }
>

What do you mean '(variably named)'???? Is the table name changing depending on the data? If not then don't use dynamic sql. If the tablename is changing then of course you have to start over for each excution.

The non dynamic solution is to simply code an insert:

exec sql
  insert into table(....)
  values(:a, :b....);

Then loop around it supplying new values each time. If you're after performance then learn how to insert arrays using the 'exec sql for :cnt' oracle extension. It screams.

Lawrence........ Received on Fri Aug 30 1996 - 00:00:00 CEST