# Using dbi in a loop to execute a stored procedure

**URL:** <https://www.ruby-forum.com/t/using-dbi-in-a-loop-to-execute-a-stored-procedure/91734>\
**Category:** Ruby\
**Created:** [April 11, 2007, 11:15pm UTC](https://www.ruby-forum.com/t/using-dbi-in-a-loop-to-execute-a-stored-procedure/91734 "2007-04-11T23:15:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![grooveska](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/34f0e0/32.png) [@grooveska](https://www.ruby-forum.com/u/grooveska)\
**Post date:** [April 11, 2007, 11:15pm UTC](https://www.ruby-forum.com/t/using-dbi-in-a-loop-to-execute-a-stored-procedure/91734/1 "2007-04-11T23:15:50Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![grooveska](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/34f0e0/32.png) [@grooveska](https://www.ruby-forum.com/u/grooveska)\
**Post date:** [April 12, 2007, 2:51am UTC](https://www.ruby-forum.com/t/using-dbi-in-a-loop-to-execute-a-stored-procedure/91734/2 "2007-04-12T02:51:05Z")

</div>

…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