# Multiple connections with DBI and Mysql

**URL:** <https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488>\
**Category:** Ruby\
**Created:** [July 31, 2010, 11:04am UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488 "2010-07-31T11:04:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [July 31, 2010, 11:04am UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488/1 "2010-07-31T11:04:57Z")

</div>

Can anyone here advise me the best way to have two classes access mysql  
via a third Mysql DB statements class?

example of what I’m trying to do is below.  
I am sure I need to use a pool of connections but am unsure as to how  
best to do this.

EVERYTHING works except for this issue.

e.g.

require ‘Mysqlclass’

class One

def initialise  
@db = Mysqlclass.new ## this connection is the 1st link!  
end  
end

require ‘Mysqlclass’

class Two  
def initialise  
@db =Mysqlclass.new ## 2nd connection and get a error relating to  
this  
end  
end

Require ‘dbi’  
class Mysqlclass

def db\_connect  
@dbh = DBI.connect( … )  
end

def get\_all\_recs  
@dbh.select\_all(‘select \* from a table’)  
end

#### Other queries etc below here

end

I can get things to work if I change the 2nd script to a method but then  
I hit other issues here so would be much happier have multiple  
connection allowed.  
I’ve trolled but not found anything that shows me how to do this using  
DBI.

I’d sort of expect to have found something like a method or parameter to  
use

eg.

DBI.connect(localhost,…,connections \<number wanted)

or

DBI.connection = 1 can change to 20

any help be grateful.

dave

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [July 31, 2010, 3:00pm UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488/2 "2010-07-31T15:00:02Z")

</div>

On Sat, Jul 31, 2010 at 2:04 AM, Dave L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I am sure I need to use a pool of connections but am unsure as to how  
> best to do this.

You might take a look at Sequel: [http://sequel.rubyforge.org/rdoc/](http://sequel.rubyforge.org/rdoc/)

HTH,

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [August 1, 2010, 2:34am UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488/3 "2010-08-01T02:34:52Z")

</div>

Hassan S. wrote:

> On Sat, Jul 31, 2010 at 2:04 AM, Dave L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > I am sure I need to use a pool of connections but am unsure as to how  
> > best to do this.
> 
> You might take a look at Sequel: [http://sequel.rubyforge.org/rdoc/](http://sequel.rubyforge.org/rdoc/)
> 
> HTH,

Thanks for the reply,

I have done a wee test (code below) but I am getting errors as shown  
below the code.

sequel.rb - my DB connections handler

require ‘sequel’

class Dopen  
def initialise  
@db = Sequel.connect(:adapter=\>‘mysql’, :host=\>‘localhost’,  
:database=\>‘test’, :user=\>‘dave’, :password=\>‘test’)  
end

def allcust  
row = @db[“SELECT \* FROM cust\_data”]  
puts " in all\_cust #{row}"  
end

end

c = Dopen.new  
c.allcust

running ruby sequel.rb give output below

sequel.rb:9:in `allcust': undefined method `[]’ for nil:NilClass  
(NoMethodError)  
from sequel.rb:16

I beleive I am missing something critical in the line row = @db["SELECT

- FROM cust\_data"] for it to give me the error above.

was expecting at best and error like  
#Sequel::MySQL::Dataset:0xb762cdcc

the next script is to call this sequel and access it’s database  
connection and methods.

One.rb calls the above class. Have another one called two.rb which I  
want called from within one to replicate multiple connections via 1  
database connection (but this is step 3 and 4 of by plans).

one.rb script below.

require ‘sequel’

class One  
def initialise  
‘in One initialise!’  
@dbh = Dopen.new

end

def link  
puts ‘in link’  
@dbh.allcust  
end

end

a=One.new  
puts a.link

errors I get for this is…  
in link  
one.rb:14:in `link': undefined method `allcust’ for nil:NilClass  
(NoMethodError)  
from one.rb:21

NOTE the 2 very end lines of sequel.rb are commented out when I call via  
one.rb.

you’re help would be grateful.

I will in the meantime continue to try and find a solution

dave

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [August 1, 2010, 4:21pm UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488/4 "2010-08-01T16:21:48Z")

</div>

On Sat, Jul 31, 2010 at 5:34 PM, Dave L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> class Dopen  
> def initialise

It’s “initialize”

> @db = Sequel.connect(:adapter=\>‘mysql’, :host=\>‘localhost’,  
> :database=\>‘test’, :user=\>‘dave’, :password=\>‘test’)  
> end
> 
> def allcust  
> row = @db[“SELECT \* FROM cust\_data”]  
> puts " in all\_cust #{row}"

`puts` returns nil – this is all you need:

def all\_customers # more ruby-ish naming 🙂  
@db[‘SELECT \* FROM customer\_data’] # likewise  
end

HTH,

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [August 2, 2010, 8:44am UTC](https://www.ruby-forum.com/t/multiple-connections-with-dbi-and-mysql/192488/5 "2010-08-02T08:44:35Z")

</div>

Hassan S. wrote:

> On Sat, Jul 31, 2010 at 5:34 PM, Dave L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > class Dopen  
> > �def initialise
> 
> It’s “initialize”
> 
> > � �@db = Sequel.connect(:adapter=\>‘mysql’, :host=\>‘localhost’,  
> > :database=\>‘test’, :user=\>‘dave’, :password=\>‘test’)  
> > �end
> > 
> > �def allcust  
> > � �row = @db[“SELECT \* FROM cust\_data”]  
> > � �puts " in all\_cust #{row}"
> 
> `puts` returns nil – this is all you need:
> 
> def all\_customers # more ruby-ish naming 🙂  
> @db[‘SELECT \* FROM customer\_data’] # likewise  
> end
> 
> HTH,

Thank you VERY MUCH.

The initialize was the problem!!! - Wood for the trees syndrome.

I had tried all manner of things - rounded brackets, single double  
quotes.  
things like @db[‘:cust\_data’].filter(‘acc\_code’ = \*)  
@db.with\_sql[‘SELECT \* FROM cust\_data’] (again with double quotes)

Many many thanks.

Hope this helps someone else who may have a similar issue.

dave.