Cyril
|
| Posted: 03/07/2002, 5:57 PM |
|
I am new to programming, I need to use JSP connect with Oracle, how can I insert a autonumber (KEY field) into my Database? Is there any JSP sample event to do so?
|
|
|
 |
Ken Hardwick
|
| Posted: 03/08/2002, 12:44 AM |
|
I am useing ASP and Oracle. I wrote and put in my global functions the following.
Then I use it in the "before insert event" to get the next number available.
fldPrimField = Next_ID("tablename","Prim_Field","")
Also..note..that since this is not a autonumber field, you will need to have the "Prim_Field" in your form twice...mark as primary/hide/rename one instance,
and have the other hidden but not marked a primary. The one not marked as primary will be the one you will assign the next_id to .
Then, you will need a different page for the edit of the same record.
Function Next_ID(T,F,W)
kenSQL = "Select max(" & F & ")+1 as next_One from " & T
if W <> "" then
kenSQL = kenSQL & " where " & W
end if
openrs rs2, kenSQL
if rs2.eof = true then
Next_id = 1
else
Next_Id = rs2("Next_one")
end if
rs2.close
set rs2 = nothing
End Function
|
|
|
|
Tom
|
| Posted: 03/09/2002, 2:14 PM |
|
Oracle has a much better way to handle this than MsAccess Autonumber scheme. You can use Oracle Sequence generator! It is guaranteed to be unique no matter how many users.
You need to first create the sequence
Create sequence myseq increment by 1 start with 1; (see your oracle book for more syntax)
Then when ever you insert a row you can call the sequence and get the value
insert into mytable (myrowid, mytext) values (myseq.nextval, 'test');
Stay away from searching the table and using a max() function since it is not guaranteed to be correct if you have more than one user on the system. (when you get the max, someone else may have got the max also at the same time and now you have a problem.
fyi
|
|
|
|
Spaceclown
|
| Posted: 04/02/2003, 9:22 AM |
|
I have a question about
"Then when ever you insert a row you can call the sequence and get the value
insert into mytable (myrowid, mytext) values (myseq.nextval, 'test')"
Do you create a trigger to do this part? I created the sequence I just don't know how to call it. Any ideas?
|
|
|
|
spaceclown
|
| Posted: 04/08/2003, 9:56 AM |
|
I have a question about
"Then when ever you insert a row you can call the sequence and get the value
insert into mytable (myrowid, mytext) values (myseq.nextval, 'test')"
Do you create a trigger to do this part? I created the sequence I just don't know how to call it. Any ideas?
|
|
|
|
spacec lown
|
| Posted: 04/11/2003, 12:06 PM |
|
I figured it out.
This is my trigger. CSR is the column name
BEGIN
IF :new.csr IS NULL THEN
SELECT AUTONUM_SEQ.NEXTVAL
INTO :new.csr
FROM dual;
END IF;
END;
Timing is before insert
|
|
|
|
|
