Re: SQL Question

The close cursor does close the ODP ( at least it puts a message in the
joblog saying it does) but the manual refers to an "internal object" created
by the Prepare statement and apparently it doesn't always go away.  The
manual refers to this as performance enhancement since you should be able to
open the cursor again without the Prepare overhead.  It doesn't, however,
say how to get rid of this "internal" object when you want to.  Subsequent
calls to the Prepare statement says it opens a new ODP, but something is not
quite right, since it returns no data.  David thinks the delay parameter
might effect it, so I'll try that tomorrow.  Thanks for the suggestions.
-----Original Message-----
From: John P Carr <jpcarr@tredegar.com>
To: RPG400-L <RPG400-L@midrange.com>
Date: Sunday, August 08, 1999 6:10 PM
Subject: Re: SQL Question


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


No errors or warnings out of the preprocessor.  This is one time, it doesn't
seem to be at fault.  In fact, no errors at runtime either, just no data.

> What do you use for the
>
> Close SQL cursor . . . . . . . . CLOSQLCSR      *ENDMOD
> Parameter ??  I use *ENDACTGRP


I've tried them all.

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


I'm doing a close cursor after every Fetch.  The joblog says the ODP is
closed sometimes and sometimes it's not.  Apparently, data management is
controlling that.  But on every new Prepare, it says a new  ODP is opened.

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

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