Making output on sqsh/Sybase readable

Hi,

I have a reasonably fundamental question on sqsh and Sybase.

Lets say that I type in a select query on a table that has quite a few columns. The output from sqsh seems to contain blank spaces for *all* columns, not just the ones that listed in the query. This is makes the output unreadable.

For an example of what I'm talking about, please see below. My question is, Is there any way to make the output more readable on sqsh (by neatly formatting the output, ignoring  the missing columns)?

Thanks in advance!

[example begins here]

[57] LOCALHOST.pubs2.1> select name from sysobjects where 1=2;

        name





        --------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------

arv2008Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
grant300Connect With a Mentor Commented:
There is no problem with SQSH; you are seeing an artifact of a change in ASE 15.

ASE 15 allows long object names, 254 characters I think.  If you look at the data model for the system tables (you do have that hung on your office wall, don't you) or do a sp_help on sysobjects, I think you will find that the "name" field now has a datatype of varchar(254).  Taking a quick look at your sample output, it looks like a fuzz over 80 characters per line and 4+ lines or about 254 bytes total.

This was not an issue in 12.5 and earlier version that only handled 32 character names.

SQSH makes the column size equal to the maximum size be default.

Regards,
Bill
0
 
arv2008Author Commented:
Thank you! That sounds like the likely cause.

Do you know of any (easy) way to make the output slightly more readable?
0
 
grant300Commented:
Sorry, I am not a SQSH user however there are a couple of tricks you can use.

The first is to put a CONVERT(VARCHAR(32),<column_name>) around the column in the select list.  This will, through the magic of TDS, fool SQSH into believing the maximum column width is just 32 characters.

Another more sophisticated approach is one that was published recently in the ISUG Tech Journal.  I don't have the exact reference but the deal was that it was a stored procedure that would "pretty print" the output for you.  If memory serves it worked against a temp table that had the raw query results then it would do a max(char_length(<column_name>)) query to find out what the maximum actual data length was for each column and then do the CONVERT(VARCHAR(#),) trick.

Oh, by the way, if you use functions in your select list, the query processor may loose or exaggerate the size of the columns.  Things like MAX(SUBSTRING.....) can confuse it so you might want to use the convert(varchar(#) if it gets out of hand.

Of course, the other option is to use more sophisticated tools.  There are three good ones out there.  The lowest cost is Aquafold Aqua Data Studio for about $395 followed by Sybase Workspace Database Developer for $495 and the queen of them all, Embarcadero RapidSQL fro about $1000 a copy.  The nice thing about these GUI tools is that they pull the results back and size the columns based on the actual content.

Regards,
Bill
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
arv2008Author Commented:
Thanks again for your reply.

> Embarcadero RapidSQL fro about $1000

I do use RapidSQL at work. This is for playing with Sybase - the DB server runs on a VMWare CentOS setup at home.

I wrote a small pager script in Perl to clean up the output a bit. It may not work for large datasets and it has some bugs, but is enough for the purposes of tinkering with Sybase.

0
 
grant300Commented:
FYI, RapidSQL works with Sybase as well as Oracle but it is not inexpensive.

Check out Aquafold's Aqua Data Studio.  It is definitely the most bang for the buck.  It works with most any database you are going to run into and even has a T-SQL debugger.

BTW, if you look at the Sybase client installation, there are a number of tools you can load to make your life easier.  Sybase Central gives you a DBA view of the database configuration and objects.  JISQL is a GUI ISQL/SQSH alternative; not great but a huge step up from command line.  There is also SQL Advantage but I think you will find JISQL more useful.

One other I forgot about was a Sybase project done in TCL/TK.  I have not used it in many years but it gave you a pretty decent interface.  It also had the advantage of being coded in TCL which is easy to modify and extend.  There is no better way to learn a database than by building tools for it.

Regards,
Bill
0
 
arv2008Author Commented:
I later discovered that sqsh can accept a mode option after \go to prettify the output.

E.g.,

> select col from table
> \go -m vert | less

This makes the output fairly readable. Other options are pretty, bc, etc.
0
All Courses

From novice to tech pro — start learning today.