Re: SQL - EXECUTE IMMEDITE

[Pete Hall <pbhall@xxxxxxxxxx>]

At 19:40 12/18/2000, Chan Le wrote:

>I have a big file, and many columns have value.  I would like to list the
>column names which have values.  I create a stored procedure.  This
>procedure executes a dynamic SQL statement string.
>
>
>set SQLSTR01 = 'select count(*) from  XXXXXXXXX   where  YYYYYYY  <> 0'
>
>
>The file name will replaces XXXXXXXX, and field name replaces YYYYYYYY
>
>I try several options but it does not work.
>
>1/ if (execute immediate SQLSTR01);
>2/ set count01 = execute immediate SQLSTR01;
>3/ set SQLSTR01 =  'select count(*)      INTO :CNT01            from
>XXXXXXXXX   where  YYYYYYY  <> 0'

There may be other ways, but here's one that should work:

   C/EXEC SQL DECLARE C1 CURSOR FOR Q1
   C/END-EXEC
>  C    eval Qry = 'SELECT COUNT(*)
>              FROM  XXXXXXXXX   WHERE  YYYYYYY  <> 0'
   C/EXEC SQL PREPARE Q1 FROM :Qry
   C/END-EXEC
   C/EXEC SQL OPEN C1
   C/END-EXEC
   C/EXEC SQL FETCH C1 INTO :RowCount
   C/END-EXEC
   C/EXEC SQL CLOSE C1
   C/END-EXEC

I've used variations on this a few times for handling dynamic order by and 
selection criteria. If you need to pass host variables into the where 
clause, you need to use parameter markers in the query string (question 
marks), and the USING clause on the OPEN in order to bind the host 
variables by making the following changes:

>  C    eval Qry = 'SELECT COUNT(*)
>              FROM  XXXXXXXXX   WHERE  YYYYYYY  <> ?'

and

   C/EXEC SQL OPEN C1 USING :Value
   C/END-EXEC

You could then reuse the cursor by closing it, changing the value of the 
host variable and the reopening it. I don't think you can use parameters 
for the table or column names though, so in this case you probably won't 
need the USING clause.

If you need to prepare the statement more than once within a single 
activation of the program, you need to specify *RUW for the connection 
method at compile time, and use CONNECT and DISCONNECT in order to force 
the cursor to be destroyed so you can rebuild it.

hth
Pete
Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---