FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Wood, David wrote:
> I have a strange problem best explained by example:
> 
> sqsh compiled against FreeTDS (broken - marked with "^^^^"):
> ------------------------------------------------------------
> 1> select asset_id, isin_nm, cusip from asset where
> asset_id=0x00038602ab1a93a5
> 2> go
>  asset_id             isin_nm              cusip
>  -------------------- -------------------- ---------
>  0x00038602ab1a93a50800 US68572YAA82         68572YAA8
>                    ^^^^              

Great!  I'm ready to sit back, put up my feet and rest on my laurels -- we
keep laurels around just for that reason, you know, just in case -- and
you go and find a bug.  Thanks a bunch!  

I tried this query with every utility we have, and they all failed:

        select cast(char(0) +char(3) +char(134) +char(2) +char(171)
+char(26) +char(147) +char(165) as binary) as MyResult


Here's what bsqldb said:

$ bsqldb -v -S$S -U$U -P$P -i binary.sql 

bsqldb:165: Verbose operation enabled
bsqldb:214: Query:
        select cast(char(0) +char(3) +char(134) +char(2) +char(171)
+char(26) +char(147) +char(165) as binary) as MyResult
bsqldb:188: dbsqlsend(): OK
bsqldb:196: dbsqlok(): OK
bsqldb:291: calling dbresults: OK
Result set 1
Freeing prior allocations
Allocating buffers
Allocating compute buffers
Metadata
col     name                            source                         
type             size    varies  
------  ------------------------------  ------------------------------ 
---------------  ------  ------  
     1                        MyResult                        MyResult    
      binary      30       1  

Data
MyResult
--------
00038602
Retrieving return status... none
1 rows affected
Retrieving output parameters... none
bsqldb:628: dbresults() returned NO_MORE_RESULTS (2):

I suppose that's one kind of answer.  Then I tried all our utilities:

$ rm -f nohup.out; for A in tsql sqsh bsqldb bsqlodbc fisql; do printf
"\n***\nTrying $A\n***\n" >> nohup.out; nohup $A -S$S -U$U -P$P  <
binary.sql; done; cat nohup.out
sending output to nohup.out
sending output to nohup.out
sending output to nohup.out
sending output to nohup.out
sending output to nohup.out

***
Trying tsql
***
locale is "C"
locale charset is "646"
1> 2> MyResult  
00038602ab1a93a500000000000000000000000000000000000000000000    
1> 
***
Trying sqsh
***
 MyResult                                                        
 ----------------------------------------------------------------
   0x00038602ab1a93a500000000000000000000000000000000000000000000

(1 row affected)

***
Trying bsqldb
***
MyResult
--------
1 rows affected
00038602

***
Trying bsqlodbc
***
                      MyResult
------------------------------
bsqlodbc: error -1: SQLExecute: SQL_ERROR: failed
bsqlodbc: error 2812: 42000: [FreeTDS][SQL Server]Could not find stored
procedure 'SQL_CUR8060000'.

***
Trying fisql
***
MyResult
--------
DB-LIBRARY error:
        Data conversion resulted in overflow
Operating-system error:
        Undefined error: 0
--------

(1 rows affected)

Not exactly stellar results.  :-(  

I made some guesses about your datatype.  Can you tell me how that
asset_id column is defined?  

Thanks again and regards, 

--jkl