CODEWITHSUNDEEP
perloracle-database
Brian Ambielli
Brian Ambielli

Reputation: 601

Passing variables into sqlplus query in perl

I'm having an issue here. I am trying to pass a variable into a sqlplus query, and it does not seem to be working.

    my $connect = DBI->connect('DBI:Oracle:',$dbuser,$dbpasswd);
  my $query = "select sum(transaction_amnt) from comm_to_cand natural join cmte_id_to_geo where cycle='?'", $cycle;
  my $query_handle = $connect->prepare($query);
  $query_handle->execute();
  $cmte_money = $query_handle->fetchrow_array();

  print 'Money: ';
  print $cmte_money;

  if($cmte_money > 0)
  {
    print 'HI';
  }
  else
  {
    print 'NOOOO';
  }

I can get the query to work when I change the "cycles" variable from a variable to a constant, and the if statement checking will print hi, so the databases work I'm positive.

I've scoured the internet, and I can't seem to find an answer.

Upvotes: 0

Views: 1157

Answers (4)

jiri.hofman
jiri.hofman

Reputation: 125

  my $connect = DBI->connect('DBI:Oracle:',$dbuser,$dbpasswd);
  # Tell the DBI that the query uses bind variable with ? (question mark)
  my $query = "select sum(transaction_amnt) from comm_to_cand natural join cmte_id_to_geo where cycle=?";
  my $query_handle = $connect->prepare($query);
  # Pass the value 
  $query_handle->execute($cycle); # assuming the variable is defined (otherwise it will pass as NULL into the query)
  $cmte_money = $query_handle->fetchrow_array();

  print 'Money: ';
  print $cmte_money;

  if($cmte_money > 0)
  {
    print 'HI';
  }
  else
  {
    print 'NOOOO';
  }

Upvotes: 0

Swamy
Swamy

Reputation: 29

Here is an example:

use strict;

    use DBI;

    my $connect = DBI->connect('DBI:Oracle:', $dbuser, $dbpasswd); 
    my $query = "select sum(transaction_amnt) from comm_to_cand natural join cmte_id_to_geo where cycle = `$cycle`"; 
    my $query_handle = $connect->prepare($query); 
    $query_handle->execute(); 
    @cmte_money = $query_handle->fetchrow_array(); 

    print 'Money: '; 
    print @cmte_money; 

    if($#cmte_money >= 0) 
    { 
      print 'HI'; 
    } 
    else 
    { 
      print 'NOOOO'; 
    }

I define a constant variable $cycle, I think like this.

Upvotes: 0

ysth
ysth

Reputation: 98508

The replacements for placeholders get passed to execute, so:

my $query = "select sum(transaction_amnt) from comm_to_cand natural join cmte_id_to_geo where cycle=?";
my $query_handle = $connect->prepare($query);
$query_handle->execute($cycle);

The code you had would have triggered warnings if you had them enabled; make sure you do and that you figure out how to respond to any you get.

Upvotes: 1

ikegami
ikegami

Reputation: 386696

First, you mean to use a placeholder but you don't.

where cycle='?'     -- This is a string

should be

where cycle=?       -- This is a placeholder

And then there's problem that you don't actually pass a value for the placeholder.

$query_handle->execute();

should be

$query_handle->execute($cycle);

Upvotes: 5

Related Questions