What is the advantage of using Sqsh with FreeTDS?

Question

newbie question here: I have FreeTDS working and querying a Microsoft SQL server 2008 r2 db, from a mint linux vm, reading the inter-webs, mostly stackoverflow, it is mentioned that I should use sqsh also with FreeTDS, SQSH & FreeTDS, I don't understand what sqsh, besides being sometype of cli I guess, adds as I can use FreeTDS to access the db.. Any info to clear this up would be appreciated.

Answer 1

I have used sqsh a lot in the past. It was really helpful.

If you have used sybase/mSServer isql, you'll know that it's a real pain. There's no real support for cmd-line editing and history. SQSH was originally created to provide a shell-like cmd-line environment for interfacing with your database. It very easy to use to extract data from a database and save to a file. You can then edit the file as needed, and use bcp or other to load data into new table.

The SQSH shell supports Environment variables, file redirection (I don't recall using input direction internallY, but being able to write

select c1, c2, c3,c7, c120 from customer > customerFile

was very handy.

See the complete list of features at http://www.sqsh.org under the features link from the menu on the left.

Commands 
Variables 
Redirection and Pipes 
Aliasing 
Command Substitution 
Backgrounding & Job Control 
SQL Batch History 
Configurable Exit Status 
Inter-Server BCP 
Remote Procedure Calls 
Semicolon "go" 
Simple Scripting 
Flow-of-Control  
Functions  
Multiple Display Styles 
X Windows Support 
Macro Pre-Processing 
Miscellaneous 
Commands

Note that Flow-of-control allows to have 1 query that returns a multi-line result set, and use do, and while loop processing on each record. This also support if ... elif ..fi blocks. I don't recall using those features that much, but I found the aliases and env vars, and redirection saved me hours.

That said, I was a motivated user. The documentation will not spoon feed to you all of the answers, so some time will be required for experimentation, head-scratching and review.

I don't have any experience with TDS except to have read about it. Basically the question you need to answer is, am I shell command-line junky, and would I be willing to spend a little time to learn how to get cmd-line power in a text-based SQL client.

IHTH.

Answer 2

I use unixodbc and freetds on ubuntu in combination with perl's ODBC module. At times (very rarely though) I use isql for a quick query but thats it. SQSH is something similar to isql with better feature. Check out the SQSH wiki

SQSH has more features than isql. However, I never came across a usecase where I had to use isql / sqsh extensively. Whatever I have, I do it via perl.

Answer 3

TDS (Tabular Data Stream) is the native client - server communication protocol that Sybase and MSSQL are using and is implemented in CT_Library, a proprietary set of middleware software and utilities. FreeDTS is the OpenSource equivalent for CT-Library. Sqsh uses CT-Library calls to communicate with the server and can be build/linked with both Sybase CT-Library or FreeTDS. Sybase CT-Library only supports TDS version 5.0 and below, while MSSQL uses TDS version 7.0 or 8.0 nowadays. These protocol versions are also supported with FreeTDS. So if you want sqsh to be able to connect to MSSQL, you will need FreeTDS as the middleware layer. FreeTDS also comes with a couple of basic utilities you can use to connect to a server and execute some SQL statements (fisql, tsql). Sqsh however provides much more functionality, like a real shell.