hakiran has asked for the wisdom of the Perl Monks concerning the following question:

A newbie to PERL DBI. I have successfully been able to work with MySQL DB's. But having quite a bit of problem getting a query to execute via my PERL DBI script. The return value comes up as zero all the time. It does not give any error during connection. So, not able to figure out why it is coming back with 0. Please help!! here is a snippet of my code. 
my $dbh = DBI->connect("DBI:Oracle:host=$host;port=$port;sid=$sid",$us
+er, $password, {PrintError=>1,RaiseError=>1}) or die "Can't connect t
+o ORACLE database:$DBI::errstr\n";

my $subj_code_query = qq{SELECT * FROM LOOKUP};
my $sth_subj = $dbh->prepare($subj_code_query);

[download]
---A function--- 
sub execute_subject_query()
{
    open(SWRT, ">SUBJECT_CODES_FOR_XML.txt");
    my $rv = $sth_subj->execute();
    if($rv == 0)
    {
        print "No Value Returned\n"; 
    }
    else
    {
print "subject_id\tsubject_sex\tsubject_type\n";
        while( my $array_ref = $sth_subj->fetchrow_arrayref)
        {
            for(my $i=0; $i<@$array_ref;$i++)
            {
                print "$array_ref->[$i]\n";<STDIN>;
                print SWRT "$array_ref->[$i]\n";
            }
        }
    }
    $sth_subj->finish();
    close SWRT;
}

[download]

Replies are listed 'Best First'.
Re: PERL DBI and oracle
by thezip (Vicar) on Jun 12, 2007 at 17:57 UTC
    • I don't see anywhere where you call the sub execute_subject_query(). I'm sure your actual code has this, right?
    • Please format your post using <c>... your code ...</c> tags
    • Perhaps you might increase the DBI trace level, as in: 
      DBI->trace(3); # Added call to trace method
my $dbh = DBI->connect("DBI:Oracle:host=$host;port=$port;sid=$sid",$us
+er, $password, {PrintError=>1,RaiseError=>1}) 
  or die "Can't connect to ORACLE database:$DBI::errstr\n";

my $subj_code_query = qq{SELECT * FROM LOOKUP}; 
my $sth_subj = $dbh->prepare($subj_code_query); 

# ---A function--- 
sub execute_subject_query() { 
  open(SWRT, ">SUBJECT_CODES_FOR_XML.txt"); 
  my $rv = $sth_subj->execute(); 
  if($rv == 0) { 
    print "No Value Returned\n";
  }
  else { 
    print "subject_id\tsubject_sex\tsubject_type\n";
    while( my $array_ref = $sth_subj->fetchrow_arrayref) { 
      for(my $i=0; $i<@$array_ref;$i++) { 
        print "$array_ref->$i\n";<STDIN>; 
        print SWRT "$array_ref->$i\n";
      }
    }
  }
  $sth_subj->finish(); close SWRT;
}

      [download]

      Where do you want *them* to go today?
[reply]
[d/l]
[select]
      Yes i have the function call in my code. I will try the DBI->trace level to 3 and see what else it throws up. -K
[reply]
        I did not see any explicit ERROR line in the trace call. I had done with 2 earlier. Like you suggested i raised it to 3, but didnt see anything extra. The variable "sid" in the DBI->connect, does that refer to the database i am tryin to connect to or somekind of an ID of the DATABASE?
[reply]
Re: PERL DBI and oracle
by wagnerc (Sexton) on Jun 12, 2007 at 21:01 UTC
    Hi. It seems ur trying to use the statement handle in two unrelated places. my $sth_subj; makes $sth_subj invisible to the execute_subject_query sub that calls the execute method. Make *all* ur method calls in the same scope and it should work.
[reply]
Re: PERL DBI and oracle
by agianni (Hermit) on Jun 12, 2007 at 20:26 UTC

    The first thing that comes to mind for me is the potential scoping issues around the $sth_subj variable. Basically, I'm wondering if the $sth_subj in execute_subject_query is actually the same one as in the code above it. Are you using the strict and warnings pragmas? 

    use strict;
use warnings;

    [download]

    If you're not, make sure you include them at the top of your script and if my hunch is right, you'll see some errors. Also, to avoid problems with things like this, it's also generally good to avoid using global variables in this manner because it's much clearer what's going on. For example, in this case, I would pass $sth_subj in as an argument to the execute_subject_query subroutine so it's clear where it's coming from. 

    perl -e 'split//,q{john hurl, pest caretaker}and(map{print @_[$_]}(joi
+n(q{},map{sprintf(qq{%010u},$_)}(2**2*307*4993,5*101*641*5261,7*59*79
+*36997,13*17*71*45131,3**2*67*89*167*181))=~/\d{2}/g));'

    [download]
[reply]
[d/l]
[select]
      HI, Yes i always use strict and warnings. I will try the passing as arguments. But i have not had problems connecting to MySQL...using similar syntax. anyway..will try a few other things at my end... thank you
[reply]

Back to Seekers of Perl Wisdom