Board index » delphi » Blob's - Cached Updates - Trigger's

Blob's - Cached Updates - Trigger's

We have developped a Delphi 3.0 (maintenance packs installed) application
which uses cached updates to access a MS SQL 6.5 (service pack 4 installed)
database.
The application can insert a new record that has all kinds of fields
including a blob. Another field called ItemID (int) is set to 0. The table
on the server has a trigger that will create a unique ID and update the
ItemID field. Everything works fine on our server, but when we try to run
it against a server located at one of our customers we get the error
"Record/Key" deleted when we insert a record that has a blob that is not
empty. If the blob is empty the problem does not occure.

We have used SQL monitor to log the sql commands between the program and
the server and we have noticed the following important differences:

1) It seems that our server is storing both the "normal" and the blob data
in a single statement (our server - line 27). The other server seems to
require more statements (customer server - lines 36 to 67).
2) As far as we can see it is line 67 (customer server) that causes the
problem. It is trying to select a record with an ItemID value that matches
the value originally supplied to the SQL server. However this value is
modified by the trigger and cannot be found by SQL server. This is why we
get the Record/Key deleted error.

Important notes:
-----------------------
As far as we can see the SQL software and NT Server software on both
servers is identical.
We have connected to both servers from the same Workstation using the same
program, BDE, IDAPI, etc...
Our Workstation is connected to our server with a network (TCP/IP) and we
connecting to our customer using a RAS connection.
Is this a timing problem? Can this be a server property? Can we solve this
using another trigger or stored procedure?
All suggestions are welcome!!!

Kind regards

Alain Sienaert
e-mail: sienaert.cor...@skynet.be

Our Server
========

16      17:16:04  SQL Prepare: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID )  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
17      17:16:04  SQL Blob IO: MSSQL - Return blob data
18      17:16:04  SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
19      17:16:04  SQL Data In: MSSQL - Param = 2, Name = Number, Type =
fldZSTRING, Precision = 25, Scale = 0, Data = CAFD11
20      17:16:04  SQL Data In: MSSQL - Param = 3, Name = Title, Type =
fldZSTRING, Precision = 80, Scale = 0, Data = Test
21      17:16:04  SQL Data In: MSSQL - Param = 4, Name = Status, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = Pending
22      17:16:04  SQL Data In: MSSQL - Param = 5, Name = ProjectLeaderID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000002
23      17:16:04  SQL Data In: MSSQL - Param = 7, Name = BusinessSegmentID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000012
24      17:16:04  SQL Data In: MSSQL - Param = 8, Name = Archive, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
25      17:16:04  SQL Data In: MSSQL - Param = 9, Name = LastUpdate, Type =
fldTIMESTAMP, Precision = 0, Scale = 0, Data = 1/14/1998 17:15:59:3000000
26      17:16:04  SQL Data In: MSSQL - Param = 10, Name = ServerID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 1
27      17:16:04  SQL Execute: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID )  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
54      17:16:04  SQL Stmt: MSSQL - Close
55      17:16:04  SQL Transact: MSSQL - XACT Commit
56      17:16:04  SQL Prepare: MSSQL -  COMMIT TRAN
57      17:16:04  SQL Execute: MSSQL -  COMMIT TRAN
64      17:16:04  SQL Stmt: MSSQL - Close
65      17:16:04  SQL Stmt: MSSQL - Close

Customer Server
===============

16      15:00:59  SQL Prepare: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID )  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
17      15:00:59  SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
18      15:00:59  SQL Data In: MSSQL - Param = 2, Name = Number, Type =
fldZSTRING, Precision = 25, Scale = 0, Data = CAFDTN16
19      15:00:59  SQL Data In: MSSQL - Param = 3, Name = Title, Type =
fldZSTRING, Precision = 80, Scale = 0, Data = Test
20      15:00:59  SQL Data In: MSSQL - Param = 4, Name = Status, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = Pending
21      15:00:59  SQL Data In: MSSQL - Param = 5, Name = ProjectLeaderID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000001
22      15:00:59  SQL Data In: MSSQL - Param = 7, Name = BusinessSegmentID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 1000012
23      15:00:59  SQL Data In: MSSQL - Param = 8, Name = Archive, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
24      15:00:59  SQL Data In: MSSQL - Param = 9, Name = LastUpdate, Type =
fldTIMESTAMP, Precision = 0, Scale = 0, Data = 1/14/1998 15:0:40:693000000
25      15:00:59  SQL Data In: MSSQL - Param = 10, Name = ServerID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 2
26      15:00:59  SQL Transact: MSSQL - XACT Begin
27      15:00:59  SQL Prepare: MSSQL -  SAVE TRAN BODAPI
28      15:00:59  SQL Execute: MSSQL -  SAVE TRAN BODAPI
35      15:00:59  SQL Stmt: MSSQL - Close
36      15:00:59  SQL Execute: MSSQL - INSERT INTO projects (ItemID ,Number
,Title ,Status ,ProjectLeaderID ,TeamMembers ,BusinessSegmentID ,Archive
,LastUpdate ,ServerID )  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
63      15:01:00  SQL Stmt: MSSQL - Close
64      15:01:00  SQL Prepare: MSSQL - SELECT TeamMembers FROM projects
WHERE ItemID=:1
65      15:01:00  SQL Data In: MSSQL - Param = 1, Name = ItemID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 0
66      15:01:00  SQL Misc: MSSQL - Set statement type
67      15:01:00  SQL Execute: MSSQL - SELECT TeamMembers FROM projects
WHERE ItemID=:1
74      15:01:00  SQL Blob IO: MSSQL - Return blob data
75      15:01:00  SQL Stmt: MSSQL - Fetch
78      15:01:00  SQL Stmt: MSSQL - EOF
79      15:01:00  SQL Stmt: MSSQL - Close
80      15:01:00  SQL Blob IO: MSSQL - Start Store
81      15:01:00  SQL Prepare: MSSQL -  ROLLBACK TRAN BODAPI
82      15:01:00  SQL Execute: MSSQL -  ROLLBACK TRAN BODAPI
89      15:01:00  SQL Stmt: MSSQL - Close

 

Re:Blob's - Cached Updates - Trigger's


On 14 Jan 1998 17:57:39 GMT, "Alain Sienaert"

Quote
<sienaert.cor...@skynet.be> wrote:
> Can we solve this
>using another trigger or stored procedure?
>All suggestions are welcome!!!

>Kind regards

>Alain Sienaert
>e-mail: sienaert.cor...@skynet.be

I always use a stored proc to generate unique id's. I think a stored
proc would solve this problem, and has other adantages as well, like
knowing the new id in the app on record inserts.
In the stored proc, use a generator, have this value returned to the
app, and insert it in the new record using the afterinsert event.

Jan den Ouden

E:mail denOu...@compuserve.com
Wezep - Netherlands

Other Threads