Re^2: Trying to use DBD::Oracle

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.

Comment on Re^2: Trying to use DBD::Oracle Download Code

Replies are listed 'Best First'.
Re^3: Trying to use DBD::Oracle by ptum (Priest) on Oct 24, 2006 at 18:47 UTC
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]

Replies are listed 'Best First'.

Re^3: Trying to use DBD::Oracle
by ptum (Priest) on Oct 24, 2006 at 18:47 UTC

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]