[patch] Class::DBI & SQL Server

[prev] [thread] [next] [Date index for 2004/06/06]

I recently started using Class::DBI with an SQL Server database, but had
to make a small change before it would work.

When inserting a row into a table that has an auto-incrementing (identity
in SQL Server) primary key, you can't specify the key & value in the query
(even the value is '' or undef).  If you try to force the insert by
setting IDENTITY_INSERT ON you lose the auto-incrementing property of the
column.

Perhaps there's an existing (or better) way to get around this, but I have
attached a patch containing my solution.  If there is a better way I'd be
interested in hearing it.

This patch has been tested against MySQL as well.

Also, as you can probably guess Class::DBI::_auto_increment_value also
doesn't work, but I've fixed that by overriding the method in my
Class::DBI::SQLServer module (which I'll upload to CPAN if the patch is
accepted).

Note: I did think about overriding _insert_row in my
Class::DBI::SQLServer module, but thought that the change would be better
off in Class::DBI as it could help with other databases.

Cheers,
-James O'Sullivan

--- lib/Class/DBI.pm.orig	Fri Apr 30 08:22:12 2004
+++ lib/Class/DBI.pm	Wed May 26 21:54:53 2004
@@ -627,6 +627,11 @@
 	my $self = shift;
 	my $data = shift;
 	eval {
+		my @primary_columns = $self->primary_columns;
+		if(@primary_columns == 1 && !defined $data->{ $primary_columns[0] })
+		{
+			delete $data->{$primary_columns[0]};
+		}
 		my @columns = keys %$data;
 		my $sth     = $self->sql_MakeNewObj(
 			join(', ', @columns),
@@ -634,7 +639,6 @@
 		);
 		$self->_bind_param($sth, \@columns);
 		$sth->execute(values %$data);
-		my @primary_columns = $self->primary_columns;
 		$data->{ $primary_columns[0] } = $self->_auto_increment_value
 			if @primary_columns == 1
 			&& !defined $data->{ $primary_columns[0] };