# Problem with a select in Oracle

**URL:** <https://rubytalk.org/t/problem-with-a-select-in-oracle/2823>\
**Category:** ruby-talk\
**Created:** [11 November 2002 23:27 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823 "2002-11-11T23:27:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mavallad](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@Mavallad](https://rubytalk.org/u/Mavallad)\
**Post date:** [11 November 2002 23:27 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823/1 "2002-11-11T23:27:35Z")

</div>

Hello,  
I am just beginning in Ruby, in fact this is my first script.

I need to copy values from a comma separated file to an Oracle  
database and just to try I would like to do it in Ruby.

I installed the ruby dbi and oracle drivers and everything looks  
looked it was working fine, until now that I want to retrieve the  
e-mail addresses from the database.

The code I am using is this one:

> **···**
>
> ########################################################################  
> require 'dbi’  
> require ‘oracle’
> 
> # Open the files
> 
> contactFile = File.open(“contact\_sel.csv”,“r”)  
> logFile = File.new(“contact.log”,“w”)
> 
> # Connect to the database
> 
> dbConnect = DBI.connect(‘DBI:Oracle:limsod’, ‘lims’, ‘mjd0340854’)
> 
> #Prepare the statement  
> sql = "SELECT client\_id, contact\_id, name, type, email " +  
> "FROM contact\_table " +  
> "WHERE client\_id = :ci\_id " +  
> "AND contact\_id = :co\_id " +  
> “AND email IS NOT NULL”
> 
> dbQuery = dbConnect.prepare(sql)
> 
> begin  
> while (line = contactFile.readline())  
> client\_id, contact\_id, status, email, phone =  
> line.split(/\s\*,\s\*/)
> 
> ```
> dbQuery.bind_param("ci_id", client_id)
> dbQuery.bind_param("co_id", contact_id)
> 
> dbQuery.execute()
> 
> if (row = dbQuery.fetch())
> logFile.print("Client: " + client_id + " Contact: " + contact_id
> 
> ```
> 
> - " is present\n")
> 
> #{row[2]}\n")  
> end  
> else  
> logFile.print(“Contact: " + contact\_id + " not present\n”)  
> end  
> end
> 
> logFile.print(“END\n”)  
> rescue EOFError  
> contactFile.close()  
> end
> 
> # Disconnect to the database
> 
> dbConnect.disconnect()
> 
> #####################################################################
> 
> Well, the problem is that I am not getting the values that are in the  
> database.
> 
> There are a bunch of records with email not null, but the script gets  
> only one record with garbage in the email part.
> 
> So I don’t understand what is wrong. Is there any problem with the '@'  
> symbol?  
> I am able to get the values from the rest of the fields, but not the  
> email field. It looks weird to me.
> 
> If this doesn’t work I will not use Ruby because need the Oracle  
> connection to do my scripts.
> 
> That’s it.  
> Thank you very much for your help  
> Manuel Valladares

---

<div class="post-metadata">

**Author:** ![Gavin\_Sinclair](https://avatars.discourse-cdn.com/v4/letter/g/db5fbb/32.png) [@Gavin\_Sinclair](https://rubytalk.org/u/Gavin_Sinclair)\
**Post date:** [12 November 2002 04:37 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823/2 "2002-11-12T04:37:37Z")

</div>

> […]
> 
> Well, the problem is that I am not getting the values that are in the  
> database.
> 
> There are a bunch of records with email not null, but the script gets  
> only one record with garbage in the email part.
> 
> So I don’t understand what is wrong. Is there any problem with the ‘@’  
> symbol?  
> I am able to get the values from the rest of the fields, but not the  
> email field. It looks weird to me.

I very much doubt that there is a problem with the ‘@’ character. What data  
type is your email address in the database? One gotcha with DBI is that  
queries always return Strings.

I recommend the following things:

- keep the problem simple: play around in ‘irb’ to experiment  
with various queries
- convert the row to a hash (row.to\_h) so you know exactly what  
you’re dealing with
- make sure the email is stored as a varchar2(nn) data type
- use #select\_(one|all) instead of #execute to perform a query

I use DBI and Oracle reasonably well. I’ve adapted my database to suit DBI  
(use VARCHAR2 wherever possible - definitely don’t use DATE).

Since it’s your first Ruby program, study the rewrite below which takes  
advantage of Ruby features (namely iterators and here documents). I’ve  
excluded logging. Some may disagree with the use of exception handling below -  
it’s just an example.

I haven’t used a prepared statement, either. It’s obviously better to get the  
program working and then concentrate on that.

Cheers,  
Gavin

> **···**
>
> From: “Manuel Valladares” [mavallad@yahoo.es](mailto:mavallad@yahoo.es)
> 
> =======
> 
> require ‘dbi’
> 
> # No need to require “Oracle”
> 
> query = \<\<-EOQ  
> SELECT client\_id, contact\_id, name, type, email  
> FROM contact\_table  
> WHERE client\_id = ?  
> AND contact\_id = ?  
> AND email IS NOT NULL  
> EOQ
> 
> def output\_row(row)  
> puts “Client: #{client\_id} Contact: #{contact\_id} is present”  
> puts “++ Contact: #{row[“CONTACT\_ID”]}”  
> puts “++ Email: #{row[“EMAIL”]}”  
> puts “++ Name: #{row[“NAME”]}”  
> end
> 
> DBI.connect(‘DBI:Oracle:limsod’, ‘lims’, ‘mjd0340854’) do |dbh|
> 
> ```
> File.open("contact_sel.csv") do |file|
> file.each do |line|
> 
> client_id, contact_id = line.split(/\s*\,\s*/)
> 
> begin
> row = dbh.select_one(query, client_id, contact_id).to_h
> output_row(row)
> rescue NameError # (there is no row so #to_h fails)
> puts "Contact: #{contact_id} not present"
> end
> 
> end # line
> end # file
> 
> ```
> 
> end # database connection

---

<div class="post-metadata">

**Author:** ![Mavallad](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@Mavallad](https://rubytalk.org/u/Mavallad)\
**Post date:** [12 November 2002 16:50 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823/3 "2002-11-12T16:50:01Z")

</div>

I am sorry guys,  
You were absolutly right, there is nothing wrong with the ‘@’ symbol.

The problem is that I did something stupid and I was connecting to the  
wrong schema in Oracle, so of course I was getting different results  
than I was expecting.

But thank you very much for the coding recommendations.

Sorry again for wasting your time,  
Manuel Valladares

mavallad@yahoo.es (Manuel Valladares) wrote in message news:[73e32e3a.0211111517.4af49211@posting.google.com](mailto:73e32e3a.0211111517.4af49211@posting.google.com)…

> **···**
>
> > Hello,  
> > I am just beginning in Ruby, in fact this is my first script.
> > 
> > I need to copy values from a comma separated file to an Oracle  
> > database and just to try I would like to do it in Ruby.
> > 
> > I installed the ruby dbi and oracle drivers and everything looks  
> > looked it was working fine, until now that I want to retrieve the  
> > e-mail addresses from the database.
> > 
> > The code I am using is this one:
> > 
> > ########################################################################  
> > require ‘dbi’  
> > require ‘oracle’
> > 
> > # Open the files
> > 
> > contactFile = File.open(“contact\_sel.csv”,“r”)  
> > logFile = File.new(“contact.log”,“w”)
> > 
> > # Connect to the database
> > 
> > dbConnect = DBI.connect(‘DBI:Oracle:limsod’, ‘lims’, ‘mjd0340854’)
> > 
> > #Prepare the statement  
> > sql = "SELECT client\_id, contact\_id, name, type, email " +  
> > "FROM contact\_table " +  
> > "WHERE client\_id = :ci\_id " +  
> > "AND contact\_id = :co\_id " +  
> > “AND email IS NOT NULL”
> > 
> > dbQuery = dbConnect.prepare(sql)
> > 
> > begin  
> > while (line = contactFile.readline())  
> > client\_id, contact\_id, status, email, phone =  
> > line.split(/\s\*,\s\*/)
> > 
> > ```
> > dbQuery.bind_param("ci_id", client_id)
> > dbQuery.bind_param("co_id", contact_id)
> > 
> > dbQuery.execute()
> > 
> > if (row = dbQuery.fetch())
> > logFile.print("Client: " + client_id + " Contact: " + contact_id
> > 
> > ```
> > 
> > - " is present\n")
> > 
> > #{row[2]}\n")  
> > end  
> > else  
> > logFile.print(“Contact: " + contact\_id + " not present\n”)  
> > end  
> > end
> > 
> > logFile.print(“END\n”)  
> > rescue EOFError  
> > contactFile.close()  
> > end
> > 
> > # Disconnect to the database
> > 
> > dbConnect.disconnect()
> > 
> > #####################################################################
> > 
> > Well, the problem is that I am not getting the values that are in the  
> > database.
> > 
> > There are a bunch of records with email not null, but the script gets  
> > only one record with garbage in the email part.
> > 
> > So I don’t understand what is wrong. Is there any problem with the ‘@’  
> > symbol?  
> > I am able to get the values from the rest of the fields, but not the  
> > email field. It looks weird to me.
> > 
> > If this doesn’t work I will not use Ruby because need the Oracle  
> > connection to do my scripts.
> > 
> > That’s it.  
> > Thank you very much for your help  
> > Manuel Valladares

---

<div class="post-metadata">

**Author:** ![Jim\_Weirich2](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@Jim\_Weirich2](https://rubytalk.org/u/Jim_Weirich2)\
**Post date:** [12 November 2002 05:12 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823/4 "2002-11-12T05:12:11Z")

</div>

> I very much doubt that there is a problem with the ‘@’ character. What data  
> type is your email address in the database? One gotcha with DBI is that  
> queries always return Strings.

That’s not entirely true. It tends to be dependent upon the database  
driver being used (this recently came to my attention). The PostgreSQL  
driver very carefully converts the SQL data types to native Ruby types.  
Evidently the Oracle driver does not do this. This is a major  
incompatibility in the drivers that should be ironed out (IMHO).

> I recommend the following things:

[… strong agreement with the given list, with comments on …]

> - convert the row to a hash (row.to\_h) so you know exactly what  
> you’re dealing with

I almost never do this. Do you do this because you use hash methods  
that aren’t supported on a row object? The original intent was that the  
row would look sufficiently like a hash so that you wouldn’t have to  
convert it. If still feel the need, perhaps it is not “sufficient”  
enough.

> **···**
>
> On Mon, 2002-11-11 at 23:37, Gavin Sinclair wrote:
> 
> ## – – Jim Weirich [jweirich@one.net](mailto:jweirich@one.net)[http://w3.one.net/~jweirich](http://w3.one.net/~jweirich)
> 
> “Beware of bugs in the above code; I have only proved it correct,  
> not tried it.” – Donald Knuth (in a memo to Peter van Emde Boas)

---

<div class="post-metadata">

**Author:** ![Gavin\_Sinclair](https://avatars.discourse-cdn.com/v4/letter/g/db5fbb/32.png) [@Gavin\_Sinclair](https://rubytalk.org/u/Gavin_Sinclair)\
**Post date:** [12 November 2002 07:09 UTC](https://rubytalk.org/t/problem-with-a-select-in-oracle/2823/5 "2002-11-12T07:09:00Z")

</div>

> > I very much doubt that there is a problem with the ‘@’ character. What  
> > data  
> > type is your email address in the database? One gotcha with DBI is that  
> > queries always return Strings.
> 
> That’s not entirely true. It tends to be dependent upon the database  
> driver being used (this recently came to my attention). The PostgreSQL  
> driver very carefully converts the SQL data types to native Ruby types.  
> Evidently the Oracle driver does not do this. This is a major  
> incompatibility in the drivers that should be ironed out (IMHO).

Good idea.

> > I recommend the following things:
> 
> [… strong agreement with the given list, with comments on …]
> 
> > - convert the row to a hash (row.to\_h) so you know exactly what  
> > you’re dealing with
> 
> I almost never do this. Do you do this because you use hash methods  
> that aren’t supported on a row object? The original intent was that the  
> row would look sufficiently like a hash so that you wouldn’t have to  
> convert it. If still feel the need, perhaps it is not “sufficient”  
> enough.

I do it because I haven’t seen any documentation that says how a row is  
supposed to behave, only code, and while I noticed the hash-like behaviour, I  
just don’t trust it. DBI::Row# is complicated, so I don’t trust it. I  
always want to use a row as a hash, so the safest thing is to convert it. At  
least then if there is a problem, I could be confident in reporting a bug!  
Furthermore, I see nothing lost by converting.

Note: when I say I don’t trust the code, I mean no slight against the code  
writer. I just don’t trust my ability to determine how I’m supposed to use it  
from looking at the code.

> – Jim Weirich [jweirich@one.net](mailto:jweirich@one.net)[http://w3.one.net/~jweirich](http://w3.one.net/~jweirich)

Gavin

> **···**
>
> From: “Jim Weirich” [jweirich@one.net](mailto:jweirich@one.net)
> 
> > On Mon, 2002-11-11 at 23:37, Gavin Sinclair wrote: