Perl MySQL INSERT question - troubleshooting

[josie23]

Egad,

I'm not a coder/programmer by nature or occupation but understand things like html and css and a small amount of perl. So, basically, I'm a perl/mysql imbecile.

But, I've been trying to find syntax to insert values into a mysql database table. I'm able to use the below syntax to insert hard-coded values like 'josie' and 'smith' but can't find working syntax to insert $scalar data from another file (which is really what perl-mysql is good for anyway... or so i thought).

Can anyone tell me why the below line of code won't insert data from the non-mysql file ("mailGroupsADP List.csv") ... into the mysql db?

$ADP_data_to_in sert = "INSERT INTO Employee(FirstN ame,LastName) VALUES($FIRST,$ LAST)";

--

I get 200 variations of the following error:

"Use of uninitialized value in concatenation (.) or string at comma_to_tab_to _db4
.pl line 22, <CSV> line 102.
DBD::mysql::st execute failed: You have an error in your SQL syntax near ')' at
line 1 at C:/Perl/site/lib/MySQL.pm line 175, <CSV> line 102."

-----------
ps: i can't even get INSERT INTO to insert scalars from a simple text file.
------------


partially redacted body of code below:

$EmployeeData = Mysql->connect($host, $db, $user, $password);
$EmployeeData->selectdb('Copy_of_Employee');
$tablename = "Employee";
$file = 'f:\\webaddress\\EmailGroupsADPList.csv';
$csv = Text::CSV->new();

$FIRST = $file[1];
$LAST = $file[0];

open (CSV, "<", $file);

while (<CSV>) {
	if ($csv->parse($_)) {
		my @columns = $csv->fields();
		$ADP_data_to_insert = "INSERT INTO Employee(FirstName,LastName) VALUES($FIRST,$LAST)";

		$query = $EmployeeData->query($ADP_data_to_insert);
	} else {
		my $err = $csv->error_input;
		print "Failed to parse line: $err";
	}
}
close CSV;

[KevinADC]

You should not be using the Mysql module any longer, you should be using DBI and the mysql drivers. Having said that I don't know if that is the cause of your problems. The error message indicates a syntax error which is not in the code you posted.

From the Mysql module:

As of March 1998, the Msql and Mysql modules are obsoleted by the DBI drivers DBD::mSQL and DBD::mysql, respectively. You are strongly encouraged to implement new code with the DBI drivers. In fact, Msql and Mysql are currently implemented as emulations on top of the DBI drivers.

[miller]

Yes, as Kevin said, you should be using DBI instead of the Mysql package.

Here's your code modified to use DBI. There appear to still be bugs in it, like the fact that $LAST and $FIRST are never set any new values. But this will be up to you to fix after you install DBI if it's not already on your system.

my $host = 'localhost'; # Set this to whatever your host is
my $db = 'Copy_of_Employee'; # Set this to whatever you database is
my $dbh = DBI->connect("dbi:mysql:$db:$host", $user, $password) or die "Database connection failed";

my $tablename = "Employee";
my $file = 'f:\\webaddress\\EmailGroupsADPList.csv';
my $csv = Text::CSV->new();

my $FIRST = $file[1];
my $LAST = $file[0];

open (CSV, "<", $file);

while (<CSV>) {
	if ($csv->parse($_)) {
		my @columns = $csv->fields();
		
		# Note how $FIRST and $LAST are never set to anything new.  Is this right?
		my $sth = $dbh->prepare(qq{INSERT INTO Employee SET FirstName=? ,LastName=?});
		$sth->execute($FIRST,$LAST) or die $dbh->errstr;

	} else {
		my $err = $csv->error_input;
		print "Failed to parse line: $err";
	}
}
close CSV;

- Miller