Newbie question about do()

I'm attempting to write a PERL script to add data to a MySQL
database. I'm using DBI, which I eventually figured out how to
install (NIGHTMARE).

This code works:

$rows = $database_handle->do("INSERT dates (date, location, city, state)"
. " VALUES('2004-01-01', 'The Iron
Nail', 'Verona', 'WI')");

This code does not:

$date = "2004-01-01";
$location = "The Iron Nail";
$city = "Verona";
$state = "WI";

$rows = $database_handle->do("INSERT dates (date, location, city, state)"
. " VALUES($date, $location, $city, $state)");

It fails with "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 'Iron Nail, Verona, WI)' at
line 1 at ./my_script line 75, line 1."

Can anyone tell me why, or how I can use variables in my INSERT call?

Many thanks!

CT


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Newbie question about do()

On Tue, 23 Mar 2004, Charles Thomas wrote:

> I'm attempting to write a PERL script to add data to a MySQL
> database. I'm using DBI, which I eventually figured out how to
> install (NIGHTMARE).
>
> This code works:
>
> $rows = $database_handle->do("INSERT dates (date, location, city, state)"
> . " VALUES('2004-01-01', 'The Iron
> Nail', 'Verona', 'WI')");
>
> This code does not:
>
> $date = "2004-01-01";
> $location = "The Iron Nail";
> $city = "Verona";
> $state = "WI";
>
> $rows = $database_handle->do("INSERT dates (date, location, city, state)"
> . " VALUES($date, $location, $city, $state)");
>
> It fails with "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 'Iron Nail, Verona, WI)' at
> line 1 at ./my_script line 75, line 1."
>
> Can anyone tell me why, or how I can use variables in my INSERT call?

It is failing because the values are not being quoted, so mysql is seeing
a query that looks like:

INSERT dates (date, localtion, city, state)
VALUES (2004-01-01, The Iron Nail, Verona, WI)

The easiest way to quote the data would be to let DBD::mysql do it for
you:

my $rows = $dbh->do(q{
INSERT dates (date, loation, city, state) VALUES (?,?,?,?)}
}, {}, $date, $location, $ctiy, $state);


HTH,

Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Newbie question about do()

On Tue, 23 Mar 2004, Charles Thomas wrote:

> I'm attempting to write a PERL script to add data to a MySQL
> database. I'm using DBI, which I eventually figured out how to
> install (NIGHTMARE).
>
> This code works:
>
> $rows = $database_handle->do("INSERT dates (date, location, city, state)"
> . " VALUES('2004-01-01', 'The Iron
> Nail', 'Verona', 'WI')");
>
> This code does not:
>
> $date = "2004-01-01";
> $location = "The Iron Nail";
> $city = "Verona";
> $state = "WI";
>
> $rows = $database_handle->do("INSERT dates (date, location, city, state)"
> . " VALUES($date, $location, $city, $state)");
>
> It fails with "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 'Iron Nail, Verona, WI)' at
> line 1 at ./my_script line 75, line 1."
>
> Can anyone tell me why, or how I can use variables in my INSERT call?

It is failing because the values are not being quoted, so mysql is seeing
a query that looks like:

INSERT dates (date, localtion, city, state)
VALUES (2004-01-01, The Iron Nail, Verona, WI)

The easiest way to quote the data would be to let DBD::mysql do it for
you:

my $rows = $dbh->do(q{
INSERT dates (date, loation, city, state) VALUES (?,?,?,?)}
}, {}, $date, $location, $ctiy, $state);


HTH,

Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org