Thread: Error messages not always reported through the ODBC driver - STATEMENT ERROR missing

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

Hi Kristis,

Kristis Makris wrote:
>
> 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:

The error seems to have occured at SQLTransact().
Probbably you should catch the error at CommitTrans() not at
ExecuteSQL().

>
> 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'"

Hmm strange, what does Control Panel show ?

regards,
Hiroshi Inoue

Hi Hiroshi,

Thanks for looking into this.

> > 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:
>
> The error seems to have occured at SQLTransact().
> Probbably you should catch the error at CommitTrans() not at
> ExecuteSQL().

Good observation, that makes perfect sense. I now modified my source so
that the CommitTrans() is called within the try block. Here's the exact
source:



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

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

     db.ExecuteSQL(strStmt);

     if (db.CanTransact())
         db.CommitTrans();

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

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

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

 return TRUE;

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

However, I'm still  unable to capture an exception. Program execution
goes past the ExecuteSQL(), past the CommitTrans(), and skips over the
catch block. Isn't that bizzare?

Here's the error message I'm now getting in the logs (still no STATEMENT ERROR):

******************************************************************************************
conn=33460928, query='BEGIN'
conn=33460928, query='SELECT Vendor_Remove(1)'
    [ fetched 1 rows ]
conn=33460928, 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=33479888, conn=33460928, status=1, num_stmts=16
            sock=33479928, stmts=33475656, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=512, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=33467416, buffer_out=33471536
            buffer_filled_in=162, buffer_filled_out=0,
buffer_read_in=162
conn=33460928, SQLDisconnect


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



> >
> > 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'"
>
> Hmm strange, what does Control Panel show ?


The Control Panel reports that I'm using the 7.01.00.06 version of the
driver. I'm sure that I'm not using the 7.01.00.05 version, since the
.05 had a problem with reporting correctly the NOTICEs reported by the
backend. I do not have that problem with the current driver I'm using
(.06?). I had installed the .06 version using the installation utility
provided.

What version do you guys get reported in your log files?


Thanks,
-Kristis

Hello Again,

> > 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:
>
> The error seems to have occured at SQLTransact().
> Probbably you should catch the error at CommitTrans() not at
> ExecuteSQL().
>

I've also tried the following from the psql command-line client:

******************************************************************************************
wats=# begin;
BEGIN
wats=# select vendor_remove(1);
ERROR:  <unnamed> referential integrity violation - key in vendor still
referenced from vendorforwellagreement
wats=# commit;
COMMIT
******************************************************************************************

psql reports the error when I issue the SELECT statement, not when I
issue the commit. Shouldn't I be able to capture an exception when I do
an ExecuteSQL() from VC++, instead of a CommitTrans() ?

Thanks,
-Kristis

---------------------------------------------------------------------
Kristis Makris                          Datasoft Corporation
kristis.makris@datasoft.com             http://www.datasoft.com
---------------------------------------------------------------------

Kristis Makris wrote:
>
> Hello Again,
>
> I've also tried the following from the psql command-line client:
>
> ******************************************************************************************
> wats=# begin;
> BEGIN
> wats=# select vendor_remove(1);
> ERROR:  <unnamed> referential integrity violation - key in vendor still
> referenced from vendorforwellagreement
> wats=# commit;
> COMMIT
> ******************************************************************************************
>
> psql reports the error when I issue the SELECT statement, not when I
> issue the commit. Shouldn't I be able to capture an exception when I do
> an ExecuteSQL() from VC++, instead of a CommitTrans() ?

Could you show me a reproducible example ?

regards,
Hiroshi Inoue

Hi Hiroshi,


> > ******************************************************************************************
> > wats=# begin;
> > BEGIN
> > wats=# select vendor_remove(1);
> > ERROR:  <unnamed> referential integrity violation - key in vendor still
> > referenced from vendorforwellagreement
> > wats=# commit;
> > COMMIT
> > ******************************************************************************************
> >
> > psql reports the error when I issue the SELECT statement, not when I
> > issue the commit. Shouldn't I be able to capture an exception when I do
> > an ExecuteSQL() from VC++, instead of a CommitTrans() ?
>
> Could you show me a reproducible example ?


I'm attaching two .sql files. Using psql, load the file "tables.sql" in
a fresh database. Then try to execute the commands in the "data.sql"
file. You should get the following error message:


test=# \i data.sql
BEGIN
psql:data.sql:2: ERROR:  <unnamed> referential integrity violation - key
in tableb still referenced from tablea
COMMIT


If you try to issue the commands included in the "data.sql" file through
ODBC using VC++ (instead of psql) you will run across the case where an
exception cannot be captured.

Thanks,
-Kristis

Hello again,


> > >
******************************************************************************************
> > > wats=# begin;
> > > BEGIN
> > > wats=# select vendor_remove(1);
> > > ERROR:  <unnamed> referential integrity violation - key in vendor
still
> > > referenced from vendorforwellagreement
> > > wats=# commit;
> > > COMMIT
> > >
******************************************************************************************
> > >
> > > psql reports the error when I issue the SELECT statement, not when
I
> > > issue the commit. Shouldn't I be able to capture an exception when
I do
> > > an ExecuteSQL() from VC++, instead of a CommitTrans() ?
> >
> > Could you show me a reproducible example ?

Ok, I've managed to reproduce the bug. I'm attaching a .zip file that
contains a VC++ project that exhibits the bug along with a few sql files
needed to load the DB schema and some sample data. In order to load the
database, the file "SQL_Files\add_all.sql" needs to be loaded through
the psql client:

test=# \i add_all.sql

Hope this helps,
-Kristis

On 27 Aug 2001 09:18:16 -0700, Kristis Makris wrote:
> Hi Hiroshi,
>
>
> > > ******************************************************************************************
> > > wats=# begin;
> > > BEGIN
> > > wats=# select vendor_remove(1);
> > > ERROR:  <unnamed> referential integrity violation - key in vendor still
> > > referenced from vendorforwellagreement
> > > wats=# commit;
> > > COMMIT
> > > ******************************************************************************************
> > >
> > > psql reports the error when I issue the SELECT statement, not when I
> > > issue the commit. Shouldn't I be able to capture an exception when I do
> > > an ExecuteSQL() from VC++, instead of a CommitTrans() ?
> >
> > Could you show me a reproducible example ?
>
>
> I'm attaching two .sql files. Using psql, load the file "tables.sql" in
> a fresh database. Then try to execute the commands in the "data.sql"
> file. You should get the following error message:
>
>
> test=# \i data.sql
> BEGIN
> psql:data.sql:2: ERROR:  <unnamed> referential integrity violation - key
> in tableb still referenced from tablea
> COMMIT
>
>
> If you try to issue the commands included in the "data.sql" file through
> ODBC using VC++ (instead of psql) you will run across the case where an
> exception cannot be captured.
>


I think I'd like to take that back. I've tried to create a VC++ project
that makes use of these two .sql files and tables, but I'm unable to
reproduce the bug. I'll make sure that I have put together both a VC++
project and a DB schema that exhibit the bug before I send anything
else.

Thanks for your patience,
-Kristis

Kristis Makris wrote:
>
> Hi Hiroshi,
>
> Thanks for looking into this.
>
> > > 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:
> >
> > The error seems to have occured at SQLTransact().
> > Probbably you should catch the error at CommitTrans() not at
> > ExecuteSQL().
>
> Good observation, that makes perfect sense. I now modified my source so
> that the CommitTrans() is called within the try block. Here's the exact
> source:
>
> ******************************************************************************************
>         try
>         {
>          if (db.CanTransact())
>                  db.BeginTrans();
>
>          strStmt = "SELECT Vendor_Remove(";
>          strStmt += LongToString(lID);
>          strStmt += ")";
>
>          db.ExecuteSQL(strStmt);
>
>          if (db.CanTransact())
>                  db.CommitTrans();
>
>         }
>         catch (CDBException* pEx)
>         {
>          if (db.CanTransact())
>                  db.Rollback();
>
>          pEx->ReportError();
>          pEx->Delete();
>
>         AfxMessageBox(_T("Record (Vendor) Delete Failed!"));
>         return FALSE;
>         }
>
>  return TRUE;
>
> ******************************************************************************************
>
> However, I'm still  unable to capture an exception. Program execution
> goes past the ExecuteSQL(), past the CommitTrans(), and skips over the
> catch block. Isn't that bizzare?

I found that CommitTrans() doesn't throw an exception.
You may have to check the return code of CommitTrans().

Too late detection of an error is another issue.
I'm examining it.

regards,
Hiroshi Inoue

I wrote:
>
> Kristis Makris wrote:

[snip]

> >
> > However, I'm still  unable to capture an exception. Program execution
> > goes past the ExecuteSQL(), past the CommitTrans(), and skips over the
> > catch block. Isn't that bizzare?
>
> I found that CommitTrans() doesn't throw an exception.
> You may have to check the return code of CommitTrans().

Note that *commit* could cause an ERROR in reality.

>
> Too late detection of an error is another issue.
> I'm examining it.

Probably I found the cause. I would commit the fix
to cvs in a day or so.

regards,
Hiroshi Inoue

> > > 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'"
> >
> > Hmm strange, what does Control Panel show ?
>
>
> The Control Panel reports that I'm using the 7.01.00.06 version of the
> driver. I'm sure that I'm not using the 7.01.00.05 version, since the
> .05 had a problem with reporting correctly the NOTICEs reported by the
> backend. I do not have that problem with the current driver I'm using
> (.06?). I had installed the .06 version using the installation utility
> provided.
>
> What version do you guys get reported in your log files?

Does anybody else get version 07.01.0005 reported in the driver logs
while running the 7.01.0006 version?

-Kristis