Assoc method on large array

[gregarican]

I am trying to invoke the assoc method on a large array. It seems to
return nil, although I can verify the elements I am querying against.
If I try the same operation with a smaller recordset the assoc method
seems to work okay. My recordset is a little over 70000 records. Are
there some limits I am exceeding? I am using Ruby 1.8.4 on Windows
using the One-Click Installer...

 

[gregarican]

I narrowed down the result set so that it's only a few records. For
some reason the array I populate with SQL results doesn't return
anything using the assoc array method. I have used irb to create a new
array that looks identical. I can do the my_array == sql_results and it
returns true. And my new array responds to the assoc method and returns
the expected record. But the array that's populated through the SQL
fetch_all method always comes back with nil. Any ideas?

 

[gregarican]

Here it is:

---------------------------------------------------
require 'dbi'

dsn=DBI.connect('DBI:ADOrovider=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:ADOrovider=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)

 

[Robert Klemme]

Here it is:

---------------------------------------------------
require 'dbi'

dsn=DBI.connect('DBI:ADOrovider=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:ADOrovider=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)

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

 

[gregarican]

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

Here it is:

---------------------------------------------------
require 'dbi'

dsn=DBI.connect('DBI:ADOrovider=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:ADOrovider=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)

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

 

[gregarican]

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

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

Here it is:

---------------------------------------------------
require 'dbi'

dsn=DBI.connect('DBI:ADOrovider=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:ADOrovider=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)

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

 

[gregarican]

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!

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

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

On 14.11.2006 21:27, gregarican wrote:
Here it is:

---------------------------------------------------
require 'dbi'

dsn=DBI.connect('DBI:ADOrovider=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:ADOrovider=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

 

[James Edward Gray II]

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.

But you're sure that's not just inspect() tricking you with an Array-
like output? I mean these lines return the expected class?

sql_result.class # => Array
sql_result.first.class # => Array

James Edward Gray II

 

[Robert Klemme]

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!

Why don't you let the DB do the selection? Why don't you use a Hash?
How did you verify that it's an Array of Arrays? Did you just look at
the output of inspect or did you actually invoke #class?

I am not sure that you actually found a good solution here.

Kind regards

robert

 

[gregarican]

I didn't let the DB do the selection because I was querying different
databases residing on different servers and combining the results in a
way that I didn't feel like jumping through SQL hoops for. Since I like
coding in Ruby better than coding in SQL I figured I'd let Ruby do the
heavy lifting. Looking at it, a Hash object would be better suited for
some of this work. Next time I will utilize this type of object.

As Mr. Gray suggested I ran resultSet.class and resultSet.first.class
and got Array and DBI::Row respectively. Guess that's why I couldn't
run the assoc method on a DBI::Row object! That makes perfect sense
now. I thought that the DBI rows returned were piped into arrays. Guess
not

 

[gregarican]

You got it. In the case of trying to sort things based on a particular
field I converted them to the same type since there was a bug where
this wasn't always 100% consistent. After doing so then the sorting
worked like a champ. The only other stumbling block was that within the
SQL recordset array each record was actually a DBI::Row object and not
an array unto itself. I should've known this but hadn't looked under
the hood of the DBI stuff for awhile. After converting these DBI::Row
objects to arrays then I could check things with the assoc method. So
things aren't broken after all!