Creating a class for returing DB results

[Darin Ginther]

I'm a ruby noob. I've managed to accomplish what I want to do in one
long procedural chain of code. I'm attempting to modularize my code to
prevent duplication, but I'm having trouble with syntax.

What I'm doing:
Manipulating a web page and then doing simple DB validation of the
results. I do all of this in a ruby script.

What I want to do:
Manipulate web page, then call a validation class, passing that class:
DBName
UserName
UserPassword
return: Single value (numeric).


I'm having trouble with two things:
1) Generic syntax
2) Return value - because Ruby auto returns the value of the last
expression, I have to explicitly return what I'm after, which seems to
drop me out out the class, prior to terminating the DB connection.


What I essentially have currently is:
dbh= DBI.connect(mydb,dbuser,dbpw)
sth = dbh.execute("SELECT count(*) FROM #{table} where code =
#{mycode}")
while row = sth.fetch do
finalcount=row[0]
puts finalcount
end
sth.finish
# disconnect from server
dbh.disconnect if dbh



So, roughly what I'm thinking is:
Class Countdb
attr_accessor :mydb, :dbuser, :dbpw, :table, :mycode

require 'DBI'

def initialize(mydb, dbuser, dbpw, table, mycode)
@mydb = mydb
@dbuser = dbuser
@dbpw = dbpw
@table = table
@mycode = mycode
end

dbh= DBI.connect(mydb,dbuser,dbpw)
sth = dbh.execute("SELECT count(*) FROM #{table} where code =
#{mycode}")
while row = sth.fetch do
finalcount=row[0] #How do I return this value?
end
sth.finish
# disconnect from server
dbh.disconnect if dbh
end



I think we can then instantiate this object via:
mydbcount = Countdb.new(val1, val2, val3, val4, val5)



I'm sure I'm making a mess here... straighten me out guys..

 

[Brian Candler]

You're close. Wrap the actual code which does the action in another
instance method, such as 'process_rows'. Then to 'return' multiple
values, the Ruby way is to yield them to a block which the caller
passes.

Class Countdb
attr_accessor :mydb, :dbuser, :dbpw, :table, :mycode

require 'DBI'

def initialize(mydb, dbuser, dbpw, table, mycode)
@mydb = mydb
@dbuser = dbuser
@dbpw = dbpw
@table = table
@mycode = mycode
end

def process_rows

dbh= DBI.connect(mydb,dbuser,dbpw)

change these to @mydb, @dbuser, @dbpw (ditto table to @table and mycode
to @mycode below)

sth = dbh.execute("SELECT count(*) FROM #{table} where code =
#{mycode}")
while row = sth.fetch do
finalcount=row[0] #How do I return this value?

yield row[0]

end
sth.finish
# disconnect from server
dbh.disconnect if dbh
end

I think we can then instantiate this object via:
mydbcount = Countdb.new(val1, val2, val3, val4, val5)

That creates an instance of the object and sets the instance variables
such as @mydb etc.

After this the caller would then do something like this:

mydbcount.process_rows { |data| p data }

 

[Brian Candler]

Of course, a real SELECT count(*) is likely only to return one value.

So you don't need a while loop, and you can just return the count.
Something like this (untested)

def count_rows
dbh= DBI.connect(@mydb,@dbuser,@dbpw)
sth = dbh.execute("SELECT count(*) FROM #{@table} where code =
#{@mycode}")
row = sth.fetch
finalcount = row[0] if row # just in case row is nil
sth.finish
dbh.disconnect
return finalcount
end

 

[Darin Ginther]

Thanks, guys.. this is a *HUGE* help...

 

[Ron Fox]

And after your done.. see rails. It's re-invented wheels are somewhat
rounder.

 

[Darin Ginther]

OK, A little more help...
I'm getting an "uninitialized constant Dbutil" error, which seems to
have something to do with the way I'm defining the class or naming the
file (currently dbutil.rb).


Also - what's the best way to include this class in an outside file?
Say I'm exercising it from webtest.rb?.. I'm somewhat familiar with the
comcept of classpath in JAVA.

#-------------------------------------------------------------#
# DB utility class.
#
# 12/30/08.
# Purpose: Make connecting to the db and validating
# data a little easier.
# Ref: http://www.ruby-forum.com/topic/174431#764368
#-------------------------------------------------------------#
# the Watir controller

Class Dbutil
attr_accessor :mydb, :dbuser, :dbpw, :table, :lead_type_code

require 'DBI'

def initialize(mydb, dbuser, dbpw, table, lead_type_code)
@mydb = mydb
@dbuser = dbuser
@dbpw = dbpw
@table = table
@lead_type_code = lead_type_code
end

def counttable
dbh= DBI.connect(@mydb, @dbuser, @dbpw, @table, @lead_type_code)
sth = dbh.execute("SELECT count(*) FROM @table where code =
@lead_type_code")
row = sth.fetch
yield row[0]
sth.finish
dbh.disconnect
end

 

[Brian Candler]

OK, A little more help...
I'm getting an "uninitialized constant Dbutil" error

You need to use the keyword 'class' not 'Class'

You also need #{@table}, or I think #@table is sufficient, inside your
quoted string. Otherwise the literal characters "@table" will be used.

Also - what's the best way to include this class in an outside file?
Say I'm exercising it from webtest.rb?

require 'dbutil'

See $LOAD_PATH if this file is not in the current directory. A common
idiom is

$LOAD_PATH.unshift File.dirname(__FILE__)+"/lib"

if there is a lib/ subdirectory adjacent to the file which is being run.

 

[Darin Ginther]

Thanks again, guys... Final solution below for reference:

class Dbutil
attr_accessor :mydb, :dbuser, :dbpw, :table, :lead_type_code

require 'DBI'

def initialize(mydb, dbuser, dbpw, table, lead_type_code)
@mydb = mydb
@dbuser = dbuser
@dbpw = dbpw
@table = table
@lead_type_code = lead_type_code
end

def counttable
dbh= DBI.connect(@mydb, @dbuser, @dbpw, @table, @lead_type_code)
sth = dbh.execute("SELECT count(*) FROM #@table where code =
#@lead_type_code")
row = sth.fetch
yield row[0]
sth.finish
dbh.disconnect
end
end