• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to Prevent Duplicate Record Values

 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am at a loss on how to make the following code more efficient so I do not get a duplicate record error.

 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What are the unique columns that are causing the issues?

Why aren't you using PreparedStatements?

You seem to be manually calculating the next index.  Doesn't the DB have some sort of sequence/auto-increment mechanism for ids?
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:What are the unique columns that are causing the issues?

Why aren't you using PreparedStatements?

You seem to be manually calculating the next index.  Doesn't the DB have some sort of sequence/auto-increment mechanism for ids?


The table that generates the index is a single field table. The record getting created has a unique key on the recseq field.

I was under the impression I was using Prepared Statements.

For my particular programing purpose I cannot use the auto sequence feature of the database.

What I need I believe is a select on an update sql statement so both functions are carried out at the same time. But I cannot figure out how to do this.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Without locking your table that produces the index whenever you need a new number out of it you cannot guarantee it will not have been nabbed by another connection.
Especially since you aren't even doing this in single transactions.

You are getting a connection, which you use to find the next number.
You then close that connection and grab a new one to update the number.

At any point in there, prior to the final commit, another connection could come in and grab that self same number.

Is there a reason you can't use the inbuilt mechanism, because you will be single-threading your app this way.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Re: PreparedStatements.
You are using plain old Statement objects there.
I don't see any prepareStatement calls on the connections, or any PreparedStatement instances.



Of course you would also want proper try/catch around those so they are closed properly.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Without locking your table that produces the index whenever you need a new number out of it you cannot guarantee it will not have been nabbed by another connection.
Especially since you aren't even doing this in single transactions.

You are getting a connection, which you use to find the next number.
You then close that connection and grab a new one to update the number.

At any point in there, prior to the final commit, another connection could come in and grab that self same number.

Is there a reason you can't use the inbuilt mechanism, because you will be single-threading your app this way.


This is exactly my problem. Is there no way to lock the record(keep it from being selected) until the up date is done?

I have changed the code some but still get the 'Duplicate' error if I try and create the new records in calling class rapdly:

 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's something that's on the database side.
You would need to tell the db to lock the table, and I've no idea how you would do that with, is it DB2?

And your PreparedStatement still aren't.

The whole point behind them is to not concatenate your variables into the query.
So this:

should be this:

And then you would use the setXXX methods on the ps to set the values.
It prevents SQL injection and, on most databases, improves performance.


ETA: It would still be nice to know why exactly you can't use any inbuilt auto generator.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:That's something that's on the database side.
You would need to tell the db to lock the table, and I've no idea how you would do that with, is it DB2?


ETA: It would still be nice to know why exactly you can't use any inbuilt auto generator.


Yes it is DB2.

The index that is generated is used by several other functions within the app when a record is created. It is more than just creating a unique index for the immediate record.

If I just used auto incrimination how could I be sure my current record creating instance would be grabbing the correct index.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have a bunch of tables that reference the same id then just pass it around.

You can get generated keys from an INSERT, I think it's on ResultSet (you need to tell the statement to return generated keys).
That gives you your ID, and then pass that on down the chain of other dependent INSERTs.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:If you have a bunch of tables that reference the same id then just pass it around.

You can get generated keys from an INSERT, I think it's on ResultSet (you need to tell the statement to return generated keys).
That gives you your ID, and then pass that on down the chain of other dependent INSERTs.


But I am not doing an insert on the NextIndex class. There is only one record with one field that gets incremented.
 
grace christopher
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use PRIMARY KEY or UNIQUE Constraint to appropriate fields for preventing duplicates when table being created.
example
Create Table with PRIMARY KEY

CREATE TABLE user_info
(
    username CHAR(50)  NOT NULL,
    email CHAR(100)  NOT NULL,
    PRIMARY KEY (username, email)
);
 
grace christopher
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use PRIMARY KEY or UNIQUE Constraint to appropriate fields for preventing duplicates when table being created.
example
Create Table with PRIMARY KEY

CREATE TABLE user_info
(
     username CHAR(50)  NOT NULL,
     email CHAR(100)  NOT NULL,
     PRIMARY KEY (username, email)
);
 
grace christopher
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use PRIMARY KEY or UNIQUE Constraint to appropriate fields for preventing duplicates when table being created.
example
Create Table with PRIMARY KEY

CREATE TABLE user_info
(
     username CHAR(50)  NOT NULL,
     email CHAR(100)  NOT NULL,
     PRIMARY KEY (username, email)
);
 
Hardik Jani
Greenhorn
Posts: 7
Android AngularJS PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Query on the Primary Key Constraint and Unique Key Constraint along with Where Conditional Claus will help to filter the data
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic