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

[Michael Higgins]

On Thu, 10 Jul 2008 10:27:56 -0400
John Myles White <jmw at johnmyleswhite.com> wrote:

> 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.
> 

[snip]

Linux 2.6.24 (Gentoo) & this is perl, v5.8.8 built for i686-linux

Microsoft SQL Server 2000 DBIx::Class 0.08010

> (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 -- 

No help, but I can confirm that I see the same behaviour. Occasionally
I put up a set of records with only one record. This inevitably throws a
warning.

I'll see about starting my script with a useless query to see if the
warnings go away. However, I do think the issue is specific to
DBIx::Class.

Cheers,

-- 
 |\  /|        |   |          ~ ~  
 | \/ |        |---|          `|` ?
 |    |ichael  |   |iggins    \^ /
 michael.higgins[at]evolone[dot]org