Re: SQL - EXECUTE IMMEDITE

[Chanh_Le@xxxxxxxxxxxxxxx]

Thank you very much for all of your helps, specially Pete's.

Thanks again,
CL





Pete Hall <pbhall@execpc.com>@midrange.com on 12/20/2000 08:22:06 PM

Please respond to MIDRANGE-L@midrange.com

Sent by:  owner-midrange-l@midrange.com


To:   MIDRANGE-L@midrange.com
cc:

Subject:  Re: SQL - EXECUTE IMMEDITE


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
+---



+---
| 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
+---