From: Zoltan Kovacs <tip@pc10.radnoti-szeged.sulinet.hu>
To: pgsql-interfaces@postgresql.org
Subject: refint doesn't work well with BDE
Date: Tue, 4 Apr 2000 14:48:29 +0200
Content-Type: text/plain
MIME-Version: 1.0
Message-Id: <00040415192800.03150@tir>
Content-Transfer-Encoding: 8bit

Hi,

referential integrity seems to have a new behaviour in 7.0 versus 6.5.2
via the Borland Database Engine -> ODBC -> pgsql connection.

This error occurs when inserting into the foreign table an unreferenced value
or deleting from the primary table which is a referenced value (my opinion is
that none of the new refint errors can reach the BDE).

Details from ODBC log with 6.5.2:
-- cut here --
conn=53084280, query='BEGIN'
conn=53084280, query='DELETE FROM "table1" WHERE "id"=1001 AND "field1"=1015 AND "field2"=9 AND "field3"='207.1600.01' AND "field4"='1''
ERROR from backend during send_query: 'ERROR:  table1_fd1: tuple referenced in table2'
conn=53084280, query='COMMIT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
                 ------------------------------------------------------------
                 hdbc=53084280, stmt=53098820, result=0
                 manual_result=0, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=54256992, parameters_allocated=5
                 statement_type=3, statement='DELETE FROM "table1" WHERE "id"=? AND "field1"=? AND "field2"=? AND "field3"=? AND "field4"=?'
                 stmt_with_params='DELETE FROM "table1" WHERE "id"=1001 AND "field1"=1015 AND "field2"=9 AND "field3"='207.1600.01' AND "field4"='1''
                 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_CUR032A3944'
                 ----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:  table1_fd1: tuple referenced in table2'
            ------------------------------------------------------------
            henv=54199504, conn=53084280, status=1, num_stmts=16
            sock=54199520, stmts=54199568, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=148, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=53090620, buffer_out=53094720
            buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
-- cut here --

BDE stops after this.

Details from ODBC log with 7.0 (PSQLODBC.DLL has the only change which is
Thomas' one specially for the "list index" problem):

-- cut here --
conn=57606264, query='BEGIN'
conn=57606264, query='INSERT INTO "tmp" ("az" ,"hiv" )  VALUES (5, 2)'
ERROR from backend during clear: 'ERROR:  <unnamed> referential integrity violation - key referenced from tmp not found in tmp2
'
conn=57606264, query='COMMIT'
-- cut here --

BDE says nothing to this. BDE thinks that everything is all right, it changes
the table (inserts a row). After making a refresh, one can realize that no
changes were made, of course.


Could you please help?

Regards,
Zoltan