RE: RPG Stored Procedures, SQL, and Closing files/Activation Groups

On VB you will get a persistent connection.

On the web connect you will need to enable persistence.

Keeping the variable information of the cursor status on the client
would be meaningless in non-persistent environment

-----Original Message-----
From: Haas, Matt [mailto:Matt.Haas@thomsonlearning.com]
Sent: Wednesday, November 06, 2002 9:35 AM
To: rpg400-l@midrange.com
Subject: RE: RPG Stored Procedures, SQL, and Closing files/Activation
Groups

Dwight,

You just need to close the cursors prior to your return. If you need to
persist information between calls, the calling application will need to
do that and pass it to your stored procedure since you are not
guaranteed to get the same connection on your next call.

Matt

-----Original Message-----
From: dslessman@nationalelectrical.com
[mailto:dslessman@nationalelectrical.com]
Sent: Wednesday, November 06, 2002 12:21 PM
To: rpg400-l@midrange.com
Subject: RPG Stored Procedures, SQL, and Closing files/Activation Groups


Hello all,

We have been creating ILE RPG stored procedures to fetch, edit, and
update
information contained in our databases.  The stored procedures are
called
from both Visual Basic and Apache/Tomcat (running on Windows and being
called through the JDBC driver and pooling).  The stored procedures are
all
compiled with CLOSQLCSR(*ENDACTGRP) we were hoping for performance
reasons.

The stored procedures use embedded SQL to perform the database I/O.
Everything is working quite well and our users are pleased with what we
have been able to do.

My question is this:  In the "fetch" stored procedures we use a SQL
prepare
(from an RPG variable that we build on the fly), SQL declare, SQL open,
then SQL set result sets cursor. The stored procedures than issue a
"RETURN" with "LR" off.

On the first call to the stored procedure life is good and the expected
results are returned.  On subsequent calls life was not as good (cursor
was
already open).  We started putting in a close to the cursor (whether it
was
open or not).  The server jobs of course are now logging many messages
that
the cursor is not open during a fetch or close.

I have since attempted to place logic in the stored procedures to only
issue the close if the file is open but subsequent calls to the stored
procedures do not seem to be maintaining my variable information.

I am sure someone out there has already resolved this.  The applications
are running fine I am just trying to reduce some of the messages that
are
logged in the server's job logs.

Any ideas?

Thanks,

Dwight Slessman

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.