in reply to DBI SQL statement in while loop

Off hand I think this calls for placeholders - something like: 
my $sql2 = qq/
  INSERT INTO transaction_details (quantity, gross_sales)
              VALUES (?, ?)
/;

my $sth = $dbh->prepare($sql2);

while (<>) {  
    my $quantity = param("quantity");
    my $gross_sales = param("gross_sales");
     
    my $rv = $sth->execute($quantity, $gross_sales);
    # Handle error if $rv is false...
}

[download]
This should handle all quoting (if any is necessary) and should also handle the conversion to "money" (which makes me think that this is a Sybase or MS-SQL database server...)

Michael

Replies are listed 'Best First'.
Re: Re: DBI SQL statement in while loop
by peppiv (Curate) on Dec 03, 2002 at 21:32 UTC
    Thanks Michael. But in using FreeTDS and DBI Sybase (thank you) I haven't been able to use placeholders. My apologies for not listing the setup: Apache/Linux/Perl -> Win2K/SQL Server 7.0
[reply]
      Ah. Well in that case you'll have to move the prepare() into the loop (or use do()) and use interpolation (with the usual caveats about NULLs, etc.)

      The naive version would look like this: 

      while (<>) {  
    my $quantity = param("quantity");
    my $gross_sales = param("gross_sales");
    my $sql = qq/
       insert into transaction_details(quantity, gross_sales)
             values($quantity, $gross_sales)
        /;
     my $rv = $dbh->do($sql);
     # check return value, etc.
}

      [download]
      You should of course check that the two input parameters are valid before submitting the SQL request.

      Michael

[reply]
[d/l]

In Section Seekers of Perl Wisdom