Embedded SQL error handling

[worksofliam]

Following on from the last blog, this one will extend your Embedded SQL knowledge. This blog will mainly focus on two things. First: SQLSTATE and how to use it to your advantage. Second: Dynamic SQL queries in Embedded SQL

SQLSTATE is define as a Char(5), although for this tutorial we're only really interested in the first 2 characters (%Subst(SQLSTATE:1:2)). You can find out what those two character codes mean here.
I also want to let you know, if you plan on debugging embedded SQL which affects a physical file in a *PROD library, make sure you STRDBG with UPDPROD(*YES) otherwise you'll get some weird SQLSTATE like 42 or 54. When you're debugging Embedded SQL and something goes wrong, always EVAL SQLSTATE and then DSPJOBLOG.. Embedded SQL will always print to the job log if you're debugging.

Back to the blog; In the previous blog, I used the following as an example:

Exec SQL SELECT CUS_BAL,
                CUS_NAME
         INTO   :Customer.CUS_BAL,
                :Customer.CUS_NAME
         FROM   CUSTOMERS
         WHERE  CUS_ID = 1;

printf(Customer.CUS_NAME + x'25');

But what if there is an error? At the moment, we're just printing Customer.CUS_NAME no matter what happens. Let's handle that using some of the 2 character codes we'd spoken about earlier. I'm using a SELECT and WHEN to check the value of gSql. gSql is defined like so and we only assign to gSql after we have used EXEC SQL statements. You may want to replace the static strings with constants.

Dcl-S  gSql Char(2);

//Embedded SQL happens here

gSql = %Subst(SQLSTATE:1:2);

Select;
  When (gSql = '00');
    printf(Customer.CUS_NAME + x'25');

  When (gSql = '01');
    printf('Probably worked, but with warnings. '
         + 'Check job log?' + x'25');

  When (gSql = '02');
    printf('No relevant data' + x'25');

  Other;
    printf('It''s worse: ' + gSql + x'25');
ENDSL;

I know it seems basic, but this type of validation may allow you to handle issues better - even display nice popups to say that no data was found.