I am dynamically creating an SQL statement that will contain info from user input. The user enters the file name and library name, and optionally a filtering clause such as TRANDATE > 2022-12-31. My program constructs the SQL statement and executes it. I am actually creating a view. All of this works fine. My problem is error capturing. I am using GET DIAGNOSTICS to get information back about the success or failure of the statement. That too works fine, but the MESSAGE_TEXT returned, in this example, is "Prepared statement S1 not found.". I am forcing this error for testing purposes by entering a field name that does not exist, such as XXXXXX. If I look in the joblog I see a diagnostic message that says "Column or global variable XXXXXX not found.". That is what I want to return to the user, but I can't find any way to get that using any of the condition 1 variables.
Is it even possible?
Is it even possible?
HTML Code:
command = 'Select * from ' + %trim(parm_liblname) + '/' + %trim(parm_filename) + ' Where ' + %trim(parm_filters); exec SQL Prepare S1 From : command; exec SQL Declare FCursor INSENSITIVE Cursor for S1; exec SQL Open FCursor; exec SQL Get Diagnostics :sqlrecordcnt = DB2_NUMBER_ROWS; exec SQL Get Diagnostics Condition 1 :sql_code = DB2_RETURNED_SQLCODE, :sql_state = RETURNED_SQLSTATE, :sql_msglen = MESSAGE_LENGTH, :sql_msgtext = MESSAGE_TEXT, :sql_msgid1 = DB2_MESSAGE_ID, :sql_msgid2 = DB2_MESSAGE_ID1, :sql_msgid3 = DB2_MESSAGE_ID2;
Comment