From: kkroculick@... (kk) Date: 2002-07-10T10:48:26+09:00 Subject: Re: decent example of how to use DBI/ODBC 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') #, @attr={}>, @trace_output=#, @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:... > Michael Neumann 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 > http://www.ch-werner.de/rubyodbc/ 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