You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Alessandro Colantoni <al...@gmail.com> on 2005/06/15 13:00:39 UTC
primary key problem sql server
Hi
I'm using ojb 1.0.1 with sql server.
When I store an object, and then I delete it directly on the db I can't
insert it a second time.
Before storing I use to check if an object with the same key exists.
ojb doesn't detect any object with the same key (correctly... I deleted it)
But when storing it catch a sql server exception because primary key already
exists.
Is it a limit of microsoft sql server (it keeps primary key somewhere?) or
I'm doing something wrong?
thanks for help
Re: primary key problem sql server
Posted by Alessandro Colantoni <al...@gmail.com>.
yes I use PB api.
that's not my problem.
if I work just with ojb everything work fine.
The problem is the following.
I delete a row directly on sql server (that has autocommit = true)with his
client;
then i try to insert a row with the same primary key with ojb.
Before doing so I do a select (with pb api) to look if the key already
exists.
Untlil here everything is good. 'Cause I retrieve a null (key does't exist).
when I do the store i catch the exception (from sql server) that says that
key already exists.
So ojb says that no such a key exists. sql server says that yes it exists.
Anyway I don't worry 'cause nobody should touch directly the database on
sql server, And if I keep inside application everything is good.
But I would like to know the reason. of corse should be better if I don't
have the problem.
thanks
On 6/20/05, Martin Kalén <mk...@apache.org> wrote:
>
> Alessandro Colantoni wrote:
> > in repository_database.xml I have
> > <jdbc-connection-descriptor jcd-alias="default"
> default-connection="true"
> > platform="MsSQLServer" jdbc-level="2.0" driver="
> > com.microsoft.jdbc.sqlserver.SQLServerDriver" protocol="jdbc"
> > subprotocol="microsoft:sqlserver"
> > dbalias="//walqasrv01:1433;DatabaseName=trayectorias" username="steria"
> > password="filemon" eager-release="false" batch-mode="false"
> > useAutoCommit="2" ignoreAutoCommitExceptions="false">
> > In OJB.properties I have
> > ObjectCacheClass=org.apache.ojb.broker.cache.ObjectCacheEmptyImpl
>
> You still didn't specify which OJB API you are using, is it PB API?
>
> Since you have set useAutoCommit="2" (explicit off) you will need
> to commit all transactions to the database before you can expect
> MS SQL to 'see' any deletes.
>
> If you are using PB API this might look something like:
>
> try {
> broker.beginTransaction();
>
> broker.store(obj);
> broker.delete(obj);
>
> broker.commitTransaction();
> } finally {
> if (broker.isInTransaction()) {
> broker.abortTransaction();
> }
> }
>
> Now if you store your object a second time, the deletion has been
> committed to the database and you should not get PK violations.
>
> See link [1] for more info about possible auto-commit settings
> in OJB.
>
> Regards,
> Martin
>
> [1] http://db.apache.org/ojb/docu/guides/repository.html#useAutoCommit
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
Re: primary key problem sql server
Posted by Martin Kalén <mk...@apache.org>.
Alessandro Colantoni wrote:
> in repository_database.xml I have
> <jdbc-connection-descriptor jcd-alias="default" default-connection="true"
> platform="MsSQLServer" jdbc-level="2.0" driver="
> com.microsoft.jdbc.sqlserver.SQLServerDriver" protocol="jdbc"
> subprotocol="microsoft:sqlserver"
> dbalias="//walqasrv01:1433;DatabaseName=trayectorias" username="steria"
> password="filemon" eager-release="false" batch-mode="false"
> useAutoCommit="2" ignoreAutoCommitExceptions="false">
> In OJB.properties I have
> ObjectCacheClass=org.apache.ojb.broker.cache.ObjectCacheEmptyImpl
You still didn't specify which OJB API you are using, is it PB API?
Since you have set useAutoCommit="2" (explicit off) you will need
to commit all transactions to the database before you can expect
MS SQL to 'see' any deletes.
If you are using PB API this might look something like:
try {
broker.beginTransaction();
broker.store(obj);
broker.delete(obj);
broker.commitTransaction();
} finally {
if (broker.isInTransaction()) {
broker.abortTransaction();
}
}
Now if you store your object a second time, the deletion has been
committed to the database and you should not get PK violations.
See link [1] for more info about possible auto-commit settings
in OJB.
Regards,
Martin
[1] http://db.apache.org/ojb/docu/guides/repository.html#useAutoCommit
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: primary key problem sql server
Posted by Alessandro Colantoni <al...@gmail.com>.
Hi
in repository_database.xml I have
<jdbc-connection-descriptor jcd-alias="default" default-connection="true"
platform="MsSQLServer" jdbc-level="2.0" driver="
com.microsoft.jdbc.sqlserver.SQLServerDriver" protocol="jdbc"
subprotocol="microsoft:sqlserver"
dbalias="//walqasrv01:1433;DatabaseName=trayectorias" username="steria"
password="filemon" eager-release="false" batch-mode="false"
useAutoCommit="2" ignoreAutoCommitExceptions="false">
In OJB.properties I have
ObjectCacheClass=org.apache.ojb.broker.cache.ObjectCacheEmptyImpl
thanks
On 6/20/05, Martin Kalén <mk...@apache.org> wrote:
>
> Alessandro Colantoni wrote:
> > I'm using ojb 1.0.1 with sql server.
> > When I store an object, and then I delete it directly on the db I can't
> > insert it a second time.
> > Before storing I use to check if an object with the same key exists.
> > ojb doesn't detect any object with the same key (correctly... I deleted
> it)
> > But when storing it catch a sql server exception because primary key
> already
> > exists.
> > Is it a limit of microsoft sql server (it keeps primary key somewhere?)
> or
> > I'm doing something wrong?
>
> This sounds like a transaction isolation problem. If you use the PB API
> with
> OJB without the two-level cache you will not have any transaction
> isolation
> and a delete will be directly visible in the cache before commit.
>
> However, if you don't use auto-commit on the Connection to MSSQL the
> database
> DELETE will not be performed until COMMIT.
>
> What is your auto-commit setting in repository_database.xml and which OJB
> API
> are you using?
>
> Regards,
> Martin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
Re: primary key problem sql server
Posted by Martin Kalén <mk...@apache.org>.
Alessandro Colantoni wrote:
> I'm using ojb 1.0.1 with sql server.
> When I store an object, and then I delete it directly on the db I can't
> insert it a second time.
> Before storing I use to check if an object with the same key exists.
> ojb doesn't detect any object with the same key (correctly... I deleted it)
> But when storing it catch a sql server exception because primary key already
> exists.
> Is it a limit of microsoft sql server (it keeps primary key somewhere?) or
> I'm doing something wrong?
This sounds like a transaction isolation problem. If you use the PB API with
OJB without the two-level cache you will not have any transaction isolation
and a delete will be directly visible in the cache before commit.
However, if you don't use auto-commit on the Connection to MSSQL the database
DELETE will not be performed until COMMIT.
What is your auto-commit setting in repository_database.xml and which OJB API
are you using?
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org