multiple connections with DBI and Mysql

[Dave Lilley]

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 <default> can change to 20

any help be grateful.

dave

 

[Hassan Schroeder]

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/>

HTH,

 

[Dave Lilley]

You might take a look at Sequel: <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.connectadapter=>'mysql', :host=>'localhost',
:database=>'test', :user=>'dave', assword=>'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:ataset: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

 

[Hassan Schroeder]

class Dopen
=A0def initialise

It's "initialize"

=A0 =A0@db =3D Sequel.connectadapter=3D>'mysql', :host=3D>'localhost',
:database=3D>'test', :user=3D>'dave', assword=3D>'test')
=A0end

=A0def allcust
=A0 =A0row =3D @db["SELECT * FROM cust_data"]
=A0 =A0puts " 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,
--=20
Hassan Schroeder ------------------------ (e-mail address removed)
twitter: @hassan

 

[Dave Lilley]

class Dopen
�def initialise

It's "initialize"

� �@db = Sequel.connectadapter=>'mysql', :host=>'localhost',
:database=>'test', :user=>'dave', assword=>'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.