0

Never tried this before, I am trying to execute a sql query from within a Ruby Cucumber Step definition . Not at all sure what to do after the connection made.

Connection (Works)

Then(/^TC-0001-Bill-Of-Laiding Query onleLisa (Lisa_One) to access existing Bill Of laiding Order$/) do

conn = DBI.connect('DBI:ODBC:GPAutomation','XXXXX','XXXXX')
conn.connected?

Framework Setup

require 'rubygems'
require 'watir-webdriver'
require 'watir'
require 'rspec'
require 'cucumber'
require 'selenium-webdriver'
require 'win32ole'
require 'rufus/scheduler'
require 'yaml'
require 'dbi'


The Query that I want to execute 

select h.bol_id,  * from bol_header h (nolock) 
inner join bol_header_info hi (nolock) on h.bol_id = hi.bol_id
where h.facility_id = '505' and h.Status = 'O' and hi.order_type = 'S'

Any Pro tips would be greatly appreciated …

KWC
  • 71
  • 10
  • You may want to check out sequel - http://sequel.jeremyevans.net/. To run your SQL queries - http://sequel.jeremyevans.net/rdoc/files/README_rdoc.html#label-Arbitrary+SQL+queries. – alannichols Apr 15 '16 at 14:43
  • And to connect to your DB - http://sequel.jeremyevans.net/rdoc/files/doc/opening_databases_rdoc.html#label-Using+the+Sequel.connect+method – alannichols Apr 15 '16 at 14:50

1 Answers1

0

You haven't told us your back-end database. Assuming that it is SQLite3 software. you would 

gem install sqlite3

Ruby code would look like:

require 'rubygems'
require 'sqlite3'

DBNAME = "hello.sqlite"
File.delete(DBNAME) if File.exists?DBNAME

DB = SQLite3::Database.new( DBNAME )
DB.execute("CREATE TABLE testdata(class_name, method_name)")

# Looping through some Ruby data classes
insert_query = "INSERT INTO testdata(class_name, method_name) VALUES(?, ?)"

[Numeric, String, Array, IO, Kernel, SQLite3, NilClass, MatchData].each do |klass|
  puts "Inserting methods for #{klass}"

  # a second loop: iterate through each method
  klass.methods.each do |method_name|
    # Note: method_name is actually a Symbol, so we need to convert it to a String
    # via .to_s
    DB.execute(insert_query, klass.to_s, method_name.to_s)
  end
end

I pulled this from a Dan Nguyen web page. Look there for more.

MikeJRamsey56
  • 2,779
  • 1
  • 20
  • 34
  • Thanks for your respnse ... It's SQL – KWC Apr 15 '16 at 16:30
  • @user3233238 Tell us the database engine. E.g. Oracle, MySql, Microsoft SQL Server, DB2, Microsoft Access, SQLLite, etc.,. My advice is google **ruby example** – MikeJRamsey56 Apr 15 '16 at 16:35
  • @user3233238 search [stackoverflow](http://stackoverflow.com/questions/3144813/proper-way-to-run-raw-sql-queries-with-sequel) – MikeJRamsey56 Apr 15 '16 at 16:37
  • Thanks , This is exactly where I am with this . I am trying to resolve the following - uninitialized constant DB (NameError) as used with DB.run select h.bol_id, * from;bol_header (nolock) inner join bol_header_info hi (nolock);on h.bol_id = hi.bol_id where h.facility_id = '505' and h.Status = 'O' and hi.order_type = 'S' – KWC Apr 15 '16 at 16:40
  • Notice that in my (well, Dan's) example that DB is initialized **DB = SQLite3::Database.new( DBNAME )**. If you initialized yours remember ruby [scoping rules](http://www.techotopia.com/index.php/Ruby_Variable_Scope#What_is_Variable_Scope.3F) for variables. – MikeJRamsey56 Apr 15 '16 at 17:00
  • You initialize *conn* but then never use it. – MikeJRamsey56 Apr 15 '16 at 17:01