SQL Server adapter

[tpope]

This was really easy to stitch together. Only problem is I don't have a way to test it:

if exists('g:autoloaded_db_adapter_sqlserver')
  finish
endif
let g:autoloaded_db_adapter_sqlserver = 1

function! db#adapter#sqlserver#canonicalize(url) abort
  let url = a:url
  if url =~# ';.*=' && url !~# '?'
    let url = tr(substitute(substitute(url, ';', '?', ''), ';$', '', ''), ';', '&')
  endif
  return db#url#absorb_params(url, {
        \ 'user': 'user',
        \ 'userName': 'user',
        \ 'password': 'password',
        \ 'server': 'host',
        \ 'serverName': 'host',
        \ 'port': 'port',
        \ 'portNumber': 'port',
        \ 'database': 'database',
        \ 'databaseName': 'database'})
endfunction

function! db#adapter#sqlserver#interactive(url) abort
  return 'sqlcmd' . db#url#as_args(a:url, '-S ', '', '', '-U ', '-P ', '-d ')
endfunction

[denzuko]

one could run SQL server in docker.

I'll have time to try it out this weekend. if there's any bugs I'll submit a pull request otherwise consider it working.

[Scoobed]

Linux — https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools

You should use the Sqlcmd instead of osql as sqlcmd is cross platform

[tpope]

Edited to use sqlcmd rather than osql.

@denzuko if you could offer positive confirmation you got it to work with both :DB sqlserver://... and :DB sqlserver://... select 1 that'd be great.

[dhazel]

@tpope for both :DB sqlserver://... and :DB sqlserver://... select 1 I am getting a second cmd window hanging open, and the command never completes.

Contents of the cmd window:

C:\WINDOWS\system32\cmd.exe /c (sqlcmd -S ^"database-server^" -U ^"UserName^" -P ^"password^" ^<C:\Users\MyUser\AppData\Local\Temp\VIi378E.tmp ^> C:\Users\MyUser\AppData\Local\Temp\VIo379F.tmp 2^>^&1)

When using the osql variant, I get the customary flash of a cmd window, and then in the vim8 messages I get the following.

Error detected while processing function db#execute_command:
line   66: 
E484: Can't open file C:\Users\MyUser\AppData\Local\Temp\VIC2DFD.tmp.dbout

[tpope]

The hanging window might be hanging waiting on input. It would be blank in the select 1 case but I'd expect to see a prompt or something in the interactive case. It looks like the output you showed is for the former, can you show me the output for the latter?

Can't open file generally means the output wasn't captured because the shell bombed out, perhaps because there's no osql executable on your system?

Can you confirm any other adapter works?

[pheslinga]

I'm able to get osql working on windows using neovim. Still trying to figure out why sqlcmd won't work, is there any way to expose the command string it's calling? Finally, is there a way to expose the trusted authentication flag?

[tpope]

You can call :echo db#adapter#sqlserver#interactive("sqlserver://...") to see the command line.

For trusted authentication, could we just pass -E automatically if a username isn't given? We could add an explicit flag but I'd rather just be smart about it if possible.

[dhazel]

I should have mentioned, I did verify both osql and sqlcmd on my system, ran them both, and executed those two test queries outside of neovim using each.

I'll look into the other suggestions shortly.

[dhazel]

Okay.

The osql variant works great in neovim on windows. Turns out I tried it in vim8 yesterday without realizing it. The osql variant in vim8 has the issues described in my first comment above.

I am running out of time today. If you are still working on a solution tomorrow I'll thoroughly try out sqlcmd and maybe get to a little debugging.

[pheslinga]

I was going to recommend a default but I wasn't sure how that would be handled coming from linux.
Running through "interactive mode" Both executions are the same: [command] -S "[Servername] SELECT 1" -U"[...]" -P"[...]" I'm not exactly sure why it's only working for osql given the expected execution for either should split out the server and query like so: [command] -S "[Servername]" -Q"SELECT 1" -U"[...]" -P"[...]"

Additionally profiling server connections confirms this given that when using sqlcmd a connection is started but no command is passed.

[tpope]

Pass only the URL to db#adapter#sqlserver#interactive, the query is provided on standard in. Test with echo "SELECT 1" | sqlcmd ....

We could limit the -E to Windows only, or just ignore the issue entirely, as surely omitting a username makes no sense if trusted connections aren't available?

[pheslinga]

I'd argue in favor of ignoring the issue, in order for it to authenticate running linux or macOS computer it requires an additional configuration file that looks pretty straight forward. https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication#authenticating-a-linux-or-macos-computer-with-active-directory

Perhaps I was unclear, passing the URL produces the appropriate connection string it's building the same thing regardless of the command, it's the subsequent steps I'm trying to take a look at.
:echo db#adapter#sqlserver#interactive("sqlserver://[]:[]@[Servername]") response:[sqlcmd/osql] -S"[Servername]" -U"..." -P"..."

Running from a command line echo SELECT 1 | [osql/sqlcmd] -S"[Servername]" -U"..." -P"..."

For whatever reason they are interpreted differently within what I assume is db.vim. Perhaps I'm wrong but is there something going on with a regular exp replace when the url is passed around. command: DB sqlserver://[...]:[...]@[Servername] < test.sql file contents: SELECT 1

[tpope]

I'm afraid this is a bit beyond me. We could start with osql only if that's working for everyone. Unlike sqlcmd, a version of osql is available with apt install freetds-bin, so deprecation aside, I'm inclined to believe it's the superior option.

[Scoobed]

I am going to try OSX version tomorrow as I have a sql Server I can connect to. I will look at the sqlcmd args also. My experience both commands function are pretty close to the same. It is just osql came out with SqlServer 2000 and sqlcmd can out with Sql2005. And Microsoft seems to really only want to support on anymore. If I get to work I will do a pull request

[Scoobed]

Also did not realize that to do -E on OS X they want Kerberos Authentication. Which I have had issues with on older SQL Server implementations. FreeTDS maybe a good option

[tpope]

Looks like half the problem is that sqlcmd on UNIX is broken. I can work around that particular issue with -i, but that breaks osql (the freetds version anyways), plus commits me to a dumb special case.

Anyways, try the sqlserver branch. It's rigged up to work with either command, but I think I'd rather pick a side before releasing.