Re: [CDBI] Dealing with non-required primary keys

> I'm trying to not require primary keys during insertion, which will work

On 9/4/05, Rusty Phillips <rustyp@xxxxxxxxx.xxx> wrote:
> I'm trying to not require primary keys during insertion, which will work
> for all RDBMSs.

But if Class::DBI doesn't know what the primary key is that was just
inserted, then it can not guarantee that it will return be able to
retrieve the correct row after the create.  Remember that after an
insert, Class::DBI will retrieve that row from the table again (in
case the database inserted any default values, or munged the data in
some other way).

MySQL has a special way of telling you what the last auto increment ID
was, but most other databases don't work the same way.  When a
sequence is used to fill in the primary key, the safest way to insert
a record, is to select the next entry from the sequence, and then
insert that value into the database as the next row.  Then you know
for sure which record in the database is the one you just inserted.

So I don't think it is possible to build a database agnostic system
for doing inserts and then successfully retrieving the primary key of
the row that was just inserted.

> Most of the Class::DBI subclasses don't just not insert anything.
> Instead, they know the name of the sequence that is autogenerated by the
> database, and refer to it in the insert clause...or at least, they know
> what it was called when the Class::DBI subclass was created.  This isn't
> always the same as the autogenerated sequence name in the current
> version of the RDBMS.  Once again, for the reasons I've stated, I'd
> rather go with a RDBMS-agnostic solution if it is possible.

If you are changing the structure of your database, then you need to
tell Class::DBI about the changes.  So if you change the name of the
sequence, you need to tell Class::DBI about that change.  I don't
think that is too much to ask.  Of course, if you use the set_up_table
method to autodetect your database settings, this would not be a
problem.  But again that is very database specific (actually I believe
some of the database drivers have to guess the name of the sequence
based on the name of the table, which is definately not ideal).

Cheers,

Cees

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi