kurt2439 has asked for the wisdom of the Perl Monks concerning the following question:
Hi Monks,
I'm having a problem restoring a database on a remote SQL server with the DBI module. I drop/add my database to clear out the tables but when I try and run this:
my $db_load_file='/home/jchase/testdb.sql'; my $sth=$dbh->prepare("source $db_load_file"); $sth->execute;
I get the following error:
DBD::mysql::st execute 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 'source /home/jchase/testdb.sql' at line 1 at sync_mumoodle.pl line 31. DBD::mysql::st execute 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 'source /home/jchase/testdb.sql' at line 1 at sync_mumoodle.pl line 31.
Not sure why the error gets thrown twice. The db_load_file exists on both the SQL server and the host I am running the perl command from. I wasn't sure how MySQL would interpret the file so I just put it in both places in the same file location to make sure that wasn't the problem.
Does this not work because DBI doesn't really encapsulate all the commands of the mysql command line? That would make sense but then how can I accomplish this without scp'ing the file over and running a system command via ssh?