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/[email protected]
