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

I figured out this one glitch. Pilot error on my part. Some values of
this array element were floats while others were strings. I had to
convert the string values to floats for the comparison sort block to
factor everything correctly. That is taken care of. But I still never
found a way around invoking the Array's assoc method on an SQL result
set. But piping the set into a new array isn't the end of the world. It
works and my script does now as well. Thanks for the feedback though!

gregarican wrote:
> I also get this error when trying to sort other array of arrays that
> represent combined SQL result sets:
>
> comparison of Array with Array failed (ArgumentError)
>
> When I create test arrays that are similar I don't run into this error.
> My result sets combine results from calculations and other fields that
> can't be combined into T-SQL statements. That's why I can't just create
> a magic result set using straight SQL. I need to manipulate the result
> set using Ruby. But there seems to be some problems I am running into.
> I will try to work with smaller result sets to see if I can further
> isolate why the sort! method is failing...
>
> But when I create
> gregarican wrote:
> > 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