Error messages not always reported through the ODBC driver - STATEMENT ERROR missing - Mailing list pgsql-odbc

Hello everyone,

I'm experiencing a problem receiving error messages correctly from
postgres. I'm running PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by
GCC egcs-2.91.66, using the 7.01.00.06 ODBC driver through VC++. In
particular, I'm receiving a CONN ERROR but no STATEMENT ERROR, with the
effect of not being able to capture an exception for it, since no
exception is raised at all.

The error message results from constraints placed on backend tables
during table definition that forbid removing a tuple from one table if
that tuple is referenced from a different tuple in another table
(using the ON DELETE RESTRICT clause). Here's the definition of the
table from which a tuple is attempted to be removed:

create table Vendor (
ID            int  primary key
                   references ContactInfo (ID)
                              on update cascade
                              on delete restrict,
VendorNumber  text
);


Here's is a copy of the log message:


************************************************************************************
conn=33461048, SQLDriverConnect( in)='DSN=wats;UID=cathy;PWD=cathy;',
fDriverCompletion=0
DSN info:
DSN='wats',server='mayhem',port='5712',dbase='wats',user='cathy',passwd='cathy'
          onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
          conn_settings=''
          translation_dll='',translation_option=''
Global Options: Version='07.01.0005', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=0,
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=1,
bools_as_char=0
                extra_systable_prefixes='dd_;', conn_settings=''
conn=33461048, query=' '
conn=33461048, query='set DateStyle to 'ISO''
conn=33461048, query='set geqo to 'OFF''
conn=33461048, query='set ksqo to 'ON''
conn=33461048, query='select oid from pg_type where typname='lo''
    [ fetched 0 rows ]
conn=33461048, query='select version()'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.2 on
i686-pc-linux-gnu, compiled by GCC egcs-2.91.66' ]
    [ PostgreSQL version number = '7.1' ]
conn=33461048,

SQLDriverConnect(out)='DSN=wats;DATABASE=wats;SERVER=mayhem;PORT=5712;UID=cathy;PWD=cathy;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=33461048, query='BEGIN'
conn=33461048, query='SELECT Vendor_Remove(1)'
    [ fetched 1 rows ]
conn=33461048, query='COMMIT'
ERROR from backend during send_query: 'ERROR:  <unnamed> referential
integrity violation - key in vendor still referenced from
vendorforwellagreement'
CONN ERROR: func=SQLTransact, desc='', errnum=110, errmsg='ERROR:
<unnamed> referential integrity violation - key in vendor still
referenced from vendorforwellagreement'
            ------------------------------------------------------------
            henv=33480008, conn=33461048, status=1, num_stmts=16
            sock=33480048, stmts=33480120, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=508, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=33467536, buffer_out=33471656
            buffer_filled_in=162, buffer_filled_out=0,
buffer_read_in=162
conn=33461048, SQLDisconnect

************************************************************************************

I'm comparing this log to a log that reports an error that does raise an
exception. This exception was raised when a tuple with a duplicate
unique field was attempted to be entered in the table.



************************************************************************************
conn=29178144, query='BEGIN'
conn=29178144, query='SELECT RRAParcel_iou('rra111', '', 2, 900.00,
89.00)'
ERROR from backend in next_tuple: 'ERROR:  Cannot insert a duplicate key
into unique index rraparcel_pkey
'
conn=29178144, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'
                 ------------------------------------------------------------
                 hdbc=29178144, stmt=29171880, result=0
                 manual_result=0, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=0, parameters_allocated=0
                 statement_type=0, statement='SELECT
RRAParcel_iou('rra111', '', 2, 900.00, 89.00)'
                 stmt_with_params='SELECT RRAParcel_iou('rra111', '', 2,
900.00, 89.00)'
                 data_at_exec=-1, current_exec_param=-1, put_data=0
                 currTuple=-1, current_col=-1, lobj_fd=-1
                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
                 cursor_name='SQL_CUR01BD20A8'
                 ----------------QResult Info
-------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=109, errmsg='ERROR:  Cannot
insert a duplicate key into unique index rraparcel_pkey
'
            ------------------------------------------------------------
            henv=29171480, conn=29178144, status=1, num_stmts=16
            sock=29171504, stmts=29171560, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=492, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=29184616, buffer_out=29188720
            buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
conn=29178144, SQLDisconnect
************************************************************************************




The major difference I'm seeing between the two logs is the "STATEMENT
ERROR" block of the log being present in cases where I'm able to capture
an exception, but not present in the cases where I cannot do so. Again,
I only have this problem when reveiving error messages resulting from
integrity constraints being violated with the ON DELETE RESTRICT clause
in a table's definition.


I'm trying to capture an exception in a try/catch block. Program
execution always goes past the catch block; no exception is raised.


************************************************************************************
        try
        {
         if (db.CanTransact())
                 db.BeginTrans();

         strStmt = "SELECT Vendor_Remove(";
         strStmt += LongToString(lID);
         strStmt += ")";

         db.ExecuteSQL(strStmt);

        }
        catch (CDBException* pEx)
        {
         if (db.CanTransact())
                 db.Rollback();

         pEx->ReportError();
         pEx->Delete();

        AfxMessageBox(_T("Record (Vendor) Delete Failed!"));
        }
************************************************************************************


Is this normal behaviour by the driver?


Incidentally, I have a couple more questions about the driver in
general:

1) I've noticed that the 7.01.00.06 driver reports in the logs that it
is a 7.01.0005 driver. Is this nomral? Is this a known bug?

   "Global Options: Version='07.01.0005'"

2) I've been unable so far to use certain Microsoft Controls within VC++
to correctly display the values of BOOL fields in a more meaningful
format. I've been mainly trying to use the DBGrid Control, and feed to
it data coming out of a view. Certain BOOL fields of that view should be
displayed in the Yes/No or True/False format that the control allows,
but the control always fails to translate that BOOL value to one of the
previously mentioned display formats. Has there been any success in
achieving this? Is there some configuration that can be done on the
driver to allow that? I've been playing with the "Advanced Options" tab
of the driver and checking/unchecking the "Bools as Char" option, but
still had no success. Any insight on this?


Thanks for all your help,
-Kristis