Can we format proper column length in isql?

[Hex.Sean]

We always see the column display size is too large.
Can isql automatically decrease the display length?
Just like mysql?
Even I add -w option, it looks not very good.

[Michael Peppler]

isql doesn't really do any output formatting (other than using columns
large enough to display the data, or the header name).

Any formatting you need to do *for isql* needs to be done in the SQL
itself.

In general one assumes that the client will do the formatting - it's just
that isql is a very simple tool and has never pretended to be anything
else :-)

You could write a perl script, or php, or VB, or... where you implement
the required behavior.

Michael

[Mark A. Parsons]

If running ASE 15.x, have you tried using the Sybase-supplied sp_autoformat stored proc.

sp_autoformat will dynamically resize columns to be 'just wide enough' to display the longest value in the result set.
Many of the system stored procs are coded to use sp_autoformat.

There's a performance overhead for using sp_autoformat so I would not suggest using this for a high volume of queries.

If you're running ASE 12.x, and have access to an ASE 15.x dataserver, you may be able to back port a copy of
sp_autoformat for use in ASE 12.x. Just pull the source code for sp_autoformat from the ASE 15.x installation (eg,
sybsystemprocs..syscomments, installmaster script).

[Bret Halford]

Not automatically.

you can generally use str() to limit the size of
displayed numerics and convert(char(n), <x>) or
convert(bin(n), <x>) to control the width (and display style
for DATE, DATETIME, and TIME) of columns.

Otherwise ISQL has no idea ahead of time what the length of
the longest value in a column is going to be, it just knows that the
datatype for the column is <x>, which will take a maximum of
<y> characters to display, and outputs accordingly

I have no experience with the mysql client - could you give more
details on exactly how it displays values of different lengths? Is
the output still lined up in columns? Any idea how it does it - perhaps
by buffering all the rows on the client side before displaying them?

For ASE, I imagine an application could be written that, say,
selects the values into a temp table first, then determines the
max length of the data in each column to determine the display,
then selects the data and displays it that way. But isql is
not that application.

Cheers,
-bret

[Carl Kayser]

"Bret Halford" <br...@sybase.com> wrote in message
news:48c14e9f$1@forums-1-dub...

> Hex.Sean wrote:
>> We always see the column display size is too large.
>> Can isql automatically decrease the display length?
>> Just like mysql?
>> Even I add -w option, it looks not very good.
>
> Not automatically.
>

(SNIP)

>
> For ASE, I imagine an application could be written that, say,
> selects the values into a temp table first, then determines the
> max length of the data in each column to determine the display,
> then selects the data and displays it that way. But isql is
> not that application.
>
> Cheers,
> -bret

That's the way I go when writing utility stored procedures. Sometimes a
nuisance coding-wise but I hate wraparound.

[Hex.Sean]

I think there should be a proper way for isql to support
this feature.
I just did a test.
Table t(id int primary key, col2 varchar(32) null)

For mysql:
mysql> select * from t;
+----+------+
| id | col2 |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | fdas |
+----+------+
5 rows in set (0.03 sec)

mysql> desc t
-> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |
| col2 | varchar(32) | YES | | NULL |
|
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

But for ASE (isql):
1> create table t(id int primary key, col2 varchar(32) null)
2> go
1> insert into t values(1, null)
2> insert into t values(2, null)
3> insert into t values(3, null)
4> insert into t values(4, null)
5> insert into t values(5, 'fdas')
6> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> select * from t
2> go
id col2
----------- --------------------------------
1 NULL
2 NULL
3 NULL
4 NULL
5 fdas

(5 rows affected)
1>

[Jason L. Froebe [TeamSybase]]

Take a look at SQSH, it supports output formatting you are requesting.
It is open source and available at http://www.sqsh.org

isql is a bare bones type of interface as you've undoubtedly discovered.

--

Jason L. Froebe
TeamSybase
http://www.froebe.net/blog
MyDatabases Free Magazine http://froebe.net/blog/mydatabases-magazine