Re: LIMIT with placeholder

[prev] [thread] [next] [Date index for 2004/06/09]

On Thu, Jun 10, 2004 at 12:47:27AM -0200, Gabor Szabo wrote:
> Replacing %s by ?
> __PACKAGE__->set_sql(latest => "SELECT * FROM __TABLE__ LIMIT ?");

I don't know any database that allows a placeholder there, so you do
need to do the %s version

> __PACKAGE__->set_sql(latest => "SELECT * FROM __TABLE__ LIMIT %s");

(NB that should be __ESSENTIAL__ rather than *)

The problem is that if you need to interpolate into the the SQL you
can't use the search_ shortcut.  You'll need to call it the long way:

  my @results = $class->sql_to_objects($class->sql_latest(10));

> BTW it is not clear (to me) from the documentation if the placeholder
> should be %s or ?.

This is documented a little more in Ima::DBI, which is where it comes
from.

The sprintf style (%s) placeholders are meant to be used in places where
SQL doesn't allow normal (?) placeholders - such as a LIMIT modifier!

As it's a rather unusual thing there aren't as many shortcuts. The
expectation is that you'll wrap the longwinded call up in a generic
method.

So in this case you'd write something like:

  __PACKAGE__->set_sql(latest => "SELECT __ESSENTIAL__ FROM __TABLE__ LIMIT %s");

  sub retrieve_latest { 
    my ($class, $count) = @_;
    return $class->sql_to_objects($class->sql_latest($count));
  }

Tony