From: Gavin Sinclair <gsinclair@...>
Date: 2002-11-12T13:37:37+09:00
Subject: Re: Problem with a select in Oracle

From: "Manuel Valladares" <mavallad@yahoo.es>


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

=======

  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