Skip to main content

SQLCMD: Alternative Way of Working with Microsoft SQL Database for Ruby

Image
By

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 SQLCMD 

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:

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[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:
Labels:Software testing

Popular posts for software testing and automation

Selenium Error "Element is not currently interactable and may not be manipulated"

By
Selenium webdriver can drive different browsers like as Firefox, Chrome or Internet Explorer. These browsers actually cover the majority of internet users, so testing these browsers possibly covers the 90% of the internet users. However, there is no guaranty that the same automation scripts can work without a failure on these three browsers. For this reason, automation code should be error-prone for the browsers you want to cover. The following error is caught when the test script run for Chrome and Internet Explorer, but surprisingly there is no error for the Firefox. Selenium gives an error like below: Traceback (most recent call last):   File "D:\workspace\sample_project\sample_run.py", line 10, in <module>     m.login()   File "D:\workspace\ sample_project \test_case_imps.py", line 335, in login     driver.find_element_by_id("id_username").clear()   File "C:\Python27\lib\site-packages\selenium-2.35.0-py2.7.egg\seleni...
Read more

Change Default Timeout and Wait Time of Capybara

By
One of the biggest challenge for automation is handling timeout problem. Most of the time, timeout is 60 seconds but it may sometimes not enough if you have badly designed asynchronous calls or the third party ajax calls. This makes handling timeout more complex. set large enough to tolerate network related problems. For Selenium based automation frameworks, like Capybara, default Webdriver timeout is set to Net::ReadTimeout (Net::ReadTimeout) Changing ReadTimeout If you have timeout problem for Capybara, it gives an error like above. This means that the page is not fully loaded in given timeout period. Even you can see that page is loaded correctly but webdriver wait until the Ajax calls finish. class BufferedIO #:nodoc: internal use only def initialize (io) @io = io @read_timeout = 60 @continue_timeout = nil @debug_output = nil @rbuf = '' end . . . . . def rbuf_fill beg...
Read more

Create an Alias for Interactive Console Work: Selenium and Capybara

By
If you are working on shell most of the time Aliases are very helpfull and time saving. For testing purposes you can use Alias for getting ready your test suites. In this post, I want to explain both running Selenium and Capybara on console and creating aliases for each.  This post is for Windows machines, if you are using Unix-like see   this post . Creating Scripts for Selenium and Capybara First of all, it is assumed that you have installed Selenium and Capybara correctly and they work on your machines. If you haven't installed, you can see my previous posts. I am using the Selenium with Python and the Capybara with Ruby. You can use several different language for Selenium but Capybara works only with Ruby.  Create scripts in a directory called scripts (in your home folder, like as  ~/scripts ) for your automation tool as following, save them as capybara.rb, sel.py :  Creating Aliases Depends on your favourite shell, you need to add the al...
Read more

Page-Object Pattern for Selenium Test Automation with Python

By
Page-object model is a pattern that you can apply it to develop efficient automation framework. With the page-model, it is possible to minimize maintenance cost. Basically page-object means that your every page is inherited from a base class which includes basic functionalities for every page. If you have some new functionalities that every page should have, you can simple add it to the base class. Base class is like the following: In this part we are creating pages which are inherited from base page. Every page has its own functionalities written as python functions. Some functions return to a new page, it means that these functions leave the current page and produce a new page. You should write as much as functions you need in the assertion part because this is the only part you can use the webdriver functions to interact with web pages . This part can be evaluate as providing data to assertion part.   The last part is related to...
Read more

Performance Testing on CI: Locust is running on Jenkins

By
For a successful Continuous Integration pipeline, there should be jobs for testing the performance of the application. It is necessary if the application is still performing well. Generally performance testing is thought as kinds of activities performed one step before going to live. In general approach it is true but don't forget to test your application's performance as soon as there is an testable software, such as an api end point, functions, and etc. For CI it is a good approach to testing performance after functional testing and just before the deployment of next stage. In this post, I want to share some info about Jenkins and Locust. In my previous post you can find some information about Locust and Jenkins. Jenkins operates the CI environment and Locust is a tool for performance testing. To run the Locust on Jenkins you need command line arguments which control the number of clients ,   hatch rate,  running locust without web interface and there s...
Read more