ClientDataSet and IBX "Key violation"
Thomas Vedel
Using ClientDataSet and IBX against an IB6 SQL dialect 3 database raises
exception EDBClient with message 'Key violation' when inserting records in
the ClientDataSet. The exception is raised by the ClientDataSet itself,
without trying to ApplyUpdates.
We are using Delphi 7 with IBX update 7.05 - but the error also exists when
using IBX update 7.04. When using the IBX components directly without using
ClientDataSets everything works fine. When using ClientDataSets without
using IBX everything works fine too.
When using Delphi 5, everything is working fine, but this is not an option.
We have been using the IBX components for a very long time, and have never
had these errors before, but earlier we used SQL dialect 1...
Any ideas or a workaroud? Any input will be greatly appreciated; we are a
bit desperate... ;-)
Best regards
Thomas Vedel
email veco at veco dot dk
Bill Todd
record? How are you generating the key? Do you get the error when you
insert the first row in the CDS or when you insert the second?
--
Bill (TeamB)
(TeamB cannot respond to questions received via email)
Thomas Vedel
I am assigning an unique values to the primary key of each record. On the
"OnNewRecord" event of the ClientDataSet is called a stored procedure which
returns the next value of the generator associated with the primary key of
the table. This works OK - the primary key field is populated with a
"proper" value at the same moment when a new record is added to the
ClientDataSet (verified by connecting the ClientDataSet to a DBGrid)
I normally do not get an error when insertning the first row in the
ClientDataSet, but the second insert fails. The number of records already in
the dataset prior to the insertion has no influence on the error. I have
tried calling ApplyUpdates / RefreshRecord on the OnAfterPost event, but it
doesn't matter either.
Best regards, and thank you for your support !
Thomas
"Bill Todd" <bi...@notthis.dbginc.com> wrote in message
news:hata4v4hasest1nm5...@4ax.com...
Bill Todd
DataSetProvider? Is the provider's ResolveToDataSet property set to
true?
Bill Todd
Thomas Vedel
tried using IBDataSet components, but I am using parameterized queries, and
it seems that parameters are not transferred correctly between the servers
remote datamodule and the client. I am using a socket connection between
server and client application.
I have tried setting the providers ResolveToDataSet property to true as well
as false (I normally set it to true so that the IBUpdateSQL statements are
used - that way can I better control how updates are applied).
- Thomas
"Bill Todd" <bi...@notthis.dbginc.com> wrote in message
news:k76b4vgcbenml5qh0...@4ax.com...
Thomas Vedel
NUMERIC(18,0)
- Thomas
"Bill Todd" <bi...@notthis.dbginc.com> wrote in message
news:6a6b4vo5gb80osjap...@4ax.com...
Bill Todd
change the primary key to an integer your problem will likely
disappear.
Thomas Vedel
In most circumstances your suggestion would be acceptable, but it is not a
good solution in this single project.
The solution will be installed in around 50 different sites (possibly more
to come), each site having its own database and app-server, and we have
decided that each database installation will be using its own interval for
assigning ID's. In this way there will never be "collisions" in the ID's,
and hence it will be simple to collect consolidate data centrally without
the need to handle "ID-collisions".
Unfortunately the logical solution based on only *one* centrally placed
database / appserver using DataSnap / internet as the transport layer for
data packages is not suitable, since only a few of the decentrally placed
sites have internet connections.
We know we might create composite primary keys instead of using a single
field as primary key, but this will involve a not trivial redesign of the
database. If you have any possible suggestion for making ClientDataSets
handle 64 bit integeres better, we would be very gratefull hearing about it.
Thank you very much again for your support. The usability of the borland
foras has again proved to be extremely high !
- Thomas
"Bill Todd" <bi...@notthis.dbginc.com> wrote in message
news:i8db4v8k48r16oj4t...@4ax.com...
Bill Todd
>to come), each site having its own database and app-server, and we have
>decided that each database installation will be using its own interval for
>assigning ID's. In this way there will never be "collisions" in the ID's,
>and hence it will be simple to collect consolidate data centrally without
>the need to handle "ID-collisions".
Probably too late now, but a GUID primary key might be an easier
solution.
>We know we might create composite primary keys instead of using a single
>field as primary key, but this will involve a not trivial redesign of the
>database. If you have any possible suggestion for making ClientDataSets
>handle 64 bit integeres better, we would be very gratefull hearing about it.
Sorry, I do not.
Thomas Vedel
> Probably too late now, but a GUID primary key might be an easier
> solution.
>
it is the quickest solution to implement (we are very short of time), and we
think that we will not be "short of" ID-values within the next few years.
I know that some people like GUIDs as primary keys, but you can never
*guarantee* that there will never be collisions
But I can think of an alternative solutions, if one still like to use 64 bit
integers as keys (together with ClientDataSets): Instead of using the value
directly I think it could be packed into a CHAR(8) CHARACTER SET NONE field
(using a stored procedure or UDF) since ClientDataSets handles this
fieldtype correctly.
Best regards
Thomas
Thomas Vedel
Latest news: We have found a very stange solution to the problem.
Removing the pfInKey providerflag from the primary key field, setting
ResolveToDataSet = false and keeping UpWhereKeyOnly solves the problem... I
guess that we will re-introduce the 64 bit keyfields to the database... :-)
Best regards
Thomas
"Thomas Vedel" <sp...@filter.on> skrev i en meddelelse
news:3e45...@newsgroups.borland.com...