DBI Issue with MySQL query

--=__Part391C1197.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Hi guys,
=20
The following query works fine from the MySQL client:
=20
LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED BY =
'|'
=20
but fails in perl with a malformed SQL syntax error:
=20
my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' =
FIELDS TERMINATED BY '|');
=20
Anybody have a clue as to why this will not work?
=20
tia,
mark


--=__Part391C1197.0__=--

Re: DBI Issue with MySQL query

On 5/10/06 7:58, "Mark Galbreath" 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.

Re: DBI Issue with MySQL query

--=__PartAB8E830C.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Of course it's a string. It was a typo.

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

>>> Paul DuBois 10-May-06 09:13:47 AM >>>

On 5/10/06 7:58, "Mark Galbreath" wrote:

> Hi guys,
> =20
> The following query works fine from the MySQL client:
> =20
> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED =
BY '|'
> =20
> but fails in perl with a malformed SQL syntax error:
> =20
> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' =
FIELDS
> TERMINATED BY '|');
> =20
> 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.





--=__PartAB8E830C.0__=--

Re: DBI Issue with MySQL query

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 10-May-06 09:13:47 AM >>>
>
> On 5/10/06 7:58, "Mark Galbreath" 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.
>
>
>
>

Re: DBI Issue with MySQL query

--=__PartBB9E91C1.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Tim,
=20
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.
=20
~mark

>>> Tim Bunce 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.
>=20
> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' =
FIELDS TERMINATED BY '|'");
> =20
> '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 10-May-06 09:13:47 AM >>>
>=20
> On 5/10/06 7:58, "Mark Galbreath" wrote:
>=20
> > Hi guys,
> > =20
> > The following query works fine from the MySQL client:
> > =20
> > LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED=
BY '|'
> > =20
> > but fails in perl with a malformed SQL syntax error:
> > =20
> > my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=
FIELDS
> > TERMINATED BY '|');
> > =20
> > Anybody have a clue as to why this will not work?
>=20
> 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.
>=20
> Also, 'db.table' doesn't look like a valid quoted identifier.
>=20
>=20
>=20
>=20



--=__PartBB9E91C1.0__=--

Re: DBI Issue with MySQL query

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" 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 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 10-May-06 09:13:47 AM >>>
>>
>> On 5/10/06 7:58, "Mark Galbreath" 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.
>>
>>
>>
>>
>

Re: DBI Issue with MySQL query

use DBI;
my $dbh = DBI->connect('dbi:mysql:test','xxx','yyy');
$dbh->do(q/load data infile 'data.txt' into table fred fields terminated by
'|'/);

works for me with mysql client 5.0.15 and dbd::mysql 3.003_1.

You do of course need special privileges to use load data.

What exactly are you using perl,mysql,dbi-wise, can you paste the exact code
you are using and the exact error you are getting.

What does a DBI trace of 3 or above output?

As others have said, don't put single quotes around your catalog and table or
you'll get an error like:

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 ''fred'' at line 1 at c.pl line 3.

You can use ` though but you need use them around the catalog and table
separately.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


On 11-May-2006 Mark Galbreath 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 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 10-May-06 09:13:47 AM >>>
>>
>> On 5/10/06 7:58, "Mark Galbreath" 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.
>>
>>
>>
>>
>
>

Re: DBI Issue with MySQL query

Ah. I believe this is the first time you've mentioned that it might have
something to do with the version of DBD::mysql. What's the last version you
used for which it worked, and the version that you're using now? That's
pertinent information; omitting it makes it more difficult for people to
help you.



On 5/11/06 9:12, "Mark Galbreath" wrote:

> Yes. And this code works with previous versions of DBD::mysql.
>
>
>>>> Paul DuBois 11-May-06 09:31:06 AM >>>
> None of those quoting options are valid for quoting _identifiers_.
> Did you try using backticks?
>
> http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
>
>
> On 5/11/06 8:02, "Mark Galbreath" 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 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 10-May-06 09:13:47 AM >>>
>>>
>>> On 5/10/06 7:58, "Mark Galbreath" 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.
>>>
>>>
>>>
>>>
>>
>>
>
>
>

Re: DBI Issue with MySQL query

--=__Part9BBEB19E.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

There is no error in the log file. The actual terminal print is:
=20
%> 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 '
=20
The actual code is:
=20
$rows =3D $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE =
'$table' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");
=20
$filename =3D mrje001.txt
$table =3D MRJE001
=20
Just is correct. There must be a bug in DBD::mysql(3)
=20
~mark

>>> Paul DuBois 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" wrote:

> Tim,
> =20
> 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.
> =20
> ~mark
>=20
>>>> Tim Bunce 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.
>>=20
>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=
FIELDS
>> TERMINATED BY '|'");
>> =20
>> 'db.table' == "database_name.table_name"
>=20
> Don't put single quotes around the table name.
>=20
> db.table
> or `db`.`table`
>=20
> should work, but 'db.table' won't.
>=20
> Tim.
>=20
>>>>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>=20
>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>=20
>>> Hi guys,
>>> =20
>>> The following query works fine from the MySQL client:
>>> =20
>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED=
BY
>>> '|'
>>> =20
>>> but fails in perl with a malformed SQL syntax error:
>>> =20
>>> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=
FIELDS
>>> TERMINATED BY '|');
>>> =20
>>> Anybody have a clue as to why this will not work?
>>=20
>> 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.
>>=20
>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>=20
>>=20
>>=20
>>=20
>=20






--=__Part9BBEB19E.0__=--

Re: DBI Issue with MySQL query

--=__PartA7828DA4.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Sorry - of course, you are right.
=20
Red Hat Linux Workstation 4.0 on Intel Pentium 4
Perl 5.8.5
DBI 1.50 (Dec 2005)
DBD::mysql 3.0003
=20
Previous version DBD::mysql 2.9008 (Jun 2005)

>>> Paul DuBois 11-May-06 10:22:10 AM >>>

Ah. I believe this is the first time you've mentioned that it might have
something to do with the version of DBD::mysql. What's the last version =
you
used for which it worked, and the version that you're using now? That's
pertinent information; omitting it makes it more difficult for people to
help you.



On 5/11/06 9:12, "Mark Galbreath" wrote:

> Yes. And this code works with previous versions of DBD::mysql.
>=20
>=20
>>>> Paul DuBois 11-May-06 09:31:06 AM >>>
> None of those quoting options are valid for quoting _identifiers_.
> Did you try using backticks?
>=20
> http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
>=20
>=20
> On 5/11/06 8:02, "Mark Galbreath" wrote:
>=20
>> Tim,
>> =20
>> 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.
>> =20
>> ~mark
>>=20
>>>>> Tim Bunce 11-May-06 08:40:24 AM >>>
>>=20
>> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>>> Of course it's a string. It was a typo.
>>>=20
>>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>> FIELDS
>>> TERMINATED BY '|'");
>>> =20
>>> 'db.table' == "database_name.table_name"
>>=20
>> Don't put single quotes around the table name.
>>=20
>> db.table
>> or `db`.`table`
>>=20
>> should work, but 'db.table' won't.
>>=20
>> Tim.
>>=20
>>>>>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>>=20
>>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>>=20
>>>> Hi guys,
>>>> =20
>>>> The following query works fine from the MySQL client:
>>>> =20
>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS =
TERMINATED BY
>>>> '|'
>>>> =20
>>>> but fails in perl with a malformed SQL syntax error:
>>>> =20
>>>> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|');
>>>> =20
>>>> Anybody have a clue as to why this will not work?
>>>=20
>>> 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.
>>>=20
>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>=20
>>>=20
>>>=20
>>>=20
>>=20
>>=20
>=20
>=20
>=20






--=__PartA7828DA4.0__=--

Re: DBI Issue with MySQL query

--=__Part16333C16.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Not here :-(
=20
As a seque, Paul's MySQL book has the file and delimiter in double =
quotes....
=20
~m

>>> "Martin J. Evans" 11-May-06 10:15:26 AM =
>>>

use DBI;
my $dbh =3D DBI->connect('dbi:mysql:test','xxx','yyy');
$dbh->do(q/load data infile 'data.txt' into table fred fields terminated =
by
'|'/);

works for me with mysql client 5.0.15 and dbd::mysql 3.003_1.

You do of course need special privileges to use load data.

What exactly are you using perl,mysql,dbi-wise, can you paste the exact =
code
you are using and the exact error you are getting.

What does a DBI trace of 3 or above output?

As others have said, don't put single quotes around your catalog and table =
or
you'll get an error like:

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 ''fred'' at line 1 at c.pl line 3.

You can use ` though but you need use them around the catalog and table
separately.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


On 11-May-2006 Mark Galbreath wrote:
> Tim,
> =20
> 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.
> =20
> ~mark
>=20
>>>> Tim Bunce 11-May-06 08:40:24 AM >>>
>=20
> On Wed, May 10, 2006 at 09:26:52AM -0400, Mark Galbreath wrote:
>> Of course it's a string. It was a typo.
>>=20
>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=

>> FIELDS TERMINATED BY '|'");
>> =20
>> 'db.table' == "database_name.table_name"
>=20
> Don't put single quotes around the table name.
>=20
> db.table
> or `db`.`table`
>=20
> should work, but 'db.table' won't.
>=20
> Tim.
>=20
>> >>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>=20
>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>=20
>> > Hi guys,
>> > =20
>> > The following query works fine from the MySQL client:
>> > =20
>> > LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS =
TERMINATED BY
>> > '|'
>> > =20
>> > but fails in perl with a malformed SQL syntax error:
>> > =20
>> > my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>> > FIELDS
>> > TERMINATED BY '|');
>> > =20
>> > Anybody have a clue as to why this will not work?
>>=20
>> 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.
>>=20
>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>=20
>>=20
>>=20
>>=20
>=20
>=20



--=__Part16333C16.1__=--

Re: DBI Issue with MySQL query

--=__PartD5F0FFD5.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Actually, $table is not enclosed in quotes. And Paul corrected the quote =
syntax in the second edition (I was using the first until just now). :-)


>>> "Mark Galbreath" 11-May-06 10:30:38 AM >>>

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 =3D $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE =
'$table' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");

$filename =3D mrje001.txt
$table =3D MRJE001

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

~mark

>>> Paul DuBois 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" wrote:

> Tim,
> =20
> 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.
> =20
> ~mark
>=20
>>>> Tim Bunce 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.
>>=20
>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=
FIELDS
>> TERMINATED BY '|'");
>> =20
>> 'db.table' == "database_name.table_name"
>=20
> Don't put single quotes around the table name.
>=20
> db.table
> or `db`.`table`
>=20
> should work, but 'db.table' won't.
>=20
> Tim.
>=20
>>>>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>=20
>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>=20
>>> Hi guys,
>>> =20
>>> The following query works fine from the MySQL client:
>>> =20
>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS TERMINATED=
BY
>>> '|'
>>> =20
>>> but fails in perl with a malformed SQL syntax error:
>>> =20
>>> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table'=
FIELDS
>>> TERMINATED BY '|');
>>> =20
>>> Anybody have a clue as to why this will not work?
>>=20
>> 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.
>>=20
>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>=20
>>=20
>>=20
>>=20
>=20








--=__PartD5F0FFD5.0__=--

Re: DBI Issue with MySQL query

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" 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 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" 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 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 10-May-06 09:13:47 AM >>>
>>>
>>> On 5/10/06 7:58, "Mark Galbreath" 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.
>>>
>>>
>>>
>>>
>>
>
>
>
>
>

Re: DBI Issue with MySQL query

--=__Part0D282711.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Oh SHIT! I had the text files in the wrong directory. It works now. I =
am so DUMB!
=20
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.
=20
mark

>>> Paul DuBois 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" wrote:

> There is no error in the log file. The actual terminal print is:
> =20
> %> 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 '
> =20
> The actual code is:
> =20
> $rows =3D $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE =
'$table'
> FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");
> =20
> $filename =3D mrje001.txt
> $table =3D MRJE001
> =20
> Just is correct. There must be a bug in DBD::mysql(3)
> =20
> ~mark
>=20
>>>> Paul DuBois 11-May-06 09:29:20 AM >>>
>=20
> Check the server's query log to see what statement actually is arriving =
on
> the server side.
>=20
> Also, you say you get a malformed SQL syntax error. What's the exact
> message you get?
>=20
>=20
> On 5/11/06 8:02, "Mark Galbreath" wrote:
>=20
>> Tim,
>> =20
>> 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.
>> =20
>> ~mark
>>=20
>>>>> Tim Bunce 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.
>>>=20
>>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>> FIELDS
>>> TERMINATED BY '|'");
>>> =20
>>> 'db.table' == "database_name.table_name"
>>=20
>> Don't put single quotes around the table name.
>>=20
>> db.table
>> or `db`.`table`
>>=20
>> should work, but 'db.table' won't.
>>=20
>> Tim.
>>=20
>>>>>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>>=20
>>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>>=20
>>>> Hi guys,
>>>> =20
>>>> The following query works fine from the MySQL client:
>>>> =20
>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS =
TERMINATED BY
>>>> '|'
>>>> =20
>>>> but fails in perl with a malformed SQL syntax error:
>>>> =20
>>>> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|');
>>>> =20
>>>> Anybody have a clue as to why this will not work?
>>>=20
>>> 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.
>>>=20
>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>=20
>>>=20
>>>=20
>>>=20
>>=20
>=20
>=20
>=20
>=20
>=20





--=__Part0D282711.0__=--

Re: DBI Issue with MySQL query

On 5/11/06 10:06, "Mark Galbreath" wrote:

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

Ah, well. Join the club. :-)


>
> 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 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" 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 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" 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 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 10-May-06 09:13:47 AM >>>
>>>>
>>>> On 5/10/06 7:58, "Mark Galbreath" 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.
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>>
>
>
>

Re: DBI Issue with MySQL query

--=__Part9ABFB080.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

On a brighter note, I just ordered your 3rd edition of "MySQL." You are =
an excellent author.
=20
~mark

>>> Paul DuBois 11-May-06 11:11:44 AM >>>

On 5/11/06 10:06, "Mark Galbreath" wrote:

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

Ah, well. Join the club. :-)


> =20
> Thank you all for all the suggestions and time you spent on my non-proble=
m. I
> wish the error message would have been a little more instructive, =
however.
> =20
> mark
>=20
>>>> Paul DuBois 11-May-06 11:01:14 AM >>>
> Huh. And you say this worked before? I don't see how it could.
>=20
> $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 (').
>=20
> 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.
>=20
> Your statement uses $datafile, but you report the value of $filename... =
typo
> in your message?
>=20
>=20
> On 5/11/06 9:30, "Mark Galbreath" wrote:
>=20
>> There is no error in the log file. The actual terminal print is:
>> =20
>> %> 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 '
>> =20
>> The actual code is:
>> =20
>> $rows =3D $dbh->do("LOAD DATA INFILE '$datafile' IGNORE INTO TABLE =
'$table'
>> FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'");
>> =20
>> $filename =3D mrje001.txt
>> $table =3D MRJE001
>> =20
>> Just is correct. There must be a bug in DBD::mysql(3)
>> =20
>> ~mark
>>=20
>>>>> Paul DuBois 11-May-06 09:29:20 AM >>>
>>=20
>> Check the server's query log to see what statement actually is arriving =
on
>> the server side.
>>=20
>> Also, you say you get a malformed SQL syntax error. What's the exact
>> message you get?
>>=20
>>=20
>> On 5/11/06 8:02, "Mark Galbreath" wrote:
>>=20
>>> Tim,
>>> =20
>>> 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.
>>> =20
>>> ~mark
>>>=20
>>>>>> Tim Bunce 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.
>>>>=20
>>>> my $rows =3D $dbh->do("LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>>> FIELDS
>>>> TERMINATED BY '|'");
>>>> =20
>>>> 'db.table' == "database_name.table_name"
>>>=20
>>> Don't put single quotes around the table name.
>>>=20
>>> db.table
>>> or `db`.`table`
>>>=20
>>> should work, but 'db.table' won't.
>>>=20
>>> Tim.
>>>=20
>>>>>>> Paul DuBois 10-May-06 09:13:47 AM >>>
>>>>=20
>>>> On 5/10/06 7:58, "Mark Galbreath" wrote:
>>>>=20
>>>>> Hi guys,
>>>>> =20
>>>>> The following query works fine from the MySQL client:
>>>>> =20
>>>>> LOAD DATA INFILE 'data.txt' INTO TABLE 'db.table' FIELDS =
TERMINATED BY
>>>>> '|'
>>>>> =20
>>>>> but fails in perl with a malformed SQL syntax error:
>>>>> =20
>>>>> my $rows =3D $dbh->do(LOAD DATA INFILE 'data.txt' INTO TABLE =
'db.table'
>>>>> FIELDS
>>>>> TERMINATED BY '|');
>>>>> =20
>>>>> Anybody have a clue as to why this will not work?
>>>>=20
>>>> 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.
>>>>=20
>>>> Also, 'db.table' doesn't look like a valid quoted identifier.
>>>>=20
>>>>=20
>>>>=20
>>>>=20
>>>=20
>>=20
>>=20
>>=20
>>=20
>>=20
>=20
>=20
>=20






--=__Part9ABFB080.0__=--