Sunday, August 26, 2012

Keyword tab completion in sqsh(MsSQL console client in Linux)

I am one of those unlucky guys who have to maintain software that uses Microsoft SQL server. So i need to manipulate data on MsSQL server from time to time. Squirrel SQL GUI client can help me to do this. It's a great tool that can work with any JDBC compliant databases, but using GUI tool it's not always an option. I've done some googling and found a blog post that explains how to connect to Microsoft SQL server from linux via console using sqsh.
Here, i am going to outline a few steps for configuring sqsh:
  1. Install sqsh and freetds from software repository or build it from source.
    NOTE: if building from source don't forget to enable readline support.
  2. Configure database servers in freetds.conf file. freetds config file is located in /usr/local/etc/ or /etc/freetds folder.
            host = myserver
            port = 1433
            tds version = 8.0
    NOTE: You can find out path to the freetds.conf file by executing 
    tsql -C | grep freetds.conf command.
  3. Configure default connection settings and style. The output of ~/.sqshrc should look like this:
    \set username=sa
    \set password=password
    \set database=DB_NAME
    \set style=pretty
    \set colsep="|"
    \set semicolon_hack=1
    \set keyword_completion=3
  4. Connect to MsSQL server by specifying server name from freetds.conf and file with connection settings:
    sqsh -S server1 -r ~/.sqshrc
    NOTE: By default sqsh will always use ~/.sqshrc file.
Now you can manipulate data via console. It works well, but it's missing autocomplete of table and column names. Luckily sqsh has -k(keywords) parameter that let's you specify a file containing a list of keywords to be used for keyword tab completion(see manual). So we need to create a list of all table and column names presented in our database, store it in ~/.sqshrc_keywords data file and feed it to the sqsh on startup.
Next sql statement will create dictionary that will be used by tab completion.
SELECT SO.NAME FROM dbo.sysobjects SO 
WHERE SO.xtype = 'U' ORDER BY SO.[name];
SELECT SO.NAME + '.' + SC.NAME FROM dbo.sysobjects SO 
INNER JOIN dbo.syscolumns SC ON =
WHERE SO.xtype = 'U' ORDER BY SO.[name], SC.colid;
In this blog post you can find more information on how to get listing of all table and column names in MsSQL. Store this sql statement in ~/.sqshrc_keywords_sql file.
Then create a script that will generate SQL dictionary and establish a connection with the database:
sqsh -S server1 -m bcp -i ~/.sqshrc_keywords_sql -o ~/.sqshrc_keywords; sed -i.bak 's/|//' ~/.sqshrc_keywords; sqsh -S server1 -k ~/.sqshrc_keywords
Use this script to connect to MsSQL database. 
Now it's possible to autocomplete table and column names with tab:

[1] Squirrel SQL client
[2] How to connect to Microsoft SQL server from Linux via command line
[3] SqSh
[4] FreeTDS
[5] Finding and listing all columns in a SQL Server database.

No comments:

Post a Comment