From: Robert Klemme <shortcutter@...>
Date: 2006-11-15T17:35:10+09:00
Subject: Re: Assoc method on large array

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