in reply to Trying to use DBD::Oracle

So, I don't know anything about running Perl in a Windows environment, but I can point out one or two very obvious thing(s) you are doing wrong. First, you want 'use DBI;' rather than 'use DBD::Oracle;' unless there is some weirdness about using SQL::Statement (which I have never used) that I don't know about. Second, you want to retrieve your results into some kind of data structure, not just let them fall to the floor:

  use strict;
  use DBI;
  # make the connection the way you have shown
  my @resultrow = ();
  my $resultref = $dbh->selectrow_arrayref("select ID from study 
    where NAME='$unique_search_string_here'");
  if (ref($resultref) eq 'ARRAY') {
    @resultrow = @{$resultref};
    # do something with $resultrow[0]
  }
  else {
    # check for error in $DBI::errstr, etc.
  }

[download]

This will work only where you have a unique search string that returns only a single row of data ... if you can have multiple rows, use selectall_arrayref, which will give you a reference to an array of array references. Take a few moments and familiarize yourself with DBI.

Replies are listed 'Best First'.
Re^2: Trying to use DBD::Oracle
by BigJoe (Curate) on Oct 24, 2006 at 18:03 UTC
    I think I would take this one step further and bind the variable that you are passing to the SQL. 
    use strict;
  use DBI;
  # make the connection the way you have shown
  my @resultrow = ();
  my $sth = $dbh->prepare("select ID from study 
    where NAME=?");
  $sth->execute($unique_search_string_here);
  my $resultref = $sth->fetchrow_arrayref;
  if (ref($resultref) eq 'ARRAY') {
    @resultrow = @{$resultref};
    # do something with $resultrow[0]
  }
  else {
    # check for error in $DBI::errstr, etc.
  }

    [download]


    --BigJoe

    Learn patience, you must.
    Young PerlMonk, craves Not these things.
    Use the source Luke.
[reply]
[d/l]

      I always forget to do that, but this is good advice from BigJoe. There are two reasons (probably more) to use bind parameters:

      • Preparing your SQL statement once and executing it inside a loop will save you a lot of overhead any time you are repeating the same operation across a range of variables. While a database cache may protect you from shooting yourself in the foot, there is no sense in trusting in that when you can code around it so easily.
      • A happy side effect of using the question-mark in the prepare and passing the variable in the execute method is that you don't have to worry about quoting strings and not quoting numbers or dates -- the DBI just takes care of it for you. This can save you considerable grief, or at least I find it does in Oracle.
[reply]
Re^2: Trying to use DBD::Oracle
by hj4jc (Beadle) on Oct 24, 2006 at 21:16 UTC
    Hi,
    Thanks for taking time to reply.
    But I think my Win32 machine is just having trouble
    using the DBI module.
    I tried tweaking things,
    and I get a variation on error messages such as:
    install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/O
+racle/Orac
le.dll' for module DBD::Oracle: load_file:The specified procedure coul
+d not be f
ound at C:/Perl/lib/DynaLoader.pm line 166.

 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expecte
+d
 at C:\PlayGround\oracle.pl line 15

    [download]
    I am really not sure where to go from here...
[reply]
[d/l]

In Section Seekers of Perl Wisdom