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:
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'
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.

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. 

For Windows:

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:

  [-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[1] print statistics[colon format]]
  [-R use client regional setting] [-b On error batch abort]
  [-v var = "value"...]
  [-X[1] 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:


Popular posts from this blog

Testing WEB Sevices and Automating SOAP Services

Performance Testing on CI: Locust is running on Jenkins

Change Default Timeout and Wait Time of Capybara

Performance Testing on CI: Integration of Locust and Jenkins

Create an Alias for Interactive Console Work: Selenium and Capybara