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).

Unfortunately, Michael's answer relies on changing the dbext source but it is such a great script that I have to accept it. (My answer does do what I asked for, but without all the power and flexibility of dbext) — Ken, Apr 18 '12 at 11:53
Thanks to David Fishburn (the script maintainer) and Michael's help, dbext now (as of v15.0) does exactly that I need! — Ken, May 03 '12 at 07:58

score 20 · Accepted Answer · edited Jun 20 '20 at 09:12

20

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.

dbext example output

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 12 '12 at 14:13

Michael Berkowski

267,341
46
444
390

Dbext is going to come in very useful - thank you. I wasn't clear enough in my question that I need non-tabular data that I can then export as csv or import into a spreadsheet. I've played/googled around with Dbext but I can't see any --batch --raw options. – Ken Apr 12 '12 at 18:19
@Ken There's an option `dbext_default_MYSQL_cmd_options` which I think you can pass --batch --raw to. – Michael Berkowski Apr 12 '12 at 18:21
@Ken I've never used it and so am not certain of the syntax but it's mentioned in `:help dbext-configure-options` – Michael Berkowski Apr 12 '12 at 18:33
Looks like it should be `:DBSetOption MYSQL_cmd_options='--batch --raw'` – Michael Berkowski Apr 12 '12 at 18:36
I've tried "let g:dbext_default_MYSQL_cmd_options = '--batch --raw'" and it doesn't make any difference. I also tried "let g:dbext_default_MYSQL_cmd_options='--batch --raw --argh'" and it gives an error on 'argh' - so it is reading the option, just ignoring it: mysql: unknown option '--argh' – Ken Apr 12 '12 at 18:54
@Ken Yeah, I'm seeing something consistent with that. MySQL is getting the option and I saw it on the command line when I had one fail for another reason, but MySQL didn't honor it ?? :-/ – Michael Berkowski Apr 12 '12 at 19:08
I'm not entirely sure what is happening - the plugin seems to be ignoring/overriding the '--batch -raw' options and doing it's own thing. – Ken Apr 13 '12 at 19:24
I did what I should have originally and went into the source. Unfortunately DB_MYSQL_execSql (in dbext.vim) has a '-t' option hardcoded in. I can inject '--batch --raw' before or after it (using extra or cmd_options) but nothing seems to override that '-t' – Ken Apr 18 '12 at 11:25
@Ken Sounds like you need to comment out that line in dbext.vim then – Michael Berkowski Apr 18 '12 at 11:31
@Ken I tested and batch/raw work properly after removing the line. – Michael Berkowski Apr 18 '12 at 11:37
2

@Ken This has been bugging me all week, and my bounty rep felt ill-gotten. I just sent the maintainer a patch to add `MYSQL_batch_output` (`--batch`) and `MYSQL_raw_output` (`--batch --raw`) options (`1|0`, both default 0). – Michael Berkowski Apr 22 '12 at 01:05
@MichaelBerkowski Hi, could you tell me what font/colorscheme you are using in the screenshot? – Stas Jaro Dec 03 '14 at 02:49
@StasJaro I think that was jellybeans. https://github.com/nanotech/jellybeans.vim I don't use it anymore. – Michael Berkowski Dec 03 '14 at 03:17

score 2 · Answer 2 · edited May 23 '17 at 12:00

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

score 1 · Answer 3 · answered Apr 12 '12 at 14:13

1

Try the dbext plugin.

But there are some others for this task.

answered Apr 12 '12 at 14:13

Zsolt Botykai

50,406
14
85
110

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

3 Answers3

Update 1.May.2012