× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2022

Re: Repeated CLOSE and OPEN of SQL cursors

I have a couple of copy-book'ed procedures which I used to monitor for
unexpected SQLSTT's. I pass an operation type ("get", "read", "insert",
etc.), an action code ("dump", "end", "ignore", etc.), a table name, and a
description of the function of the code after each SQL statement. The
routine will execute a dump, end the program, write to an error log, write
to the application's error message routine, or push a message into the job
log.

For insertion, deletion, or reading-for-update *exceptions*, the routine
usually returns softly and lets the application handle the situation. But
if there's some other error, the program traps the error, tells the user,
and tells me.

If you're importing data and don't check it rigorously before insertion
into your tables, a routine of this type will save you a lot of grief--it
will help find bad data without the application imploding on the user. The
other scenario is after program modifications have been made but not
adequately tested: as those undetected errors become visible,
troubleshooting is made much easier thanks to the instrumentation built
into the program.

--reeve



On Mon, Oct 10, 2022 at 7:47 AM Alan Campin <alan0307d@xxxxxxxxx> wrote:

Very good point. The biggest single error that RPG programmers make with
SQL is not error checking each SQL statement.

In this case, you need to check the Prepare, Open and Fetch statements for
errors each time.

For prepare,
Exec Sql Prepare stmt
If SqlState <> SQL_STATE_OK; // '00000'
Throw error
EndIf.

Open the same thing.
Exec Sql Open Cursor
If SqlState <> SQL_STATE_OK;
Throw error
EndIf;

On a fetch

Exec Sql Fetch Next
From cursor
Select;
When SqlState = SQL_STATE_NO_ROW; // '02000'
// close cursor processing.
Return or leave.
When SqlState = SQL_STATE_OK;
// Ok processing.
Other;
// Throw error.
Endsl;

On Mon, Oct 10, 2022 at 7:10 AM Daniel Gross <daniel@xxxxxxxx> wrote:

Hi Alan,

what Darren wrote is often true - especially when you only code

dow SQLCODE = *zero;
...

Because loops like that are do not detect SQL errors (SQLCODE < *zero).

The best way is, to step debug the program, and find out, what happens.

And of course - you have to make sure, that the SFL is cleared and RRN is
reset when starting over.

Regards,
Daniel


Am 10.10.2022 um 16:04 schrieb Darren Strong <darren@xxxxxxxxx>:

Usually this happens because there is one or more values that you
cannot bring into your program because the value is not compatible with
the
variable. For example, if one field is sometimes null, and your RPG
variable can't except a NULL, then the SQL fetch fails, you get a code
that
you assume is the end of the list code, and your Dox loop exits. When you
sort (order by) the list, these nulls come at different times in the
cursor
fetch.


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan
Cassidy
Sent: Monday, October 10, 2022 9:26 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Repeated CLOSE and OPEN of SQL cursors

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


I have one last thing stumping me for now to finish a major code change
to boost performance in one program I'm working on, relating to SQL
cursors.

The RPG program opens an SQL cursor and the entire subfile is input.
Then the user requests a different "ORDER BY" than the default. I use
the same SQL for the sort, the whole thing is identical except for
tacking
on the different ORDER BY clause at the end of the dynamically
constructed
SQL statement.

At the top of the subroutine that does the cursor open, the code first
does an Exec SQL Close M1, where M1 is the cursor. Then re-open. So if
this
is the second or third time, etc., opening the cursor, the code first
runs
the line "exec SQL Close M1".

The issue is that the sorted open is getting sometimes around two or
three times (it varies) as many rows returned as the first open.

Can someone point me to why or how that could happen?

--Alan Cassidy



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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.