Re: DBI Issue with MySQL query

Oh ***! I had the text files in the wrong directory. It works now. I am so DUMB!

Thank you all for all the suggestions and time you spent on my non-problem. I wish the error message would have been a little more instructive, however.

mark

Paul DuBois <paul@xxxxxxxxx> 11-May-06 11:01:14 AM >>>

Huh. And you say this worked before? I don't see how it could.

$table is an identifier and shouldn't be surrounded by single quotes. That
particular name doesn't need any quoting, but if it did, the quote character
should be backtick (`), not apostrophe (').

Also, you might need to say \\n rather than \n, or perhaps even \\\\n
to compensate for both Perl and MySQL stripping escape characters. But that
wouldn't result in the syntax error you're seeing, it would just make the
statement not parse data correctly.

Your statement uses $datafile, but you report the value of $filename... typo
in your message?


On 5/11/06 9:30, "Mark Galbreath" <GalbreathM@xxxxxxx> wrote:

There is no error in the log file. The actual terminal print is:

%> import_dbf.pl: DBD::mysql::db do failed: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL version for the right
syntax to use near 'MRJE001' FIELDS TERMINATED BY '|' LINES TERMINATED BY '

The actual code is:

$rows = $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE '$table'
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");

$filename = mrje001.txt
$table = MRJE001

Just is correct. There must be a bug in DBD::mysql(3)

~mark

Paul DuBois <paul@xxxxxxxxx> 11-May-06 09:29:20 AM >>>

Check the server's query log to see what statement actually is arriving on
the server side.

Also, you say you get a malformed SQL syntax error. What's the exact
message you get?


On 5/11/06 8:02, "Mark Galbreath" <GalbreathM@xxxxxxx> wrote:

Tim,

I've tried every imaginable option: single quotes, double quotes, escaped
quotes, table name only, no quotes....nothing works. I think there is a bug
in DBD::mysql(3). I have spent hours and hours googling this problem and no
one else has reported it.

~mark

Tim Bunce <Tim.Bunce@xxxxxxxxx> 11-May-06 08:40:24 AM >>>
On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
Of course it's a string. It was a typo.

my $rows = $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
FIELDS
TERMINATED BY '|'");

'db.table' == "database_name.table_name"

Don't put single quotes around the table name.

db.table
or `db`.`table`

should work, but 'db.table' won't.

Tim.

Paul DuBois <paul@xxxxxxxxx> 10-May-06 09:13:47 AM >>>

On 5/10/06 7:58, "Mark Galbreath" <GalbreathM@xxxxxxx> wrote:

Hi guys,

The following query works fine from the MySQL client:

LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY
'|'

but fails in perl with a malformed SQL syntax error:

my $rows = $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'
FIELDS
TERMINATED BY '|');

Anybody have a clue as to why this will not work?

That hasn't the faintest hope of working. You should post the actual code
that you're using. do() with a non-string argument is surely not your
actual code.

Also, 'db.table' doesn't look like a valid quoted identifier.