Decent example of how to use DBI/ODBC

ruby-talk
1

Hello,

Can someone give me a simple example of how to parse through a
recordset using ruby’s DBI module? I have looked through the
documentation but can’t find what I want. I can create the handle,
return the table names etc but how I parse though records? I know how
to do this with python (easily using mxODBC, ado etc)…must be
missing something? I am on 1.6.7 on Win2k…

Thanks in advance…

Keith

2

You should have a look at the DBI specification at http://ruby-dbi.sf.net/DBI_SPEC.html.

Here a simple example to output column “t” of all rows in table “test”.

DBI.connect(“dbi:xxx:xxx”, “user”, “passwd”) do |dbh|
dbh.select_all(“SELECT t FROM test”) {|row| p row[‘t’] }
end

Regards,

Michael

···

On Tue, Jul 09, 2002 at 11:25:24AM +0900, kk wrote:

Hello,

Can someone give me a simple example of how to parse through a
recordset using ruby’s DBI module? I have looked through the
documentation but can’t find what I want. I can create the handle,
return the table names etc but how I parse though records? I know how
to do this with python (easily using mxODBC, ado etc)…must be
missing something? I am on 1.6.7 on Win2k…

3

Michael Neumann uu9r@rz.uni-karlsruhe.de wrote in message news:20020709083716.GA6916@rz.uni-karlsruhe.de…

Hello,

Can someone give me a simple example of how to parse through a
recordset using ruby’s DBI module? I have looked through the
documentation but can’t find what I want. I can create the handle,
return the table names etc but how I parse though records? I know how
to do this with python (easily using mxODBC, ado etc)…must be
missing something? I am on 1.6.7 on Win2k…

You should have a look at the DBI specification at http://ruby-dbi.sf.net/DBI_SPEC.html.

Here a simple example to output column “t” of all rows in table “test”.

DBI.connect(“dbi:xxx:xxx”, “user”, “passwd”) do |dbh|
dbh.select_all(“SELECT t FROM test”) {|row| p row[‘t’] }
end

Regards,

Michael

Keith:

I got a lot of useful information from the Syngress book.

As example you asking for check the attached script.

Class Pipe is generalization of example from the book mentioned that
allows to copy data between identical databases / tables. (It can be
production and test databases, for example.)

The last function in the class is actually realization for some
specific group of tables that are related. I am trying to pipe all
the data from the three tables using only the rows that are specified
by the value of the field case_id. (Multiple case_id can be specified)

I am using ODBC driver in NT environment to work with ORACLE database
so you can see some flavor of ORACLE in this example. (Unfortunately,
I never succeeded to find oracle.so file that is missing in the
version 1.6.5 I am running, but the odbc.so file that I downloaded
from
ODBC Binding for Ruby is running quite well)

···

On Tue, Jul 09, 2002 at 11:25:24AM +0900, kk wrote:

=================

class Pipe

def initialize(src_url, dst_url)
   @src_url = src_url
   @dst_url = dst_url
end

############################################
def pipe_tbl(src, dst, tbl, src_criteria=nil, src_sql=nil)

   if src_sql.nil?		# no sql given. create it as default
	
	src_sql = %[
	SELECT * 
	FROM #{tbl}]

	src_sql += %[
	WHERE #{src_criteria}
	] unless src_criteria.nil?
   end
	
   # puts "#{src_sql0}"
   i = 0
   stmt = nil
   src.select_all(src_sql) do |row|
        if stmt.nil?
	   fields = row.field_names.join(",\n")
	   qs = (['?'] * row.size).join(',')
	   stmt = "INSERT INTO #{tbl} (#{fields}) VALUES (#{qs})"
	end
	# puts stmt
	disable_tbl_triggers(dst, tbl)
	ret = dst.do(stmt, *row.to_a)
	enable_tbl_triggers(dst, tbl)
	dst.commit
	i += 1
   end
   puts "=#{i} rows are copied for table #{tbl}"
end

############################################
def disable_tbl_triggers(dst, tbl)
sql_st = “ALTER TABLE #{tbl} DISABLE ALL TRIGGERS”
dst.do(sql_st)
end
############################################

def enable_tbl_triggers(dst, tbl)
	sql_st = "ALTER TABLE #{tbl} ENABLE ALL TRIGGERS" 
	dst.do(sql_st)
end

############################################

def pipe_tbl_list(tbl_list)
   DBI.connect(*@src_url.split('#')) do |src|
      DBI.connect(*@dst_url.split('#')) do |dst|
 	 tbl_list.each do |cmp|
	     pipe_tbl(src, dst, *cmp.split('#'))
	 end
      end
   end
end

############################################
def delete_tbl(dst, tbl, src_criteria=nil, src_sql=nil)
if src_sql.nil?
src_sql = “DELETE FROM #{tbl} \n”
src_sql += “WHERE #{src_criteria}” unless src_criteria.nil?
end
# puts src_sql
ret = dst.do(src_sql)
puts “=#{ret} rows deleted from #{tbl}”
end
############################################
def delete_tbl_list(tbl_list)

deletes records from tbl_list in dst database using the same format

as in pipe_tbl_list()
DBI.connect(*@dst_url.split(‘#’)) do |dst|
tbl_list.reverse.each do |cmp|
delete_tbl(dst, *cmp.split(‘#’))
end
end
end
############################################
def pipe_brs(cases)
# For every table specify: table name, criteria for select, and
(optionally) entire sql statement
# (if you do not like the automatically generated) – all seprated by
‘#’ sign
# Input parameter CASES is array of possible case_id_value

	tbls = ['legal#case_id=case_id_value',
				 'group#case_id=case_id_value',
				 'subscriber#case_id=case_id_value']
	
	cases.each do |t|
		tbl_list = tbls.collect {|tbl| 'brs.' + tbl.sub(/case_id_value/,

t.to_s)}
# puts tbl_list
delete_tbl_list(tbl_list) # in case the data already exist in dst
pipe_tbl_list(tbl_list)
end
end
############################################
############################################
############################################

src_url = “DBI:ODBC:prod_odbc#login_name#password”
dst_url = “DBI:ODBC:test_odbc#login_name#password”

pipe = Pipe.new(src_url, dst_url)

pipe.pipe_brs([2522,2523,2524,2525])

=====================

Regards,

Ed

4

All,

Thanks for your help. The oracle example really helps too. I’ll have
to check out the syngress book.

Keith

edk_ruby@hotmail.com (Edward Kremer) wrote in message news:bfa52e4.0207091058.20412fca@posting.google.com…

···

Michael Neumann uu9r@rz.uni-karlsruhe.de wrote in message news:20020709083716.GA6916@rz.uni-karlsruhe.de…

On Tue, Jul 09, 2002 at 11:25:24AM +0900, kk wrote:

Hello,

Can someone give me a simple example of how to parse through a
recordset using ruby’s DBI module? I have looked through the
documentation but can’t find what I want. I can create the handle,
return the table names etc but how I parse though records? I know how
to do this with python (easily using mxODBC, ado etc)…must be
missing something? I am on 1.6.7 on Win2k…

You should have a look at the DBI specification at http://ruby-dbi.sf.net/DBI_SPEC.html.

Here a simple example to output column “t” of all rows in table “test”.

DBI.connect(“dbi:xxx:xxx”, “user”, “passwd”) do |dbh|
dbh.select_all(“SELECT t FROM test”) {|row| p row[‘t’] }
end

Regards,

Michael

Keith:

I got a lot of useful information from the Syngress book.

As example you asking for check the attached script.

Class Pipe is generalization of example from the book mentioned that
allows to copy data between identical databases / tables. (It can be
production and test databases, for example.)

The last function in the class is actually realization for some
specific group of tables that are related. I am trying to pipe all
the data from the three tables using only the rows that are specified
by the value of the field case_id. (Multiple case_id can be specified)

I am using ODBC driver in NT environment to work with ORACLE database
so you can see some flavor of ORACLE in this example. (Unfortunately,
I never succeeded to find oracle.so file that is missing in the
version 1.6.5 I am running, but the odbc.so file that I downloaded
from
ODBC Binding for Ruby is running quite well)

=================

class Pipe

def initialize(src_url, dst_url)
@src_url = src_url
@dst_url = dst_url
end

############################################
def pipe_tbl(src, dst, tbl, src_criteria=nil, src_sql=nil)

 if src_sql.nil?		# no sql given. create it as default
  
  src_sql = %[
  SELECT * 
  FROM #{tbl}]

  src_sql += %[
  WHERE #{src_criteria}
  ] unless src_criteria.nil?
 end
  
 # puts "#{src_sql0}"
 i = 0
 stmt = nil
 src.select_all(src_sql) do |row|
      if stmt.nil?
     fields = row.field_names.join(",\n")
     qs = (['?'] * row.size).join(',')
     stmt = "INSERT INTO #{tbl} (#{fields}) VALUES (#{qs})"
  end
  # puts stmt
  disable_tbl_triggers(dst, tbl)
  ret = dst.do(stmt, *row.to_a)
  enable_tbl_triggers(dst, tbl)
  dst.commit
  i += 1
 end
 puts "=#{i} rows are copied for table #{tbl}"

end
############################################
def disable_tbl_triggers(dst, tbl)
sql_st = “ALTER TABLE #{tbl} DISABLE ALL TRIGGERS”
dst.do(sql_st)
end
############################################

def enable_tbl_triggers(dst, tbl)
sql_st = “ALTER TABLE #{tbl} ENABLE ALL TRIGGERS”
dst.do(sql_st)
end
############################################

def pipe_tbl_list(tbl_list)
DBI.connect(@src_url.split(‘#’)) do |src|
DBI.connect(@dst_url.split(‘#’)) do |dst|
tbl_list.each do |cmp|
pipe_tbl(src, dst, *cmp.split(‘#’))
end
end
end
end
############################################
def delete_tbl(dst, tbl, src_criteria=nil, src_sql=nil)
if src_sql.nil?
src_sql = “DELETE FROM #{tbl} \n”
src_sql += “WHERE #{src_criteria}” unless src_criteria.nil?
end
# puts src_sql
ret = dst.do(src_sql)
puts “=#{ret} rows deleted from #{tbl}”
end
############################################
def delete_tbl_list(tbl_list)

deletes records from tbl_list in dst database using the same format

as in pipe_tbl_list()
DBI.connect(*@dst_url.split(‘#’)) do |dst|
tbl_list.reverse.each do |cmp|
delete_tbl(dst, *cmp.split(‘#’))
end
end
end
############################################
def pipe_brs(cases)

For every table specify: table name, criteria for select, and

(optionally) entire sql statement

(if you do not like the automatically generated) – all seprated by

‘#’ sign

Input parameter CASES is array of possible case_id_value

  tbls = ['legal#case_id=case_id_value',
  			 'group#case_id=case_id_value',
  			 'subscriber#case_id=case_id_value']
  
  cases.each do |t|
  	tbl_list = tbls.collect {|tbl| 'brs.' + tbl.sub(/case_id_value/,

t.to_s)}
# puts tbl_list
delete_tbl_list(tbl_list) # in case the data already exist in dst
pipe_tbl_list(tbl_list)
end
end
############################################
############################################
############################################

src_url = “DBI:ODBC:prod_odbc#login_name#password”
dst_url = “DBI:ODBC:test_odbc#login_name#password”

pipe = Pipe.new(src_url, dst_url)

pipe.pipe_brs([2522,2523,2524,2525])

=====================

Regards,

Ed

5

Can anyone tell me why I get this error when I access a database
(MSSQL Server 2K)using DBI:ODBC? :

irb(main):008:0> URL = ‘DBI:ODBC:slxwork’, ‘sysdba’, ‘masterkey’
[“DBI:ODBC:slxwork”, “sysdba”, “masterkey”]
irb(main):009:0> URL = ‘DBI:ODBC:slxwork’
“DBI:ODBC:slxwork”
irb(main):010:0> dbh = DBI.connect(URL, ‘sysdba’, ‘masterkey’)
#<DBI::DatabaseHandle:0xd063e0
@handle=#<DBI::DBD::ODBC::Database:0xd06338
@handle=#ODBC::Database:0xd063b0, @attr={}>,
@trace_output=#<RW_CONSOLE:0x8eacc0>, @trace_mode=2>
irb(main):011:0> rows = dbh.select_all(“SELECT * FROM ACCOUNT”)
TypeError: no implicit conversion from string
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in []' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in each’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:665:in initialize' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in new’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in execute' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:584:in select_all’
from (irb):11

It looks like there is a type conversion problem. Do I have to
convert the returned object type to a string object? If so, can any
one give me some insight? Thanks again for your help…

Keith

edk_ruby@hotmail.com (Edward Kremer) wrote in message news:bfa52e4.0207091058.20412fca@posting.google.com…

···

Michael Neumann uu9r@rz.uni-karlsruhe.de wrote in message news:20020709083716.GA6916@rz.uni-karlsruhe.de…

On Tue, Jul 09, 2002 at 11:25:24AM +0900, kk wrote:

Hello,

Can someone give me a simple example of how to parse through a
recordset using ruby’s DBI module? I have looked through the
documentation but can’t find what I want. I can create the handle,
return the table names etc but how I parse though records? I know how
to do this with python (easily using mxODBC, ado etc)…must be
missing something? I am on 1.6.7 on Win2k…

You should have a look at the DBI specification at http://ruby-dbi.sf.net/DBI_SPEC.html.

Here a simple example to output column “t” of all rows in table “test”.

DBI.connect(“dbi:xxx:xxx”, “user”, “passwd”) do |dbh|
dbh.select_all(“SELECT t FROM test”) {|row| p row[‘t’] }
end

Regards,

Michael

Keith:

I got a lot of useful information from the Syngress book.

As example you asking for check the attached script.

Class Pipe is generalization of example from the book mentioned that
allows to copy data between identical databases / tables. (It can be
production and test databases, for example.)

The last function in the class is actually realization for some
specific group of tables that are related. I am trying to pipe all
the data from the three tables using only the rows that are specified
by the value of the field case_id. (Multiple case_id can be specified)

I am using ODBC driver in NT environment to work with ORACLE database
so you can see some flavor of ORACLE in this example. (Unfortunately,
I never succeeded to find oracle.so file that is missing in the
version 1.6.5 I am running, but the odbc.so file that I downloaded
from
ODBC Binding for Ruby is running quite well)

=================

class Pipe

def initialize(src_url, dst_url)
@src_url = src_url
@dst_url = dst_url
end

############################################
def pipe_tbl(src, dst, tbl, src_criteria=nil, src_sql=nil)

 if src_sql.nil?		# no sql given. create it as default
  
  src_sql = %[
  SELECT * 
  FROM #{tbl}]

  src_sql += %[
  WHERE #{src_criteria}
  ] unless src_criteria.nil?
 end
  
 # puts "#{src_sql0}"
 i = 0
 stmt = nil
 src.select_all(src_sql) do |row|
      if stmt.nil?
     fields = row.field_names.join(",\n")
     qs = (['?'] * row.size).join(',')
     stmt = "INSERT INTO #{tbl} (#{fields}) VALUES (#{qs})"
  end
  # puts stmt
  disable_tbl_triggers(dst, tbl)
  ret = dst.do(stmt, *row.to_a)
  enable_tbl_triggers(dst, tbl)
  dst.commit
  i += 1
 end
 puts "=#{i} rows are copied for table #{tbl}"

end
############################################
def disable_tbl_triggers(dst, tbl)
sql_st = “ALTER TABLE #{tbl} DISABLE ALL TRIGGERS”
dst.do(sql_st)
end
############################################

def enable_tbl_triggers(dst, tbl)
sql_st = “ALTER TABLE #{tbl} ENABLE ALL TRIGGERS”
dst.do(sql_st)
end
############################################

def pipe_tbl_list(tbl_list)
DBI.connect(@src_url.split(‘#’)) do |src|
DBI.connect(@dst_url.split(‘#’)) do |dst|
tbl_list.each do |cmp|
pipe_tbl(src, dst, *cmp.split(‘#’))
end
end
end
end
############################################
def delete_tbl(dst, tbl, src_criteria=nil, src_sql=nil)
if src_sql.nil?
src_sql = “DELETE FROM #{tbl} \n”
src_sql += “WHERE #{src_criteria}” unless src_criteria.nil?
end
# puts src_sql
ret = dst.do(src_sql)
puts “=#{ret} rows deleted from #{tbl}”
end
############################################
def delete_tbl_list(tbl_list)

deletes records from tbl_list in dst database using the same format

as in pipe_tbl_list()
DBI.connect(*@dst_url.split(‘#’)) do |dst|
tbl_list.reverse.each do |cmp|
delete_tbl(dst, *cmp.split(‘#’))
end
end
end
############################################
def pipe_brs(cases)

For every table specify: table name, criteria for select, and

(optionally) entire sql statement

(if you do not like the automatically generated) – all seprated by

‘#’ sign

Input parameter CASES is array of possible case_id_value

  tbls = ['legal#case_id=case_id_value',
  			 'group#case_id=case_id_value',
  			 'subscriber#case_id=case_id_value']
  
  cases.each do |t|
  	tbl_list = tbls.collect {|tbl| 'brs.' + tbl.sub(/case_id_value/,

t.to_s)}
# puts tbl_list
delete_tbl_list(tbl_list) # in case the data already exist in dst
pipe_tbl_list(tbl_list)
end
end
############################################
############################################
############################################

src_url = “DBI:ODBC:prod_odbc#login_name#password”
dst_url = “DBI:ODBC:test_odbc#login_name#password”

pipe = Pipe.new(src_url, dst_url)

pipe.pipe_brs([2522,2523,2524,2525])

=====================

Regards,

Ed

6

Which version of Ruby/DBI do you use?

Regards,

Michael

···

On Wed, Jul 10, 2002 at 10:48:26AM +0900, kk wrote:

Can anyone tell me why I get this error when I access a database
(MSSQL Server 2K)using DBI:ODBC? :

irb(main):008:0> URL = ‘DBI:ODBC:slxwork’, ‘sysdba’, ‘masterkey’
[“DBI:ODBC:slxwork”, “sysdba”, “masterkey”]
irb(main):009:0> URL = ‘DBI:ODBC:slxwork’
“DBI:ODBC:slxwork”
irb(main):010:0> dbh = DBI.connect(URL, ‘sysdba’, ‘masterkey’)
#<DBI::DatabaseHandle:0xd063e0
@handle=#<DBI::DBD::ODBC::Database:0xd06338
@handle=#ODBC::Database:0xd063b0, @attr={}>,
@trace_output=#<RW_CONSOLE:0x8eacc0>, @trace_mode=2>
irb(main):011:0> rows = dbh.select_all(“SELECT * FROM ACCOUNT”)
TypeError: no implicit conversion from string
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in []' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in each’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:665:in initialize' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in new’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in execute' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:584:in select_all’
from (irb):11

7

Michael,

I am using Ruby 1.6.7 on windows 2K. The DBI version is the latest DBI
package from RAA 0.0.16.

Regards,
Keith

Michael Neumann uu9r@rz.uni-karlsruhe.de wrote in message news:20020710074450.GA3519@rz.uni-karlsruhe.de…

···

On Wed, Jul 10, 2002 at 10:48:26AM +0900, kk wrote:

Can anyone tell me why I get this error when I access a database
(MSSQL Server 2K)using DBI:ODBC? :

irb(main):008:0> URL = ‘DBI:ODBC:slxwork’, ‘sysdba’, ‘masterkey’
[“DBI:ODBC:slxwork”, “sysdba”, “masterkey”]
irb(main):009:0> URL = ‘DBI:ODBC:slxwork’
“DBI:ODBC:slxwork”
irb(main):010:0> dbh = DBI.connect(URL, ‘sysdba’, ‘masterkey’)
#<DBI::DatabaseHandle:0xd063e0
@handle=#<DBI::DBD::ODBC::Database:0xd06338
@handle=#ODBC::Database:0xd063b0, @attr={}>,
@trace_output=#<RW_CONSOLE:0x8eacc0>, @trace_mode=2>
irb(main):011:0> rows = dbh.select_all(“SELECT * FROM ACCOUNT”)
TypeError: no implicit conversion from string
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in []' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in each’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in collect' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:714:in column_names’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:665:in initialize' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in new’
from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:553:in execute' from C:/ruby/lib/ruby/site_ruby/1.6/dbi/dbi.rb:584:in select_all’
from (irb):11

Which version of Ruby/DBI do you use?

Regards,

Michael