Forum: Ruby Using dbi in a loop to execute a stored procedure...

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
grooveska (Guest)
on 2007-04-12 01:15
(Received via mailing list)
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 (Guest)
on 2007-04-12 04:51
(Received via mailing list)
...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
This topic is locked and can not be replied to.