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
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.
<:((><
···
On Jul 28, 2004, at 18:04, Lennon Day-Reynolds wrote:
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.)
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.
···
On Thu, 29 Jul 2004 07:04:07 +0900, Lennon Day-Reynolds <rcoder@gmail.com> wrote:
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
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
···
On Wednesday 28 July 2004 15:04, Lennon Day-Reynolds wrote:
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.
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
···
On Thu, 29 Jul 2004 07:18:48 +0900, Nathaniel Talbott <nathaniel@talbott.ws> wrote:
On Jul 28, 2004, at 18:04, Lennon Day-Reynolds wrote:
> 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.
<:((><
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
···
On Thu, 29 Jul 2004 09:14:40 +0900, Sean O'Dell <sean@celsoft.com> wrote:
On Wednesday 28 July 2004 15:04, Lennon Day-Reynolds wrote:
> 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
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.
<:((><
···
On Jul 28, 2004, at 18:38, Lennon Day-Reynolds wrote:
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.
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
···
On Wednesday 28 July 2004 17:31, Lennon Day-Reynolds wrote:
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.
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.
···
On Thu, 29 Jul 2004 09:31:15 +0900, Lennon Day-Reynolds <rcoder@gmail.com> wrote:
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
On Thu, 29 Jul 2004 09:14:40 +0900, Sean O'Dell <sean@celsoft.com> wrote:
> On Wednesday 28 July 2004 15:04, Lennon Day-Reynolds wrote:
> > 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
>
>
From: Nathaniel Talbott [mailto:nathaniel@talbott.ws]
> 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.
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/
···
On Jul 28, 2004, at 18:38, Lennon Day-Reynolds wrote:
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
···
On Thu, 29 Jul 2004 07:52:17 +0900, Nathaniel Talbott <nathaniel@talbott.ws> wrote:
On Jul 28, 2004, at 18:38, Lennon Day-Reynolds wrote:
> 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.
<:((><
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
···
On Wednesday 28 July 2004 18:04, Carl Youngblood wrote:
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
···
On Thu, 29 Jul 2004 10:04:07 +0900, Carl Youngblood <carl.youngblood@gmail.com> wrote:
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: I like the idea of a seperate component that handles nothing but
ID generation. Properly done, it could easily extend to other useful
applications (sessions IDs for non-RDBMS-backed webapps, etc.).
Since you could always just increment the counter again whenever a
database transaction failed and had to be retried, I support you
wouldn't really even need to manage the two operation under a single
transactional context.
Now the only question is what underlying storage engine to
use...Berkeley DB? CDB? Flat files? PStore?
Okay, looks like I have a good weekend project now.
Lennon
At least for the MSSQL server I think the most appropriate query is
select SCOPE_IDENTITY()
just after the insert statement.
You could also use
select IDENT_CURRENT('table_name')
but I suspect this isn't appropriate
due to concurrency issues.
Keep up the good work and let us know about the ODBC adapter.
I for one could use it to try out ActiveRecord on Windows.
Actually I am a bit surprised that it wasn't the first adapter built.
Nathaniel Talbott <nathaniel@talbott.ws> wrote in message news:<C445240B-E0E8-11D8-9668-000A95CD7A8E@talbott.ws>...
···
On Jul 28, 2004, at 18:38, Lennon Day-Reynolds wrote:
> 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.
<:((><
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.
<:((><
···
On Jul 28, 2004, at 19:04, David Naseby wrote:
From: Nathaniel Talbott [mailto:nathaniel@talbott.ws]
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
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.
<:((><
···
On Jul 28, 2004, at 19:31, Lennon Day-Reynolds wrote:
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.
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.
···
On Thu, 29 Jul 2004 10:29:03 +0900, Sean O'Dell <sean@celsoft.com> wrote:
On Wednesday 28 July 2004 18:04, Carl Youngblood wrote:
> 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
I would use a regular old file. If you have a persistent layer, maintain it
internally as a native integer and write it out each time it's incremented as
text. Re-load the value from the file when the persistent layer first loads.
Sean O'Dell
···
On Wednesday 28 July 2004 23:06, Lennon Day-Reynolds wrote:
Sean: I like the idea of a seperate component that handles nothing but
ID generation. Properly done, it could easily extend to other useful
applications (sessions IDs for non-RDBMS-backed webapps, etc.).
Now the only question is what underlying storage engine to
use...Berkeley DB? CDB? Flat files? PStore?
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