Very good, I agree with you.
Here is my solution for the Oracle database:
1) Create a sequence (an Oracle object that gives you always a unique
integer value, also in a
multiuser application):
CREATE SEQUENCE THETABLE_SEQ;
2) Create a trigger on table THETABLE
create trigger tID_THETABLE BEFORE INSERT
on THETABLE
for each row
begin
select THETABLE_SEQ.nextval into :NEW.ID from dual;
end;
/
The database will calculate the unique value for the column ID
automatically, you simply have to do:
INSERT INTO theTable (fFirstName, fLastName) VALUES ('John', 'Smith');
PS: If your table already contains data, you can allign the sequence with
the following anonimous
block of PL/SQL:
begin
declare
lMaxValue number;
lSeqValue number;
begin
select max(THETABLE.ID) into lMaxValue from THETABLE;
select THETABLE_SEQ.nextval into lSeqValue from dual;
while lSeqValue <= lMaxValue loop
select THETABLE_SEQ.nextval into lSeqValue from dual;
end loop;
end;
end;
/
Have a nice day, Paolo.
-----Original Message-----
From: Nanduri Amarnath <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: venerd� 29 ottobre 1999 15.27
Subject: Re: Question that is puzzling me
>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
===========================================================================
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
