You are sending multiple statements to MySQL and it seems that it does not like this. I often use $/ = ");\n" to read in a MySQL dump and feed it to a database statement by statement with little overhead.

... near ';
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 S' at line 7 at resultsDBsource.pl line 34, <FH> chunk 1.
[download]

(UPDATE: I should have checked the mysql manual first; C-style comments are allowed, and comments starting with /*! are "special": not ignored, but used to flag statements that use mysql-specific extensions to SQL, and the digits following /*! indicate what (earliest) version of mysql is needed for the extended statement to work. What do you know about the version of the server you are using?)

Rather than reading the whole file as a into a single scalar, you should use the idea mentioned above, setting $/ to the string that marks the end of an sql statement, and read the file one statement at a time, and -- this is important -- on each statement, check for "/*commented strings*/" and remove them before preparing the statement.

If you are trying to load a database on a server where you do not have login shell access, you might want to have mysql running on your own machine, and try loading the dump file locally via a shell command line:

  mysql test < dump.file
[download]

(If you are able to connect to the target database from anywhere via a login-shell "mysql" command, why not just load the dump file that way?)

Hi,
I don't have access to the target DB machine.
I have to automate the perl script, the script will search for ".sql" files and those files have to updated to DB.

I still prefer to use mysql command line for this task. If you think you have to use a perl sript, then I assume you have remote access to the DB server, in which case you can also use the mysql client from your host.

SQL files (.sql) are meant to be executed in batch mode, each statement is terminated with a semicolon (";"). With DBI, you execute query one statement at a time and semicolons are disliked :-)

Finally, if you still need a perl script to automate DB updating (still assuming you the remote access), this might help (sorry, I don't bother to test it).

#!/usr/bin/perl
use strict;
use warnings;

my $sql_file = '/path/to/sql/file.sql';
exit 0 # silently
  unless -r $sql_file;

my $user = 'user';
my $pass = 'pass';
my $db = 'somedb';

system "mysql -u$user -p$pass $somedb < $sql_file"
  and die "can't update $db\n";
[download]

Also, you may want to consider about DB backup and/or hotcopy, as both facilities are provided by mysql, AFAIK.

---

Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

You can always do a system "mysql $connection_data < $sql_file" (assuming that the mysql client is installed on the machine where the script is running)

Use the command-line interface and check out the command in your $sql variable. Once you get it working there, then try it with your code.

I can't be any more specific, because the error message indicates a problem in your SQL which you haven't bothered to provide.

...roboticus