From: gregarican <greg.kujawa@...>
Date: 2006-11-15T22:35:05+09:00
Subject: Re: Assoc method on large array

When I try resultSet.class in irb I get back an Array. And the
resultSet object is indeed an array of arrays. It starts out like
[[100,"Joe Schmoe", "1313 Anywhere St.", "Anywhere", "OH",
"43000-0000", "614-555-1212"], ... and so on. The only way I can use
the assoc method on this record set is by copying it into a new array,
iterating over the contents row by row.

Another oddity I found. I have another record set that I want to sort
by the seventh element. This record set is similarly an array of
arrays. It is similar in structure to the resultSet object, except
there is an extra element tacked on at the end. Purchase amounts, which
is the element I want to sort by. This result set starts out like
[[100,"Joe Schmoe", "1313 Anywhere St.", "Anywhere", "OH",
"43000-0000", "614-555-1212", 1200.50], ... and so on. When I tried a
test in irb by creating an array of arrays with just a few rows I
passed in the following sort! method:

purchaseHistory.sort! { |a,b| b[7] <=> a[7] }

This worked, in that my array of arrays was then sorted in descending
order of purchase amounts, the element residing in slot 7 in each
array. But when I tried this same operation on my production result set
I got back an error stating 'undefined method `>' for false:FalseClass
(NoMethodError).' There must be a row in the result set that's throwing
the comparison off. Have to dig deeper into it. I am thinking of a way
to capture just where the sort! block is bombing out...

Robert Klemme wrote:
> On 14.11.2006 21:27, gregarican wrote:
> > Here it is:
> >
> > ---------------------------------------------------
> > require 'dbi'
> >
> > dsn=DBI.connect('DBI:ADO:Provider=SQLOLEDB;Connect Timeout=5;Data
> > Source=my_server;Initial Catalog=my_db;Persist Security
> > Info=True;Trusted_Connection=Yes;')
> > queryString = "SELECT C.CustNo, C.FullName, C.Street, C.City, C.State,
> > C.zip, C.Phone "
> > queryString << "from TblCustInfo C, TblCustMailOuts M "
> > queryString << "where C.CustNo = M.CustNo "
> > sqlStmt=dsn.prepare(queryString)
> > sqlStmt.execute
> > resultSet=sqlStmt.fetch_all
> > match=resultSet.assoc(integerValue)
> > ---------------------------------------------------
> >
> > My resultSet is an array and I can inspect its contents. The first
> > element of each row of the array is an integer. When I try to do the
> > resultSet.assoc() method to pull one of these out I always get nil. No
> > matter what I do.
> >
> > Here's what I did that worked, however:
> >
> > ---------------------------------------------------
> > require 'dbi'
> >
> > dsn=DBI.connect('DBI:ADO:Provider=SQLOLEDB;Connect Timeout=5;Data
> > Source=my_server;Initial Catalog=my_db;Persist Security
> > Info=True;Trusted_Connection=Yes;')
> > queryString = "SELECT C.CustNo, C.FullName, C.Street, C.City, C.State,
> > C.zip, C.Phone "
> > queryString << "from TblCustInfo C, TblCustMailOuts M "
> > queryString << "where C.CustNo = M.CustNo "
> > sqlStmt=dsn.prepare(queryString)
> > sqlStmt.execute
> > resultSet=sqlStmt.fetch_all
> > resultCustSet=[]
> > resultSet.each { | row | resultCustSet << [row[0], row[1], row[2],
> > row[3], row[4], row[5], row[6]] }
> > match=resultCustSet.assoc(integerValue)
> > ---------------------------------------------------
> >
> > The only difference that I see is that I build a new array out of the
> > elements of the SQL recordset array. The contents are identical...
>
> Actually I believe this to be wrong: please read the docs of #assoc again:
> http://www.ruby-doc.org/core/classes/Array.html#M002259
>
> You need an Array of Arrays.  But resultSet is the return value of
> #fetch_all which might look line an Array of Arrays but actually
> is likely something different.  (An easy test would be to print out the
> class of resultSet and of the first member of resultSet.)
>
> Two additional remarks: If you want to do these kinds of lookups and you
> are copying the result set anyway then the proper data structure would
> be a Hash which is far more efficient for these kinds of lookups.  But:
>
> If you just need an individual record it is much more efficient to let
> the DB select exactly that value.
> 
> Kind regards
> 
> 	robert