Comments inline below.
On Wednesday 09 January 2002 18:13, Paul DuBois wrote:
> At 17:23 -0800 1/9/02, Kyle Hayes wrote:
[snipped replication explanation]
> >The program works by getting the contents of the update log and using
> > the Perl DBI do() function on each statement.
> >
> >The problem that occurs causes this (debug output):
> >
> > SQL: use acc;
> >
> > DBD::mysql::db do failed: You have an error in your SQL syntax near
> > '; ' at line 1 at /opt/bin/replicator_client line 199, <GEN2> line 4.
> >
> >I have found that this works _IFF_ I strip off the trailing linefeed
> > after the semicolon at the end of the statement. I could swear that
> > we create SQL statement in Perl "here docs" that end with a semicolon
> > and several empty lines. Is there a DBI guru that can tell me what is
> > going on? I really notice this especially in "USE" statements...
>
> Don't add the semicolon. That's for when you use the mysql client,
> which you aren't doing.
Hmm, most of our SQL is generated with the semicolon. It definitely works
with the semicolon. I print out what I send to DBI->do() and it has a
semicolon.
> The error message is complaining about the semicolon, by the way,
> not the linefeed.
As far as I can tell, this is not true. Here's the main loop that works
(changed slightly to protect the innocent :-):
$input_line = <$input>;
if($input_line =~ m/^\;$/) {
# OK, we have the end of a SQL statement.
# remove trailing newline
chomp($input_line);
# add this to the rest of the SQL
$sql .= $input_line;
# run it through the database
if(length($sql)>0 ) {
# use eval because we want to catch errors.
eval { $rc = $dbh->do($sql); };
} else {
$rc = 1;
}
# did we have a problem?
if(!defined($rc) or $@) {
# bad news, we have a crash problem.
... print out stuff for the error log file...
exit(1);
}
# SQL successfully handled, reset the SQL accumulation variable.
$sql = '';
} else {
# no match, still part of the SQL statement. Strip comments.
if($input_line !~ m/^\s*\#/) {
$sql .= $input_line;
}
}
Obviously, you need to wrap this in a loop, but this is what is working
now. I have added debugging printouts at various places above and can
state that the SQL that goes through DBI does have a trailing semicolon,
but not a trailing newline. Really weird.
Best,
Kyle
--
Cut costs, Fax smart. Use iPrint2Fax worldwide and save!
- iPrint2Fax software is FREE, no fax modem required.
- FREE iPrint2Fax to a single email address.
- iPrint2Fax to PSTN based Fax (Up to 95% Savings)
- iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails
in the time it takes to send just one!
==========================================
FREE software download available at www.iPrint2Fax.com
==========================================
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
