On Wed, Jul 22, 2009 at 7:26 AM, Peter Rabbitson
<[email protected]<rabbit%[email protected]>
> wrote:
> On Tue, Jul 21, 2009 at 05:11:22PM -0500, fREW Schmidt wrote:
> > Hey guys,
> >
> > I am trying to autoincrement the last part of a composite primary key and
> > it's not working like I hoped and dreamed that it would. It's close
> though!
> >
> > Here's the code in my result class:
> >
> > sub new {
> > > my $class = shift;
> > > my (@args) = @_;
> > > my $self = $class->next::method(@args);
> > > $self->id($self->_generate_id);
> > > return $self;
> > > }
> > >
> > > method _generate_id {
> > > return $self->result_source->resultset->search({
> > > work_order_id => $self->work_order_id,
> > > work_scope_id => $self->work_scope_id
> > > },{
> > > columns => { new_id => \'MAX(id) + 1' },
> > > })->get_column('new_id')->as_query;
> > > }
> > >
> >
> > Here is the generated SQL:
> >
> > INSERT INTO WorkScopeOperations (
> > > department,
> > > description,
> > > id,
> > > signature_required,
> > > work_order_id,
> > > work_scope_id
> > > ) VALUES (
> > > ?,
> > > ?, (
> > > SELECT MAX(id) + 1 FROM WorkScopeOperations me
> > > WHERE ( ( work_order_id = ? AND work_scope_id = ? ) )
> > > ),
> > > ?,
> > > ?,
> > > ?
> >
> > );
> > >
> >
> > Here is the error message:
> >
> > [error] DBIx::Class::ResultSet::create(): DBI Exception: DBD::ODBC::st
> > > execute failed: [Microsoft][SQL Native Client][SQL Server]Subqueries
> are not
> > > allowed in this context. Only scalar expressions are allowed.
> (SQL-42000)
> > >
> >
> > I read online about the syntax to do what I want with SQL Server, which
> > leads me to believe that I probably can't do this with DBIC at this
> point,
> > but hopefully I'm wrong.
> >
> > Any tips?
> >
>
> Here is tip#1 - how about showing us the SQL which will make MSSQL happy?
> :)
>
Indeed. The following works:
> INSERT INTO WorkScopeOperations (
> department,
> description,
> id,
> signature_required,
> work_order_id,
> work_scope_id
> ) SELECT
> ?
> '?,
> MAX(id) + 1,
> ?,
> ?,
> ?
> FROM WorkScopeOperations me
> WHERE ( ( work_order_id = ? AND work_scope_id = ? ) )
--
fREW Schmidt
http://blog.afoolishmanifesto.com
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
