Re: Dynamic query generation

You should be using strict. I think $q.="$key, "; should be $query.="$key, ";

update: and you're creating a new lexical variable $query within the if ($action eq ..., which goes out of scope.

my $query;
if ($action eq 'Problem Submit')        
  $query = "INSERT INTO Ticket_Data (";

  for  $key ( keys %ORA_MAPPING ) {
     my $value = $ORA_MAPPING{$key};
     next if $key eq "Data_id";
     $query.=" $value, "; # or did you really mean $key here?
  }
[download]

Also note that you should be careful if you build queries from external/dynamic input. Use placeholders if you can, and DBI->quote otherwise.

"What should it profit a man, if he should win a flame war, yet lose his cool?"

Comment on Re: Dynamic query generation Select or Download Code

Replies are listed 'Best First'.
Re^2: Dynamic query generation by chanakya (Friar) on May 05, 2005 at 12:19 UTC
Joost Thanks for pointing out the error, Here's the updated code %ORA_MAPPING = ( 'Data_id' => 'Ticket_Data_seq.nextval', 'Action' => "Problem Submit", 'Cust_Ticket_Number' => "", 'Our_Ticket_Number' => "$data->{'TicketNumber'}", 'msg_id' => "", 'time_stamp' => "", 'Description' => "", 'EL_Edesc' => "", 'prov_id' => "EECO::UTN::BPL", 'provclientid' => "BPC::123", 'prov_client_org' => "", 'prov_client_buz' => "", 'prov_schema_type' => "" ); my $query = "INSERT INTO Ticket_Data ("; if ($action eq 'Problem Submit') for $key ( keys %ORA_MAPPING ) { $value = $ORA_MAPPING{$key}; next if $key eq "Data_id"; $query .=" $key, "; } $query .= " ) VALUES ($ORA_MAPPING{$_} )"; } elsif ($action eq 'Update') { ... } [download] Is it possible to build the query using the above code or is there any other better approach. I'd like to know that approach as well. Many thanks for your time	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: Dynamic query generation
by chanakya (Friar) on May 05, 2005 at 12:19 UTC

%ORA_MAPPING = (
        
        'Data_id' => 'Ticket_Data_seq.nextval',
        'Action' => "Problem Submit",
        'Cust_Ticket_Number' => "",
        'Our_Ticket_Number' => "$data->{'TicketNumber'}",
        'msg_id' =>    "",
        'time_stamp'     =>    "",
        'Description' =>    "",
        'EL_Edesc' =>    "",
        'prov_id' =>    "EECO::UTN::BPL",
        'provclientid' => "BPC::123",
        'prov_client_org' =>    "",
        'prov_client_buz' =>    "",
        'prov_schema_type' =>    ""
        );
        

my $query = "INSERT INTO Ticket_Data (";
if ($action eq 'Problem Submit')        
  

  for  $key ( keys %ORA_MAPPING ) {
     $value = $ORA_MAPPING{$key};
     next if $key eq "Data_id";
    $query .=" $key, ";

  }

  $query .= " ) VALUES ($ORA_MAPPING{$_} )";

} elsif ($action eq 'Update') {
...
}
[download]

[reply]
[d/l]