| Subject: | Bug: Cannot use Placeholder for LIMIT or OFFSET expressions |
| Date: | Thu, 24 Jan 2019 11:43:52 -0800 |
| To: | bug-DBD-ODBC [...] rt.cpan.org |
| From: | David Wheeler <david [...] justatheory.com> |
While developing Snowflake support for Sqitch. When preparing statements, I often use placeholders, which are denoted in the query by a question mark (?). This works great for, say, WHERE clauses; however, it does not work for LIMIT or OFFSET expressions. Sample script:
#!/usr/bin/env perl -w
use strict;
use warnings;
use v5.10;
use DBI;
my $dsn = 'dbi:ODBC:Server=iovation.snowflakecomputing.com;Driver=Snowflake';
my $dbh = DBI->connect($dsn, 'dwheeler', 'MLcpLLDDy7T7bVrHxe9W2QgECvY.GK]kJdgFfnvQFp>yT)Zx', {
PrintError => 0,
RaiseError => 1,
AutoCommit => 1,
odbc_utf8_on => 1,
});
$dbh->do('USE DATABASE dwheeler');
$dbh->do('USE WAREHOUSE dwheeler');
my $sth = $dbh->prepare(
'SELECT table_name FROM information_schema.tables LIMIT ?'
);
$sth->execute(3);
say join ', ', @{ $dbh->selectcol_arrayref($sth) };
When I run this script, the output is: Invalid row count '?' in limit clause (SQL-2201W) at /Users/david/bin/try line 24. Which makes me think that the ? isn't being parsed and replaced with the argument to execute(). Note that this does work with other databases, including those that require an ODBC driver.
So I reported it as a bug to Snowflake back in August; they got back to me today. They said:
Show quoted text
> We created a small test application that runs the exact same SQL statement on top of our latest ODBC driver:
>
> ODBC_Class odbc;
>
> SQLRETURN rc = SQLConnect(odbc.ConHandle, (SQLCHAR *const)"SnowflakeDSII", SQL_NTS, nullptr, 0, nullptr, 0);
>
> odbc.check_rc(rc, LINE, FILE);
> rc = SQLAllocHandle(SQL_HANDLE_STMT, odbc.ConHandle, &odbc.StmtHandle);
>
> odbc.check_rc(rc, LINE, FILE);
>
> rc = SQLPrepare(odbc.StmtHandle, (SQLCHAR *)"SELECT table_name FROM information_schema.tables LIMIT ?;", SQL_NTS);
>
> odbc.check_rc(rc, LINE, FILE);
>
> int intVal = 3;
> rc = SQLBindParameter(odbc.StmtHandle, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &intVal, 0, NULL);
>
> odbc.check_rc(rc, LINE, FILE);
>
> rc = SQLExecute(odbc.StmtHandle);
> odbc.check_rc(rc, LINE, FILE);
>
> odbc.GetResultset(true);
>
> SQLDisconnect(odbc.ConHandle);
> }
>
> It can print out the correct result and there is no error thrown, so I guess there is something happening in this Perl's DBD:: ODBC library.
So now I'm reporting it here. Is this an issue in DBD::ODBC or in the Snowflake ODBC driver?
Thanks,
David