Re: timestamp truncation?

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