Skip to Content.
Sympa Menu

freetds - Re: [freetds] timestamp truncation?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] timestamp truncation?
  • Date: Fri, 14 Nov 2008 18:03:19 +0100

Il giorno ven, 14/11/2008 alle 01.03 -0500, James K. Lowden ha scritto:
> Using bsqlodbc, I get an error for the following query. I think it's
> related to conversion of a TIMESTAMP datatype. We seem to be losing a
> byte somewhere. I'm not sure where the error is.
>
> bsqldb output is OK. The problem appears to be in the ODBC layer or
> perhaps between keyboard and chair.
>
> Results follow.
>
> --jkl
>
>
> bsqldb output:
>
> Data
> i ts sd d
> ----------- -- -------------------------- --------------------------
> 42 00 Nov 14 2008 04:59PM Nov 14 2008 04:58PM
> Retrieving return status... none
> 1 rows affected
>
>
> Query:
>
> $ cat test/datetime.sql
> use FreeTDS
> go
>
> create table at( i int, ts timestamp, sd smalldatetime, d datetime )
> go
> insert at (i, sd, d) values (42, getdate(), getdate())
> select * from at
> drop table at
> go
>
>
> Output:
>
> $ (cd build/src/apps/ && make) && TDSDUMP=dump build/src/apps/bsqlodbc -S
> varley -U jkl -P jkl -i test/datetime.sql -v -V3 2>&1 | tail -11
> col name type value type name
> size varies
> ------ ------------------------------ ---------- ------------------
> ------ ------
> 1 i 4 SQL_INTEGER
> 10 -1
> 2 ts -2 SQL_BINARY
> 8 -1
> 3 sd 93 SQL_TYPE_TIMESTAMP
> 16 -1
> 4 d 93 SQL_TYPE_TIMESTAMP
> 23 -1
> i ts sd d
> ---------- -------- ---------------- -----------------------
> SQLMoreResults returned SQL_NO_DATA
> bsqlodbc:359: Query:
> "[FreeTDS][SQL Server]Data truncated"
>

>From my dump

net.c:768:Sending packet
0000 03 01 00 29 00 00 01 00-0a 00 73 00 70 00 5f 00 |...).... ..s.p._.|
0010 65 00 78 00 65 00 63 00-75 00 74 00 65 00 00 00 |e.x.e.c. u.t.e...|
0020 00 00 26 04 04 02 00 00-00 |..&..... .|

token.c:497:tds_process_tokens(0x13e47f0, 0x7fff245a460c,
0x7fff245a4608, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:662:Received packet
0000 04 01 00 7b 00 33 01 00-ff 11 00 c3 00 01 00 00 |...{.3.. ........|
0010 00 81 04 00 00 00 09 00-26 04 01 69 00 50 00 01 |........ &..i.P..|
0020 00 ad 08 00 02 74 00 73-00 00 00 09 00 6f 04 02 |.....t.s .....o..|
0030 73 00 64 00 00 00 09 00-6f 08 01 64 00 d1 04 2a |s.d..... o..d...*|
0040 00 00 00 08 00 00 00 00-00 00 00 13 89 04 54 9b |........ ......T.|
0050 11 03 08 54 9b 00 00 eb-8e d7 00 ff 11 00 c1 00 |...T.... ........|
0060 01 00 00 00 ff 01 00 c7-00 00 00 00 00 79 00 00 |........ .....y..|
0070 00 00 fe 00 00 e0 00 00-00 00 00 |........ ...|

so, timestamp is 00 00 00 00 00 00 00 13 which should display as
0000000000000013 which is 16 bytes long (plus a needed terminator so it
require 17 bytes with single char encoding)


>
> Log:
>
> odbc.c:1585:SQLBindCol(0x8051000, 1, 1, 0x804e300, 10, 0x804f444)
> odbc.c:1585:SQLBindCol(0x8051000, 2, 1, 0x804e330, 8, 0x804f450)
> ^^^ eight
> odbc.c:1585:SQLBindCol(0x8051000, 3, 1, 0x804e360, 16, 0x804f45c)
> odbc.c:1585:SQLBindCol(0x8051000, 4, 1, 0x804d240, 23, 0x804f468)
> odbc.c:3724:SQLFetch(0x8051000)
> ...
> odbc.c:3360:odbc_process_tokens: tds_process_tokens returned 1
> odbc.c:3361: result_type=4040, TDS_DONE_COUNT=0, TDS_DONE_ERROR=0
> odbc.c:3431:odbc_process_tokens: returning result_type 4040
> convert_tds2sql.c:152:odbc_tds2sql: src is 56 dest = 1
> convert_tds2sql.c:262:odbc_tds2sql: outputting character data destlen = 10
>
> convert_tds2sql.c:152:odbc_tds2sql: src is 173 dest = 1
> convert_tds2sql.c:262:odbc_tds2sql: outputting character data destlen = 7
> /* seven ?? */

Yes, it take into account space for terminator and make return correct
value from tdsconvert. In this case it convert 3 bytes to 6 characters
(tdsconvert do not write terminator)

> convert_tds2sql.c:152:odbc_tds2sql: src is 58 dest = 1
> convert_tds2sql.c:262:odbc_tds2sql: outputting character data destlen = 16
>
> convert_tds2sql.c:152:odbc_tds2sql: src is 61 dest = 1
> convert_tds2sql.c:262:odbc_tds2sql: outputting character data destlen = 23
>
> error.c:409:odbc_errs_add: "Data truncated"
>

In this case it seems bsqldb is wrong. Also this is a warning, you could
print output, which lead to



lt-bsqlodbc:266: Verbose operation enabled
lt-bsqlodbc:360: Query:
create table at( i int, ts timestamp, sd smalldatetime, d datetime )
???
Metadata
col name type value type name
size varies
------ ------------------------------ ---------- ------------------
------ ------
SQLMoreResults returned SQL_NO_DATA
lt-bsqlodbc:360: Query:
insert at (i, sd, d) values (42, getdate(), getdate())
select * from at
drop table at
???
Metadata
col name type value type name
size varies
------ ------------------------------ ---------- ------------------
------ ------
1 i 4 SQL_INTEGER
10 -1
2 ts -2 SQL_BINARY
8 -1
3 sd 93 SQL_TYPE_TIMESTAMP
16 -1
4 d 93 SQL_TYPE_TIMESTAMP
23 -1
i ts sd d
---------- -------- ---------------- -----------------------
"[FreeTDS][SQL Server]Data truncated"
42 000000 2008-11-14 18:0 2008-11-14 18:01:58.35
SQLMoreResults returned SQL_NO_DATA
lt-bsqlodbc:360: Query:

lt-bsqlodbc: error -1: SQLPrepare: SQL_ERROR: failed
lt-bsqlodbc: error 8180: 42000: [FreeTDS][SQL Server]Statement(s) could
not be prepared.



(see 000000 as ts which is the truncated data)

freddy77






Archive powered by MHonArc 2.6.24.

Top of Page