From: grooveska <ryangs@...>
Date: 2007-04-12T06:15:07+09:00
Subject: Using dbi in a loop to execute a stored procedure...

I can get this to work once, but dbi throws the following error the
2nd time through:

c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:623:in `prepare': Database
connection
was already closed! (DBI::InterfaceError)
        from createcompany.rb:31

Here is my code:

require "dbi"


db = DBI.connect('dbi:ODBC:GP', 'sa', 'UMsys#07')

numberofcomps = 1

puts "Enter the number of companies to be created:  "
STDOUT.flush
maxnumberofcompanies = gets.chomp

puts "Enter the base database name:  "
STDOUT.flush
dbname = gets.chomp

puts "Enter the base company name:  "
STDOUT.flush
companyname= gets.chomp

puts "Enter the dataset name:  "
STDOUT.flush
datasetname = gets.chomp

while numberofcomps <= maxnumberofcompanies.to_i

if numberofcomps < 10

fulldbname = dbname + "0" +numberofcomps.to_s
fullcmpnyname = companyname + "0" + numberofcomps.to_s + "-" +
datasetname
storedproc = "EXEC createCompanyProc @databaseName=" +fulldbname+ ",
@companyName=""\'" + fullcmpnyname + "\'"
stmt = db.prepare(storedproc)
stmt.execute
stmt.finish
#db.disconnect
puts "Company " + fullcmpnyname +" Created"
numberofcomps += 1

else
fulldbname = dbname + numberofcomps.to_s
fullcmpnyname = companyname + numberofcomps.to_s
storedproc = "EXEC createCompanyProc @databaseName="+ fulldbname +",
@companyName=""\'"+ fullcmpnyname +"\'"
stmt = db.prepare(storedproc)
stmt.execute
stmt.finish
#db.disconnect
puts "Company " + fullcmpnyname +" Created"
numberofcomps += 1
end

db.disconnect
puts "==== Company Creation Completed ===="
end

Any advice or ideas would be greatly appreciated.

Thanks!