RE: prepared SELECT - was: runsqlstm

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
As I said before, you can use it on a cursor.  You cannot use it on a
simple select.

Try this instead
INSERT_STMT1 =  'INSERT INTO QAYPETIDX ',
                '(QRECN, QTITY, QTISTY) ',
                'VALUES(1, 2, 3) '

SELECT_STMT2 =  'SELECT RRN(T), QRECN, QTITY, QTISTY ',
                'FROM QAYPETIDX T',
                'ORDER BY 3   ',
                'FOR FETCH ONLY '
EXECSQL,
     'PREPARE S1 FROM :SELECT_STMT2'
EXECSQL,
     'EXECUTE S1'
EXECSQL,
     'PREPARE S2 FROM :INSERT_STMT1'
EXECSQL,
     'EXECUTE S2'

The insert will work, but the select will fail.

This technique is often done as a poor man's interactive SQL.  However I
also think that a simple select into will fail.


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Alexei Pytel" <pytel@us.ibm.com>
Sent by: midrange-l-admin@midrange.com
07/19/2002 12:53 PM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        RE: prepared SELECT - was: runsqlstm



I am surprised...
This a fragment from a REXX program (the fact that it is in REXX is
irrelevant - I've just had it handy):

SELECT_STMT1 =  'SELECT RRN(T), QRECN, QTITY, QTISTY ',
                'FROM QAYPETIDX T',
                'FOR UPDATE OF QRECN     '

SELECT_STMT2 =  'SELECT RRN(T), QRECN, QTITY, QTISTY ',
                'FROM QAYPETIDX T',
                'ORDER BY 3   ',
                'FOR FETCH ONLY '
EXECSQL,
     'PREPARE S1 FROM :SELECT_STMT1'
EXECSQL,
     'DECLARE C1 CURSOR FOR S1'
EXECSQL,
     'OPEN C1 '

SIGNAL ON ERROR NAME CLOSE1

EXECSQL,
     'PREPARE S2 FROM :SELECT_STMT2'
EXECSQL,
     'DECLARE C2 CURSOR FOR S2'
 EXECSQL,
      'OPEN C2 '

 SIGNAL ON ERROR NAME CLOSE2

 UPDATE_STMT =   'UPDATE QAYPETIDX ',
                 'SET QRECN = ?  ',
                 'WHERE CURRENT OF C1'

 EXECSQL,
      'PREPARE S3 FROM :UPDATE_STMT'

...........

    EXECSQL,
          'FETCH C1 INTO :REC_RRN, :REC_NUMBER,',
                      '  :REC_TYPE, :REC_SUBTYPE '


...........

     EXECSQL,
        'EXECUTE S3 USING :REC_NUMBER'


    Alexei
always speaking for myself




                      rob@dekko.com
                      Sent by:                  To:
midrange-l@midrange.com
                      midrange-l-admin@m        cc:
                      idrange.com               Subject:  RE: runsqlstm


                      07/19/2002 12:37
                      PM
                      Please respond to
                      midrange-l





This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I don't think you can.  In fact, I've had to do some single fetch cursors
because I could not prepare a simple select.  Yet I could set up the whole
select portion of the cursor.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Alexei Pytel" <pytel@us.ibm.com>
Sent by: midrange-l-admin@midrange.com
07/19/2002 12:07 PM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        RE: runsqlstm



>  There is also a related limitation that you cannot have a SELECT
statement
>  be a prepared SQL statement.

What do you mean? You can certainly prepare SELECT statement ...


Alexei
always speaking for myself


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.






_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.