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

[arielCo]

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

[James K. Lowden]

On Wed, 12 Jun 2013 17:56:32 -0430

Post by arielCo
1>> select 100.0 foo
2>> go
foo
---
Data conversion resulted in overflow
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).

Hmm, that shouldn't happen. DB-Library shouldn't connect using a
version of the TDS protocol that might return datatypes it can't
interpret. (I doubt the alias matters, btw.)

It's fine to connect with a version older than the latest one
supported by the server. If you connect, say, with TDS 4.2 to a
modern SQL Server and select a BIGINT, the server will return it on
the wire as a VARCHAR. What appears to be happening here is
different: fisql appears to be connecting with TDS 7.something, and is
then flummoxed by the returned type. Or there's a bug in fisql in how
it binds/converts.

Try this:

$ export TDSDUMP=./dump
$ fisql ....
$ less dump

and look for two things:

1. Near the top of the file, it will say what version of the TDS
protocol is being used for the connection.

2. Near the bottom of the file, it will say what conversion was
requested and failed.

That should shed some light.

--jkl

[David Chang]

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

Post by arielCo
I'm getting these errors with FreeTDS 1.91 against SQL Server 2008
$ ~/freetds/bin/fisql -S prueba -U ariel
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
---
Data conversion resulted in overflow
Error 0
---
(1 rows affected)
1>> select 1.0
2>> go
Data conversion resulted in overflow
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
---
Data conversion resulted in overflow
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
4>> go
(1 rows affected)
Data conversion resulted in overflow
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

[arielCo]

This post might be inappropriate. Click to display it.

[Frediano Ziglio]

It's a dblib problem, happen also on x64.

/**
* \ingroup dblib_core
* \brief Determine size buffer required to hold the results returned by
dbsprhead(), dbsprline(), and dbspr1row().
*
* \param dbproc contains all information needed by db-lib to manage
communications with the server.
* \return size of buffer requirement, in bytes.
* \remarks An esoteric function.
* \sa dbprhead(), dbprrow(), dbspr1row(), dbsprhead(), dbsprline().
*/
DBINT
dbspr1rowlen(DBPROCESS * dbproc)

dbspr1rowlen is used to compute memory for column title/column row. The
select return a SYBNUMBERIC which is not handle by _get_printable_size
(used by dbspr1rowlen to compute data length) so the size allocated for the
row for numeric is just the length of the column name. Obviously if column
name is too short conversion fails. I don't know if dbspr1row have to
NUL-terminate the buffer, actually it does not causing fisql to get a
buffer overflow during strlen/fputs.

Frediano



2013/6/13 arielCo <arielco at gmail.com>

Post by arielCo
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
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
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
------
Data conversion resulted in overflow
Error 0
------
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

Post by David Chang
Ariel,
In your freetds.conf file, did you set the tds version to 8.0? For
[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

Post by arielCo
I'm getting these errors with FreeTDS 1.91 against SQL Server 2008
$ ~/freetds/bin/fisql -S prueba -U ariel
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
---
Data conversion resulted in overflow
Error 0
---
(1 rows affected)
1>> select 1.0
2>> go
Data conversion resulted in overflow
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
---
Data conversion resulted in overflow
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
4>> go
(1 rows affected)
Data conversion resulted in overflow
Error 0
Am I doing something terribly wrong? These work in SQL Server

Management Studio.

Post by David Chang

Post by arielCo
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

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Thu, 13 Jun 2013 10:59:42 -0430

Post by arielCo
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"

Right, that's a bug in fisql, one I actually vaguely recall.

tdsconvert() in effect takes two 3-tuples of {type, buffer ponter, and
length}, one input and one output. The output length is the last
parameter and, as you see above, the value provided is 1. dbconvert()
detects that the required size, 3, is less than the size of the buffer
provided by the application, 1, and returns an overflow error.

The mistake in fisql is in src/apps/fisql/fisql.c line 196, where
get_printable_column_size() sizes its character buffer according to
the larger of

1. column size, dbcollen()
2. length of the column *name*, strlen(dbcolname())

Column size as a string buffer size is inappropriate for integer
types. It's usually OK because the column name is big enough. You
will find you can convert any literal you like, as long as you give it
the alias "supercalifragilistic".

The right thing to do -- your mission, should you choose to accept it
-- is to use tdswillconvert() instead to determine the size.

HTH.

--jkl

[arielCo]

Post by James K. Lowden
The right thing to do -- your mission, should you choose to accept it
-- is to use tdswillconvert() instead to determine the size.

Challenge accepted. I'll keep you posted.


Ariel Cornejo
+58?412?8083546
+58?416?6189113

[arielCo]

James, I substituted dbwillconvert() successfully in the 0.91 tarball,
but in Git it now boolean-izes the result of tds_willconvert:
return tds_willconvert(srctype, desttype) ? TRUE : FALSE;
Which makes more sense given the function name, but dbwillconvert
remains a scalar.

Since there are three mostly-redundant switch statements, and the one
in tds_willconvert is almost complete, maybe:
* have a single switch statement in, say, get_printable_size(type,size)
* _get_printable_size(colinfo) would be a wrapper
* tds_willconvert(srctype, desttype) calls get_printable_size(),
perhaps mapping SYBINTN to SYBINT8 since the size doesn't matter
* dbwillconvert stays a wrapper
* both *willconvert() functions return a boolean

Whaddyathink? (:

Ariel Cornejo
+58?412?8083546

Post by arielCo

Post by James K. Lowden
The right thing to do -- your mission, should you choose to accept it
-- is to use tdswillconvert() instead to determine the size.

Challenge accepted. I'll keep you posted.
Ariel Cornejo
+58?412?8083546
+58?416?6189113

[James K. Lowden]

On Fri, 21 Jun 2013 18:14:10 -0430

Post by arielCo
James, I substituted dbwillconvert() successfully in the 0.91 tarball,
return tds_willconvert(srctype, desttype) ? TRUE : FALSE;
Which makes more sense given the function name, but dbwillconvert
remains a scalar.
Since there are three mostly-redundant switch statements, and the one
* have a single switch statement in, say, get_printable_size
(type,size)
* _get_printable_size(colinfo) would be a wrapper
* tds_willconvert(srctype, desttype) calls get_printable_size(),
perhaps mapping SYBINTN to SYBINT8 since the size doesn't matter
* dbwillconvert stays a wrapper
* both *willconvert() functions return a boolean

Hi Ariel,

Thanks for working on this, first of all, on behalf of the community.

My preference would be:

1. put all the work in tds_willconvert()
2. #define PRINTABLE_SIZE(x, SYBCHAR)
3. remove _get_printable_size() and get_printable_size
4. return int

because

1. get_printable_size() could otherwise return a
value for an input type that tds_convert() cannot handle (or that
tds_willconvert() says cannot be handled). This way, an invalid input
is uniformly and correctly handled.

2. This is C, and the rule in C is that 0 is false and anything else
is true. Reducing the return code from "size of output" to "yes or no"
removes information to no advantage. (I have no sympathy for the C
programmer who writes

if (dbwillconvert(SYBINT, SYBCHAR) == TRUE)

because, well, there oughta be a law.)

3. No self-respecting C function, except I/O, should begin with "get".
It's a *function*! It maps input to output. Observe:

int len = get_printable_size(SYBINT);
int len = printable_size(SYBINT);

The "get" is just nonce noise. In the same way we don't say

double x = compute_square_root(y);
or
double x = get_sqrt(y);
but
double x = sqrt(y);

Save typing. Read faster. Go home early. ;-)

Hope you're convinced, and thanks again for putting in the time.

--jkl

Post by arielCo

On Thu, Jun 13, 2013 at 6:06 PM, James K. Lowden

Post by James K. Lowden
The right thing to do -- your mission, should you choose to accept it
-- is to use tdswillconvert() instead to determine the size.

Challenge accepted. I'll keep you posted.
Ariel Cornejo
+58?412?8083546
+58?416?6189113

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[arielCo]

* I agree wholeheartedly, with a minor change. A function returning an
int would be better called something like tds_convert_size, to give a
user/reader the right idea about the return value; and of course the
user would test for convertibility like "if (bufsize =
tds_convert_size(...))" or "if (colwidth = PRINTABLE_SIZE(...))".

* This function needs to accept the source column size, so that the
PRINTABLE_SIZE macro produces meaningful info for variable-width data
types like SYBCHAR. Then, PRINTABLE_SIZE(type, size) would call
tds_convert_size(type, size, SYBCHAR).

* Now, I mentioned that a recent commit introduced the boolean-ization
that you rightly despise, and it broke my original fix:

http://gitorious.org/freetds/freetds/commit/1bb190f304604bd7338c5df9bddf81af7e7a9ad9
dbwillconvert(int srctype, int desttype)
{
tdsdump_log(TDS_DBG_FUNC, "dbwillconvert(%s, %s)\n",
tds_prdatatype(srctype), tds_prdatatype(desttype));
- return tds_willconvert(srctype, desttype);
+ return tds_willconvert(srctype, desttype) ? TRUE : FALSE;
}

It addresses "Bug #58 reported by Mikhail Teterin", but I don't even
know where the tracker is and I have to see that I keep or replicate
Frediano's fix. Of course, I have grep the whole tree for calls to
*_willconvert and get_printable_size anyway, so I'll check how the
return values are used currently.

From a compulsive "fixer", glad to help. I'll get to it over the next week.

Ariel Cornejo
+58?412?8083546

On Fri, 21 Jun 2013 18:14:10 -0430

Post by arielCo
James, I substituted dbwillconvert() successfully in the 0.91 tarball,
return tds_willconvert(srctype, desttype) ? TRUE : FALSE;
Which makes more sense given the function name, but dbwillconvert
remains a scalar.
Since there are three mostly-redundant switch statements, and the one
* have a single switch statement in, say, get_printable_size
(type,size)
* _get_printable_size(colinfo) would be a wrapper
* tds_willconvert(srctype, desttype) calls get_printable_size(),
perhaps mapping SYBINTN to SYBINT8 since the size doesn't matter
* dbwillconvert stays a wrapper
* both *willconvert() functions return a boolean

Hi Ariel,
Thanks for working on this, first of all, on behalf of the community.
1. put all the work in tds_willconvert()
2. #define PRINTABLE_SIZE(x, SYBCHAR)
3. remove _get_printable_size() and get_printable_size
4. return int
because
1. get_printable_size() could otherwise return a
value for an input type that tds_convert() cannot handle (or that
tds_willconvert() says cannot be handled). This way, an invalid input
is uniformly and correctly handled.
2. This is C, and the rule in C is that 0 is false and anything else
is true. Reducing the return code from "size of output" to "yes or no"
removes information to no advantage. (I have no sympathy for the C
programmer who writes
if (dbwillconvert(SYBINT, SYBCHAR) == TRUE)
because, well, there oughta be a law.)
3. No self-respecting C function, except I/O, should begin with "get".
int len = get_printable_size(SYBINT);
int len = printable_size(SYBINT);
The "get" is just nonce noise. In the same way we don't say
double x = compute_square_root(y);
or
double x = get_sqrt(y);
but
double x = sqrt(y);
Save typing. Read faster. Go home early. ;-)
Hope you're convinced, and thanks again for putting in the time.
--jkl

Post by arielCo

On Thu, Jun 13, 2013 at 6:06 PM, James K. Lowden

Post by James K. Lowden
The right thing to do -- your mission, should you choose to accept it
-- is to use tdswillconvert() instead to determine the size.

Challenge accepted. I'll keep you posted.
Ariel Cornejo
+58?412?8083546
+58?416?6189113

_______________________________________________
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

[James K. Lowden]

On Thu, 27 Jun 2013 15:58:31 -0430

Post by arielCo
Of course, I have grep the whole tree for calls to
*_willconvert and get_printable_size anyway, so I'll check how the
return values are used currently.

Have you tried cscope?

Post by arielCo
From a compulsive "fixer", glad to help. I'll get to it over the next week.

I agree with your suggestions and look forward to the patch!

--jkl