[CDBI] set_sql() and bind columns

[prev] [thread] [next] [Date index for 2005/08/11]

I suspect this might be more of a problem with my SQL than with CDBI.
(Plus, there's likely a much better way to do this.)

I have a table and one column is an integer "sort_order".  In my
table display I want to have "move up" and "move down" links -- that
adjusts the "sort_order" values on the table to rearrange the overall
sort order.  (The top is 1, so move up means decrease sort_order.)

Now, when I run the code I get and exception:

  DBI bind_columns: invalid number of arguments: got handle + 0, expected handle + between 1 and -1

But the odd thing is that the table is actually rearranged as I want.
So the update is happening, but I'm still getting the error.  I
suspect I'm not passing the bind parameters correctly, or maybe the
CASE statement is causing problems.

Here's the code:

# This returns the items that are *below* the item
# No point in moving $item up if it's already at the top

__PACKAGE__->set_sql('can_move_up', <<'SQL');
    SELECT id FROM __TABLE__
        WHERE
            sort_order <= (SELECT sort_order FROM __TABLE__ WHERE id = ?)
             AND id != ?;
SQL

sub can_move_up {
    my ( $class ) = @_;
    die "Can't move item up in sort order because no sort_order column"
        unless $class->find_column('sort_order');
    return $class->search_can_move_up( $class->id, $class->id );
}



# This moves $item up the list (lower number) and moves everything
# at $item->sort_order -1 down one (sort_order+1).
# (FIX - bumps items when they don't need to be incremented)

__PACKAGE__->set_sql('move_up', <<'SQL');
    UPDATE __TABLE__
    SET sort_order =
        CASE
            -- subtract one from the item's sort, unless it's already "1"
            WHEN id = ? AND sort_order > 1 THEN sort_order-1

            -- for other items that are greater or equal to sort-1
            -- bump their sort order
            WHEN id != ? AND sort_order >= (select sort_order from __TABLE__ where id = ?)-1
                    THEN sort_order+1

            -- all others, leave alone
            ELSE sort_order
        END;
SQL



sub move_item_up {
    my ( $class ) = @_;
    return unless $class->can_move_up;
    my $id = $class->id;
    $class->search_move_up( $id, $id, $id );
    $class->dbi_commit;
}



        -- 
        Bill Moseley
moseley@xxxx.xxx


_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi