Getting SQL error details in Pro C
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:
- There is no way to have Oracle indicate which field in the SQL statement caused the error.
- 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.:
- 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,dst_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