Thankyou very much guys for your feedback. I have found a solution which i want
to share with you, so that in future if you
guys run into the same problem, you will have an idea of how to tackle it. This
was based on Nicholas Barrington's method for which i give him full credit.
Solution:
------------
Consider this... if the DBA by chance deletes a row off the Table, then the
record count will be wrong and i will be getting undesirable Exceptions. This
leads to failure of the design. Instead of me trying to create the Primary Key i
will let the database create the primary key for me. Even if the DBA has deleted
records, the database keeps track of the keys it has generated. This will
guarantee that it will not generate a duplicate key. This is especially true in
SQL Server database. I am not sure about Oracle though.
Like Nicholas has said, let the database create the Primary Key.
INSERT INTO theTable (fFirstName, fLastName) VALUES ('John', 'Smith');
instead of simply using
INSERT INTO theTable (15, 'John', 'Smith');
where 15 is the primary key value;
Here the SQL Server will create the Primary Key 15 ( instead of being created by
the User ).
You can even ask the SQL Server for the Primary Key it has created by using the
following command after an INSERT Statement.
SELECT @@IDENTITY.
This returns the Primary Key.
Combine this with synchronized methods and SingleThreadModel Interface. This i
am pretty sure will lead to a solid design.
Cheers,
Amar..
Nicholas Barrington <[EMAIL PROTECTED]> on 10/28/99 08:15:14
PM
Please respond to Nicholas Barrington <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
cc: (bcc: Amarnath Nanduri/IT/VANCPOWER)
Subject: Re: Question that is puzzling me
If you are using record counts to indicate a primary key for your next
record you may run into the situation where two users access the "enter new
data" page reasonably close together in time. User X will submit their data
(which I'm assuming will create an INSERT INTO string or similar if you are
using SQL) based on a primary key generated from the number of records
currently in the table + 1. If user Y submits their data at a similar time,
then their "count the number of existing records" query may end up running
*BEFORE* user X has completed their INSERT query. This will result in both
user X and user Y trying to insert a record with the same primary key.
To get around this it is nice to ensure that you are using a completely
unique key, which can be generated either by the database itself (if you are
using an SQL db you have the option to set a field as incremental) or by a
record counter that you keep yourself. If you choose the former you need to
use an INSERT query that inserts all fields EXCEPT the primary key field.
E.G.
INSERT INTO theTable (fFirstName, fLastName) VALUES ('John', 'Smith');
instead of simply using
INSERT INTO theTable (15, 'John', 'Smith');
where 15 is the primary key value;
If you choose the latter option, you get around the server restart problem
by having a function that runs at app startup that counts the number of
records in the table and then creates a variable which holds your "number of
current records", which is then updated every time a user submits a query.
Hope this helps!
-----Original Message-----
From: Nanduri Amarnath [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 29, 1999 5:24 AM
To: [EMAIL PROTECTED]
Subject: Re: Question that is puzzling me
The problem with the private static int reckey variable is that if i stop
and
re-start the web server , again i
will be doing the count from 0 ( instead of from where i actually left off
from
the previous calls, before stopping the server ).
You are stating that natural order counts cause problems, in the long run.
Can
you please elaborate on that. Thankyou.
Cheers,
Amar..
"Campbell" <[EMAIL PROTECTED]> on 10/28/99 03:10:11 PM
To: Amarnath Nanduri/IT/VANCPOWER@VANCPOWER, [EMAIL PROTECTED]
cc:
Subject: Re: Question that is puzzling me
I think this is what you are looking for: synchronize your servlet method...
you may want to create a private static int reckey variable accessible
through a public getRecCounter method (synchronized), rather than using the
natural order count, described. Natural order counts cause problems, in the
long run.
Phil
-----Original Message-----
From: Nanduri Amarnath <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, October 28, 1999 12:56 PM
Subject: Question that is puzzling me
>Guys/Girls ,
> I am calling a servlet from a jsp. The servlet implements the
>SingleThreadModel.
>The servlet has a doPost() method that writes data to a table in the
database.
>The primary key
>for each row of this Table is got by counting the ( number of rows in the
Table
>+ 1 ). This is done in the
>doPost() method. Also i am setting autoCommit() to false ( in the
Connection
>Object ) and doing either a
>complete commit() or a complete rollback().
>
>My question is... if different people hit this page simultaneously, will i
still
>be getting unique primary keys for every person
>(or) if not is there a better way to do it......... Thanks in advance.
>
>Cheers,
>Amar..
>
>===========================================================================
>To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
>FAQs on JSP can be found at:
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
>
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
