Monday, January 08, 2007

Freezing titles ala spreadsheet land.

Every now and then I have to read the results of a SQL query on the command line, and I find the following setup to work wonders in VIM. At least this works well with MySQL, as its command line query tool is kind enough to spit out the output in tab-delineated form.

Say you have a query to perform. Do the following:

echo "select * from tableX;" | mysql -uUser -pPassword databaseName | vim +'set scb sbo+=hor sbo-=ver nowrap ts=30 bt=nofile| sp | exe "norm \<c-w>j\<c-w>_\<c-e>"' -

This will automatically space out the tabs so you can see each of the search result columns with the data, and then it creates two panes - one for the column descriptions, and one for the data. The scroll options are setup so that you can navigate around the data and always see the column description with the data (particularly helpful for large result sets).

The only real improvement I can think of is to use the VIM dbext plugin. I often find myself on several different machines I don't use often enough to warrant customizing plugins/settings, so this quick and dirty sql result browser setting is very handy to have around.


aliangchen said...
This comment has been removed by a blog administrator.
Dr. S said...

I am trying to understand this: I am ok with the scrollbind options.. Could you tell me how do the sp and exe functions operate.

DeeSumm said...

Hey there,

sp: it splits the buffer into two windows (one to show the header as seen at the top of the screenshot, one to show the 'body' of row data)
exe: basically do some key sequences ala 'normal mode' to make the top window only row tall, and then place the cursor in the 'body' window so that you can navigate around.

I actually still use this mapping every now and then, particularly on remote machines - but I have come to love the CSV plugin when editing TSV/CSV type files.