Can I execute a MySQL query from Vim visual selection with output in a new buffer

Question

Given a file which consists of multiple line MySQL queries, eg

SELECT foo, bar, etc
FROM blah
WHERE something or other
LIMIT etc

Is there any way I can visually select a query in Vim, pipe it through MySQL, and see the query and result in a new buffer?

Clarification: I don't want tabular output, but something that can be further processed in vim or imported into a spreadsheet (like the tab-separated output that you get from mysql --batch) (Ubuntu Linux).

Answer 1

The Dbext plugin supports this behavior.

Visually select the SQL statement, and run :DBExecRangeSQL to execute it.

The result will be returned into a new split at the bottom of your current viewport.

There are lots and lots of options for controlling the output window. See :help dbext for the glorious details.

Update 1.May.2012

Version 15.0 of the plugin has been released with this functionality built in.

The default -t flag can be overridden

Default setting:

let g:dbext_default_MYSQL_extra = '-t'

Overridden for batch setting

let g:dbext_default_MYSQL_extra = '--batch --raw'

Dbext hard-codes the -t option to MySQL, but if that line is removed from dbext.vim, on line 2278 in DB_MYSQL_execSql (of my current version), you can pass the --batch and --raw options:

:DBSetOption MYSQL_cmd_options='--batch --raw'

To restore tabular output:

:DBSetOption MYSQL_cmd_options='-t'

I tested this successfully on my installation.

Answer 2

Thanks to Michael and Zsolt Botykai for suggesting dbext and other vim plugins - they don't seem to provide raw output though.

I've put the following in my .vimrc, inspired by Matias' answer here. This is my first attempt at vimscript, so caveat emptor...

function Query() range
    " use a temp file for result
    let s:tmpfile = system('mktemp')
    " single line copy of query followed by blank line
    echo system('echo '.shellescape(join(getline(a:firstline,a:lastline)," ")).
    \ ' > '.s:tmpfile)
    echo system('echo >> '.s:tmpfile)
    " pipe through mysql into temp file
    echo system('echo '.shellescape(join(getline(a:firstline,a:lastline),"\n")).
    \ '| mysql --batch --silent --raw &>> '.s:tmpfile)
    " and open in new buffer
    exec 'ed '.s:tmpfile
endfunction
" select query and <F5>
vmap <F5> :call Query()<cr>

Visually select query, press F5 (or :call Query()), and the result is opened in a new buffer.

It assumes linux (with mktemp) and gets connection details from .my.cnf