Re: SQL Question

[John P Carr <jpcarr@xxxxxxxxxxxx>]

        You should not have to do all that.   I have programs that do
PREPARE's and
        on subsequent calls I CLOSE cursor and do the prepare over again and
it works
        great.    I think your problem might be on the CRTSQLRPG (Whatever)
command.
        What do you use for the 

         Close SQL cursor . . . . . . . . CLOSQLCSR      *ENDMOD    
        Parameter ??  I use *ENDACTGRP

        Here's the F1 help

        
..........................................................................
                            Close SQL cursor (CLOSQLCSR) - Help

        

          Specifies when SQL cursors are implicitly closed, SQL prepared

          statements are implicitly discarded, and LOCK TABLE locks are
released. 
          SQL cursors are explicitly closed when the user issues the CLOSE,

          COMMIT, or ROLLBACK (without HOLD) SQL statements.

        

          The possible values are:

        

          *ENDACTGRP

              SQL cursors are closed and SQL prepared statements are
implicitly   
              discarded, and LOCK TABLE locks are released when the
activation    
              group ends.

        

          *ENDMOD

              SQL cursors are closed and SQL prepared statements are
implicitly   
              discarded when the module is exited.  LOCK TABLE locks are
released 
         when the first SQL program on the call stack ends.
        
............................................................................
................................

        My SQL's are in Called Modules and I don't want to close the cursor
when
        I return to the calling program.   Yours sounds like you use
*ENDMOD

        John Carr
        ------------------------------


        From:   Nelson C. Smith@ncsmith on 08/08/99 02:38 PM
        To:     RPG400-L@RPG400-L@midrange.com@SMTP@EXCHCONNECT
        cc:      

        Subject:        Re: SQL Question

        What is the proper SQL way to close a file (or an ODP) that has been
opened
        with a Prepare, Declare Cursor, Open Cursor, Fetch, Close Cursor
series of
        statements?  As I understand it, the close cursor statement doesn't
close
        the file, thereby eliminating a lot of overhead on subsequent open
cursors.
        This is good, but sometimes you might really need to close the file
        altogether.  I don't see any kind of SQL statement to do that.

        This is generic copy code used in procedures in service programs and
even
        though the cursor is closed and the procedure goes away, the service
program
        persists and I am having problems getting rid of the old ODP when I
need to
        change the keys in the Select Statement.  Blowing away the service
program
        between calls with a RclActGrp command does fix the problem, but is
somewhat
        of a sledge-hammer method when I would like to have something a
little more
        precise.  I would like to just close whatever the Declare
established and
        not have the overhead of reinitializing the service program each
time.

        A new Prepare and Declare doesn't seem to do it.  I get no data
returned on
        the subsequent calls even though a message is returned saying "1
rows
        returned" and no error codes of any kind.  Apparently, it is trying
to still
        use the ODP established by the first Prepare and Declare.  Any
suggestions?

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



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