| Subject: | "getRowsWhere" method uses Postgres syntax "LIMIT ALL" even on MySQL DB's |
| Date: | Wed, 28 Jul 2010 09:55:49 -0700 (PDT) |
| To: | bug-DB [...] rt.cpan.org |
| From: | Roger Kenneth Trussell <joejavacavalier2001 [...] yahoo.com> |
Hi there,
My name is Roger Trussell.
I noticed a quirky problem with the DB::Table CPAN module.
I'm still using Fedora 10 with Perl v5.10.0 .
I noticed that the getRowsWhere methods of the Table and Row objects currently uses "LIMIT ALL" when $options->{'pageLength'} is not specified. I remember reading the MySQL documentation about SELECT statements served by MySQL. That documentation states, "For compatibility with PostgreSQL, MySQL also supports the
LIMIT row_count OFFSET
offset syntax." I noticed that problems occur when "LIMIT ALL" is used. I guess that ALL is not an acceptable for "row_count".
I like to propose the following changes to the Perl code that seems to work for me.
(I know it works for MySQL. I don't know the implications of removing "LIMIT ALL" for other database servers. You could use the "get_info" method of the dbh object to determine what type of database server you're using. Anyways, here is my code change proposal. Thanks. Sincerely, Roger
Look for "sub getRowsWhere" in the Rows.pm file.
Look for the lines of code that start with [ if ($where ne '') ].
if ($where ne '')
{
if ($options->{'pageLength'} ne 'ALL')
{
$get_obj_sql = sprintf("SELECT %s, %s FROM %s WHERE %s ORDER BY %s LIMIT %s OFFSET %s",
join(', ', $table->primaryKeys()),
join(', ', $table->fields()),
$table->name(),
$where,
$options->{'orderBy'},
$options->{'pageLength'},
$options->{'pageOffset'});
}
else
{
$get_obj_sql = sprintf("SELECT %s, %s FROM %s WHERE %s ORDER BY %s",
join(', ', $table->primaryKeys()),
join(', ', $table->fields()),
$table->name(),
$where,
$options->{'orderBy'});
}
}
else
{
if ($options->{'pageLength'} ne 'ALL')
{
$get_obj_sql = sprintf("SELECT %s, %s FROM %s ORDER BY %s LIMIT %s OFFSET %s",
join(', ', $table->primaryKeys()),
join(', ', $table->fields()),
$table->name(),
$options->{'orderBy'},
$options->{'pageLength'},
$options->{'pageOffset'});
}
else
{
$get_obj_sql = sprintf("SELECT %s, %s FROM %s ORDER BY %s",
join(', ', $table->primaryKeys()),
join(', ', $table->fields()),
$table->name(),
$options->{'orderBy'});
}
}