| Author |
Topic |
|
gburns
Starting Member
9 Posts |
 Posted - 2002-05-29 : 05:26:13
|
| I currently have an identity field in an invoice table. I would like to retrieve this autonumber and increment it, WITHOUT actually inserting a record in the table until later.It will be a few seconds while I turn over control to my online payment processor (who needs an invoice number). My design is to only write to the invoice table after payment success. i.e. *after* payment processing.Does anybody have any hints for me? My challenge is how to retrieve the identity field and increment it without an actual table insert.Or should I just use a secondary table to generate invoice numbers? If I were to do this, I guess I would have to manage this temp table somehow. Issues?Looking for good, clean design options.Thank you,gburns |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-05-29 : 05:51:33
|
| If you are using SQl 2000, try IDENT_CURRENT('table_name')BOL: Returns the last identity value generated for a specified table in any session and any scope. You could then increment the value returned.Be sure to lock the table for inserts between the time that you got the value and the time that you actually insert. Sounds a bit dogdy to me... |
 |
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-05-29 : 06:06:22
|
| You could use a permanent separate table with the id numbers - but there are the locking issues... Or you could generate the invoice number from your code with a GUID or todays date and time or someting and then use that for the insert and don't use an auto-incrementing field in the invoice table. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 10:42:14
|
| What's wrong with 'actually' inserting into the table? Wrap it in a transaction, and if the payment processor returns an exception, roll it back.setBasedIsTheTruepath<O> |
|
|
|
gburns
Starting Member
9 Posts |
Posted - 2002-05-29 : 14:05:23
|
quote:
If you are using SQl 2000, try IDENT_CURRENT('table_name')
Good idea, but unfortunately I use MS SQL Server 7.0 and that function is not available to me.Thanks,gburns |
|
|
|
gburns
Starting Member
9 Posts |
Posted - 2002-05-29 : 14:14:30
|
quote:
You could use a permanent separate table with the id numbers - but there are the locking issues...
Yes I think that this is a solid design option.I do not understand any potential lock issue though. Am I missing something.gburns |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 14:17:57
|
| Explain why you don't want to insert the row in a transaction and roll back if an exception occurs?setBasedIsTheTruepath<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-29 : 14:22:53
|
| I agree 110% with setbased: just wrap the whole thing in a transaction and roll it back if some part of the process fails. Your situation is a textbook example of database transactions, literally; I've seen probably a dozen published examples that use the exact scenario you describe.You'll get gaps in your invoice numbers, but that's fine; you DON'T want a failed invoice number applied to a future successful invoice, just to avoid gaps. If you need to reconcile failed invoices later on, how would you distinguish them from the successful invoice? |
|
|
|
|
Incrementing identity field BEFORE table insert