#29 sqsh output like MySQL

I'm using sqsh to connect to a MS SQL server. I am striving to get output formatted like MySQL:

+==============================+=============+
| date                         | count       |
+==============================+=============+
| 2010-02                      |        1242 |
| 2010-03                      |        1126 |
| 2010-04                      |         832 |
+------------------------------+-------------+

I've tried all the \style settings. The closest I can get is pretty which outputs like this:

+==============================+=============+
| date                         | count       |
+==============================+=============+
| 2010-02                      |        1242 |
+------------------------------+-------------+
| 2010-03                      |        1126 |
+------------------------------+-------------+
| 2010-04                      |         832 |
+------------------------------+-------------+

But all the unnecessary separator lines wastes half the vertical screen space.

I asked on Stack Exchange how can I make sqsh output readable, compact columns like MySQL, and someone suggested I should suggest a patch, so here it is.

I hope the authors of this great piece of software are active and can publish a new version with this very simple change in the near future. Thanks!

[Martin Wesdorp]

I will add a new option "-l" to the \go command that will suppress the separator lines in pretty style output. (Like suppressing headers and footers with -h and -f). This new functionality will be available in the next sqsh-2.5 release that will be released in Q1 2014.

For example:

[1] SYSDTA.sa.master.1> \set semicolon_cmd="\go -mpretty -l"
[1] SYSDTA.sa.master.1> select * from sysloginroles;
+=============+=============+========+
|        suid |        srid | status |
+=============+=============+========+
|           1 |           0 |      1 |
|           1 |           1 |      1 |
|           1 |           2 |      1 |
|           1 |           3 |      1 |
|           3 |           0 |      1 |
|           1 |          10 |      1 |
+-------------+-------------+--------+

(6 rows affected)

HTH, Martin

[Anonymous]

Wow! That was quick! Looking forward to the release. Or I'll be happy to test a preview right now if possible.

[Martin Wesdorp]

Hi,

I just checked in the changes into CVS. You can download the most recent src package by selecting "Download GNU tarball" from the "Code" tab and "web-based CVS repository viewer" link. (http://sqsh.cvs.sourceforge.net/viewvc/sqsh/). Please note that the documentation is not updated yet. You can use the option -l to the "\go -mpretty" command to get MYSQL like output.

Please let me know your results and findings.

Cheers,
Martin.

[Anonymous]

The installation fails with:

configure: error: Unable to locate Sybase installation. Check your SYBASE environment variable setting.

The INSTALL file says I need "Sybase CT-Library", "Open Client 11.x" or "64 bit installation of ASE". What I actually need to install is a bit unclear, and the Sybase website had several broken links on what seemed to be the right choice.

Could you provide specific download instructions? I am on OS X.

(I already have sqsh installed as a Homebrew package, but I couldn't find any obvious candidate on my local system for setting the SYBASE Bash variable).

[Martin Wesdorp]

You will need FreeTDS as middleware in order to build sqsh on OS X. Check http://www.freetds.org for download and installation instruction. You will also need the readline development package. Suppose you install freetds in /usr/local, you then have to set the SYBASE environment variable to /usr/local and run configure again in your sqsh root dir (see conf.sh for some examples and instructions to run configure).

HTH, Martin.

[Anonymous]

I already have freetds and readline from Homebrew.

export SYBASE=/usr/local did wonders for ./configure.

However, when running make I get a new error:

gcc -c -g -O2  -o tsql.o tsql.c
tsql.y:4:14: fatal error: 'malloc.h' file not found
    #include <malloc.h>
             ^
1 error generated.

[Martin Wesdorp]

Looks like you are missing a development library package. File malloc.h should be in /usr/include. I am not familiar with OS X, but normally speaking this would be installed by a package named libc6-dev or glibc-headers or something like that.

HTH, Martin.

[Anonymous]

Replacing <malloh.h> with <stdlib.h> solved the problem. You may want to update the source code with this. Anyway, compilation was successful and the fix works! Thank you!

However, another problem with this build: When I press the cursor keys on the command line, the characters ^[[A^[[B^[[C^[[D are echoed. This makes the version unusable. Is this a bug, or how can I reconfigure my system to make the cursor keys work again?

[Martin Wesdorp]

Hi,

Thanks for the update. I will have a look into the malloc.h problem. It appears that this one is only included in some new code (sqsh_parser) contributed by somebody else and that was only introduced in sqsh-2.4.

Regarding your problem with the cursor keys, I think you lack readline support. Did you run configure with --with-readline? If so, did configure complain about being unable to find a readline include file, library or something? It should show something like:

...
checking for libreadline... /lib
checking for readline/readline.h... /usr/include
checking for readline link... -lreadline -lcurses
checking for rl_completion_matches in -lreadline... yes
...

You do have a readline-dev package installed, do you? What does the src/Makefile contain on READLINE entries from line 46? Should be something like:

DEF_READLINE    = -DUSE_READLINE
READLINE_LIBDIR =
READLINE_INCDIR = # -I/usr/include
READLINE_LIBS   = -lreadline -lcurses

The configure script checks for the libreadline.a or libreadline.so file in:
SEARCH_PATH="/lib:/usr/lib:/usr/local/lib:${HOME}/lib:${HOME}/usr/lib"
and the readline/readline.h in
SEARCH_PATH="/usr/include:/usr/local/include:${HOME}/include:${HOME}/usr/include"

If your locations differ, you may have to edit the src/Makefile and specify the correct locations using these variables, eg

DEF_READLINE    = -DUSE_READLINE
READLINE_LIBDIR = -L/opt/readline/lib
READLINE_INCDIR = -I/opt/readline/include
READLINE_LIBS   = -lreadline -lcurses

You then also may need to set the LD_LIBRARY_PATH environment variable.

HTH,
Martin.

[Anonymous]

Tried ./configure --with-readline, then make:

In file included from cmd_do.c:40:
./sqsh_readline.h:41:13: error: conflicting types for 'add_history'
extern void add_history      ();
            ^
/usr/include/readline/readline.h:146:7: note: previous declaration is here
int              add_history(const char *);
                 ^
In file included from cmd_do.c:40:
./sqsh_readline.h:42:29: error: expected ';' after top level declarator
extern void _rl_clear_screen PARAMS((void));
                            ^
                            ;

src/Makefile:

DEF_READLINE    = -DUSE_READLINE
READLINE_LIBDIR =
READLINE_INCDIR = # -I/usr/include

[Anonymous]

[Martin Wesdorp]

Hi,

Looking at sqsh_readline.h it appears from the comments that some platforms/OS's or distributions lack(ed) the history.h file that is related to readline.h. Because of this some function prototypes related to readline history processing have been declared here as well, but do not match the function argument declarations exactly. That may lead to the compilation issues you encountered.

I implemented a change here to only declare the prototypes in sqsh_readline.h in case the history.h was not already included and the prototype arguments now match the actual function arguments. Hopefully this solves your compilation problem on OS X.

I also changed the occurrences of malloc.h to stdlib.h were appropriate. The malloc.h include file is deprecated and is not available on all platforms and distribution. Thanks for your remark on this.

Could you please download the source package again from CVS and retry the installation and let me know your results? In the mean time thank you for your help and patience in making the sqsh installation more robust.

Cheers,
Martin.

[Anonymous]

Still getting the same error. Did you update the tarball? I downloaded it from here: http://sqsh.cvs.sourceforge.net/viewvc/sqsh/

[Martin Wesdorp]

The tarball is automatically created by Sourceforge, but I am not sure when it is recreated exactly. Could be only once a day at night. I just checked the tarball and it contains my last changes. Could you please check the modification date of the sqsh_readline.h file in the src directory? It should be "Jan 21 15:07". If you have the correct version then I have to dig deeper to the cause of the errors you encounter.

Brgds, Martin.

[Anonymous]

2181 Jan 21 15:07 sqsh_readline.h

[Martin Wesdorp]

Looks like the readline implementation on OS X is not compatible. What version of readline is OS X using? Could you try to install GNU readline 6.2 from http://ftp.gnu.org/gnu/readline/

Suppose you install GNU readline in /usr/local you can specify that location in the sqsh Makefile

DEF_READLINE = -DUSE_READLINE
READLINE_LIBDIR =
READLINE_INCDIR = -I/usr/local/include

[Anonymous]

My readline installation is stable 6.2.4 via Homebrew. Shouldn't this be sufficient?

http://tiswww.case.edu/php/chet/readline/rltop.html

[Martin Wesdorp]

Could you please attach your /usr/include/readline/readline.h and /usr/include/readline/history.h file, so I can have a look at the OSX readline implementation. It looks like OSX defines add_history as a function returning int, while on all other platforms this function is declared void.

[Martin Wesdorp]

Could be that your readline implementation is basically a port from the NetBSD editline library that is not compatible with GNU readline implementations. As I understand, there are legal issues on Apple OS X with GNU GPL licenses, and that might be the reason why there is no standard GNU readline library package available. (Correct me if I'm wrong, :-) ).
So the best solution would be to build readline yourself from the sources, I suggest.

[Anonymous]

You are probably right. Installing readline from Homebrew says the following:

OS X provides the BSD libedit library, which shadows libreadline.

In order to prevent conflicts when programs look for libreadline
we are defaulting this GNU Readline installation to keg-only.

If you build your own software and it requires this formula,
you'll need to add to your build variables.

I solved this by doing:

export LDFLAGS="-L/usr/local/opt/readline/lib"
export CPPFLAGS="-I/usr/local/opt/readline/include"

Then I got another problem:

~/sqsh$ sudo make install
lemon -q -s -q tsql.y
make[1]: lemon: No such file or directory
make[1]: *** [tsql.c] Error 1

I solved this by doing:

brew install lemon

Finally, sqsh was installed. But I still get the ^[[C control characters when pressing cursor keys. (Note that this DOES work with the official release of sqsh in Homebrew).

Any ideas?

[Martin Wesdorp]

Normally speaking you would not need lemon nor flex to make the sqsh_parser.a archive library because the generated files are already there. Because the timestamps of these files in CVS are not correct, "make" thinks the source files are newer and need to be processed again. In the final distribution package of sqsh-2.5 I will make sure the generated files will be touched so will not be recreated again with lemon or flex. You ran into this problem because of the CVS tarball download, I am afraid.

OK, so now you are able to build sqsh, so we are getting somewhere. Could you please check with

ldd /usr/local/bin/sqsh

which readline library will be loaded. I assume sqsh is linked dynamically and is trying to load a version of libreadline.so.6, but is it the correct one? How is the original homebrew sqsh version created, statically or dynamically linked? What happens if you try to set

LD_LIBRARY_PATH=/usr/local/opt/readline/lib:$LD_LIBRARY_PATH

in your shell, does that make any difference in the "ldd" output and does sqsh works correctly then or is it still encountering cursor key problems?

HTH, Martin.

[Anonymous]

My mistake this time. I forgot ./configure --with-readline. Now sqsh works with cursor keys!

~$ otool -L /usr/local/bin/sqsh
/usr/local/bin/sqsh:
      /usr/local/lib/libct.4.dylib (compatibility version 5.0.0, current version 5.0.0)
      /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1197.1.1)
      /usr/local/opt/readline/lib/libreadline.6.2.dylib (compatibility version 6.0.0, current version 6.2.0)
      /usr/lib/libncurses.5.4.dylib (compatibility version 5.4.0, current version 5.4.0)

[Anonymous]

DELETE THIS REQUEST