Forum: Ruby Creating a class for returing DB results

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.
1742c7dbb6d313e951cd78acf817febe?d=identicon&s=25 Darin Ginther (dginther)
on 2008-12-29 21:06
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..
753dcb78b3a3651127665da4bed3c782?d=identicon&s=25 Brian Candler (candlerb)
on 2008-12-29 21:46
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
> 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 }
753dcb78b3a3651127665da4bed3c782?d=identicon&s=25 Brian Candler (candlerb)
on 2008-12-29 22:03
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

>> mydbcount = Countdb.new(val1, val2, val3, val4, val5)
>> p mydbcount.count_rows
1742c7dbb6d313e951cd78acf817febe?d=identicon&s=25 Darin Ginther (dginther)
on 2008-12-29 22:19
Thanks, guys.. this is a *HUGE* help...
F6a834b9e424a1e5b199db450462b6cd?d=identicon&s=25 Ron Fox (Guest)
on 2008-12-30 13:05
(Received via mailing list)
And after your done.. see rails.  It's re-invented wheels are somewhat
rounder.
1742c7dbb6d313e951cd78acf817febe?d=identicon&s=25 Darin Ginther (dginther)
on 2008-12-30 16:55
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
753dcb78b3a3651127665da4bed3c782?d=identicon&s=25 Brian Candler (candlerb)
on 2008-12-30 19:52
Darin Ginther wrote:
> 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.
1742c7dbb6d313e951cd78acf817febe?d=identicon&s=25 Darin Ginther (dginther)
on 2008-12-31 16:43
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
This topic is locked and can not be replied to.