Return db-generated values after insert

[Anon]

I am writing a page that performs an insert of data into a MS SQL Server db from info entered in a PHP-generated form. I am using ODBC to connect to the db. I want to obtain the db-generated values (like primary key value) for the next page. How do I do this?

I know from prior experience that SQL in Oracle supports an insert clause called 'returning'. However, I believe that is a fairly recent addition. So, it would seem that this issue would exist using many different databases.

Thanks in advance for any information.

[bretticus]

Jim Wentz wrote:

I am writing a page that performs an insert of data into a MS SQL Server db from info entered in a PHP-generated form. I am using ODBC to connect to the db. I want to obtain the db-generated values (like primary key value) for the next page. How do I do this?

I know from prior experience that SQL in Oracle supports an insert clause called 'returning'. However, I believe that is a fairly recent addition. So, it would seem that this issue would exist using many different databases.

Thanks in advance for any information.

Yeah, send the insert query then a select query like this:

sql = "insert into mstable (field) values ('value'); select @@identity"

Then use php function: mssql_next_result

to move to the next record set and grab the value.

I've never tried this but I believe it would work. Good luck!

[Anon]

I'm not sure that will necessarily return the db-generated values for the record created by that insert statement. I believe that select statement could return the values for some other record created at some short time period after my specified insert statement. This is probably unlikely given the speed at which the commands are / should be occuring. However, the remote chance is still unacceptable. There must be another way.

[Anon]

I'm not sure that will necessarily return the db-generated values for the record created by that insert statement.>>

It will. @@identity is per connection.