calling the column_name in the table while connecting via Oracle DB

Question

I am trying to get the value from the table(employee)connecting through the oracle database. Since there are 100s of values in one column, I would need to iterate the table and get the exact value.

I have the code that works if I use the index no. such as row[1] but I wanted to use the column_name "first name" instead of row[1]. Below is the code that I have which works. Code:

def load_borrower

connection = OCI8.new('usrname', 'pwd', //host:portno/sid') 

connection.exec(("SELECT BI_PREFIX, BI_FNAME, BI_MNAME, BI_LNAME, B.BI_SUFFIX, BI_ID_TYPE, BI_ID_NUMBER, BI_DOB,    B1.*, R.*, M.*, C.*, L.* FROM EMPLOYEE, SC_BORROWERPREF_NEW S1, BORROWER_NEW B, BORROWERPREF_NEW B1, RES_ADD R, MAIL_ADD M, CLOS_ADD C, LLORD_ADD L WHERE S2=SCENARIO_ID = S1.SCENARIO_ID AND S1.PREF_ID = B1.PREF_ID AND B1.BORROWER_ID = B.BORROWER_ID AND B1.PREF_ID = R.RES_PREF_ID AND B1.PREF_ID = M.MAIL_PREF_ID AND B1.PREF_ID = C.CLOS_PREF_ID AND B1.PREF_ID = L.LLORD_PREF_ID  AND S.RELEASE_ID= "1" AND S.SCENARIO_NO = '2' ORDER BY S1.SC_BORROWERPREF_ID") do |row|

$BI_PREFIX=row[0].to_s

$BI_FNAME=row[1].to_s

$BI_MNAME=row[2].to_s

$BI_LNAME=row[3].to_s

$BI_SUFFIX=row[4].to_s

$BI_BI_ID_TYPE=row[5].to_s

$BI_BI_ID_NUMBER=row[6].to_s

$BI_DOB=row[7].to_s

$BI_EMAIL=row[9].to_s

$BI_CELL_PH=row[11].to_s

$BI_WORK_PH=row[12].to_s

$BI_PREF_CONT=row[13].to_s

$BI_MAR_STATUS=row[16].to_s

$BI_EMP_STATUS=row[23].to_s

$BI_EDUC_YEARS=row[17].to_s

$BI_NUM_DEPEND=row[21].to_s

end

end

Now I'm running the above functions below

load_borrower

So the code above right now works fine. But As you can see from above, I am defining the variables from the db table as row[5], row[24] like that which is very hectic and time consuming although it works. So I was just wondering if we have any method or command to use the column_name such that it gets the value from the row and the column such as row['Emp_id'] instead of finding about the index of every column_name.

I am not sure if this is a drawback of Ruby as it treates the table from the db as an array and may be that's why we can't specify by column_name.

Answer 1

Firstly it appears you are a bit confused by the boundaries and separations between the various bits of technology you are using. There is no Watir in the code you provided, NONE. it's all pure Ruby and a tiny bit of stuff from the OCI8 Gem. A GEM is a standard way that Ruby folks use to distribute code libraries and programs written in the Ruby language. See HERE for more info to better understand what a Gem is and how they are used.

Watir is another Ruby gem that is for driving web-browsers, and you might be using it elsewhere in your code, but it doesn't relate to this question or OCI8 other than both of them being Ruby code libraries distributed as Gems. So lets leave it aside so as to not confuse things.

The behavior you are seeing is how the OCI8 gem works, NOT anything to do with Ruby specifically. If you want something more elegant, then look into different gems that have been created for doing db access with Ruby, for example ActiveRecord, which was suggested in another answer already. The OCI8 Gem only returns an array if you have the results feeding into a block like you do in your current code. Otherwise the results are in an object called a Cursor, and you can use the cursor's fetch_hash method to get fetched data as a Hash. The hash keys are column names. (see http://ruby-oci8.rubyforge.org/en/api_OCI8Cursor.html)

Allow me to strongly recommend that you spend a little time learning a bit more about the Ruby language before you tear much further into your current project. Given the nature of the coding you seem to be doing, I'd advise you to read Brian Marik's book "Everyday Scripting with Ruby", thats going to give you a lot better understanding of the technology you are using, and you'll understand better when we toss around terms like 'hash' as I just did.

If you will allow a bit of general advice in terms of how you are going about interfacing with your database. IMHO, you should be taking advantage of the db by constructing a query that returns JUST the data you want, instead of grabbing huge amounts of data and trying to parse through it manually. It's better use of the resource, uses less memory, takes less time to transfer the info from the db, and no matter how good your parsing code might be, it won't be as good as what the Oracle people wrote. Let the db do the heavy lifting, that's what it's there for.

If what you are dealing with here is data to drive your testing, or validate results, rather than construct one huge monolithic array, I'd recommend you use a much more modular approach. Use one global variable such as the EMP_ID of the current user you are testing with or against, and have the test code get query results for just the values needed for each validation, or a small logical group of validations like the parts of an address. It's a lot easier to build up stuff that way on a case by case basis working as you go, instead of trying to write the whole data retrieval bit in one giant piece that will be a nightmare to maintain.

As it stands all your test code that is verifying function or validating how the site works is going to be tightly coupled to a big monolithic piece that fetches the data from the db. that creates a lot of dependencies and makes your test code hard to maintain. If you deal with things in a more modular way, where each validation step retrieves just the data it needs, then it's a lot easier to expand or modify your test code as the site or database changes.

Answer 2

This should work

connection = OCI8.new('usrname', 'pwd', //host:portno/sid') 

cursor = connection.exec(("SELECT BI_PREFIX ...")
cols = cursor.get_col_names

while r = cursor.fetch
  $BI_PREFIX=r[cols.index('BI_PREFIX')].to_s
  ...
end

Answer 3

If you had an array containing the column names then you could zip it up with the row array and build a hash:

Hash[column_names.zip( row )]

I would recommend using activerecord for this though.