Re: [CDBI] ODBC last_insert_id Problems

James Hargreaves wrote:
> Hi guys!
> 
> I normally use MySQL as my backend database, but for a particularly
> cheapskate project (MySQL costs £25 per year on my hosting) I have
> been forced to use MS Access accessed via ODBC.
> 
> This was fine until I started trying to insert items into the database
> using the usual My::Class->insert(...) method. Items are inserted
> correctly but I receive an error due to there being no support (so far
> as I am aware) for the LAST_INSERT_ID function in MS Access. All this
> is probably not helped by my not being able to use the latest version
> of Class-DBI since I am running on windows and it doesn't compile
> correctly :(

Have a look at Class::DBI::MSSQL. It adds last_insert_id support for 
Microsofts databases, and works well with Access and SQL Server.

> So basically - what can I do? I cannot change the database - I might
> be able to access it directly (rather than through ODBC) but I am not
> sure if this will help or how to do this? The problem occurs in the
> _auto_increment_value method (called in the _insert_row method) and I
> can't see any way to fix this function without trashing something
> else.


FWIW, I've had better results with DBD::ADO than with DBD::ODBC, at least in 
combination with Access. It has its own set of quirks, but e.g. unicode 
support is much better.

HTH,

Rhesa