ISQL may truncate BINARY value displayed as "Text"

[Breck_Carter]

When using SQL Anywhere 12 ISQL with "Text" checked in Tools - Options - SQL Anywhere - Style, BINARY values can be truncated according to the length of the column name.

At first I thought this was expected behavior, but after being misled about a value, I no longer hold that opinion; 'tis a bug, not a feature 🙂

Rather than try to describe the rather funky "rules", the following example will demonstrate:

SELECT 0x30313233343536373839 AS a,
       a AS bbbbbbbbbbbbb,
       a AS cccccccccccccc,
       a AS ddddddddddddddddddddd, 
       a AS eeeeeeeeeeeeeeeeeeeeee, 
       a AS fffffffffffffffffffffffffff

a            bbbbbbbbbbbbb cccccccccccccc ddddddddddddddddddddd eeeeeeeeeeeeeeeeeeeeee fffffffffffffffffffffffffff 
------------ ------------- -------------- --------------------- ---------------------- --------------------------- 
0x3031323334 0x30313233343 0x303132333435 0x3031323334353637383 0x30313233343536373839 0x30313233343536373839      
(1 rows)
Execution time: 0.083 seconds

[jeff_albion · Accepted solution]

Thanks for the bug report - I have opened this issue internally for investigation as CR #731939. We will update this thread when we have a response.

[jack_schueler]

It looks like the algorithm for the output width is 2 for the 0x + sizeof(item). The size of the binary value is 10 bytes.

A work-around is to cast the hexadecimal string to a type twice as long (20 instead of default 10). Here's an example.

SELECT CAST( 0x30313233343536373839 as BINARY(20) ) AS a,
       a AS bbbbbbbbbbbbb,
       a AS cccccccccccccc,
       a AS ddddddddddddddddddddd,
       a AS eeeeeeeeeeeeeeeeeeeeee,
       a AS fffffffffffffffffffffffffff;

[Breck_Carter]

CAST ( 0x30313233343536373839 AS BINARY ( 10 ) ) seems to make it work as well, so perhaps there's something deeper going on here 🙂

[jack_schueler]

Running from the command line like this?

dbisql -c dba,sql read c:\\temp\\bin.sql

It doesn't for me. Perhaps it depends on the build.

[Breck_Carter]

My mistake... ignore my comment.