Getting SQL error details in Pro C

From: Alan Polansky <alanp_at_tibinc.com>
Date: Tue, 23 Nov 1999 16:58:59 -0500
Message-ID: <s3m3grpurrp20_at_corp.supernews.com>



I am using Pro C in a C/C++ program to add rows, via embedded SQL, to a database table. When there is an error in the content of the data being added, I have an SQL exception defined to print out detailed information, such as the Oracle error code and an Oracle text message describing the error.

However, I have found that if the error is due to a data error, such as a field too large, or the wrong data type for the field, then:

  1. There is no way to have Oracle indicate which field in the SQL statement caused the error.
  2. The SQL statement contained in the Oracle text message does not contain the actual field values, but only the internal Oracle PRO C variables (i.e. ":b3" instead of "AGENCY 49".

Is there a way to print this information out?

I am using Oracle 8.0.5.0.0 on Solaris x86 2.6.

i.e.:

  1. In my code, I register an SQL error handler such as:

EXEC SQL WHENEVER SQLERROR DO sql_error( "vInsertLogInput() failed. Could not add the log_input record" );

2) I am using the following code in my sql_error() function to print out error information if an embedded SQL statement fails. ...
// Get Oracle error message.

sqlglm( ora_err_msg, &err_ret_len, &err_ret_len );

// Get SQL statement text.

sqlgls( sql_stmt, &sql_ret_len, &sql_func_code ); .
sprintf( err_msg, "Oracle error. Reason:%.*s. At column %ld in the SQL statement. SQL statement: %.*s. SQL function code %ld.\n", err_ret_len,

            ora_err_msg,
            sqlca.sqlerrd[ 4 ] + 1,     // change to be relative to 1.
            sql_ret_len,
            sql_stmt,
            sql_func_code
            );

3) This produces the following output error message if, i.e. the field src_agency has a length of 50 characters, but is defined as a VARCHAR2(25) in the database, and the code tries to add this row.

vInsertLogInput() failed. Could not add the log_input record.:Oracle error. Reason:ORA-01401: inserted value too large for column. At column 1 in the SQL statement. SQL statement: insert into

log_input(add_date,app_id,content_type,entry_type,src_agency,src_device_id,s
rc_device_type,src_group,src_interface_id,src_user_id,src_unit,msg_text_id,s
eq_nbr,dst_agency,dst_device_id,dst_device_type,dst_group,dst_interface_id,d
st_user_id,dst_unit,Version) values
(TO_DATE(:b0,'YYYYMMDDHH24MISS'),:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,lo g_msg_text_seq.currval ,log_msg_seq.nextval ,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18). SQL function code 3. Received on Tue Nov 23 1999 - 22:58:59 CET

Original text of this message