Database unique ID duplication problem
Dodgy
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
ap
>
> 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?
> 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?
> 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]);
> 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
Dodgy
on about something:
>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.
>> 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.
>> 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.
>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.
>> 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.
>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
Grent
>
> 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
It seems that the table you are inserting into has an identity as the
primary key. Behind the scenes, ADO uses @@IDENTITY to return the identity
that was inserted, into the TField linked to it, but it will return the
last identity inserted as a result of your insert. If you have some sort
of trigger on the table in question, and that trigger inserts into another
table that also has an identity, you'll get this value back! E.g. If a
trigger is inserting into some sort of auditing table. So I suspect this
is what is happening in your case.
> 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.
ADO should really use Scope_Identity() or Ident_current('THE_TABLE_NAME')
to return the correct identity. So you'll have to do this yourself and use
this as the link ID instead.
Hopefully your head isn't done in anywmore.
Grent