Stupid ODBC!

[Lennon Day-Reynolds]

So, in response to David's call for contributions of adapters for
Active Record, I've been working on an ODBC adapter. Unfortunately,
I've come up against a limitation of the ODBC spec when compared to
most native database APIs there appears to be no standard way of
getting an identifier for the last row inserted. Since one of the
primary methods that an Active Record database adapter must implement
('insert') is expected to return this value, I appear to be stuck for
the moment.

Does anyone have a general solution for this they've used (or seen
described/mentioned) elsewhere? Alternately, might there be a
workaround to remove this requirement from the adapter interface in
Active Record, w/o breaking the rest of the system? (David, that one's
all yours to answer, most likely.)

Lennon

 

[Nathaniel Talbott]

So, in response to David's call for contributions of adapters for
Active Record, I've been working on an ODBC adapter. Unfortunately,
I've come up against a limitation of the ODBC spec when compared to
most native database APIs there appears to be no standard way of
getting an identifier for the last row inserted. Since one of the
primary methods that an Active Record database adapter must implement
('insert') is expected to return this value, I appear to be stuck for
the moment.

Does anyone have a general solution for this they've used (or seen
described/mentioned) elsewhere? Alternately, might there be a
workaround to remove this requirement from the adapter interface in
Active Record, w/o breaking the rest of the system? (David, that one's
all yours to answer, most likely.)

As far as I know, getting the last row id is specific to the underlying
database, so you'll probably have to allow users of your ODBC adapter
to specify a bit of code for retrieving it. Of course, you could
provide a collection of the most common ones, too.

Does ODBC have a way to get the type of database it's sitting on? If it
does, you could even auto-select the right method for the user (or warn
them if there isn't one for their database).

HTH,


Nathaniel
Terralien, Inc.

<(><

 

[Lennon Day-Reynolds]

That's what I was afraid of. Maybe I can use the 'info' method on the
database handle immediately after connecting to get the connect log
strings, and just grep for known driver types to figure out what to
use to replace rowid.

It also means that the ODBC code is going to have to reproduce or call
a whole bunch of existing adapter-specific stuff, though, which
ideally would mean refactoring at least those methods out into some
sort of database utilities module.

The problem is that it appears that SQL Server (and most like the Jet
engine, too) has *no* way to get said information -- if you want a
'rowid', it has to be in your table schema.

Looks like this will be a bit more than a few hunded lines of code in
one class, and so won't likely be usable soon enough for the project
I'm working on right now. Time to brush up on my ADO, I guess!

Lennon

 

[Nathaniel Talbott]

The problem is that it appears that SQL Server (and most like the Jet
engine, too) has *no* way to get said information -- if you want a
'rowid', it has to be in your table schema.

Here's the code I used for SQL Server:

SELECT @@identity

I forget all the forms of that, and the exact scope it operates in, but
it should at least get you started.

HTH,


Nathaniel
Terralien, Inc.

<(><

 

[Carl Youngblood]

Oracle and PostgreSQL use sequences, MySQL uses auto_increment fields
with GET_LAST_INSERT_ID(), can't remember what MS Access uses, nor
what SQLite uses. This is not really ODBC's fault. I think that the
SQL spec should specify some standard way of getting the last inserted
ID, as well as the count of the number of rows that were affected by
the last query.

 

[David Naseby]

From: Nathaniel Talbott [mailto:[email protected]]

Here's the code I used for SQL Server:

SELECT @@identity

I forget all the forms of that, and the exact scope it operates in, but
it should at least get you started.

I believe its recommended to use

SELECT scope_identity()

on Sql Server. Ref:
http://weblogs.asp.net/rosherove/archive/2003/11/13/37217.aspx

No idea about Jet, sorry

David
http://homepages.ihug.com.au/~naseby/

 

[Nathaniel Talbott]

From: Nathaniel Talbott [mailto:[email protected]]

Here's the code I used for SQL Server:

SELECT @@identity

I forget all the forms of that, and the exact scope it operates in,
but
it should at least get you started.

I believe its recommended to use

SELECT scope_identity()

on Sql Server. Ref:
http://weblogs.asp.net/rosherove/archive/2003/11/13/37217.aspx

Ah, I should have said, "SQL Server 7"... it didn't have
scope_identity(). Which of course means that you can't just have a
specific method per DB because you may actually need one per DB version
:-/


Nathaniel
Terralien, Inc.

<(><

 

[Lennon Day-Reynolds]

Well, that's closer, but it still isn't a totally general solution,
since the table schema has to include an 'IDENTITY' column, right?
That would mean that individual tables in a SQL Server database being
used as an Active Record backend could need different unique id logic.
That's not impossible, of course, it's just way more complexity than
the current adapters require.

Lennon

 

[Nathaniel Talbott]

Well, that's closer, but it still isn't a totally general solution,
since the table schema has to include an 'IDENTITY' column, right?
That would mean that individual tables in a SQL Server database being
used as an Active Record backend could need different unique id logic.
That's not impossible, of course, it's just way more complexity than
the current adapters require.

I guess I don't understand the problem... why does this mean that
individual tables in a SQL Server database could need different unique
id logic?


Nathaniel
Terralien, Inc.

<(><

 

[Lennon Day-Reynolds]

Warren: thanks for the pointer. That function does indeed tell you
which columns serve as a unique identifier for the table, but it
doesn't directly support the functionality I need for the Active
Record adapter: namely, immediately after inserting a row, fetch the
unique, internal, and hopefully immutable id for that row.

Nathaniel: While SQL Server does have convenience functions for
checking the current/most recent identity value, it does not require
that you have any IDENTITY column in a table schema at all. In fact,
many database design guidelines suggest you avoid them entirely, as
they are simple numeric identifiers which have no semantic
relationship to the data in your column. I'm not enough of a SQL
Server guru to know in practice what portion of tables will lack an
IDENTITY field, but I do know that the possibility, along with the
suggestions I've seen in "best practices" documents to avoid them,
makes me nervous about relying on that feature.

All: I've email David about the issue, and hopefully we'll be able to
figure out a minimally-invasive way to seperate the unique id
functionality in the database adapters from the insert method.

I'm happy to have more suggestions in the meantime, though.

Lennon

 

[Sean O'Dell]

So, in response to David's call for contributions of adapters for
Active Record, I've been working on an ODBC adapter. Unfortunately,
I've come up against a limitation of the ODBC spec when compared to
most native database APIs there appears to be no standard way of
getting an identifier for the last row inserted. Since one of the
primary methods that an Active Record database adapter must implement
('insert') is expected to return this value, I appear to be stuck for
the moment.

Just to throw a little confusion on this subject:

A number of database engine autoid implementations I've seen generate a unique
ID by locating the highest existing ID and incrementing it by one. It's
possibly for the same ID to be re-used after some records are deleted and new
ones created. If you have records linked across tables using those autoid's,
that can be a problem.

What I almost always do is generate a new, unique ID myself at the application
layer or in a table made for tracking unique IDs (always increment, never
decrement). When you INSERT a new record, give it that unique ID in your
INSERT, then simply SELECT it afterwards WHERE id= the new ID you gave it.

I wouldn't depend on any engine's autoid feature, even if it works
TheRightWay(tm), if you're at all concerned about portability.

Sean O'Dell

 

[Lennon Day-Reynolds]

Sean,

I agree with you, and when I've inevitably rolled my own
object/relational mapping tools, I've included persistent OID handling
in that layer, rather than using any database-internal features.
However, that usually requires modifying the database schema in some
way -- i.e., adding a name-mangled table like '__my_oids__', and
managing it seperately from normal insert operations.

I can also understand why Active Record follows the "worse is better"
model in this case: since the most popular open source databases
(MySQL, PostgreSQL, and SQLite) support some sort of
auto-row/insert-id functionality, why not just take advantage of it?

Both approaches have their own costs and risks. On the one hand,
assuming that the functionality will be there can obviously burn you,
as I'm discovering trying to shoehorn in some sort of solution for SQL
Server. However, if you build your own solution, you either have to
modify the database schema, (which, in the case of the project I'm
hoping to use Active Record on, is absolutely *not* an option) or keep
the OID records somewhere other than the primary database, which
complicates transaction handling considerably.

Basically, it's just starting to look like Active Record may not work
perfectly for the project I'm working on right now, which is hardly
the end of the world.

Lennon

 

[Sean O'Dell]

Server. However, if you build your own solution, you either have to
modify the database schema, (which, in the case of the project I'm
hoping to use Active Record on, is absolutely *not* an option) or keep
the OID records somewhere other than the primary database, which
complicates transaction handling considerably.

This won't help in your particular case, but in general I advocate putting all
database activity into a middle layer and having applications call only the
middle layer, never the database directly. Managing unique IDs is something
a middle layer can do very well, and it can just use a raw binary file (fast
and safe) instead of the database.

Sean O'Dell

 

[Nathaniel Talbott]

Nathaniel: While SQL Server does have convenience functions for
checking the current/most recent identity value, it does not require
that you have any IDENTITY column in a table schema at all. In fact,
many database design guidelines suggest you avoid them entirely, as
they are simple numeric identifiers which have no semantic
relationship to the data in your column. I'm not enough of a SQL
Server guru to know in practice what portion of tables will lack an
IDENTITY field, but I do know that the possibility, along with the
suggestions I've seen in "best practices" documents to avoid them,
makes me nervous about relying on that feature.

Then I don't understand something about your request; you said you were
looking for:

standard way of getting an identifier for the last row inserted

If a row is expected to have an identifier, then it's going to have to
have an identity column. I believe other databases, such as Postgres
and MySQL, don't force you to have an identity column, either, so I
don't see what makes SQL Server any more onerous than other databases
(in this regard; I have lots of other reasons to hate it if you need
some).

Am I missing some context here on Active Record?


Nathaniel
Terralien, Inc.

<(><

 

[Carl Youngblood]

Why not take the approach of using a autoid function if you have it
but creating an extra table for storing counters if you don't? This
seems like a perfectly acceptable solution.

 

[Sean O'Dell]

Why not take the approach of using a autoid function if you have it
but creating an extra table for storing counters if you don't? This
seems like a perfectly acceptable solution.

If portability is not much of an issue, and the autoid implementation works
properly (i.e. doesn't just +1 to the highest ID), this should be fine. If
you ever expect to port it to other engines, or the autoid is like MySQL's
is, or at least used to be, autoid is pretty dicey.

Sean O'Dell

 

[Carl Youngblood]

MySQL has an internal counter variable for every table that never gets
decremented. It does not take the highest existing ID. AFAIK it
hasn't done this for years.

 

[Sean O'Dell]

MySQL has an internal counter variable for every table that never gets
decremented. It does not take the highest existing ID. AFAIK it
hasn't done this for years.

Are you positive?

If you create a record with an autoid field, and it gets a value of 20, then
delete that record, then create another one, you're sure it won't get a value
of 20?

Sean O'Dell

 

[Lennon Day-Reynolds]

On Thu, 29 Jul 2004 10:02:25 +0900, Nathaniel Talbott

Then I don't understand something about your request; you said you were
looking for:
If a row is expected to have an identifier, then it's going to have to
have an identity column. I believe other databases, such as Postgres
and MySQL, don't force you to have an identity column, either, so I
don't see what makes SQL Server any more onerous than other databases
(in this regard; I have lots of other reasons to hate it if you need
some).

Am I missing some context here on Active Record?

The SQL Server concept of IDENTITY is different from a ROWID (as in
Oracle, Postgres, etc.), as it is an *optional* type, much like a
primary key is optional.

This is only an issue becuase of the requirements of Active Record;
namely, the fact that an Active Record object needs a persistent,
unique identifier, even if the underlying database table row doesn't
have one.

In Postgres et. al., that's easy, since the internal value that the
database engine uses to refer to a row is accessible via the
afore-mentioned DBMS-specific functionality. SQL Server still *uses* a
globally unique id to refer to each row in a database, it just doesn't
expose that value to client applications through ODBC.

Lennon

 

[Lennon Day-Reynolds]

Why not take the approach of using a autoid function if you have it
but creating an extra table for storing counters if you don't? This
seems like a perfectly acceptable solution.

This is most certainly not acceptable for my current needs, since
several of the databases I would like to access via Active Record are
production systems, and there is no conceivable way I could bring
about schema changes to a live instance.

Lennon