Mysql: How to use SQL file as query source

[EdoardoPedrotti]

I'm trying to execute a .sql script with the source command. This is my code:

function sourceDump(callback) {
        console.log('   Using totem database.');
        dbConn.query('use totem;', function(err, rows, fields) {
            if (err) {
                console.log(err);
                console.log('   Unable to change database.')
                callback();
                return;
            }
            console.log('   Loading dump file...');
            var path = '/home/lounge3/TotemUpdateServer/data/dump/totem.sql';
            dbConn.query('source /home/lounge3/TotemUpdateServer/data/dump/totem.sql;', function (err, rows, fields) {
                if (err) {
                    console.log(err);
                    console.log('   Unable to execute script.');
                    callback();
                    return;
                }
                console.log('   Dump executed.');
                callback();
            });
        });

The same command works fine in the mysql command line. When i try to use it with node.js and the mysql module I get this error:

{ [Error: ER_PARSE_ERROR: 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/lounge3/TotemUpdateServer/data/dump/totem.sql' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

[dougwilson]

"source" is not a MySQL command, but rather a comment that is specific to the mysql command like program (http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html)

To execute a SQL script using this library, you will need to read the file into node as a string and then send that string as a query. You will want to also enable multi statements. I'll have a follow up comment with an example.

[dougwilson]

Simple example:

var fs = require('fs');
var mysql = require('mysql');

var connection = mysql.createConnection({
  multipleStatements: true, // because your file probably contains multiple statements
  // your settings here
});

var source = fs.readFileSync('/home/lounge3/TotemUpdateServer/data/dump/totem.sql', 'utf8');

connection.query(source, function(err){
  if (err) throw err;
  console.log('done!');
  connection.end();
});

[EdoardoPedrotti]

Thank you! That really helped!