Oppolzer - Informatik / Blog

It seems as if only DB2 z/OS tells the location of syntax errors in the SQL statement
in SQLERRD (5), following PREPARE.

It has yet to be confirmed for Windows, but my old DB2 on OS/2 does the following:

-104 in SQLCODE on some syntax error (which is a good example, because I know
for sure, that DB2 on z/OS gives the location in SQLERRD (5) in this case; same goes
for SQLCODE -199)

- zero in SQLERRD (5) ... which is different from what DB2 on z/OS does

- in SQLERRMC, we get three informations:

a) the symbol that caused the syntax error
b) some symbols read before (which is helpful, 20 chars ca.)
c) some symbols which would have been ok in this place

the informations are separated by X'FF', as usual

Of course, you can scan the SQL statement for the 20 chars from SQLERRMC and
try to find the missing information from SQLERRD (5) this way, but this is not as convenient
as on z/OS, and it would be a problem, if the statement part shown is not unique throughout
the SQL statement.

I will test it on DB2 for Windows 9.5 later this day.

Kind regards

Bernd




Am 15.01.2015 um 20:32 schrieb Bernd Oppolzer:
> FWIW:
>
> I looked into the error diagnosis part of my ETL tool,
> which is a portable (ANSI) C program, and so it works on all platforms,
> that is: DB2 for z/OS, DB2 LUW - on Linux, other Unixes, Windows, even
> OS/2.
>
> There is an Oracle version, too.
>
> The SQLERRD (5) value to get information about syntax errors (that is,
> the position of the syntax error in the SQL statement string) is only examined
> in the mainframe variant.
>
> It looks like this:
>
>
> #ifdef HOST
>
>         check_syntaxfehler (pglob, sqlca.sqlerrd [4], 1);
>
> #endif
>
>
> so this function call is activated only, when we are on the mainframe.
>
> And then:
>
>
> void check_syntaxfehler (sglobal *pglob,
>                            int fehlerpos,
>                            int sqlnummer)
> {
>
>      ....
>
>      if (fehlerpos > 0)
>      {
>         error (pglob, 'I', 104, NULL, NULL);
>
>
> the program only does something special (that is: output the SQL statement
> and show the location of the syntax error), when sqlerrd {4] ... which
> is the 5th element
> (C counts from zero) ... is greater than zero.
>
> So I think: I did some tests in the past on DB2 LUW, and this field sqlerrd [4]
> is not returned by DB2 LUW ... or it has another meaning ... or I didn't find
> information about it.
>
> If I find some time in the next days, I'll try it  ... on Windows with DB2 V9.5
> and on OS/2 V3.0 with DB2 V1.3 (!).
>
> Kind regards
>
> Bernd
>
>