Re: batch update

From: Ian Harisay (imharisa_at_nuskin.com)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 11:21:56 -0600
To: <dbi-users@perl.org>

If you want to use placeholders rewrite your code this way.
 
my $sql = 'UPDATE IGNORE ordes SET activation_date=?, state=? WHERE
vo_nr = ? AND orders_id = ?';
 
foreach my $row (@$array_ref) {
  $dbh->do($sql, undef, ($row->{activation_date}, $row->{state},
$row->{vo_nr}, $row->{orders_id})) || die $dbh->errstr();
}
 
I added the die statement just as an example that you should test for
errors. You can also capture the result if it is needed. Oh, and
sorry, I didn't see that you did note where line 388. Just doing this
may solve your problem.
 
>>>Ian Harisay <imharisa@nuskin.com> 09/10 11:11 am >>>
 
Which line is 388? Also, you would be much better off using
 
placeholders.
 
 
>>>xaver biton <xwvwe@callwayonline.de> 09/10 5:56 am >>>
 
 
Hi,
 
 
I'want to write a script which update a DB in Internet.
 
 
from thr local DB I do a select query and while the select query run
 
 
foreach row I do a UPDATE query:
 
 
 
$sth_orders_local->execute($lasttime, $starttime) or die couldn't
 
 
execute query orders remote: $DBI::errstr; # fetch the local table
 
 
 
 
my $array_ref = $sth_orders_local->fetchall_arrayref( { orders_id => 1,
 
 
vo_nr => 1, activation_date => 1, state => 1 } );
 
 
 
foreach my $row (@$array_ref) {
 
 
 
my $sql = <<EOT;
 
 
UPDATE IGNORE orders SET activation_date = $row->{activation_date},
 
 
state = $row->{state}
 
 
WHERE vo_nr = $row->{vo_nr} AND orders_id =$row->{orders_id}
 
 
EOT
 
 
$dbh->do($sql); #line 388
 
 
}
 
 
 
if I use a print instead of the sql update query everything work fine
 
 
print \ttest1 $row->{orders_id}, test2 $row->{vo_nr}, test3
 
 
$row->{activation_date}, test4 $row->{state}\n;
 
 
 
but with the sql update statment I recieve the following erorr:
 
 
Use of uninitialized value in concatenation (.) or string at
 
 
proe-balance.pl line 388.
 
 
DBD::mysql::db do failed: You have an error in your SQL syntax. Check
 
 
the manual that corresponds to your MySQL server version for the right
 
 
syntax to use near 'WHERE vo_nr = 012345 AND orders_id =100002012345' at

 
 
 
line 2 at proe-balance.pl line 388.
 
 
DBD::mysql::db do failed: You have an error in your SQL syntax. Check
 
 
the manual that corresponds to your MySQL server version for the right
 
 
syntax to use near 'WHERE vo_nr = 012345 AND orders_id =100002012345' at

 
 
 
line 2 at proe-balance.pl line 388.
 
 
Process perl exited with code 2
 
 
 
Please help I'm disperate
 
 
 
Thks
 
 
 
Xaver Biton

Relevant Pages

  • Re: Iterative loop through SQL expression
    ... Allan: ... I was able to accomplish this with help from your book, "Pro SQL 2005 IS", ... with an Execute SQL Task, a Script Task, and the ForEach ... I've tried to set up the ForEach Loop ...
    (microsoft.public.sqlserver.dts)
  • Re: making this query run faster?
    ... foreach { ... From your query, it's not clear why you need to join 'affiliates', all info you need is already in 'orders' table. ... Ask sql questions in sql group. ...
    (comp.lang.php)
  • Re: dataset row count problem PPC - SQLCE
    ... I do a lot of CF projects with SQL CE, so I have a single reusable ... interact with the database. ... private bool _authenticateUser(string userName, string password) ... foreach ...
    (microsoft.public.sqlserver.ce)
  • Re: Using a variable from a foreach loop task (SQL Server 2005)
    ... Why are you using an ADO.NET connection manager? ... If you are going to SQL Server then there are claims this is a lot slower than the OLEDB equivalent. ... database are being copied though. ... 2)The next task is a ForEach Loop Container, ...
    (microsoft.public.sqlserver.dts)
  • Re: cat *.txt > .txt...........roy
    ... > #Prepare the insert SQL ... That is not the way to write a `foreach` loop. ... Chris Devers ...
    (perl.beginners)
Loading