[Dbix-class] Microsoft SQL Server Primary Key Auto-Increment Woes

[John Myles White]

I've recently been tasked at work with porting a large in-house web  
app that uses DBIx::Class from MySQL to Microsoft SQL Server. In the  
process, I've come across a problem where the auto-incrementing  
primary key column of the first row created using a DBIx::Class schema  
object is given an undef value rather than its true numeric value. All  
subsequent calls to create() against the same schema object give the  
proper numeric value of the primary key. Also, this problem does not  
come up if any other method such as find() or search() has been  
previously called against the schema object. Only a create() that is  
the very first method called on a given schema fails.

I unfortunately do not know enough about the architecture of  
DBix::Class to solve this by myself, but I am inclined to assume based  
on my initial poking around that the schema is not aware that it needs  
to use MSSQL specific workarounds for last_insert_id until after the  
first query is made to the database.

The following code snippet exhibits the problem consistently while  
using DBIx::Class 0.08009 with ActiveState's Perl build 822 and  
Microsoft SQL Server 2005.

my $schema = Schema->connect($dsn, $user, $pwd);

my $first_object = $schema->resultset('Object')->create({other_column  
=> 13});

if (not defined $first_object->object_id)
{
     print "No ID on first_object\n";
}

my $second_object = $schema->resultset('Object')->create({other_column  
=> 13});

if (not defined $second_object->object_id)
{
     print "No ID on second_object\n";
}

The next bit of code does not have any problems because a search() is  
called on the schema before the first create().

my $schema = Schema->connect($dsn, $user, $pwd);

my @objects = $schema->resultset('Object')->search();

my $first_object = $schema->resultset('Object')->create({other_column  
=> 13});

if (not defined $first_object->object_id)
{
     print "No ID on first_object\n";
}

my $second_object = $schema->resultset('Object')->create({other_column  
=> 13});

if (not defined $second_object->object_id)
{
     print "No ID on second_object\n";
}

If anyone wants more thorough code to test this, I can provide a  
complete tarball by e-mail to those interested.

My hope in writing to the mailing list is that someone can point me in  
the right direction to work on solving this at the level of the  
DBIx::Class source itself (assuming I'm not making an ass of myself by  
thinking that the problem is with DBIx::Class rather than with my use  
of it). With a little guidance, I would be happy to come up with a  
patch myself. At present I have a means of working around the problem  
temporarily by simply making useless queries in advance of any calls  
to create().

Thanks in advance for any comments anyone has to offer.

  -- John