[Dbix-class] subquery insert for MSSQL

[fREW Schmidt]

On Wed, Jul 22, 2009 at 7:26 AM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> 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 a=
nd
> > 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 =3D shift;
> > >    my (@args) =3D @_;
> > >    my $self =3D $class->next::method(@args);
> > >    $self->id($self->_generate_id);
> > >    return $self;
> > > }
> > >
> > > method _generate_id {
> > >    return $self->result_source->resultset->search({
> > >          work_order_id =3D> $self->work_order_id,
> > >          work_scope_id =3D> $self->work_scope_id
> > >       },{
> > >          columns  =3D> { new_id =3D> \'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 =3D ? AND work_scope_id =3D ? ) )
> > >     ),
> > >     ?,
> > >     ?,
> > >     ?
> >
> > );
> > >
> >
> > 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 =3D ? AND work_scope_id =3D ? ) )



-- =

fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090722/b2c=
512b8/attachment-0001.htm