Using dbi in a loop to execute a stored procedure...

[grooveska]

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!

 

[ChrisH]

....numberofcomps += 1

end

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

Any advice or ideas would be greatly appreciated.

that db.disconnect will be executed the first time through the while
loop, move it to after the 'end' of the loop