Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

I'm hoping someone will just recognize this and know what the problem is, because if not it means a lot more work on my part to figure it out. I've run into a problem where it appears DBI is incorrectly binding a number as a string in the limit on a query.

I'm using DBI and CGI::Application in a website, part of which is a catalog / shopping cart / purchase system. Products in the catalog are broken into pages of 20 items each. This breaking of pages is done by encoding the number of prizes into a link, and limiting my select based on that number. So I have a link like:

<A href="catalog.cgi?startitem=21">Next</A>

where the startitem is set dynamicly based on the current page. (there are other params passed here too, but anyway) The actual query looks something like:

$sth = $self->param('dbh_shopping')->prepare(qq{ SELECT ... a somewhat large query ... WHERE catalogId = ? LIMIT ?, ? }); my $start_item = ($self->query->param('startitem') > 0) ? $self->query->param('startitem') - 1 : 0;
So what I want to do after that is:
$sth->execute( $self->param('catalog_id'), $start_item, 20 );

However, DBI binds $start_item as a string, and quotes it. This causes an error, as limit doesn't take a string. Here's where it gets bizzare. I tried playing with $start_item in all sorts of ways, trying to figure out why DBI was turning it into a string. Some examples of things that didn't work: (DBI still quoted the param)

$start_item + 0 int($start_item) my $start_item2 = $start_item;

As other tests, I tried putting a hardcoded number in the call to $sth->execute(), and it worked. I tried assigning the $start_item in the second part of the limit (third bound param instead of second) and DBI quoted it. So far, the only way I've been able to get it working is the insane:

my $this_stupid_counter = 0; for (my $x=0; $x<$self->query->param('startitem'); $x++) { $this_stupid_counter++; } $sth->execute( $self->param('catalog_id'), $this_stupid_counter, 20 );

which should absolutely not be necessary. Any ideas?

In reply to DBI (incorrectly) bound variables by Xaositect

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others scrutinizing the Monastery: (11)
    As of 2019-10-15 11:04 GMT
    Find Nodes?
      Voting Booth?