[freetds] compute rows

matthew.green at datamartcomputing.com matthew.green at datamartcomputing.com
Mon Feb 8 03:53:14 EST 2016


In case it helps:

Sybase 15.7 client with sqsh produces "predictable" results, with the correct headings. 

On the other hand isql itself produced results more like fisql:

1> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), count(col1), avg(col2), max(col3), min(col3)
2> go
 col1        col2        col3
 ----------- ----------- -----------
           1           1           1
           2           2           2
           3           3           3


Compute Result:
 ----------- ----------- ----------- ----------- ----------- -----------
         -----------
           6           2           1           3           2           3
                   1

Sybase on Windows (isql GUI) gives:

col1        col2        col3        
----------- ----------- ----------- 
1           1           1           
2           2           2           
3           3           3           
(3 rows)

Sum(col1)   Avg(col1)   Min(col1)   Count(col1) Avg(col2)   Max(col3)   Min(col3)   
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
6           2           1           3           2           3           1           

but isql from a DOS box gives the same output as Solaris.

All a bit of a mess, even when using the vendor version!

I tried to track the TDS stream with Ribo and I couldn't see any obvious difference in communication flow in regard to the results returned in each case. I'd therefore guess that the presentation must be a client side decision.

Cheers,

Matthew.





February 7 2016 1:50 PM, "Frediano Ziglio" <freddy77 at gmail.com> wrote:
> 2016-02-06 9:33 GMT+00:00 John Kendall <john at capps.com>:
> 
>> I am having trouble with the output of compute rows. I found the following exchange from 2007
>> between jkl and Michael Peppler that summarizes what I'm experiencing in 2016:
>> 
>> http://lists.ibiblio.org/pipermail/freetds/2007q1/020954.html
>> 
>> To summarize, sqsh (and other tools linked to freetds) do not produce usable output for compute
>> rows. Michael Peppler says that sqsh acts correctly when linked to sybase libs. I have an old
> copy
>> of isql that handles compute statements in a very predictable way, for example:
>> 
>> select col1=1, col2=1, col3=1 into tempdb..tab
>> insert tempdb..tab select 2,2,2
>> insert tempdb..tab select 3,3,3
>> go
>> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), count(col1), avg(col2),
>> max(col3), min(col3)
>> go
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum
>> ===========
>> 6
>> avg avg
>> =========== ===========
>> 2 2
>> min min
>> =========== ===========
>> 1 1
>> count
>> ===========
>> 3
>> max
>> ===========
>> 3
>> 
>> (4 rows affected)
>> 
>> sqsh, linked to freetds, produces:
>> 
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> 
>> ===========
>> 6
>> 
>> ===========
>> 2
>> 
>> ===========
>> 1
>> 
>> =========== =========== ===========
>> 3 2 3
>> 
>> ===========
>> 1
>> 
>> (4 rows affected)
>> 
>> Note how sqsh doesn't label the aggregate columns, and it combined count, avg and max into one
> row,
>> while not combining the two min rows. Pretty useless.
>> 
>> I no longer have access to sybase libs, so I cannot test sqsh linked to them.
>> Just for comparison, fisql has a different set of problems:
>> 
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum avg min count avg max min
>> ----------- ----------- ----------- ----------- ----------- ----------- -----------
>> 6 2 1 3 2 3 1
>> 
>> (4 rows affected)
>> 
>> Micheal's theory was that an API call in CTlib wasn't quite right, such as ct_describe() or
>> ct_compute_info().
>> Any ideas anyone?
>> Thanks,
>> John
> 
> Weird request. MS is trying to remove (if not already) support for COMPUTE.
> Probably the better way is to looks at the dumps (TDSDUMPs), and see
> what functions
> return. Then looks at documentation and callers (fisql, sqsh) and try
> to understand what's
> wrong.
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds


More information about the FreeTDS mailing list