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)



MIME-Version: 1.0
X-Spam-Status: No, score=-1.9 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1] autolearn=ham
X-Mailer: MessagingEngine.com Webmail Interface - ajax-36e4bfd3
X-Me-Proxy: <xmx:eBVKXFWV2sQ_Kje2lhVc-UnA3e2xm8gBP0ArGZgwkMDD37HMuLDvHQ> <xmx:eBVKXA8S17FeUQYT6izSnd5swiDp_Fcdm67MCHjAmEesZnfjFc1I-A> <xmx:eBVKXKwwUcewpi_-b1s1e5_wxhF4JdybETUFxBkEF1GqGdhdO9WsKg> <xmx:eBVKXL6PXnIyEdLP1zeWnv0u2pdF9eh1-BnjEaE4R0hB1KVi_UVSag>
X-Cpan.org: This message routed through the cpan.org mail forwarding service. Please use PAUSE pause.perl.org to configure your delivery settings.
X-Spam-Flag: NO
content-type: text/plain; charset="utf-8"
Message-ID: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Spam-Score: -1.9
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 48450240288 for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Thu, 24 Jan 2019 14:53:03 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id btGS7fiIp7Sa for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Thu, 24 Jan 2019 14:53:01 -0500 (EST)
Received: from xx1.develooper.com (xx1.develooper.com [207.171.7.115]) by hipster.bestpractical.com (Postfix) with ESMTPS id 243172401F6 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 24 Jan 2019 14:53:00 -0500 (EST)
Received: from localhost (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with ESMTP id B4B05120225 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 24 Jan 2019 11:52:59 -0800 (PST)
Received: from xx1.develooper.com (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with SMTP id DC769121C34 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 24 Jan 2019 11:52:56 -0800 (PST)
Received: from out5-smtp.messagingengine.com (out5-smtp.messagingengine.com [66.111.4.29]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by xx1.develooper.com (Postfix) with ESMTPS id 505C0120225 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 24 Jan 2019 11:52:55 -0800 (PST)
Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id C4EF822267 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 24 Jan 2019 14:43:52 -0500 (EST)
Received: from web6 ([10.202.2.216]) by compute1.internal (MEProxy); Thu, 24 Jan 2019 14:43:52 -0500
Received: by mailuser.nyi.internal (Postfix, from userid 99) id 192DD41EB; Thu, 24 Jan 2019 14:43:52 -0500 (EST)
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] pobox.com
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] messagingengine.com
Delivered-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
Subject: Bug: Cannot use Placeholder for LIMIT or OFFSET expressions
Return-Path: <david [...] justatheory.com>
X-Me-Sender: <xms:eBVKXI2bk9sASXmxHbLL6vmWO9ZoMz9CzgmC0jmUDKBCJItJ3Y6BXg>
X-RT-Mail-Extension: dbd-odbc
X-Original-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pobox.com; h= message-id:from:to:mime-version:content-transfer-encoding :content-type:date:subject; s=mar2016.fm; bh=qa5rFaaPZLSVIWwCb+O upXxzS/raN+G7oipHxRZENTU=; b=wbFA1vdbNH3pZ7/TMzjbOrjlP9qUKskEHw2 olX4JnKEbKyXHduuOlw9ilMOGs3qoYLkyPqXQVxVVW3RmeIM9Wm/rCBY1Nls5lex czzHlIRq2Cj5y58Px0tiGMB6ihhYTZgvNvFH65Ussg5Bz4rF4iAgSE0LgSPdVhaZ DlJj90BI=
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:message-id:mime-version:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=qa5rFa aPZLSVIWwCb+OupXxzS/raN+G7oipHxRZENTU=; b=a0wB/FdGK004S/1a48dxqY cj8T+7et7d6RiyGOqywcC3B4hx/t6xtFa59FtJHQkPilZGv0AtAj0yahvQu0SXtu HCgt/ztWkMKedkag+ayIq1jJdNhHOtssZSHNHy9D2XlphP9ysqTrNqKk01rHhg4V e5Y3zh6vLYPLYVI/YwhqD5D45UYOe812ge3xJQt8wzJMRd5f2A+KeHQN9z1zd1Hq XGYpP5gWQmD1sNrEH8Tp1AG7mxAw5Iie4lX2pphUGwn1+FVkXsU1xbPd1fm+lZrQ f/Nnsenjqs9IhcknxvprIRyyC7O4ivYS7QEdT5j84j2akUkUNwgQ1kt2hrZAhzAA ==
X-PMX-Spam: Gauge=X, Probability=10%, Report=' URI_HOSTNAME_CONTAINS_EQUALS 0.4, HTML_00_01 0.05, HTML_00_10 0.05, SUPERLONG_LINE 0.05, BODYTEXTP_SIZE_3000_LESS 0, BODY_SIZE_2000_2999 0, BODY_SIZE_5000_LESS 0, BODY_SIZE_7000_LESS 0, DATE_TZ_NA 0, DKIM_SIGNATURE 0, LEGITIMATE_SIGNS 0, MSG_THREAD 0, NO_URI_HTTPS 0, SPF_NEUTRAL 0, WEBMAIL_SOURCE 0, WEBMAIL_XMAILER 0, __ANY_URI 0, __CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __FORWARDED_MSG 0, __FRAUD_MONEY_CURRENCY 0, __FRAUD_MONEY_CURRENCY_DOLLAR 0, __HAS_FROM 0, __HAS_MSGID 0, __HAS_X_MAILER 0, __MIME_TEXT_ONLY 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_VERSION 0, __NO_HTML_TAG_RAW 0, __PHISH_SPEAR_STRUCTURE_1 0, __PHISH_SPEAR_STRUCTURE_2 0, __PHISH_SPEAR_SUBJ_PREDICATE 0, __SANE_MSGID 0, __TO_MALFORMED_2 0, __TO_NO_NAME 0, __URI_NO_MAILTO 0, __URI_NO_WWW 0, __URI_NS , __zen.spamhaus.org_ERROR '
Date: Thu, 24 Jan 2019 11:43:52 -0800
X-Spam-Level:
X-Greylist: delayed 542 seconds by postgrey-1.34 at xx1.develooper.com; Thu, 24 Jan 2019 11:52:56 PST
X-PMX-Version: 5.6.1.2065439, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2019.1.24.194816
To: bug-DBD-ODBC [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: David Wheeler <david [...] justatheory.com>
X-Me-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtledriedvgdduvdejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfquhhtnecuuegrihhlohhuthemucef tddtnecuogetfeejfedqtdegucdlhedtmdenucfjughrpefkhffvggfgtgfofffusehtje ertdertdejnecuhfhrohhmpeffrghvihguucghhhgvvghlvghruceouggrvhhiugesjhhu shhtrghthhgvohhrhidrtghomheqnecuffhomhgrihhnpehsnhhofihflhgrkhgvtghomh hpuhhtihhnghdrtghomhenucfrrghrrghmpehmrghilhhfrhhomhepuggrvhhiugesjhhu shhtrghthhgvohhrhidrtghomhenucevlhhushhtvghrufhiiigvpedt
X-RT-Original-Encoding: utf-8
X-RT-Interface: Email
Content-Length: 2391
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
MIME-Version: 1.0
In-Reply-To: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-1182-1548505920-1753.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 3176
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
MIME-Version: 1.0
X-Spam-Status: No, score=-3.9 tagged_above=-99.9 required=10 tests=[AWL=2.000, BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, FROM_OUR_RT=-4] autolearn=ham
In-Reply-To: <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org>
X-Me-Proxy: <xmx:1opMXBEpvFQBcaxSGn4XymxJlk0h5sKILXWUYJl0MZv7_ZrwNVmrgA> <xmx:1opMXHGRJCFceDjeGZ1iPtDR3Et4YdNwBG18zebwSvTTKkLlzKiExg> <xmx:1opMXPSRmZiBDbDP90inn06zEsce_IXknN3tRF-eoe43Hs0UjdXwFQ> <xmx:1opMXK_MHVgY-RBxNcmE4wx0LTh_oWBlB2RvzJ1tAOJnW9Y0EQYpLw>
X-Mailer: MessagingEngine.com Webmail Interface - ajax-36e4bfd3
X-Cpan.org: This message routed through the cpan.org mail forwarding service. Please use PAUSE pause.perl.org to configure your delivery settings.
X-Spam-Flag: NO
X-RT-Interface: API
References: <RT-Ticket-128346 [...] rt.cpan.org> <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Message-ID: <1548520149.516768.1644125192.2AFACDBF [...] webmail.messagingengine.com>
Content-Type: multipart/mixed; boundary="_----------=_15485201495167682"
X-Spam-Score: -3.9
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] pobox.com
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] messagingengine.com
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 425C5240220 for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Sat, 26 Jan 2019 11:29:30 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 6MK2QA97ThMq for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Sat, 26 Jan 2019 11:29:27 -0500 (EST)
Received: from xx1.develooper.com (xx1.develooper.com [207.171.7.115]) by hipster.bestpractical.com (Postfix) with ESMTPS id 708DE2401C0 for <bug-DBD-ODBC [...] rt.cpan.org>; Sat, 26 Jan 2019 11:29:27 -0500 (EST)
Received: from localhost (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with ESMTP id 12F4D11EBA0 for <bug-DBD-ODBC [...] rt.cpan.org>; Sat, 26 Jan 2019 08:29:26 -0800 (PST)
Received: from xx1.develooper.com (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with SMTP id 72CC311D89F for <bug-DBD-ODBC [...] rt.cpan.org>; Sat, 26 Jan 2019 08:29:18 -0800 (PST)
Received: from out5-smtp.messagingengine.com (out5-smtp.messagingengine.com [66.111.4.29]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by xx1.develooper.com (Postfix) with ESMTPS id 7C7B011EBA0 for <bug-DBD-ODBC [...] rt.cpan.org>; Sat, 26 Jan 2019 08:29:11 -0800 (PST)
Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 7A1EE2188C for <bug-DBD-ODBC [...] rt.cpan.org>; Sat, 26 Jan 2019 11:29:10 -0500 (EST)
Received: from web6 ([10.202.2.216]) by compute1.internal (MEProxy); Sat, 26 Jan 2019 11:29:10 -0500
Received: by mailuser.nyi.internal (Postfix, from userid 99) id E5C9042CF; Sat, 26 Jan 2019 11:29:09 -0500 (EST)
Delivered-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
Subject: Re: [rt.cpan.org #128346] Bug: Cannot use Placeholder for LIMIT or OFFSET expressions
X-Me-Sender: <xms:1opMXCRa7KpX-ro-pH7IASe7exib9duURjlY8lahU9RwNLxUAIGLVQ>
Return-Path: <david [...] justatheory.com>
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pobox.com; h= message-id:from:to:mime-version:content-transfer-encoding :content-type:in-reply-to:references:subject:date; s=mar2016.fm; bh=tSL+etbSCndXYfK/XCd6OGSJi/m7zOkeKGmzd/LKrKw=; b=gVMzFRkQ9dOi H+HBpXHcKIF5DUC3Ei/mswhdIzWNHrl+LN2l8+xkTKZwy2FlhdHcavAfxpoLqrPv RjSIoh5cx9ulzofgOWbokYU5QucCdd2HTErG3+h4MYlJahX+XRbCkUZeBWjAoLyg iiX47wvjN8iEvwlaBthl+C8zgkSfvOE=
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=tSL+etbSCndXYfK/XCd6OGSJi/m7zOkeKGmzd/LKr Kw=; b=OW2UykR7TEA6ZT9krHX+92AQ57KdZar9u2hA+qpFLqOiBJIep6fj8kiee lJkQyLFOLQJqH5VuGTTqKkqvPqf3HhMk+or/e38xy4JFmEuIAspAptjsqFQ7LC9p BR/nD5SwDAzSR+LUkWF+OzVwK1Ro6qZHDUb0HrqPzIAnRlxmm/JRgdezSLPHMyGd ShD4yWsFx3QbT06AaUnQOKZF5YOzMG2Am4GuoG7uNyxyewqx04zPARFAlZ3B0mik tnENj2zubeEFqQsVLJuxwJ/GsvcMouUwr1TfqdN7JtD0RgDRm7qSXGI4i+JlmVtI 0HKc8LmOnzn72SxFIYymugJjqhO9g==
X-Original-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbd-odbc
Date: Sat, 26 Jan 2019 08:29:09 -0800
X-PMX-Spam: Gauge=XII, Probability=12%, Report=' BASE64_ENC_TEXT 0.5, URI_HOSTNAME_CONTAINS_EQUALS 0.4, HTML_00_01 0.05, HTML_00_10 0.05, MIME_TEXT_ONLY_MP_MIXED 0.05, SUPERLONG_LINE 0.05, BODYTEXTP_SIZE_3000_LESS 0, BODYTEXTP_SIZE_400_LESS 0, BODY_SIZE_10000_PLUS 0, DATE_TZ_NA 0, DKIM_SIGNATURE 0, IN_REP_TO 0, LEGITIMATE_SIGNS 0, MSG_THREAD 0, NO_URI_HTTPS 0, REFERENCES 0, SPF_NEUTRAL 0, TXT_ATTACHED 0, WEBMAIL_SOURCE 0, WEBMAIL_XMAILER 0, __ANY_URI 0, __BOUNCE_CHALLENGE_SUBJ 0, __BOUNCE_NDR_SUBJ_EXEMPT 0, __CP_NAME_BODY 0, __CT 0, __CTE 0, __CTYPE_HAS_BOUNDARY 0, __CTYPE_MULTIPART 0, __CTYPE_MULTIPART_MIXED 0, __DQ_NEG_HEUR 0, __DQ_NEG_IP 0, __FRAUD_MONEY_CURRENCY 0, __FRAUD_MONEY_CURRENCY_DOLLAR 0, __HAS_ATTACHMENT 0, __HAS_ATTACHMENT1 0, __HAS_FROM 0, __HAS_MSGID 0, __HAS_X_MAILER 0, __IN_REP_TO 0, __MIME_TEXT_ONLY 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_TEXT_P2 0, __MIME_VERSION 0, __NO_HTML_TAG_RAW 0, __PHISH_SPEAR_STRUCTURE_1 0, __PHISH_SPEAR_SUBJ_PREDICATE 0, __REFERENCES 0, __SANE_MSGID 0, __SUBJ_ALPHA_NEGATE 0, __SUBJ_REPLY 0, __TO_MALFORMED_2 0, __TO_NAME 0, __TO_NAME_DIFF_FROM_ACC 0, __TO_REAL_NAMES 0, __URI_NO_MAILTO 0, __URI_NO_WWW 0, __URI_NS , __zen.spamhaus.org_ERROR '
X-Spam-Level:
X-PMX-Version: 5.6.1.2065439, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2019.1.26.161816
To: Martin J Evans via RT <bug-DBD-ODBC [...] rt.cpan.org>
Content-Transfer-Encoding: 7bit
X-Me-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtledrieeigdeludcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfhuthenuceurghilhhouhhtmecufedt tdenucenucfjughrpefkhffvggfgtgfojghfufffsehmjeerreertdejnecuhfhrohhmpe ffrghvihguucghhhgvvghlvghruceouggrvhhiugesjhhushhtrghthhgvohhrhidrtgho mheqnecuffhomhgrihhnpegtphgrnhdrohhrghenucfrrghrrghmpehmrghilhhfrhhomh epuggrvhhiugesjhhushhtrghthhgvohhrhidrtghomhenucevlhhushhtvghrufhiiigv pedt
From: David Wheeler <david [...] justatheory.com>
RT-Message-ID: <rt-4.0.18-30114-1548520171-1673.128346-0-0 [...] rt.cpan.org>
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
X-RT-Original-Encoding: utf-8
Content-Length: 20
Here you go. David
content-type: text/plain; charset="utf-8"; name="odbc-snowsql.txt"
Content-Disposition: attachment; filename="odbc-snowsql.txt"
Content-Transfer-Encoding: base64
X-RT-Original-Encoding: utf-8
Content-ID: <1548520143.516767.47d13f1e43f77ac82e5d1f42867d569ac5956608.4360740E [...] content.messagingengine.com>
Content-Length: 16426
Download odbc-snowsql.txt
text/plain 16k

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

MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-30114-1548520171-1673.128346-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <RT-Ticket-128346 [...] rt.cpan.org> <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org> <1548520149.516768.1644125192.2AFACDBF [...] webmail.messagingengine.com> <rt-4.0.18-30114-1548520171-1673.128346-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-13885-1548595985-721.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1688
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-13885-1548595985-721.128346-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <RT-Ticket-128346 [...] rt.cpan.org> <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org> <1548520149.516768.1644125192.2AFACDBF [...] webmail.messagingengine.com> <rt-4.0.18-30114-1548520171-1673.128346-0-0 [...] rt.cpan.org> <rt-4.0.18-13885-1548595985-721.128346-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-25998-1548690734-1666.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 287
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-25998-1548690734-1666.128346-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <RT-Ticket-128346 [...] rt.cpan.org> <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org> <1548520149.516768.1644125192.2AFACDBF [...] webmail.messagingengine.com> <rt-4.0.18-30114-1548520171-1673.128346-0-0 [...] rt.cpan.org> <rt-4.0.18-13885-1548595985-721.128346-0-0 [...] rt.cpan.org> <rt-4.0.18-25998-1548690734-1666.128346-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-28001-1548700548-1235.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 496
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
MIME-Version: 1.0
X-Spam-Status: No, score=-4.4 tagged_above=-99.9 required=10 tests=[AWL=1.500, BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, FROM_OUR_RT=-4] autolearn=ham
In-Reply-To: <rt-4.0.18-28001-1548700548-263.128346-6-0 [...] rt.cpan.org>
X-Me-Proxy: <xmx:J05PXETlmBm11zcuM-FCHHgbVC4IGBMJcTJtJvYU1PP2BA-M20HJJA> <xmx:J05PXNlVi0PVD_1C6v9C99jdzQR8m5PUSMXt9OMH0vS7QuMEwOnqEA> <xmx:J05PXISn517ebq4nxti2YCPz0mX6ITMjM9z62Wxr-YnlCuEvrSpB1Q> <xmx:J05PXPgx8XJlLEis3EcXZhA5fcdwujAj7uJqjOajEn0ISlbULWzlBw>
X-Mailer: MessagingEngine.com Webmail Interface - ajax-36e4bfd3
X-Cpan.org: This message routed through the cpan.org mail forwarding service. Please use PAUSE pause.perl.org to configure your delivery settings.
X-Spam-Flag: NO
X-RT-Interface: API
References: <RT-Ticket-128346 [...] rt.cpan.org> <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-1182-1548505921-1752.128346-6-0 [...] rt.cpan.org> <1548520149.516768.1644125192.2AFACDBF [...] webmail.messagingengine.com> <rt-4.0.18-30114-1548520171-1673.128346-6-0 [...] rt.cpan.org> <rt-4.0.18-13885-1548595985-721.128346-6-0 [...] rt.cpan.org> <rt-4.0.18-25998-1548690734-1666.128346-6-0 [...] rt.cpan.org> <rt-4.0.18-28001-1548700548-263.128346-6-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Message-ID: <1548701222.159558.1645459288.4D19311E [...] webmail.messagingengine.com>
content-type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
X-Spam-Score: -4.4
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] pobox.com
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] messagingengine.com
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 464E0240200 for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Mon, 28 Jan 2019 18:09:44 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4RWYhzP206pB for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Mon, 28 Jan 2019 18:09:41 -0500 (EST)
Received: from xx1.develooper.com (xx1.develooper.com [207.171.7.115]) by hipster.bestpractical.com (Postfix) with ESMTPS id 6266324001D for <bug-DBD-ODBC [...] rt.cpan.org>; Mon, 28 Jan 2019 18:09:41 -0500 (EST)
Received: from localhost (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with ESMTP id 2BC767CFA2 for <bug-DBD-ODBC [...] rt.cpan.org>; Mon, 28 Jan 2019 15:09:40 -0800 (PST)
Received: from xx1.develooper.com (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with SMTP id 5F7087CFDF for <bug-DBD-ODBC [...] rt.cpan.org>; Mon, 28 Jan 2019 15:08:37 -0800 (PST)
Received: from out5-smtp.messagingengine.com (out5-smtp.messagingengine.com [66.111.4.29]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by xx1.develooper.com (Postfix) with ESMTPS id 58D087D6D8 for <bug-DBD-ODBC [...] rt.cpan.org>; Mon, 28 Jan 2019 14:57:56 -0800 (PST)
Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 8BE6D2297C for <bug-DBD-ODBC [...] rt.cpan.org>; Mon, 28 Jan 2019 13:47:03 -0500 (EST)
Received: from web2 ([10.202.2.212]) by compute1.internal (MEProxy); Mon, 28 Jan 2019 13:47:03 -0500
Received: by mailuser.nyi.internal (Postfix, from userid 99) id 0911462304; Mon, 28 Jan 2019 13:47:03 -0500 (EST)
Delivered-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
Subject: Re: [rt.cpan.org #128346] Bug: Cannot use Placeholder for LIMIT or OFFSET expressions
X-Me-Sender: <xms:J05PXI4AbGmEjE_f4Oj0-vMvhqlFfOERyU3Vky8tN--6RE-xM-bVIQ>
Return-Path: <david [...] justatheory.com>
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pobox.com; h= message-id:from:to:mime-version:content-transfer-encoding :content-type:in-reply-to:references:subject:date; s=mar2016.fm; bh=uaULPKHQnyo5yS6sbRqWdAVgxWhPOTM0XYk0okSqHcI=; b=E7xitkh2NVLZ JDVhojV1COYNINmrCAA5jKLTugv+J58XtLpAMTSyHyIXxRzjZbb6YGxbyUdAi1iQ RHQU0UvipFgo2q9AbWNZJ58hriIFMnf7s+5aakP7R9SOXYEJsA4QQ+k88EtvDM6b 1Ivt7uLAoP1kY6OaBTru7DMFkwqEKyw=
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=uaULPKHQnyo5yS6sbRqWdAVgxWhPOTM0XYk0okSqH cI=; b=DVzQcWOgwmxNrQz5FAs0t7YM1Km4nQMBg+8TxHbNxnGhRiXCJEpstC4+M ZrakQ7KNgh6Hk+K6FfwQyI4klaFvx7qGbGdfqpx+IFNCo52Audu7TdzfRWf1/sKt sixg0fmkTDVBoVF4tapwe3g16Z+wM/UOKFHHNvUfCx7WXNWbCUNyc0l5wm3lGy6K cjDC2i82XQpiOoHE876jBPeaAkgabP70dDcOkdog2K/BnwetGK0SCOrqb2+fWKpy aHWMVnHsdXqU5rAJASePbSLl6rBm3bb+MaXahxQdkbGnGYfFsdOt/aZOGTZ2Gk3C NorF8aWNDc9jGlgEsIiwd9Y98L1Lw==
X-Original-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbd-odbc
Date: Mon, 28 Jan 2019 10:47:02 -0800
X-PMX-Spam: Gauge=IIIIIIII, Probability=8%, Report=' HTML_00_01 0.05, HTML_00_10 0.05, BODYTEXTP_SIZE_3000_LESS 0, BODYTEXTP_SIZE_400_LESS 0, BODY_SIZE_1000_LESS 0, BODY_SIZE_2000_LESS 0, BODY_SIZE_300_399 0, BODY_SIZE_5000_LESS 0, BODY_SIZE_7000_LESS 0, DATE_TZ_NA 0, DKIM_SIGNATURE 0, IN_REP_TO 0, LEGITIMATE_SIGNS 0, MSG_THREAD 0, REFERENCES 0, SINGLE_URI_IN_BODY 0, SPF_NEUTRAL 0, URI_WITH_PATH_ONLY 0, WEBMAIL_SOURCE 0, WEBMAIL_XMAILER 0, __ANY_URI 0, __BOUNCE_CHALLENGE_SUBJ 0, __BOUNCE_NDR_SUBJ_EXEMPT 0, __CP_URI_IN_BODY 0, __CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __DQ_NEG_HEUR 0, __DQ_NEG_IP 0, __HAS_FROM 0, __HAS_MSGID 0, __HAS_X_MAILER 0, __HTTPS_URI 0, __IN_REP_TO 0, __MIME_TEXT_ONLY 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_VERSION 0, __NO_HTML_TAG_RAW 0, __PHISH_SPEAR_STRUCTURE_1 0, __PHISH_SPEAR_STRUCTURE_2 0, __PHISH_SPEAR_SUBJ_PREDICATE 0, __REFERENCES 0, __SANE_MSGID 0, __SINGLE_URI_TEXT 0, __SUBJ_ALPHA_NEGATE 0, __SUBJ_REPLY 0, __TO_MALFORMED_2 0, __TO_NAME 0, __TO_NAME_DIFF_FROM_ACC 0, __TO_REAL_NAMES 0, __URI_IN_BODY 0, __URI_NOT_IMG 0, __URI_NO_MAILTO 0, __URI_NO_WWW 0, __URI_NS , __URI_WITH_PATH 0, __zen.spamhaus.org_ERROR '
X-Spam-Level:
X-PMX-Version: 5.6.1.2065439, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2019.1.28.225115
To: Martin J Evans via RT <bug-DBD-ODBC [...] rt.cpan.org>
Content-Transfer-Encoding: 7bit
X-Me-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtledrjedtgdduvddvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfquhhtnecuuegrihhlohhuthemucef tddtnecuogetfeejfedqtdegucdlhedtmdenucfjughrpefkhffvggfgtgfojghfufffse htjeertdertdejnecuhfhrohhmpeffrghvihguucghhhgvvghlvghruceouggrvhhiuges jhhushhtrghthhgvohhrhidrtghomheqnecuffhomhgrihhnpegtphgrnhdrohhrghdpgh hithhhuhgsrdgtohhmnecurfgrrhgrmhepmhgrihhlfhhrohhmpegurghvihgusehjuhhs thgrthhhvghorhihrdgtohhmnecuvehluhhsthgvrhfuihiivgeptd
From: David Wheeler <david [...] justatheory.com>
RT-Message-ID: <rt-4.0.18-6305-1548716985-669.128346-0-0 [...] rt.cpan.org>
Content-Length: 338
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
MIME-Version: 1.0
In-Reply-To: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-14182-1548784067-801.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 528
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-14182-1548784067-801.128346-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <1548359032.3289312.1642831464.39AECA91 [...] webmail.messagingengine.com> <rt-4.0.18-14182-1548784067-801.128346-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-7397-1548872196-1295.128346-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 1056
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.