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

[Matt S Trout]

On Fri, Jul 11, 2008 at 01:41:40PM -0700, Michael Higgins wrote:
> 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.

Are you guys using MSSQL via ODBC?

I think because that tries to wrap $storage->insert it won't work unless
the $storage is already connected.

$schema->storage->ensure_connected;

should work as a query-free workaround if so.

Assuming that's true, can Marc Mims (the author) or anybody else who's
interested start looking at a fix?

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/