Multiple database support

[David Miller]

On 9/2/2003 10:05 AM -0700, Bruce Armstrong \[TeamSybase\] wrote:

> --- David Miller <justdave at syndicomm.com> wrote:
>>
>> FWIW, Sybase has "identity" which is a loose
>> equivalent to MySQL's
>> "auto_increment", however, Sybase's implementation
>> sucks.  We would up
>> creating a separate table with a list of tables and
>> key numbers, and
>> lock/incremenet/unlock the given row in that table
>> when we need a new key.
>>
>
> Ok, I'll bite.  What's wrong with Sybase's
> implementation?  (I assume you mean ASE.  ASA
> implements it as well, but better).

Getting the value of @@identity back after an insert is unreliable.  You
can't get @@identity back if you use placeholders "?" to insert data
because ASE "emulates" placeholders by temporarily creating a stored
procedure to do the placeholder replacement, and @@identity is out of scope
once the procedure exits, so you have no way to retrieve it.  @@identity
also seems to fall out of scope for *any* context change, not just the next
insert.  The main thing that throws a kink into all of this (besides the
placeholders) is that Sybase ASE can't handle more than one query handle on
the same connection, so DBD::Sybase has to open multiple connections to the
server to emulate it.  This means there's no guarantee your "SELECT
@@identity" query is going to wind up on the same connection that your
INSERT was sent on unless you turn autocommit off and handle your own
transactions (which until very recently wasn't very cross-DB compatible).
-- 
Dave Miller      Project Leader, Bugzilla Bug Tracking System
http://www.justdave.net/             http://www.bugzilla.org/