Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 128346
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: david [...] justatheory.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 2.3k
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
Download (untitled) / with headers
text/plain 3.1k
On Thu Jan 24 14:53:04 2019, david@justatheory.com wrote: Show quoted text
> 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: >
> > 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
Thanks for the report David. By sheer coincidence I was looking at sqitch yesterday. I can't think of any reason why this would happen right now. Some C code parses the SQL looking for ? but your SQL of "SELECT table_name FROM information_schema.tables LIMIT ?" is so simple I cannot see why it would fail. I think it is more likely to be something like the ODBC driver not supporting SQLDescribeParam. Can you set DBI_TRACE=15=x.log and export it then run the sample script and send me the log output please. Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #128346] Bug: Cannot use Placeholder for LIMIT or OFFSET expressions
Date: Sat, 26 Jan 2019 08:29:09 -0800
To: Martin J Evans via RT <bug-DBD-ODBC [...] rt.cpan.org>
From: David Wheeler <david [...] justatheory.com>
Here you go. David
Download odbc-snowsql.txt
text/plain 16k

Message body is not shown because sender requested not to inline it.

Download (untitled) / with headers
text/plain 1.6k
On Sat Jan 26 11:29:31 2019, david@justatheory.com wrote: Show quoted text
> Here you go. > > David
There is a difference between C ODBC calls the snowflake guys sent you and what DBD::ODBC did: check_for_unicode_param - sql_type=unknown, described=VARCHAR -get_param_type bind 1 3 value_len=1 maxlen=9 null=0) bind 1 value_type:1 VARCHAR cs=16777216 dd=0 bl=1 SQLBindParameter: idx=1: io_type=1, name=1, value_type=1 (SQL_C_CHAR), SQLType=12 (VARCHAR), column_size=16777216, d_digits=0, value_ptr=7fc1dd826d90, buffer_length=1, ind=1, param_size=16777216 Param value = 3 -rebind_param +dbd_st_execute_iv(7fc1dc843ac8) dbd_st_finish(7fc1dc843ac8) outparams = 0 SQLExecute/SQLExecDirect(7fc1dd925100)=-1 !!dbd_error2(err_rc=-1, what=st_execute/SQLExecute, handles=(7fc1db448e90,7fc1db4492d0,7fc1dd925100) !SQLError(7fc1db448e90,7fc1db4492d0,7fc1dd925100) = (2201W, 2010, SQL compilation error: Invalid row count '?' in limit clause) The invalid row count '?' mislead us, I think it is whinging that the value supplied for that placeholder is invalid. DBD::ODBC did bind the value 3 to that placeholder but as a VARCHAR and not a number and I suspect that is why it complains. Their ODBC driver describes the parameter as a VARCHAR: SQLDescribeParam 1: SqlType=VARCHAR(12) param_size=16777216 Scale=0 Nullable=1 so that is what DBD::ODBC did. I would probably argue this is a bug in their driver 1) they described it as a VARCHAR 2) then they refused to accept it as a VARCHAR. You might be able to workaround it by specifying the bind type when you bind the parameter but I cannot test right now. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 287b
On 2019-01-27 08:33:05, MJEVANS wrote: Show quoted text
> I would probably argue this is a bug in their driver 1) they described > it as a VARCHAR 2) then they refused to accept it as a VARCHAR.
Sounds right to me. Thanks for the analysis, Martin. I've followed up in their support issue. Best, David
Download (untitled) / with headers
text/plain 496b
On Mon Jan 28 10:52:14 2019, DWHEELER wrote: Show quoted text
> On 2019-01-27 08:33:05, MJEVANS wrote: >
> > I would probably argue this is a bug in their driver 1) they > > described > > it as a VARCHAR 2) then they refused to accept it as a VARCHAR.
> > Sounds right to me. Thanks for the analysis, Martin. I've followed up > in their support issue. > > Best, > > David
No problem David. Glad I could help. The workaround might still work for you until they fix it. Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #128346] Bug: Cannot use Placeholder for LIMIT or OFFSET expressions
Date: Mon, 28 Jan 2019 10:47:02 -0800
To: Martin J Evans via RT <bug-DBD-ODBC [...] rt.cpan.org>
From: David Wheeler <david [...] justatheory.com>
Download (untitled) / with headers
text/plain 338b
On Mon, Jan 28, 2019, at 10:35 AM, Martin J Evans via RT wrote: Show quoted text
> No problem David. Glad I could help. The workaround might still work for > you until they fix it.
Oh, Sqitch just inlines the value for now. https://github.com/sqitchers/sqitch/blob/660ad175817253c484be0471674e876bbfafae42/lib/App/Sqitch/Engine/snowflake.pm#L392 D
Download (untitled) / with headers
text/plain 528b
I heard back from Snowflake: Show quoted text
> We have confirmed that this is an issue on Snowflake ODBC driver. > > For SQLDescribeParam(), SF ODBC driver would only return the number of parameters and hardcode each param type as SQL_VARCHAR with size 16777216. > > Currently, SF ODBC driver doesn’t support SQLDescribeParam which would contain other metadata information such as data type, etc along with a number of bind parameters.
They took on the task of fixing it, so I thin you can close this report. Many thanks, Martin! David
On Tue Jan 29 12:47:47 2019, DWHEELER wrote: Show quoted text
> I heard back from Snowflake: >
> > We have confirmed that this is an issue on Snowflake ODBC driver. > > > > For SQLDescribeParam(), SF ODBC driver would only return the number > > of parameters and hardcode each param type as SQL_VARCHAR with size > > 16777216. > > > > Currently, SF ODBC driver doesn’t support SQLDescribeParam which > > would contain other metadata information such as data type, etc along > > with a number of bind parameters.
> > They took on the task of fixing it, so I thin you can close this > report. Many thanks, Martin! > > David
Thanks for getting back to me David. I've closed the RT now. However, I suspect they are going to find fixing this somewhat harder than they might imagine given their comments. Accepting the VARCHAR for an integer parameter might not be too bad but providing a good SQLDescribeParam is not easy at all (and I know as I have some experience of this with other databases and writing ODBC drivers for them). Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.