Getting the id of a new row from ODBC

davetshave

Hi all.

A while back I made a web-application based on mysql and php.

I made a database abstraction layer in case I ever wanted to use a different combination than the one above.

To my horror when I started trying to use access instead of mysql I discovered that I was unable to get the insert id back from an insert query. If I use odbc_exec to do the insert then it returns a result identifier, but I can't get the id of the inserted row out of that identifier in any way.

I thought about using a procedure defined in access, but if I try to define a query in access then I am presented with some demented GUI which seems hellbent on wanting to make creating a query 'easier' by saving me the effort of typing it in.

Anyone see a solution to this problem other than killing access? 🙂

Thanks in advance.

David.

bhush

Hey David,what u can do is-
u can keep this field as auto-increment(which must be the case already, i hope). So the last id inserted will be the maximum value for that field. ur next query will be to select max(id) from table so as to get the latest id.
Bhushan

davetshave

You are spot on with your answer and that is how I 'solved' the problem. If I was building the system from the ground up then it wouldn't have been so annoying.

It's just that I have built the system using mysql and had written the code on the assumption that if you insert something then the query returns the id which auto-increment generates. I can't make a generic ODBC function to do inserts which returns the value of that which is inserted because it's a different table each time.

So I just select the max(id), but it did mean altering ten scripts or so. The idea of my database abstraction layer was to avoid that sort of thing 🙁

Oh well, next time I develop I'll use ODBC because then I know I have the most limited set of database functions in the world and won't make the mistake of relying on functions which are not common to all SQL environments.

In any case thanks for your advice 🙂

Greetings,

David.

Anon

Using select max(id) to get the last inserted auto increment value is not a reliable method in a multi user environment.
You can end up with the wrong value which will cause havoc in your data.

DBMSs have (different) builtin functions for retrieving this value that should be used instead.

davetshave

Yes I realised that in theory if someone does an insert before I locate the max value then it can lead to inconsistencies. For that reason I use the parameters of the data which was just inserted to locate the max id too, the chances of two identical records being inserted virtually simultaneously is small.

I know that most DBMS\\'s have other functions for returning the identity of the record which was just inserted and I used the appropriate one when I was developing under MYSQL, my problem is how to achieve the same with an ODBC connected database. I checked the odbc reference of the php manual for hour without finding how to do it. I know it is possible under ASP 🙁