Return more details on the actual error instead of "invalid column data"

[mridoni]

BTW: Would there be the option to return more details on the actual error (maybe in SQLERRM and only use "invalid column data" as fallback)? I think other sql environments provide the source / target parameter count that has wrong data in some of the SQLERRD fields (but I'm not sure), maybe this would additionally be possible, too?

Note: I'm sure that seeing this error is only a side effect of a previous error, still looking... (but that's unrelated to this issue).

Originally posted by @GitMensch in https://github.com/mridoni/gixsql/issues/107#issuecomment-1225747198

[mridoni]

I have to look at what information is available for the various drivers, but this can probably be done.

[GitMensch]

How is this solved now? Do you have this covered in the testing code already?

[mridoni]

Actually some work for this stayed out, I am reopening it

[GitMensch]

This may need to be moved out to a separate issue - not sure, but Oracle provides an additional library call that can be used to get the error message of up to 200 characters - SQLGLM and a CALLable wrapper around that for DSNTIAR (the DB2 variant=.

It likely will also be useful to be provided for migrations.

[GitMensch]

Addition to the one above: MicroFocus' (now OpenText, now RocketSoftware) OpenESQL (which references "open systems" like GNU/Linux) has one extension, that their last addition COBOL-IT's new CitOESQL also supports. It would be good to support that as well.

From MF's docs:

If you declare the data item MFSQLMESSAGETEXT, it is updated with a description of the exception condition whenever SQLCODE is non-zero.c must be declared as a character data item, PIC X(n), where n can be any legal value. This is particularly useful, as ODBC error messages often exceed the 70-byte SQLCA message field.

Adding both this extension and SQLGLM would mean:

• at runtime:
  • if SQLCODE is non-zero, then set an internal static variable (either 1024 or 500 chars) with the error message as C string
  • add an SQLGLM entry point that will just copy that to the passed text with strncpy and then sets the passed size var, if its pointer isn't NULL
• at compile time:
  • check in the program and EXEC SQL INCLUDEs if MFSQLMESSAGETEXT is defined
  • if yes either:
    • include a CALL "SQLGLM" USING MFSQLMESSAGETEXT, LENGTH OF MFSQLMESSAGETEXT, OMITTED after each call (=no ABI change at all)
  • or:
    • set a pointer "somewhere" in the structure(s) from this program to this field, as well as the length (likely an ABI change, which I personally currently won't mind)
    • if the structure for the caller contains that pointer, use this information to copy it, space-fill with spaces

Possibly interesting from CitOESQL:

ODBC error messages start with one or more component names in square brackets followed by the message text. These identify where the error message was detected, for example, by the ODBC Driver Manager or the ODBC driver, or the database server. CitOESQL removes any text in square brackets from SQLERRMC, but not from MFSQLMESSAGETEXT.