By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,276 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,276 IT Pros & Developers. It's quick & easy.

Perl MySQL INSERT question - troubleshooting

josie23
P: 1
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 ("mailGroupsADPList.csv") ... into the mysql db?

$ADP_data_to_insert = "INSERT INTO Employee(FirstName,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:

Expand|Select|Wrap|Line Numbers
  1. $EmployeeData = Mysql->connect($host, $db, $user, $password);
  2. $EmployeeData->selectdb('Copy_of_Employee');
  3. $tablename = "Employee";
  4. $file = 'f:\\webaddress\\EmailGroupsADPList.csv';
  5. $csv = Text::CSV->new();
  6.  
  7. $FIRST = $file[1];
  8. $LAST = $file[0];
  9.  
  10. open (CSV, "<", $file);
  11.  
  12. while (<CSV>) {
  13.     if ($csv->parse($_)) {
  14.         my @columns = $csv->fields();
  15.         $ADP_data_to_insert = "INSERT INTO Employee(FirstName,LastName) VALUES($FIRST,$LAST)";
  16.  
  17.         $query = $EmployeeData->query($ADP_data_to_insert);
  18.     } else {
  19.         my $err = $csv->error_input;
  20.         print "Failed to parse line: $err";
  21.     }
  22. }
  23. close CSV;
  24.  
Apr 13 '07 #1
Share this Question
Share on Google+
2 Replies


KevinADC
Expert 2.5K+
P: 4,059
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.
Apr 14 '07 #2

miller
Expert 100+
P: 1,089
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.

Expand|Select|Wrap|Line Numbers
  1. my $host = 'localhost'; # Set this to whatever your host is
  2. my $db = 'Copy_of_Employee'; # Set this to whatever you database is
  3. my $dbh = DBI->connect("dbi:mysql:$db:$host", $user, $password) or die "Database connection failed";
  4.  
  5. my $tablename = "Employee";
  6. my $file = 'f:\\webaddress\\EmailGroupsADPList.csv';
  7. my $csv = Text::CSV->new();
  8.  
  9. my $FIRST = $file[1];
  10. my $LAST = $file[0];
  11.  
  12. open (CSV, "<", $file);
  13.  
  14. while (<CSV>) {
  15.     if ($csv->parse($_)) {
  16.         my @columns = $csv->fields();
  17.  
  18.         # Note how $FIRST and $LAST are never set to anything new.  Is this right?
  19.         my $sth = $dbh->prepare(qq{INSERT INTO Employee SET FirstName=? ,LastName=?});
  20.         $sth->execute($FIRST,$LAST) or die $dbh->errstr;
  21.  
  22.     } else {
  23.         my $err = $csv->error_input;
  24.         print "Failed to parse line: $err";
  25.     }
  26. }
  27. close CSV;
  28.  
- Miller
Apr 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.