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):
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!



Comments

  1. How about using SQL GET DIAGNOSTICS to determine the error and message text to publish the actual error message?

    ReplyDelete
    Replies
    1. Dear Ragavendar,

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

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

      It 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

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

    Also 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, ...).

    ReplyDelete
    Replies
    1. 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.

      I would like to express my gratitude for bringing these important aspects to light.

      I'm eager to hear more from you!

      Delete
    2. @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.

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

      Delete
  3. 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).
    Fetch 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;

    ReplyDelete
    Replies
    1. Thank you, Massimo, for your insightful comment!
      Your 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! 👏

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

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

      Delete
    3. 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
    4. @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.
      @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...

      Delete
    5. Hi @Massimo. I appreciate your energetic style and the passion you bring to the debate.
      Diversity of opinions is what makes this exchange of ideas interesting.

      I hope the discussion leads to further responses and insights from those involved.

      Delete
    6. @Massimo It's a shame that you're not open to learn something from other people.

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

      Delete
    7. @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.
      So, 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...

      Delete
  4. @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.

    PS1. My first post already recommended to use the SQL state (instead of SQLCODE)... so no, I didn't miss it !

    ReplyDelete
  5. I use this structrure:
    open
    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

    ReplyDelete
    Replies
    1. Thank you for your valuable contribution. I take this opportunity to 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!

      Delete
  6. Hi Aldo!

    I'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!

    ReplyDelete
    Replies
    1. Dear Christian,

      Thank 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

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

    ReplyDelete
  8. Having two FETCH statements is neither easier to read nor easier to maintain.

    ReplyDelete
    Replies
    1. You 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.

      Delete
    2. I would agree with Paul and Daniel

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

      Delete
    3. I appreciate the advantage of keeping the code cleaner.

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

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

    ReplyDelete
    Replies
    1. Thank you for sharing your insights!

      It's valuable to hear different approaches and experiences.

      If you have any further suggestions or thoughts, feel free to share.

      Delete

Post a Comment

Popular posts from this blog

IBMi (AS400) fans only ‘ Memories (IBM Coding Forms)

IBM i (AS400) fans only ' How to read a TXT file in the IFS with SQL