subquery insert for MSSQL

5 messages

subquery insert for MSSQL

Reply Threaded

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?

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

--
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/dbix-class@...

Peter Rabbitson-2

Re: subquery insert for MSSQL

Reply Threaded

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? :)

_______________________________________________
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/dbix-class@...

fREW Schmidt

Re: subquery insert for MSSQL

Reply Threaded

On Wed, Jul 22, 2009 at 7:26 AM, Peter Rabbitson <[hidden email]> 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/dbix-class@...

Peter Rabbitson-2

Re: subquery insert for MSSQL

Reply Threaded

On Wed, Jul 22, 2009 at 09:30:19AM -0500, fREW Schmidt wrote:

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

I don't quite follow... What are the bind values that the top placeholders
take? Also '?, seems to be a typo... I think.

_______________________________________________
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/dbix-class@...

fREW Schmidt

Re: subquery insert for MSSQL

Reply Threaded

On Thu, Jul 23, 2009 at 4:48 PM, Peter Rabbitson <[hidden email]> wrote:

On Wed, Jul 22, 2009 at 09:30:19AM -0500, fREW Schmidt wrote:
>
> 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 = ? ) )
>
>
>

I don't quite follow... What are the bind values that the top placeholders
take? Also '?, seems to be a typo... I think.

yeah, '? is a typo. Sorry about that.

I'm not really sure what you don't understand about the above. I'll retype it without the placeholders I guess:

INSERT INTO WorkScopeOperations (
   department,
   description,
   id,
   signature_required,
   work_order_id,
   work_scope_id
) SELECT
   'department foo',
   'description blah',
   MAX(id) + 1,
   'mechanics sig required',
   1121, -- work_order_id
   2 -- work_scope_id
      FROM WorkScopeOperations me
      WHERE ( ( work_order_id = 1121 AND work_scope_id = 1 ) )

Does that answer the question?