Last ID

I'm developing a ticketing system. When a user loads the "open new ticket" page, I want to populate the ticket # field. How would I do this against an Oracle table?

Thank you in advance!

qball~"I have node idea?!"

[Cubes]

I would suggest holding off on displaying the number until after they've submitted the ticket.

Why? Well, in order to 'reserve' a new ID number, no matter what method you use (almost -- you could keep a whole separate sequence and just update that when you request a new ID), you will have to actually insert a record into the database with that ID. Now, if the user changes their mind/crashes their computer/wanders off never to return (you know, all those annoying human things users do), you have a blank ticket sitting there in the database that will never get filled in.

Sure, you could write a cleanup script to run periodically and delete those blank rows, but why bother? And what about all the reports and queries before the cleanup runs? Now you also need some extra ticket state to indicate 'initiated but not submitted'. Blah.

That said, the Oracle sequence method will work just fine, but the select max(id)+1 method is more portable.

[Hero Zzyzzx]

The best way would be to set the id field as an auto_increment field (or whatever the Oracle equivalent is) and you won't need to worry about it, the field will get updated/increment on every new ticket insertion.

Make the SQL engine do your work for you!

Barring that, this should work with Oracle, though I do most of my SQL in mySQL. This assumes that your tickets are numerical:

select max(id)+1 from tickets

I suggest you run this statement after the user has submitted input and right before you're going to update the table, so you don't have to worry about simultaneous submissions as much. You can use this value as your id.

You probably should look into subselects, they would allow you to do something like:


insert into auth (id,stuff,stuff2) values((select max(id)+1 from ticke
+ts),$stuff1,$stuff2);

[tilly]

YMMV, but I have found that if you are trying to copy data from one database to another, that auto increment fields are far more trouble than they are worth. Perhaps Oracle does a better job with this than Sybase...

[wardk]

You'll want to generate a "sequence", setting an initial number and an increment.

create sequence seq_name 
   start with 1
   increment by 1
   maxvalue 999999
   nocycle
   cache 20;

then you'll do something similar to this:

INSERT INTO table_name 
  ( id_no, col1, col2 )
values 
  ( seq_name.NEXTVAL, '$col1', '$col2' )

if you have few inserts and want to not have skipped sequence numbers, then avoid using a cache, it tends to cause "gaps" (at least in our environment)

good luck!

PS: wait for the insert to get the nextval, then display it post-insert. not before, or you'll get into trouble with non-uniqueness, which is what sequences are made to avoid :-)

[malloc]

Hi, not sure if this is what you are asking, but in Oracle, you would want to create a sequence, lets call it id_seq, and you would insert id_seq.nextval into the table, which would always return the next unique ticket number. Check the oradocs on sequences, should be no prob.
Hope this helps,
-malloc

UPDATE: Cubes made an excellent point about wasting empty rows in the main table and transactional support. I would handle this by first popping the top value off of the sequence with something like
$id = "select id_seq.nextval from dual"
and storing that in your script, and only inserting that value into the database when the user "stumbits" the ticket.