RE: prepared SELECT - was: runsqlstm -- MIDRANGE-L

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Thank you for pulling up the documentation that proves the point I was
making.  Then maybe the limitation on the select in runsqlstm is related
to the limitation on the select statement in imbedded sql.

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 02:27 PM
Please respond to midrange-l


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



Document "DB2 Universal Database for iSeries SQL Reference" has this to
say
about SELECT INTO statement:

"This statement can only be embedded in an application program. It is an
executable
   statement that cannot be dynamically prepared. It must not be specified
in REXX. "

 Alexei
always speaking for myself




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


                      07/19/2002 02:15
                      PM
                      Please respond to
                      midrange-l





This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
All valid points.  Yes the assumed target for the output was the display.
Hence the term 'poor mans STRSQL'.

However I don't think a simple
select datafld into :Myhostvar
 from myfield
where primarykey='xyz'
Can be prepared and executed without a cursor.  And I've had to set up
some cursors for this.  Which, when you think of it, 'should have' only
one return row.

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 01:53 PM
Please respond to midrange-l


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



I do not understand your point...
By definition, SELECT statement returns a set of records.
To work with a set of records in a procedural language, you need a cursor
(to iterate through the set of records returned).
So yes, SELECT requires a cursor.

In your example

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'  <-- this won't work, you need a cursor

When statement (*) is executed without cursor - what is the target of
select?
Where values for RRN(T), QRECN, QTITY, QTISTY are supposed to go.
When you work with STRSQL or QM, there is an implicit target - display
screen or printable report - with a lot of rules, how this should look
like.
When you use procedural language, it is your responsibility to provide a
target for select list. And the vehicle to relate select list to program
variables is cursor (and a FETCH 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.