Is there a way to get the value of a field that has a sequence attached in an sql query?
For example, I have a db with a table for customers and one for invoices. INVNO is a field in the invoices table that is auto-incrementing via a sequence.
When I add a record to this table I use
Then I want to update a field in the Customers table to give the last invno
To do this i use this to get the last invoice number added
rs = App.GlobalDB.sqlSelect( "select last_value FROM invoices_invno_seq" )
Then I add that number to a field in the customers table.
I can see this being an issue if another computer adds a record between the invoice record being added and the query for last_value in the sequence. It would pickup the wrong invoice number.
Is there a way on the initial insert to receive a return value for the auto-incremented field?
What is the best way to achieve what i am doing? Im sure my way can’t be the standard!
if you are inserting the customer primary key as a foreign key in the invoice table, then you can save a step on having to having to update the customer table with the last record entered.
instead of updating customers table, you can simply create a select statement that joins customers and invoice table on customer id, order by invoice pk as desc, and select top 1 row where customer id = the id that you are looking for.
another item to mention is if your application is already built around using the last invoice value in the customers table, you could always create a trigger to update the value for you.
Thanks @Eli Ott , But I think this is just a simpler way to do what I am already doing? But would face the same issues as my method? Unless I am missing something?
One thing I do is query the NEXTVAL() ahead of time. I have a little helper:
Function GetSequenceId(Extends db As Database, seqName As String) As Integer
Dim rs As RecordSet
rs = db.SQLSelect("SELECT NEXTVAL('" + seqName + "')")
if db.Error or rs.EOF then
raise new AMPSDatabaseError(db, "Could not get the next sequence value for '" + seqName + "'", CurrentMethodName)
end if
dim id as Integer = rs.IdxField(1).IntegerValue
rs.Close
return id
End Function
This gives me the ability to create objects and use them as relational items right from the get go.
In the code above, you’ll need to change your raise new exception code of course. You can now:
However, you may not need that. If you are not going to use the dr variable later and need the sequence ID in your code, then why not just set your default value for INVO to be NEXTVAL('invoices_invo_seq')?
dr = New DatabaseRecord
dr.integerColumn(“CID”) = cid
dr.dateColumn(“idate”) = date()
dr.BooleanColumn(“settled”) = false
dr.integerColumn(“amount”) = amount
dr.integerColumn(“owing”) = amount
dr.integerColumn(“paid”) = 0
App.GlobalDB.InsertRecord(“Invoices”, dr)
Dim rstWithRowId As RecordSet = db.SQLSelect(“SELECT lastval()”)
Then the record will be inserted and the last value from the sequence (invno field) will be returned to rstWithRowId variable.
And this is locked to the session of the current user. If another user inserts into the database between the insert and select lastval() then it will still only return the lastval from the session (computer) the initial insert was created?
I should mention, I have not setup any sort of users or anything if that makes any difference. Very new to SQL not even sure how sessions work. Also this is not done within a transaction, if that makes a difference. Haven’t figured out that yet either.
also would it be
Dim rstWithRowId As RecordSet = db.SQLSelect(“SELECT lastval()”)
OR
Dim rstWithRowId As RecordSet = db.SQLSelect(“SELECT Invoices lastval()”)
And would I need to specify the fieldname?
[quote=192527:@Andrew Willyan]@Jeremy Cowgar , I don’t think I am being clear.
I leave the field invno out of the insert, that lets the database give it the next value from the sequence.
What I need at run time, is a return of the value given by the sequence so i can then write it to another table.
So after my insert, i then need the value for invno to write it to the table customers in a field called lastinvno[/quote]
@Andrew Willyan, That is the exact use case for the code I gave. Simply “pre-fetch” the NEXTVAL() and tell your record to use it. Then you also have the value hanging around for other database tables. NEXTVAL() will get the next sequence ID and also increment the sequence in the same process. Thus, your new record has the right ID and you have the right ID to insert to other records.
The thing I like about pre-fetching the NEXTVAL is that I do not have to worry about the order of events. For example, maybe there is a trigger on your database which inserts other records. Or maybe you are inserting multiple records in a row. Getting the NEXTVAL() ahead of time, you always know what the record is that you are interested in.
Ok @Eli Ott and @Jeremy Cowgar , It seems both these methods and the original one by @Rich Hatfield all will do what I need.
I think I will use @Eli Ott method as it is the simplest to implement.
Thank you all for your help.
As a side note, I think if I ever master creating procedures and triggers i will use @Rich Hatfield method as It leaves the work for the DB rather than my software, which should be quicker.
I use RETURNING in Postgres. It’s super easy
Example:
rs=db.SQLSelect(“INSERT INTO table … RETURNING id”)
This will return the value of column “id” in your recordet after inserting your data to the db.
