nigelbogle
asked on
WEIRD!!! Problems when inserting and updating
This is causing me an awful amount of hastle!!!! I have a delphi program which talks to a SQL SERVER database over an ODBC link.... so far so good! ...... The program interacts with the user and, depending on changes made, creates a few SQL statements which run as one transaction.
It gets a bit tricky here but, what follows are the statements I'm trying to run as ONE transaction.
DELETE FROM COLLECTIONS WHERE ID = 24
DELETE FROM DELIVERY WHERE ID = 24
DELETE FROM LOADS WHERE LOAD_NUMBER = '4'
UPDATE dbo.DIARY SET LORRY_ID = 1, DRIVER_ID = 0, TEXT = 'Hello World', DATE = '12/08/98' WHERE ID = 3
DECLARE @@MYLOADID INTEGER
INSERT INTO LOADS (LOAD_NUMBER, TRAILER_ID, TYPE_OF_GOODS, COMPLETED, CUSTOMER_ID, DIARY_ID) VALUES ('4', 0, 'COMPUTERS', 'N', 2, 3)
SELECT @@MYLOADID = @@IDENTITY
INSERT INTO DELIVERY (LOAD_ID, ADDRESS_ID, DATE, DELIVERED, DIARY_ID) VALUES (@@MYLOADID, 1, '12/08/98', 'N',3)
INSERT INTO COLLECTIONS (LOAD_ID, ADDRESS_ID, DATE) VALUES (@@MYLOADID, 3, '12/08/98')
UPDATE DELIVERY SET DELIVERED = 'Y' WHERE (LOAD_ID = (SELECT ID FROM LOADS WHERE LOAD_NUMBER = '4')) AND (ADDRESS_ID = 1)
Now, just so you know there is a certain amount of referential integrity in the database BUT nothing which should cause the problem I'm having.
At the time these statements are run the relevant records are available in the database for deletion, update, etc.
The PROBLEM (now we get to it) is this. Before I run these statements there IS some text in the 'TEXT' column in the DIARY table (Let's say "HERE IS THE TEXT") but when I run the above statements in this order the text gets deleted instead of UPDATED to "Hello World".
If I run the Update statement on its own it works BUT when I run the statements in the above order it dosen't. I DON'T get any error message..
Can some genius help???? PLEASE! before I go round the twist..
Thanks
Nigel.
It gets a bit tricky here but, what follows are the statements I'm trying to run as ONE transaction.
DELETE FROM COLLECTIONS WHERE ID = 24
DELETE FROM DELIVERY WHERE ID = 24
DELETE FROM LOADS WHERE LOAD_NUMBER = '4'
UPDATE dbo.DIARY SET LORRY_ID = 1, DRIVER_ID = 0, TEXT = 'Hello World', DATE = '12/08/98' WHERE ID = 3
DECLARE @@MYLOADID INTEGER
INSERT INTO LOADS (LOAD_NUMBER, TRAILER_ID, TYPE_OF_GOODS, COMPLETED, CUSTOMER_ID, DIARY_ID) VALUES ('4', 0, 'COMPUTERS', 'N', 2, 3)
SELECT @@MYLOADID = @@IDENTITY
INSERT INTO DELIVERY (LOAD_ID, ADDRESS_ID, DATE, DELIVERED, DIARY_ID) VALUES (@@MYLOADID, 1, '12/08/98', 'N',3)
INSERT INTO COLLECTIONS (LOAD_ID, ADDRESS_ID, DATE) VALUES (@@MYLOADID, 3, '12/08/98')
UPDATE DELIVERY SET DELIVERED = 'Y' WHERE (LOAD_ID = (SELECT ID FROM LOADS WHERE LOAD_NUMBER = '4')) AND (ADDRESS_ID = 1)
Now, just so you know there is a certain amount of referential integrity in the database BUT nothing which should cause the problem I'm having.
At the time these statements are run the relevant records are available in the database for deletion, update, etc.
The PROBLEM (now we get to it) is this. Before I run these statements there IS some text in the 'TEXT' column in the DIARY table (Let's say "HERE IS THE TEXT") but when I run the above statements in this order the text gets deleted instead of UPDATED to "Hello World".
If I run the Update statement on its own it works BUT when I run the statements in the above order it dosen't. I DON'T get any error message..
Can some genius help???? PLEASE! before I go round the twist..
Thanks
Nigel.
ASKER
Thanks cognition for the answer I appreciate your swift response but the statements are the problem not the method of getting the text in and out.
The SQL statements are run using a TQuery under transaction control. All the statements should execute or there will be a rollback. The statements seem to execute OKAY due to the fact that the correct data is in the tables to allow them to do so but when I look in the TEXT COLUMN of the DIARY TABLE, the COLUMN has been cleared completely when it should show the text "Hello World"
Thanks anyway.
Nigel.
The SQL statements are run using a TQuery under transaction control. All the statements should execute or there will be a rollback. The statements seem to execute OKAY due to the fact that the correct data is in the tables to allow them to do so but when I look in the TEXT COLUMN of the DIARY TABLE, the COLUMN has been cleared completely when it should show the text "Hello World"
Thanks anyway.
Nigel.
Have you tried breaking the Delete statements out to their own transaction?
The problem may be in the transaction caching.
Hope this helps.
Thanks
Jeff
The problem may be in the transaction caching.
Hope this helps.
Thanks
Jeff
ASKER
Jeff,
I tried separating the delete part into its own section but still NO joy!!! The Text column is still blank?????
Thanks all the same. I'm grateful for the effort.
Nigel
I tried separating the delete part into its own section but still NO joy!!! The Text column is still blank?????
Thanks all the same. I'm grateful for the effort.
Nigel
try to change the update of diary table with this statements:
DECLARE @@ptrval varbinary(16)
SELECT @@ptrval = TEXTPTR(text)
FROM diary
WHERE ID = 3
WRITETEXT diary.text @@ptrval 'hello world'
I hope will solve your problem
DECLARE @@ptrval varbinary(16)
SELECT @@ptrval = TEXTPTR(text)
FROM diary
WHERE ID = 3
WRITETEXT diary.text @@ptrval 'hello world'
I hope will solve your problem
ASKER
mhamra,
Thank-you for your answer..... This problem is proving difficult I think even for those with some know-how.... I tried what you suggested but it didn't work the column is still blank...
I get the feeling that the effect is to do with the other statements surrounding the set of statements....
But that's only a guess...
Thanks anyway mhamra! Keep trying everyone.
Thank-you for your answer..... This problem is proving difficult I think even for those with some know-how.... I tried what you suggested but it didn't work the column is still blank...
I get the feeling that the effect is to do with the other statements surrounding the set of statements....
But that's only a guess...
Thanks anyway mhamra! Keep trying everyone.
Don't suppose there are any triggers on LOADS, DELIVERY or COLLECTIONS?
ASKER
No, Sorry....... This one really is proving really difficult for everybody!!!
Thanks for the try...
Nigel.
Thanks for the try...
Nigel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look in books online.
In VB there are methods such as getchunk, and appendchunk for dealing with Text and Image data. There are probably equivalents in Delphi.