Skip to Content.
Sympa Menu

freetds - Re: [freetds] "Data conversion resulted in overflow" with float constants

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: arielCo <arielco AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] "Data conversion resulted in overflow" with float constants
  • Date: Thu, 13 Jun 2013 10:59:42 -0430

Update: It happens only with fisql, thus far only with float results.

David: I tried tds version = 8.0 but it makes no difference. (It's
actually SQL Server 2005 - I don't know whether that matters).

James: I enabled the dump as you said, tried "select 1.0" (failed) and
"select 1.0 foo" (successful), and compared the dumps (attached). Here
is the part about the conversion:

dblib.c:3264:dbspr1row(1001166d0, , 1)
dblib.c:3162:dbdata(1001166d0, 1)
dblib.c:3133:dbdatlen(1001166d0, 1)
dblib.c:3142:dbdatlen() type = 108, len= 35
dblib.c:2217:dbconvert(1001166d0, SYBNUMERIC, 1001146e0, 35, SYBCHAR,
100109b40, 1)
dblib.c:2349:dbconvert() calling tds_convert
dblib.c:2352:dbconvert() called tds_convert returned 3
dblib.c:2455:dbconvert() outputting 3 bytes character data destlen = 1
dblib.c:7929:dbperror(1001166d0, 20049, 0)
dblib.c:7981:20049: "Data conversion resulted in overflow"
dblib.c:4880:dbdead(1001166d0) [alive]
dblib.c:8002:"Data conversion resulted in overflow", client returns 2
(INT_CANCEL)
dblib.c:2482:35 bytes type 108 -> 47, destlen 1 < 3 required

I did more tests and I found an interesting relationship and new
results: the alias string has to be at least as long as the formatted
result; strlen(result)-1 may result in garbage appended to the output
and strlen-2 overflows. See for yourself:

1>> select 1.0/4.0 xxxxxxxx
2>> go
xxxxxxxx
--------
0.250000

1>> select 1.0/4.0 xxxxxxx
2>> go
xxxxxxx
-------
0.2500002

1>> select 1.0/4.0 xxxxxx
2>> go
xxxxxx
------
DB-LIBRARY error:
Data conversion resulted in overflow
Operating-system error:
Error 0
------


And the weirder results:

1>> select 1.0/4.0 xxxxxxx
2>> go
xxxxxxx
-------
0.25000039.22

1>> select 12345.0 xxxxxx
2>> go
xxxxxx
------
12345.0P

1>> select 12345.0 xxxxxx
2>> go
xxxxxx
------
12345.0p2

1>> select 12345.0 xxxxxx
2>> go
xxxxxx
------
12345.00

1>> select 12345.0 xxxxxx
2>> go
xxxxxx
------
12345.0p9.0 xxx

1>> select 12345.0 xxxxxx
2>> go
xxxxxx
------
12345.0.000 xx

I should have mentioned: I compiled 64-bit on SunOS 5.10 SPARC, with
gcc 3.4.3. I have other problems with type conversion in DBD::Sybase,
but maybe I should discuss that in a separate thread.

Thanks,
Ariel Cornejo


On Wed, Jun 12, 2013 at 7:34 PM, David Chang <dchang AT fsautomation.com> wrote:
> Ariel,
>
> In your freetds.conf file, did you set the tds version to 8.0? For example:
>
> [DBMS1]
> host = 192.168.1.1
> port = 1433
> tds version = 8.0
>
> I tried your statements using my own DB-Library program (not fisql) and
> they all worked fine. I did this against SQL Server 2012, SQL Server
> 2008 R2, and SQL Server 2005. I don't have a copy of SQL Server 2008.
>
> DC
>
> On 6/12/2013 3:26 PM, arielCo wrote:
>> I'm getting these errors with FreeTDS 1.91 against SQL Server 2008
>> which I can't make sense of:
>>
>> $ ~/freetds/bin/fisql -S prueba -U ariel
>> Password:
>>
>> 1>> select 1.1 foo
>> 2>> go
>> foo
>> ---
>> 1.1
>> (1 rows affected)
>>
>> 1>> select 1.11 foo
>> 2>> go
>> foo
>> ---
>> 1.11
>> (1 rows affected)
>>
>> 1>> select 1.111 foo
>> 2>> go
>> foo
>> ---
>> DB-LIBRARY error:
>> Data conversion resulted in overflow
>> Operating-system error:
>> Error 0
>> ---
>> (1 rows affected)
>>
>> 1>> select 1.0
>> 2>> go
>>
>> DB-LIBRARY error:
>> Data conversion resulted in overflow
>> Operating-system error:
>> Error 0
>> (1 rows affected)
>>
>> 1>> select 99.0 foo
>> 2>> go
>> foo
>> ---
>> 99.0
>> (1 rows affected)
>>
>> 1>> select 100.0 foo
>> 2>> go
>> foo
>> ---
>> DB-LIBRARY error:
>> Data conversion resulted in overflow
>> Operating-system error:
>> Error 0
>> ---
>>
>>
>> Apparently I can't write a literal float with more than four
>> characters. Furthermore, I can't write any decimals without a column
>> alias (fourth query above).
>>
>> Since I'm a complete MS SQL newbie, I tried the example provided in
>> the Transact-SQL documentation and it fails too:
>>
>> 1>> DECLARE @myval decimal (5, 2)
>> 2>> SET @myval = 193.57
>> 3>> SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
>> 4>> go
>> (1 rows affected)
>>
>> DB-LIBRARY error:
>> Data conversion resulted in overflow
>> Operating-system error:
>> Error 0
>>
>>
>> Am I doing something terribly wrong? These work in SQL Server Management
>> Studio.
>>
>> Thanks in advance,
>> ariel cornejo
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

Attachment: dump.ok
Description: Binary data

Attachment: dump.nok
Description: Binary data




Archive powered by MHonArc 2.6.24.

Top of Page