[storm] Fwd: need help with result.get_insert_identity(), please.

[Gustavo Niemeyer]

Hello Veron,

Sorry for the delayed response.

>    I am trying to write a database front end for adodbapi on windows
> platforms to access Microsoft SQL server tables. So far the effort has gone
> surprisingly well. I am starting out by trying to make the tutorial run, but
> I have hit a sticking point.
>   When storm flushes 'joe' for the first time, store.py calls
> result.get_insert_identity() to obtain a "where" clause to find the id value
> assigned by the database engine.  I cannot figure out how I might be able to
> structure such a "where" statement. (perhaps a weird embedded SELECT of some
> type?)
>
>   SQL server will return the value I need as the result of the SQL function
> "SCOPE_IDENTITY()". So in T-SQL I would write something like:
>>
>> INSERT INTO person (name) VALUES ('Joe Jones')
>>
>> SELECT SCOPE_IDENTITY()
>
> and the result of the select will by my ID value.

You can do pretty much what you described, so I'm not sure of where
your doubt actually lies.  If you just return an expression from
get_insert_identity() that compares the primary key with
"SCOPE_IDENTITY()", you'll have a good start.

It'd also be very informative to investigate how that's currently done
in the different databases, since there are optimizations which may be
done to avoid the second query which is needed with
get_insert_identity().  Have a look at the postgres and mysql backends
to get an idea (note that trunk may have some useful changes in this
area).

> So is there a way to mangle a WHERE statement to get what I want? Or is the
> correct answer to change the code in store.py in the area of:

No, there's no need to change the generic Store code.

-- 
Gustavo Niemeyer
http://niemeyer.net