[Dbix-class] subquery insert for MSSQL

[fREW Schmidt]

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 =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?

Note: I also tried this with rows =3D> 1 (TOP 1) and no max but instead
sorting and doing top 1.

-- =

fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090721/227=
3c5dd/attachment.htm