Problem with a select in Oracle

ruby-talk
1

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")

    if (!(row[1] == ""))
  logFile.print("++ Contact: #{row[1]} e-mail: #{row[3]} Name:

#{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

2

[…]

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

=======

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

3

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…

···

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")

    if (!(row[1] == ""))
  logFile.print("++ Contact: #{row[1]} e-mail: #{row[3]} Name:

#{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

4

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

5

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 http://w3.one.net/~jweirich

Gavin

···

From: “Jim Weirich” jweirich@one.net

On Mon, 2002-11-11 at 23:37, Gavin Sinclair wrote: