Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: mertap [...] upcmail.cz
Cc:
AdminCc:

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



From mertap [...] upcmail.cz Sat Sep 25 13: 28:50 2010
MIME-Version: 1.0
X-Spam-Status: No, score=-10.599 tagged_above=-99.9 required=10 tests=[BAYES_00=-2.599, RCVD_IN_DNSWL_HI=-8] autolearn=ham
X-Spam-Flag: NO
X-Cloudmark-Analysis: v=1.1 cv=BBXmjR/jrMMj1wHBsuD7Cdv69cLzNztNz9uR8CXiauo= c=1 sm=0 a=QeYLabUBbFAA:10 a=wPDyFdB5xvgA:10 a=VzlUwyjaKlPB3RqlUzEA:9 a=hy_iv-rpJZ0USRe14-gA:7 a=H3QHu3z8l0MqscqMaUMjXkI77z0A:4 a=QEXdDO2ut3YA:10 a=3m8sBld6pIfU3J8IEaUA:9 a=HI34hnDkPnsDuPhVLEwA:7 a=7WE4hdaw5JRVe7j5HJkacmZbsh4A:4 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117
X-Virus-Checked: Checked by ClamAV on 16.mx.develooper.com
Message-ID: <201009251931.49337.mertap [...] upcmail.cz>
Content-Type: Multipart/Mixed; boundary="Boundary-00=_FIjnM2T0rc8cFB/"
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Spam-Score: -10.599
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 38950240BFF for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Sat, 25 Sep 2010 13:28:50 -0400 (EDT)
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 mgHiCMR-PIs0 for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Sat, 25 Sep 2010 13:28:48 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id E9F0D240BEE for <bug-dbd-odbc [...] rt.cpan.org>; Sat, 25 Sep 2010 13:28:47 -0400 (EDT)
Received: (qmail 3107 invoked by uid 103); 25 Sep 2010 17:31:58 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 25 Sep 2010 17:31:58 -0000
Received: from fep19.mx.upcmail.net (HELO fep19.mx.upcmail.net) (62.179.121.39) by 16.mx.develooper.com (qpsmtpd/0.80) with ESMTP; Sat, 25 Sep 2010 10:31:55 -0700
Received: from edge03.upcmail.net ([192.168.13.238]) by viefep19-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100925173151.XHUB1892.viefep19-int.chello.at [...] edge03.upcmail.net>; Sat, 25 Sep 2010 19:31:51 +0200
Received: from localhost ([84.42.177.169]) by edge03.upcmail.net with edge id B5Xp1f02a3fgNGs035XqTy; Sat, 25 Sep 2010 19:31:51 +0200
Received: from localhost ([127.0.0.1]) by localhost with esmtp (Exim 4.69) (envelope-from <mertap [...] upcmail.cz>) id 1OzYbF-0001IT-KX; Sat, 25 Sep 2010 19:31:49 +0200
Delivered-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
Subject: Invalid precision value (SQL-HY104) error on inserts
User-Agent: KMail/1.9.9
Return-Path: <mertap [...] upcmail.cz>
X-RT-Mail-Extension: dbd-odbc
X-Original-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
X-Spam-Check-BY: 16.mx.develooper.com
Date: Sat, 25 Sep 2010 19:31:49 +0200
X-Spam-Level:
X-Sourceip: 84.42.177.169
To: bug-DBD-ODBC [...] rt.cpan.org
From: Petr Merta <mertap [...] upcmail.cz>
Content-Length: 0
Content-Type: text/plain; charset="utf-8"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
X-RT-Original-Encoding: utf-8
Content-Length: 1385
Download (untitled) / with headers
text/plain 1.3k
Hi, I'd like to report what I believe is a bug in DBD::ODBC code, causing HY104 errors on varchar INSERTs when running against Microsoft SQL server. Working demo code is given as attachment, changing the connection string should be enough for making it run. The bug appeared when running on Windows (2k3, vista, win7), with various perl distributions and versions (Strawberry Perl both 32/64bit, ActivePerl, Cygwin). DBD::ODBC version primarily tested was 1.25, but the bug showed up also in 1.24 and 1.21. As for database environment, the bug seems rather independent of version of database server and client connectivity drivers. The sample code was tested against MS SQL Server 2K, 2k5, 2k8 and 2k8r2, with client drivers ranging from default win2k SQL server drivers to SQL Server Native Client 10. Interesting enough, the code works as expected when run on rather antique cygwin installation (1.5.25, perl 5.10.0, dbi 1.607, dbd::odbc 1.18). (complete detailed info on tested versions is also given in comments in the end of the sample code) There are two INSERTs in demo code. The first one is plain INSERT into one of the tables; it succeeds. The second INSERT is combined with SELECT from the other table, and this is the one which fails with HY104. Should you need any further information, please let me know. Regards, -- Petr Merta, mertap@upcmail.cz
content-type: application/x-perl; name="perldbi.pl"
content-disposition: attachment; filename="perldbi.pl"
Content-Transfer-Encoding: 7bit
Content-Length: 2374
Download perldbi.pl
text/x-perl 2.3k

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

MIME-Version: 1.0
In-Reply-To: <201009251931.49337.mertap [...] upcmail.cz>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <201009251931.49337.mertap [...] upcmail.cz>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-24883-1285449068-1628.61630-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 2424
Download (untitled) / with headers
text/plain 2.3k
On Sat Sep 25 13:32:07 2010, mertap@upcmail.cz wrote: Show quoted text
> Hi, > > > I'd like to report what I believe is a bug in DBD::ODBC code, causing > HY104 > errors on varchar INSERTs when running against Microsoft SQL server. > > Working demo code is given as attachment, changing the connection > string > should be enough for making it run. > > The bug appeared when running on Windows (2k3, vista, win7), with > various perl > distributions and versions (Strawberry Perl both 32/64bit, ActivePerl, > Cygwin). DBD::ODBC version primarily tested was 1.25, but the bug > showed up > also in 1.24 and 1.21. > > As for database environment, the bug seems rather independent of > version of > database server and client connectivity drivers. The sample code was > tested > against MS SQL Server 2K, 2k5, 2k8 and 2k8r2, with client drivers > ranging > from default win2k SQL server drivers to SQL Server Native Client 10. > > Interesting enough, the code works as expected when run on rather > antique > cygwin installation (1.5.25, perl 5.10.0, dbi 1.607, dbd::odbc 1.18). > > (complete detailed info on tested versions is also given in comments > in the > end of the sample code) > > There are two INSERTs in demo code. The first one is plain INSERT into > one of > the tables; it succeeds. The second INSERT is combined with SELECT > from the > other table, and this is the one which fails with HY104. > > Should you need any further information, please let me know. > > > Regards,
Hi, I've not tried your code yet but I will get around to it. I suspect you'll find that the MS SQL Server ODBC driver is attempting to rearrange your SQL in order to find out what the parameters are and failing to come up with the right SQL. You can probably see this if you enough about SQL Server and can monitor the SQL being executed or you can set DBI_TRACE=15=x.log in your environment and rerun your script then examine the x.log file to see if SQLDescribeParam is failing. Try splitting the failing do into a prepare, bind_param and execute and setting the TYPE of each bound parameter to SQL_VARCHAR. See the DBD::ODBC FAQ for examples (http://search.cpan.org/~mjevans/DBD-ODBC-1.25/FAQ#Why_do_I_get_errors_with_bound_parameters_and_MS_SQL_Server?). I will look at this in more detail but in the mean time you can help yourself and me by trying my suggestion and providing a log. Thanks. Martin -- Martin J. Evans Wetherby, UK
From mertap [...] upcmail.cz Sun Sep 26 02: 55:50 2010
MIME-Version: 1.0
X-Spam-Status: No, score=-10.599 tagged_above=-99.9 required=10 tests=[BAYES_00=-2.599, RCVD_IN_DNSWL_HI=-8] autolearn=ham
In-Reply-To: <rt-3.8.HEAD-24883-1285449068-1372.61630-6-0 [...] rt.cpan.org>
X-Spam-Flag: NO
X-Cloudmark-Analysis: v=1.1 cv=ZDt3ILgE3VX9xkJc7+9REBIC4hFTL6V7NAq0V56QRNQ= c=1 sm=0 a=yMOi6a3AMK4A:10 a=AC4b6R8PCdgA:10 a=wPDyFdB5xvgA:10 a=eNcD7ojaAAAA:8 a=BZP5TJipSl4VBKdec4wA:9 a=jFGwJa697ktUXJ3Fdme8dUcN3qkA:4 a=QEXdDO2ut3YA:10 a=JdbuSqh6ZKOmxII3nggA:9 a=d1A_RTLRyWGY7mRmbmQjEMzw9YQA:4 a=IKIoO-ieCDEA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117
References: <RT-Ticket-61630 [...] rt.cpan.org> <201009251931.49337.mertap [...] upcmail.cz> <rt-3.8.HEAD-24883-1285449068-1372.61630-6-0 [...] rt.cpan.org>
X-Virus-Checked: Checked by ClamAV on 16.mx.develooper.com
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Content-Type: Multipart/Mixed; boundary="Boundary-00=_p8unMtzQtmzmAYf"
Message-ID: <201009260858.49763.mertap [...] upcmail.cz>
X-Spam-Score: -10.599
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 88235240E6D for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Sun, 26 Sep 2010 02:55:50 -0400 (EDT)
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 GPB7atg5VRR0 for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Sun, 26 Sep 2010 02:55:48 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id C2428240D15 for <bug-dbd-odbc [...] rt.cpan.org>; Sun, 26 Sep 2010 02:55:47 -0400 (EDT)
Received: (qmail 10673 invoked by uid 103); 26 Sep 2010 06:58:58 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 26 Sep 2010 06:58:58 -0000
Received: from fep11.mx.upcmail.net (HELO fep11.mx.upcmail.net) (62.179.121.31) by 16.mx.develooper.com (qpsmtpd/0.80) with ESMTP; Sat, 25 Sep 2010 23:58:56 -0700
Received: from edge05.upcmail.net ([192.168.13.212]) by viefep11-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100926065851.CQTL4509.viefep11-int.chello.at [...] edge05.upcmail.net> for <bug-dbd-odbc [...] rt.cpan.org>; Sun, 26 Sep 2010 08:58:51 +0200
Received: from localhost ([84.42.177.169]) by edge05.upcmail.net with edge id BJyq1f0033fgNGs05Jyrjq; Sun, 26 Sep 2010 08:58:51 +0200
Received: from localhost ([127.0.0.1]) by localhost with esmtp (Exim 4.69) (envelope-from <mertap [...] upcmail.cz>) id 1OzlCD-00028a-UC for bug-DBD-ODBC [...] rt.cpan.org; Sun, 26 Sep 2010 08:58:49 +0200
Delivered-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
User-Agent: KMail/1.9.9
Subject: Re: [rt.cpan.org #61630] Invalid precision value (SQL-HY104) error on inserts
Return-Path: <mertap [...] upcmail.cz>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbd-odbc
Date: Sun, 26 Sep 2010 08:58:49 +0200
X-Sourceip: 84.42.177.169
X-Spam-Level:
To: bug-DBD-ODBC [...] rt.cpan.org
From: Petr Merta <mertap [...] upcmail.cz>
RT-Message-ID: <rt-3.8.HEAD-24885-1285484343-318.61630-0-0 [...] rt.cpan.org>
Content-Length: 0
Content-Type: text/plain; charset="utf-8"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
X-RT-Original-Encoding: utf-8
Content-Length: 1672
Download (untitled) / with headers
text/plain 1.6k
On Saturday 25 September 2010 23:11:08 Martin J Evans via RT wrote: Show quoted text
> Hi, > > I've not tried your code yet but I will get around to it. > > I suspect you'll find that the MS SQL Server ODBC driver is attempting > to rearrange your SQL in order to find out what the parameters are and > failing to come up with the right SQL. You can probably see this if you > enough about SQL Server and can monitor the SQL being executed or you > can set DBI_TRACE=15=x.log in your environment and rerun your script > then examine the x.log file to see if SQLDescribeParam is failing. Try > splitting the failing do into a prepare, bind_param and execute and > setting the TYPE of each bound parameter to SQL_VARCHAR. See the > DBD::ODBC FAQ for examples > (http://search.cpan.org/~mjevans/DBD-ODBC-1.25/FAQ#Why_do_I_get_errors_with >_bound_parameters_and_MS_SQL_Server?). > > I will look at this in more detail but in the mean time you can help > yourself and me by trying my suggestion and providing a log.
Martin, first I have to apologize I was not more careful when looking for bug cause/solution; I've relied on google little too much for this one :-) Your suggestion with splitting the code to prep-bind-exec works like a charm. As for me, I'm quite happy with this solution, I suppose it won't be a problem to handle failing statements this way. So please consider the urgency of my report rather low. There are logs attached gathered with single do() call and another one gathered with splitted command. It really looks like SQLDescribeParam is not able to handle long varchars. Thanks for your time, I really appreciate your help. -- Petr Merta, mertap@upcmail.cz
content-type: application/x-zip; name="log.zip"
content-disposition: attachment; filename="log.zip"
Content-Transfer-Encoding: base64
Content-Length: 10896
Download log.zip
application/x-zip 10.6k

Message body not shown because it is not plain text.

MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-24885-1285484343-318.61630-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <RT-Ticket-61630 [...] rt.cpan.org> <201009251931.49337.mertap [...] upcmail.cz> <rt-3.8.HEAD-24883-1285449068-1372.61630-6-0 [...] rt.cpan.org> <201009260858.49763.mertap [...] upcmail.cz> <rt-3.8.HEAD-24885-1285484343-318.61630-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-24882-1285592945-1507.61630-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 3416
Download (untitled) / with headers
text/plain 3.3k
On Sun Sep 26 02:59:03 2010, mertap@upcmail.cz wrote: Show quoted text
> On Saturday 25 September 2010 23:11:08 Martin J Evans via RT wrote:
> > Hi, > > > > I've not tried your code yet but I will get around to it. > > > > I suspect you'll find that the MS SQL Server ODBC driver is
> attempting
> > to rearrange your SQL in order to find out what the parameters are
> and
> > failing to come up with the right SQL. You can probably see this if
> you
> > enough about SQL Server and can monitor the SQL being executed or
> you
> > can set DBI_TRACE=15=x.log in your environment and rerun your script > > then examine the x.log file to see if SQLDescribeParam is failing.
> Try
> > splitting the failing do into a prepare, bind_param and execute and > > setting the TYPE of each bound parameter to SQL_VARCHAR. See the > > DBD::ODBC FAQ for examples > > (http://search.cpan.org/~mjevans/DBD-ODBC-
> 1.25/FAQ#Why_do_I_get_errors_with
> >_bound_parameters_and_MS_SQL_Server?). > > > > I will look at this in more detail but in the mean time you can help > > yourself and me by trying my suggestion and providing a log.
> > Martin, > > first I have to apologize I was not more careful when looking for bug > cause/solution; I've relied on google little too much for this one :-)
Not a problem. Show quoted text
> Your suggestion with splitting the code to prep-bind-exec works like a > charm.
:-) Show quoted text
> As for me, I'm quite happy with this solution, I suppose it won't be a > problem to handle failing statements this way. So please consider the > urgency > of my report rather low.
I would but I don't like rts hanging around. Show quoted text
> There are logs attached gathered with single do() call and another one > gathered with splitted command. It really looks like SQLDescribeParam > is not > able to handle long varchars.
Your log shows: SQLDescribeParam failed reverting to default SQL bind type -9 07009 [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index 42000 [Microsoft][ODBC SQL Server Driver]Syntax error or access violation It is nothing to do with long varchars it is your SQL which is breaking the ODBC driver. SQLPrepare INSERT INTO EventLog (ComputerIDC, EventMessage) SELECT cs.ComputerIDC, ? FROM ComputerSlice AS cs WHERE cs.ComputerSliceID = ? In order for the SQL Server ODBC driver to work out what the 2 parameters are it attempts to rearrange your SQL into a select for the parameter columns from a table e.g., select a_col from mytable where b_col = ? SQL Server rearranges it into "select b_col from mytable" and hence finds out what column b_col is. In your SQL one of the parameters is a string not related to a column so SQLDescribeParam fails and DBD::ODBC has no choice to assume a default (it chose SQL_WCHAR). Then you tried to pass input parameter data of 4002 chrs which is too big for an SQL_WCHAR and hence the HY104 invalid precision error. So, although I see why this failed I am slightly surprised DBD::ODBC did not default the parameter type to SQL_WLONGVARCHAR when it saw the parameter was > 4000 bytes. I will look into this as it may mean you can go back to how you had it although the SQLDescribeParam will have to be called and fail - so slightly more work than specifying a bind type. Show quoted text
> Thanks for your time, I really appreciate your help.
No problem. I'll try and get back to you soon. Martin -- Martin J. Evans Wetherby, UK
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-24882-1285592945-1507.61630-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <RT-Ticket-61630 [...] rt.cpan.org> <201009251931.49337.mertap [...] upcmail.cz> <rt-3.8.HEAD-24883-1285449068-1372.61630-6-0 [...] rt.cpan.org> <201009260858.49763.mertap [...] upcmail.cz> <rt-3.8.HEAD-24885-1285484343-318.61630-0-0 [...] rt.cpan.org> <rt-3.8.HEAD-24882-1285592945-1507.61630-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-24883-1285596864-819.61630-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 1395
Download (untitled) / with headers
text/plain 1.3k
On Mon Sep 27 09:09:05 2010, MJEVANS wrote: Show quoted text
> So, although I see why this failed I am slightly surprised DBD::ODBC did > not default the parameter type to SQL_WLONGVARCHAR when it saw the > parameter was > 4000 bytes. I will look into this as it may mean you can > go back to how you had it although the SQLDescribeParam will have to be > called and fail - so slightly more work than specifying a bind type.
ok, I see why this happens now. When SQLDescribeParam fails DBD::ODBC picks either SQL_WCHAR or SQL_WLONGVARCHAR to fall back on. If the parameter is > 4000 bytes it uses the latter so in your case it used SQL_WCHAR. However, in your case the parameter is 2001 bytes but will be converted to wide characters hence doubling the length to 4002. A varchar should be able to hold 4000 characters not bytes so yet another issue in SQL Server. You kind of had it nailed yourself with this comment in your code: # THIS IS IT; "X" x 2000 works ok, x 2001 will fail; however, value goes to column VARCHAR(8000) The SQL Server ODBC driver appears broken as 2001 characters should fit into a VARCHAR(8000) especially when they are all 'X'. I could sort of understood it if it was a varchar(4000). I am however, going to change DBD::ODBC to switch to longvarchar at 2000 bytes instead of 4000. I hope you'll be happy with me writing this issue off now. Martin -- Martin J. Evans Wetherby, UK
From mertap [...] upcmail.cz Tue Sep 28 04: 19:50 2010
MIME-Version: 1.0
X-Spam-Status: No, score=-10.599 tagged_above=-99.9 required=10 tests=[BAYES_00=-2.599, RCVD_IN_DNSWL_HI=-8] autolearn=ham
In-Reply-To: <rt-3.8.HEAD-24883-1285596865-1665.61630-6-0 [...] rt.cpan.org>
Content-Disposition: inline
X-Spam-Flag: NO
X-Cloudmark-Analysis: v=1.1 cv=ZDt3ILgE3VX9xkJc7+9REBIC4hFTL6V7NAq0V56QRNQ= c=1 sm=0 a=yMOi6a3AMK4A:10 a=AC4b6R8PCdgA:10 a=wPDyFdB5xvgA:10 a=IkcTkHD0fZMA:10 a=CjbI6waiT8rvzizhXu4A:9 a=qIhgZMn40tIo6sJ_tGUA:7 a=LQhyH5mfFbil0c61AsI4r40V1c0A:4 a=QEXdDO2ut3YA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117
References: <RT-Ticket-61630 [...] rt.cpan.org> <rt-3.8.HEAD-24882-1285592945-1507.61630-6-0 [...] rt.cpan.org> <rt-3.8.HEAD-24883-1285596865-1665.61630-6-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Content-Type: text/plain; charset="utf-8"
Message-ID: <201009281022.50109.mertap [...] upcmail.cz>
X-RT-Original-Encoding: utf-8
X-Spam-Score: -10.599
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 190A2240E98 for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Tue, 28 Sep 2010 04:19:50 -0400 (EDT)
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 LZfigBwRkWqr for <cpan-bug+dbd-odbc [...] hipster.bestpractical.com>; Tue, 28 Sep 2010 04:19:46 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 8B51C240CD4 for <bug-dbd-odbc [...] rt.cpan.org>; Tue, 28 Sep 2010 04:19:45 -0400 (EDT)
Received: (qmail 18196 invoked by uid 103); 28 Sep 2010 08:22:59 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 28 Sep 2010 08:22:59 -0000
Received: from fep11.mx.upcmail.net (HELO fep11.mx.upcmail.net) (62.179.121.31) by 16.mx.develooper.com (qpsmtpd/0.80) with ESMTP; Tue, 28 Sep 2010 01:22:57 -0700
Received: from edge01.upcmail.net ([192.168.13.236]) by viefep11-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100928082252.ROP4509.viefep11-int.chello.at [...] edge01.upcmail.net> for <bug-dbd-odbc [...] rt.cpan.org>; Tue, 28 Sep 2010 10:22:52 +0200
Received: from localhost ([84.42.177.169]) by edge01.upcmail.net with edge id C8Nq1f02E3fgNGs018Nr2b; Tue, 28 Sep 2010 10:22:52 +0200
Received: from localhost ([127.0.0.1]) by localhost with esmtp (Exim 4.69) (envelope-from <mertap [...] upcmail.cz>) id 1P0VSc-0003sa-AN for bug-DBD-ODBC [...] rt.cpan.org; Tue, 28 Sep 2010 10:22:50 +0200
Delivered-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
User-Agent: KMail/1.9.9
Subject: Re: [rt.cpan.org #61630] Invalid precision value (SQL-HY104) error on inserts
Return-Path: <mertap [...] upcmail.cz>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+dbd-odbc [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbd-odbc
Date: Tue, 28 Sep 2010 10:22:50 +0200
X-Sourceip: 84.42.177.169
X-Spam-Level:
To: bug-DBD-ODBC [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: Petr Merta <mertap [...] upcmail.cz>
RT-Message-ID: <rt-3.8.HEAD-28744-1285662185-1007.61630-0-0 [...] rt.cpan.org>
Content-Length: 1620
Download (untitled) / with headers
text/plain 1.5k
On Monday 27 September 2010 16:14:26 Martin J Evans via RT wrote: Show quoted text
> ok, I see why this happens now. When SQLDescribeParam fails DBD::ODBC > picks either SQL_WCHAR or SQL_WLONGVARCHAR to fall back on. If the > parameter is > 4000 bytes it uses the latter so in your case it used > SQL_WCHAR. However, in your case the parameter is 2001 bytes but will be > converted to wide characters hence doubling the length to 4002. A > varchar should be able to hold 4000 characters not bytes so yet another > issue in SQL Server. > > You kind of had it nailed yourself with this comment in your code: > > # THIS IS IT; "X" x 2000 works ok, x 2001 will fail; however, value goes > to column VARCHAR(8000) > > The SQL Server ODBC driver appears broken as 2001 characters should fit > into a VARCHAR(8000) especially when they are all 'X'. I could sort of > understood it if it was a varchar(4000).
well, you're right. I never consider possibility that this "2000" is almost completely unrelated to column size; now when I've checked it I could see it fails on the same boundary for varchar(7000) too. I simply thought there has to be some magic in the process causing strings grow four times bigger. Hm, I probably should have warned you: I'm merely a user, most of that things going under the hood is greek to me :-) Show quoted text
> I am however, going to change DBD::ODBC to switch to longvarchar at 2000 > bytes instead of 4000.
ok Show quoted text
> > I hope you'll be happy with me writing this issue off now.
of course. You did a nice piece of work here. Thanks again, and good luck with future development. Petr -- Petr Merta, mertap@upcmail.cz


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.