perl apostrophe problem

Do you mean that there is an apostrophe in $Subject or$SubmitTime or $CompletionTime, or $Status ?

I think subject

Thanks, I actually found a problem while dealing with this.  My function gets called 50 times, and it really slows the page load.  Is there a way to save up the update statements then executem then once at the end?  Maybe with a long string?  Does that make sense?

Place the prepare statement that ozo showed outside of the sub (just prior to calling the sub), and put the execute statement inside of the sub.

Thanks, but I am not sure I understand.  

When I add what ozo suggested, my new fuction will look like what I am pasting below.  If I add something inside and outside, it will still be one call for each time through my loop.  I need a way to concatinate update statements then execute them all at once.  I know how to just append to a string, but the prepare and execute method below is not a string, so I can't just concatinate.  Did that make sense?

sub updateDB
{
($Subject, $SubmitTime, $CompletionTime, $Status, $TransactionID) = @_ ;

$sth = $dbh->prepare(UPDATE tracker set SubmitTime = ?,CompletionTime = ?,Status = ?,SubmitTime = ? where orderid =?");
$sth->execute($SubmitTime, $CompletionTime, $Status, $SubmitTime, $Subject);


$sql = "UPDATE tracker set SubmitTime = '$SubmitTime',CompletionTime = '$CompletionTime',Status = '$Status',SubmitTime = '$SubmitTime' where orderid ='" . $Subject . "'";

Thanks for your help!  I am really not too good with Perl.  I did what you said, but I am getting an error DBD::mysql::st execute failed: Lost connection to MySQL server during query

I am not using a function (at least until I get everythign working well)

I tried it with and without $statement->finish;, but it seems to fail on the second call of execute.  For some reason the connection closes.  Thanks again!

$sql = "UPDATE tracker set SubmitTime = ?,CompletionTime = ?,Status = ? where orderid =?";
$dbh = DBI->connect('DBI:mysql:tracker:sites.net:3306', 'user', 'pass', { RaiseError => 1, AutoCommit => 1 }) ;
$statement = $dbh->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";

foreach $Item (@Items) {
#updateDB($Item->value->{'Subject'}, $Item->value->{'SubmitTime'}, $Item->value->{'CompletionTime'}, $Item->value->{'Status'}, $Item->value->{'TransactionID'});
$statement->execute($Item->value->{'SubmitTime'}, $Item->value->{'CompletionTime'}, $Item->value->{'Status'}, $Item->value->{'Subject'});
$statement->finish;  
}
$dbh->disconnect;

Shouldn't $Item be dereferenced like this?

$Item->{value}->{'SubmitTime'}

the way I was referencing it worked.

Thanks for your help.  I just tested my script using three methods.

1. Make a function that updates the db every time the way I had it above.  Initiate db connection prep statement, execute it disconnect.

2. Build a long string on the loop seperated with ; (I replaced the ' with ''), then execute it in one statement after the loop, bu preparing it, then executing.

3. Use your method, when I prepare it once, then execute it on the loop.

I tested with 10, 50, and 500 and my results are pretty much the same.
5 sec for 10, 15/16 for 50, and 30 for 500.

It doesn't seem to matter which method I use, but yours seems cleaner.

Any parting thoughts on how to maybe speed this up?

Those numbers seem very slow for a db.  Is the db server load very high?  Is there other processing in your script taking a lot of time?

To be clear, you are only executing the connect and prepare statements once, then the execute a bunch of times, correct?

Yes, prep, loop { execute }

If I have it loop 500 times, it takes about 30 sec.  If I have it loop 500 times, and comment out the exectue statement, It takes 1 second.


foreach $faxItem (@faxItems)
{
$statement->execute($faxItem->value->{'SubmitTime'}, $faxItem->value->{'CompletionTime'}, $faxItem->value->{'Status'}, $faxItem->value->{'Subject'});
}

What about if you print, to determine the time to get the values from the $faxItems.

open(my $log, ">log.txt") or die "log: $!\n";
foreach $faxItem (@faxItems)
{
  print
    $faxItem->value->{'SubmitTime'},
    $faxItem->value->{'CompletionTime'},
    $faxItem->value->{'Status'},
    $faxItem->value->{'Subject'},
    "\n";
}
close($log);

Select allOpen in new window

Good thinking, but that didn't work.  It only took 1 sec with 500 loops.
This:
#$statement->execute($faxItem->value->{'SubmitTime'}, $faxItem->value->{'CompletionTime'}, $faxItem->value->{'Status'}, $faxItem->value->{'Subject'});
Was changed to:
print $faxItem->value->{'SubmitTime'}. "=" . $faxItem->value->{'CompletionTime'}. "=" . $faxItem->value->{'Status'}. "=" . $faxItem->value->{'Subject'} . "<BR>";

Sounds like you have a problem with your database server or the network connection between the web server and database server.

Or your database server is way overworked or underpowered.  What is the cpu and memory load on it?