Bug #19594 for Class-DBI-AbstractSearch: Uses SQL::Abstract::Limit incorrectly, causes failure for some db dialects

Tue May 30 02:44:13 2006 Guest - Ticket created

Subject:

Uses SQL::Abstract::Limit incorrectly, causes failure for some db dialects

Thanks for a great module but I have hit this: Class::DBI::AbstractSearch claims to provide support for SQL::Abstract::Limit's implementation of the "limit" and "limit dialect" features. In fact it does this incompletely only by calling SQLAbstractLimit's "where" function. This function only works for some dialects; to support other engines in a more general way the "select" function should have been called. The caller dies with "can't build a stand-alone WHERE clause for $syntax_name". ( where syntax_name is any of several valid values).

Tue May 30 03:07:23 2006 miyagawa [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #19594] Uses SQL::Abstract::Limit incorrectly, causes failure for some db dialects
Date:	Tue, 30 May 2006 16:06:42 +0900
To:	bug-Class-DBI-AbstractSearch [...] rt.cpan.org
From:	"Tatsuhiko Miyagawa" <miyagawa [...] gmail.com>

Can you give me test file to confirm the bug and a patch to fix that? On 5/30/06, Guest via RT <bug-Class-DBI-AbstractSearch@rt.cpan.org> wrote: Show quoted text

> > Tue May 30 02:44:13 2006: Request 19594 was acted upon. > Transaction: Ticket created by guest > Queue: Class-DBI-AbstractSearch > Subject: Uses SQL::Abstract::Limit incorrectly, causes failure for some db > dialects > Broken in: (no value) > Severity: Important > Owner: Nobody > Requestors: frankc@ddb.nsw.gov.au > Status: new > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=19594 > > > > Thanks for a great module but I have hit this: > Class::DBI::AbstractSearch claims to provide support for > SQL::Abstract::Limit's implementation of the "limit" and "limit > dialect" features. > In fact it does this incompletely only by calling > SQLAbstractLimit's "where" function. This function only works for > some dialects; to support other engines in a more general way > the "select" function should have been called. The caller dies > with "can't build a stand-alone WHERE clause for $syntax_name". ( > where syntax_name is any of several valid values). >

-- Tatsuhiko Miyagawa

Tue May 30 03:07:24 2006 The RT System itself - Status changed from 'new' to 'open'

Sat Jun 03 14:39:20 2006 rjbs [...] cpan.org - Correspondence added

I'm not sure how to produce a great test for this, but this is a real problem. Some limit dialects can't be put in the WHERE clause. MSSQL, for example, works like this: SELECT TOP 10 * FROM table WHERE column='value' -- rjbs

Sun Jun 04 22:58:57 2006 Guest - Correspondence added

From:

Frank Carnovale

Yes it's MSSQL that we have, hence our problem. The "failing test case" is simply any test using MSSQL . Anyway I have been working on a fix.. attached as diff and pasted here.. seems to do the job ok.. regds frank 30,35c30,33 < my $sql = SQL::Abstract::Limit->new(%$attr, limit_dialect=>$class); < my $table = $class->table; < my $fields = [ $class->primary_columns ]; < my ($stmt, @bind) = $sql->select($table, $fields, $where, $order, $limit, $offset); < $class->set_sql('_CDBIABSTRACTSEARCH', $stmt); < $class->sth_to_objects($class->sql__CDBIABSTRACTSEARCH($stmt), \@bind); --- Show quoted text

> my $sql = SQL::Abstract::Limit->new(%$attr); > my($phrase, @bind) = $sql->where($where, $order, $limit,

$offset); Show quoted text

> $phrase =~ s/^\s*WHERE\s*//i; > return $class->retrieve_from_sql($phrase, @bind);

30,35c30,33 < my $sql = SQL::Abstract::Limit->new(%$attr, limit_dialect=>$class); < my $table = $class->table; < my $fields = [ $class->primary_columns ]; < my ($stmt, @bind) = $sql->select($table, $fields, $where, $order, $limit, $offset); < $class->set_sql('_CDBIABSTRACTSEARCH', $stmt); < $class->sth_to_objects($class->sql__CDBIABSTRACTSEARCH($stmt), \@bind); --- > my $sql = SQL::Abstract::Limit->new(%$attr); > my($phrase, @bind) = $sql->where($where, $order, $limit, $offset); > $phrase =~ s/^\s*WHERE\s*//i; > return $class->retrieve_from_sql($phrase, @bind);