 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Dodgy
Guest
|
 Posted: Tue Feb 06, 2007 7:27 pm Post subject: Database unique ID duplication problem |
 |
|
Hi,
I'm not sure if this is a Delphi problem, or some "quirk" of MS SQL
server 2005, but it's really causing me agro, so if anyone has ever
seen this, and more importantly solved it, I would be very grateful!
I have 2 tables on an MS SQL Server 2005 database. I connect using a
TADOConnection
ConnectionString:Provider=SQLOLEDB.1;Password=*****;Persist Security
Info=True;User ID=*****;Initial Catalog=my_database;Data
Source=my_server_ip
And a TADOQuery which uses the above connection.
I insert 1 record into the first table with
adoquery1.Open;
adoquery1.Insert;
adoquery1.FieldByName('datetime').AsDateTime:=now;
adoquery1.FieldByName('zone').asstring:=Request.ContentFields.values['idcode'];
adoquery1.FieldByName('type').asstring:='reglist';
adoquery1.FieldByName('server').asstring:=copy(LocalIP,1,49);
adoquery1.FieldByName('PA_id').asstring:=''; // TBA, maybe
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
And then I insert some other records into the 2nd table, which I link
to the first table with the "id" field I log above.
The problem is every now and then the id field comes back with
something really out of sequence, and when I look in the database it's
not the id that is associated with the record I just made!
The output of my log procedure shows things like
Link ID 222019
Link ID 222020
Link ID 9719
Link ID 222022
Link ID 222023
Sure enough, when I try to find 9719 in the database, it either
doesn't exist, or it's a really old record from days ago. If I look at
222021 (which I don't have in my log), sure enough I find the record I
inserted, which was reported as being 9719.
Any ideas? It's really doing my head in.
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
 |
ap
Guest
|
| Posted: Wed Feb 07, 2007 4:57 am Post subject: Re: Database unique ID duplication problem |
|
|
Dodgy wrote:
| Quote: |
adoquery1.FieldByName('PA_id').asstring:=''; // TBA, maybe
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
|
It's not easy to understand code written by someone else, and without
any comments. It took me a while to find that the Log-line is for your
own personal debugging aid only, or is it?
| Quote: | The problem is every now and then the id field comes back with
|
Where exactly is the ID strange, in your adoquery1 or adoquery2 table?
You also gave no hint where does this first adoquery1 get it's 'id'
content, AutoInc Field probably? Nor any hint how you pass the same ID
to the second adoquery2. The Log-routine you introduced probably is not
your means to do that, or is it?
| Quote: | If I look at
222021 (which I don't have in my log), sure enough I find the record I
inserted, which was reported as being 9719.
|
Then, that clearly tells that in those cases something is happening
between these two lines:
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
The cursor in ADOQuery has moved elsewhere, to some older record. This
time it happens to be 9719, and your logging routine writes it down.
Could you have forgotten some sly code in your ADOQuery1.AfterPost
event?
Or if there was nothing, then you could try haunting that something by
adding one more line to Logging code:
Log('Link ID Before Post '+adoquery1.FieldByName('id').AsString]);
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
| Quote: | Any ideas? It's really doing my head in.
|
I know the feeling:) And once you find the reason, you can't believe it
took you two whole days to catch this kind of silly typo in your own
code.
Sorry if I undestood the whole question and problem wrong, I do not use
TADO myself. Yet many of these DB and SQL problems are quite universal.
-ap |
|
| Back to top |
|
|
Dodgy
Guest
|
| Posted: Wed Feb 07, 2007 6:11 pm Post subject: Re: Database unique ID duplication problem |
|
|
On Wed, 07 Feb 2007 00:57:00 +0200, ap <ap (AT) nomail (DOT) please.com> waffled
on about something:
| Quote: | Dodgy wrote:
adoquery1.FieldByName('PA_id').asstring:=''; // TBA, maybe
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
It's not easy to understand code written by someone else, and without
any comments. It took me a while to find that the Log-line is for your
own personal debugging aid only, or is it?
|
Yes, sorry, it's just my own logging routine. Just does a few things
like check compiler param for debug being set and creates/appends to a
log file of todays date.
In this instance it might as well be showmessage.
| Quote: | The problem is every now and then the id field comes back with
Where exactly is the ID strange, in your adoquery1 or adoquery2 table?
You also gave no hint where does this first adoquery1 get it's 'id'
content, AutoInc Field probably? Nor any hint how you pass the same ID
to the second adoquery2. The Log-routine you introduced probably is not
your means to do that, or is it?
|
Sorry, yes the ID is an autoinc field in database. I didn't bother
with any of the adoquery2 code as I have already narrowed the error
down to retrieving the ID from the insert.
| Quote: | If I look at
222021 (which I don't have in my log), sure enough I find the record I
inserted, which was reported as being 9719.
Then, that clearly tells that in those cases something is happening
between these two lines:
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
The cursor in ADOQuery has moved elsewhere, to some older record. This
time it happens to be 9719, and your logging routine writes it down.
Could you have forgotten some sly code in your ADOQuery1.AfterPost
event?
|
Nope, no afterpost.
| Quote: | Or if there was nothing, then you could try haunting that something by
adding one more line to Logging code:
Log('Link ID Before Post '+adoquery1.FieldByName('id').AsString]);
adoquery1.Post;
Log('Link ID '+adoquery1.FieldByName('id').AsString]);
|
I'll give that a try, but I wasn't sure if the ID field would have
been generated at that point before the post.
| Quote: | Any ideas? It's really doing my head in.
I know the feeling:) And once you find the reason, you can't believe it
took you two whole days to catch this kind of silly typo in your own
code.
|
Even worse than that, this is somebody else's code!
Originally it was mine, then someone else vandalised it (it's the only
word for it) then they left and I get it back.
| Quote: | Sorry if I undestood the whole question and problem wrong, I do not use
TADO myself. Yet many of these DB and SQL problems are quite universal.
-ap
|
No, I think you've got the right idea re the problem. Sorry for
causing confusion with my log routine.
Thanks for the suggestions, I'll put some more logging in and see if
the ID field is more reliable before the post.
Cheers
Dodgy.
--
MUSHROOMS ARE THE OPIATE OF THE MOOSES |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
