IBMi (AS400) fans only ' SQLCODE values: Dear readers, unleash your suggestions!
This RPG code uses a DOU loop to fetch data from a cursor named C1 until the SQLCODE is not equal to 0, indicating the end of the recordset.
Inside the loop, it clears a data structure (RecordDsC1), fetches data into it, and then checks if the fetch was successful.
If successful, it assigns values and writes to an output file.
After the loop, it checks if the SQLCODE is not 100, indicating unexpected termination, and displays a warning message if needed.
// Loop until SQLCODE is not equal to 0
dow sqlCode = 0 ;
// Clear the data structure RecordDsC1
clear RecordDsC1;
// Fetch data into RecordDsC1 using cursor C1
exec sql fetch C1 into :RecordDsC1;
// If fetch was successful (SQLCODE = 0)
if sqlcode = 0 ;
// Assign values and write to output
W1QUEU = rDsW1QUEU;
write W1SF1;
endif;
enddo;
// Check if SQLCODE is not 100 (indicating unexpected termination)
if sqlCode <> 100;
// Build a warning message
wMsg='Warning: +
The selection has terminated unexpectedly before the end of the recordset. +
The error code is WSF110_sqlCode ' + %editc(sqlCode:'X') + ' Please press Enter +
and contact support.';
// Display the warning message
DSPMSG01CL(wMsg);
endif;
Since the loop terminates for an SQLCODE different from 0, it would be good to check with which SQLCODE value the program exits the loop. The typical value should be SQLCODE = 100, which means 'I have correctly read the entire recordset.' If the SQLCODE value is different from 100, it would be advisable to take some action. What do you suggest doing?
Dear readers, unleash your suggestions!
Edit (22/01/2024):
Inside the loop, it clears a data structure (RecordDsC1), fetches data into it, and then checks if the fetch was successful.
If successful, it assigns values and writes to an output file.
After the loop, it checks if the SQLCODE is not 100, indicating unexpected termination, and displays a warning message if needed.
// Loop until SQLCODE is not equal to 0
dow sqlCode = 0 ;
// Clear the data structure RecordDsC1
clear RecordDsC1;
// Fetch data into RecordDsC1 using cursor C1
exec sql fetch C1 into :RecordDsC1;
// If fetch was successful (SQLCODE = 0)
if sqlcode = 0 ;
// Assign values and write to output
W1QUEU = rDsW1QUEU;
write W1SF1;
endif;
enddo;
// Check if SQLCODE is not 100 (indicating unexpected termination)
if sqlCode <> 100;
// Build a warning message
wMsg='Warning: +
The selection has terminated unexpectedly before the end of the recordset. +
The error code is WSF110_sqlCode ' + %editc(sqlCode:'X') + ' Please press Enter +
and contact support.';
// Display the warning message
DSPMSG01CL(wMsg);
endif;
Since the loop terminates for an SQLCODE different from 0, it would be good to check with which SQLCODE value the program exits the loop. The typical value should be SQLCODE = 100, which means 'I have correctly read the entire recordset.' If the SQLCODE value is different from 100, it would be advisable to take some action. What do you suggest doing?
Dear readers, unleash your suggestions!
Edit (22/01/2024):
I clarify the underlying question of my post:
I've implemented a loop that fetches data until the SQLCODE is not equal to 0, signifying the end of the recordset.
I've included a section to handle the scenario where the SQLCODE is not 100, indicating an unexpected termination before reaching the end of the recordset.
I'm curious to hear your suggestions on what actions should be taken if SQLCODE is not 100.
In my post, I proposed displaying a warning message. However, I'm open to alternative ideas or improvements from your experience.
Feel free to unleash your suggestions and share your insights on how to handle this situation effectively in RPG programming.
Looking forward to your valuable input!
How about using SQL GET DIAGNOSTICS to determine the error and message text to publish the actual error message?
ReplyDeleteDear Ragavendar,
DeleteThank you for your valuable suggestion! Using SQL GET DIAGNOSTICS is indeed a great idea to precisely determine the error and extract meaningful error message text. This approach enhances the program's ability to handle unexpected terminations more effectively.
I would love to invite you to share an example or provide additional insights on how to implement SQL GET DIAGNOSTICS in this context. Your practical expertise will not only enrich the discussion but also benefit other readers who may encounter similar scenarios.
Your collaboration is highly appreciated, and I look forward to hearing more from you!
As pointed out by another user Paul, it is good to handle errors inside DOW loop itself. I was referring to using NUMBER and RETURNED_SQLSTATE as documented at https://www.ibm.com/docs/en/i/7.5?topic=statements-get-diagnostics
DeleteIt is explained for z/os in below link as example 2. Sadly similar example missing in i7.5 page though.
https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-get-diagnostics
First of all, a DoW requires a fetch before the DoW statement and one just before the EndDo. The actual code should be between the DoW and fetch statement. Your current construction fails if SQLCOD is not properly initialized before the DoW and will act even more strange if you'll use other SQL instructions as actual code.
ReplyDeleteAlso the use of SQLCOD is not a good idea for proper error handling (as you'll miss certain warnings). Better is to use the SQL state (first two positions indicate warning, error, no records found, ...).
Thank you for your insightful feedback! Your points regarding the proper use of a DoW loop in conjunction with SQL fetch statements and the consideration of SQL state for more effective error handling are highly appreciated.
DeleteI would like to express my gratitude for bringing these important aspects to light.
I'm eager to hear more from you!
@Paul - that kind of loop is OK - because if you use FETCH, there has to be an OPEN before the loop - the OPEN will set the SCLCODE to 0 if successful - if not, the loop isn't entered and the SQLCODE diagnosis can kick in.
DeleteOf course the classic solution is:
FETCH
dow SQLCODE = 0;
// process
FETCH
enddo;
But with this construct, you have to repeat the FETCH - and this means, if you modify the result row, you have to modify two FETCH statements.
Bad idea is also conditioning the loop on SQLCODE value ! Should you execute other SQL instructions inside the loop, you might encounter unpredictable results (eg. exit loop when not expected).
ReplyDeleteFetch loops MUST be coded like this:
dcl-s $exit ind;
Declare cursor, open, etc...
$exit = *off;
dow $exit = *off;
Exec SQL FETCH cursor INTO :ds ;
select;
when SQLCODE = 0;
// do something
when SQLCODE = 100;
$exit = *on;
other;
$exit = *on;
endsl;
enddo;
Thank you, Massimo, for your insightful comment!
DeleteYour suggestion on handling the fetch loop is indeed valuable. I appreciate your expertise and the cautionary note about potential issues with conditioning the loop on SQLCODE. Your provided code snippet offers a more robust approach, ensuring better control and predictability, especially when dealing with multiple SQL instructions inside the loop.
Your contribution is truly appreciated! 👏
Sorry to be harsh and with all respect but in my opinion this is all but clean code. You added 9 lines of additional coding just to avoid writing another Fetch instruction and abusing the DoW program logic.
DeleteIt keeps amazing me that people still don't know how to do structured programming and how and when to implement a DoU or DoW instruction correctly.
Following is the only correct example (and SQLCheckstate is a procedure that can be written based on earlier input in this article).
Declare cursor, open, etc...
Exec SQL FETCH cursor INTO :ds ;
DoW not SQLCheckstate();
// Insert your stuff
Exec SQL FETCH cursor INTO :ds ;
EndDo;
This code is less than 50% as your example and easier to read and maintain !
Thank you, Paul, for sharing your perspective! I appreciate your emphasis on clean and efficient code. Your alternative example certainly streamlines the fetch loop and provides a more concise solution. It's great to have different viewpoints and approaches to improve code readability and maintainability. Your insights are valuable, and I'm sure they will be helpful to readers exploring different coding styles. Thanks again for contributing to the discussion! 👍
Delete@Paul Nicolay It keeps amazing me that some people believes they are the only ones capable of writing a DOW loop... Remember that good code not always means less lines ! And, worst of all... why two FETCH ? In a read loop from a main table, the read operation should be one and only one ! Anyway, stay safe with your certainties, fortunately I don't have to work with you.
Delete@Aldo Succi your replies look clearly as if they are generated from ChatGPT or similar... Am I wrong ? Should I be right, it would be quite disappointing... better write in italian, translate the text and paste it here than these not very credible replies...
Hi @Massimo. I appreciate your energetic style and the passion you bring to the debate.
DeleteDiversity of opinions is what makes this exchange of ideas interesting.
I hope the discussion leads to further responses and insights from those involved.
@Massimo It's a shame that you're not open to learn something from other people.
DeleteIf you keep on insisting that ...
$exit = *off;
dow $exit = *off;
... is the correct usage of a DoW loop then there's not much benefit in trying to teach you that such construct will always be performed once meaning that it is a typical DoU construct and therefor incorrect for reading a database file (as it normally has 0 to n records).
Basic structured programming should have teached you that DoU means 1-n iterations, while DoW means 0-n iterations.
PS. The reason for two fetches is the fact that a fetch doesn't return a boolean and can't be used directly on the DoW expression so this is just a language limitation, otherwise you could have coded DoW not Fetch...
@Paul Nicolay "It's a shame that you're not open to learn something from other people." WHAT ??? How dare you speaking like that about people you don't know ? Listen, first of all you should learn being humble and accept points of view that are different from yours ! Also because with your questionable reasoning about DOW you completely missed out the main thing I pointed out in my first comment, the fact you should not test SQLCODE to exit loop.
DeleteSo, don't you dare judging people like this ! Code your DOW as you want and don't expect that everyone considers your opinion as the best !!!
Bye bye
@Aldo Sorry for the "flame", but I cannot accept everything...
@Massimo I don't have to know you to see that you're missing the basic principles of structured programming concerning the correct usage of DoU or DoW loops. This is by the way not related to "my opinion" but a general rule that every school kid learns during one of their first programming lessons.
ReplyDeletePS1. My first post already recommended to use the SQL state (instead of SQLCODE)... so no, I didn't miss it !
I use this structrure:
ReplyDeleteopen
dow 1=1
fetch
IF SQLCOD < 0 OR SQLCOD = 100
close
leave
endif
// Insert your stuff
if your stuff must be exclude record that in sql is impossible check
iter
endif
enddo
Thank you for your valuable contribution. I take this opportunity to clarify the underlying question of my post:
DeleteI've implemented a loop that fetches data until the SQLCODE is not equal to 0, signifying the end of the recordset.
I've included a section to handle the scenario where the SQLCODE is not 100, indicating an unexpected termination before reaching the end of the recordset.
I'm curious to hear your suggestions on what actions should be taken if SQLCODE is not 100.
In my post, I proposed displaying a warning message. However, I'm open to alternative ideas or improvements from your experience.
Feel free to unleash your suggestions and share your insights on how to handle this situation effectively in RPG programming.
Looking forward to your valuable input!
Hi Aldo!
ReplyDeleteI'd say that I usually use DOU instead of DOW.
For example:
Imagine a table named "CUSTOMERS", and what I want is retrieve the customer ID and his name...
// Retrieving data in a loop
exec sql
declare c1 cursor for
select id, name
from customers;
exec sql
open c1;
dou sqlcode <> 0;
exec sql
fetch c1 into :id, :name;
if sqlcode <> 0;
leave;
endif;
// Do something with id and name....
enddo;
exec sql
close c1;
Other thing that I usually do, is loading the information into a multidimensional data structure, and once it is loaded I close the cursor.... for that, I usually do an only one fetch, like this:
dcl-ds data dim(*auto:10000) qualified;
id zoned(5) inz;
name varchar(50);
end-ds;
%elem(data) = 0;
exec sql
declare c2 cursor for
select id, name
from customers;
exec sql
open c2;
Maxrows = 1000;
exec sql
fetch c2 for :Maxrows rows into :data;
exec sql
close c2;
This way, as the structure is an *auto structure, the fetch automatically sets the number of elements of the structure.
Of course, you should check for sqlcode after the fetch... and if there's any error, you should show something on screen ...
Greetings!
Dear Christian,
DeleteThank you for your valuable contribution to the discussion! Your insights into using DOU instead of DOW and efficiently loading data into a multidimensional data structure provide additional perspectives for my readers. Much appreciated!
By the way, I'd like to take this opportunity to recommend your YouTube channel to my readers:
you can find Christian's insightful content at
https://www.youtube.com/c/ChristianLarsenRPG
Really funny to see people write additional code to change the behavior of an endless loop or do until to finally act like a do while structure. If you do such things you should really ask yourself if you're doing the correct things !
ReplyDeleteHaving two FETCH statements is neither easier to read nor easier to maintain.
ReplyDeleteYou must be joking... writing 9 lines of additional code and ignoring structured programming techniques is easier than a single FETCH ? I guess this discussion is pointless, good luck with your coding practices... at least you don't use a GOTO anymore.
DeleteI would agree with Paul and Daniel
DeleteI've always done a fetch before starting a loop with a fetch in the loop.
This has been standard for a native read as well.
Creating all sorts of variables is unnecessary and makes the code dirty. Of course if you are going to run sql within sql then that is another ball game and not good coding - respectfully in my opinion.
I appreciate the advantage of keeping the code cleaner.
DeletePrioritizing code readability is a good practice.
Each developer may have their own coding style, and your emphasis on readability aligns well with good programming practices.
Thank you for your valuable input!
Aldo your code is correct if you want to use SQLCODE assuming the opening of the cursor C1 is the line of code that sets SQLCODE to 0 - assuming you are doing a simple read as in your code. You don't need the extra fetch IF you are not bothered that you enter the loop once. However a fetch to start saves going into the loop if the file is empty probably better - what you don't really want is all sorts of extra variables to control the loop. The $exit proposal goes against international coding best practice as this changes in relation to CCSID's on source physical files. I've seen it many times.
ReplyDeleteThank you for sharing your insights!
DeleteIt's valuable to hear different approaches and experiences.
If you have any further suggestions or thoughts, feel free to share.