SQLCMD: Alternative Way of Working with Microsoft SQL Database for Ruby
If you have Microsoft SQL server in your test environment and want to populate test data in Capybara, Calabash scripts you will most probably have difficult time to installing appropriate Ruby gems. One of most popular ruby gem for Mssql connection is tiny_tds but I was not able to install it for both Windows and Unix like environment just because of dependencies. I got the following errors:
Errno::EACCES: Permission denied @ rb_sysopen - /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/tiny_tds-1.1.0/.codeclimate.yml An error occurred while installing tiny_tds (1.1.0), and Bundler cannot continue. Make sure that `gem install tiny_tds -v '1.1.0'` succeeds before bundling.
Another popular ruby gem is sequel. It does not give an error for installation but when it comes to usage, it gives the following error for not found adapter. Main problem with the Mssql in unix is finding compatible adapter. See the log:
SQLCMD is a windows tool for designed for executing simple Mssql queries and Stored Procedures or .sql files by command prompt or Power Shell on windows. You can do the same thing with the SQLCMD project which is written as nodejs project on github. It doesn't have all the functionalities of windows embedede SQLCMD but it is pretty enough for testing project, and missing features can be easily added to this project. In this post I want to explain how we can use SQLCMD tools for connecting Microsoft SQL Server by Ruby, Capybara and Calabash, for the environments both Windows and Unix like machines.
irb#1(main):002:0> db = Sequel.odbc("MyODBCDatabaseName", :user => "my_user_name", :password => "my_password") Sequel::AdapterNotFound: LoadError: cannot load such file -- odbc from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:120:in `require' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:120:in `require' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/adapters/odbc.rb:3:in `
' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:68:in `require' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/2.3.0/rubygems/core_ext/kernel_require.rb:68:in `require' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/database/connecting.rb:98:in `load_adapter' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/database/connecting.rb:28:in `adapter_class' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/database/connecting.rb:56:in `connect' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/core.rb:109:in `connect' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/core.rb:428:in `adapter_method' from /Users/mesutgunes/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/sequel-4.42.1/lib/sequel/core.rb:438:in `odbc'
Installation Of SQLCMD
For Window install the executable from Window download center and click to install it. For Unix compatible version run the following command to install npm package. As the intent of the SQLCM for unix is not providing the functionality of the windows version, there missing features and notation differences.
npm install -g sqlcmdjs
Using the tool is simple first you need to connect the server by connection string and running your queries on the console.
sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword
For Unix:For now connection parameters are not in capital letter:
sqlcmd -s server1\SQLExpress -u SqlUserAccount -p SqlPassword
To Run Queries:
sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword -d data_base_name
To Run .sql Files:
cat /path/to/sql_file.sql | sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword -d data_base_name
To Run Queries with a Paramater:
cat /path/to/sql_file.sql | sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword -d data_base_name -m paramater='sql-parameter'
If you want to use same sql query for both windows and unix you need to use this updated version of SQLCMD until it is merged to master. I have sent pull request to this project but it is not merged yet.
git clone https://github.com/gunesmes/sqlcmd.git-b hotfix/paremeter_update /path/to/sqlcmd
cat /path/to/sql_file.sql |/path/to/sqlcmd/sqlcmd.js -S server1\SQLExpress -U SqlUserAccount -P SqlPassword -d data_base_name -m paramater='sql-parameter'
List of paramaters:
Sqlcmd [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-N encrypt connection] [-C trust the server certificate] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p print statistics[colon format]] [-R use client regional setting] [-b On error batch abort] [-v var = "value"...] [-X disable commands[and exit with warning]] [-? show syntax summary]
Using SQLCMD with Ruby
Finally, the aim of this post is to show the using of SQLCMD for Capybara and Calabash script for both Unix and Windows test environments. However you can use this tool for needs straight-forward. See the following example: