MySQL query doesn't work in datamapper

[robyger]

I am trying to add a database named filemanager to the MySQL database installed by PReS Connect and populate it on the fly inside the datamapper.

I have a simple query which creates the database and its table (files) it they don’t exist. The query executes with no issues in MySQL Workbench but fails in the datamapper postprecessor with following error:

JavaException: java.sql.SQLSyntaxErrorException: 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 ‘USE filemanager;
CREATE TABLE IF NOT EXISTS files (filename CHAR(20) CHARA’ at line 2

My script is as follow:

var connection = db.connect(‘jdbc:mysql://localhost:3306’, ‘root’, ‘password’);
var sql = ‘CREATE DATABASE IF NOT EXISTS filemanager;\n’;
sql += ‘USE filemanager;\n’;
sql += ‘CREATE TABLE IF NOT EXISTS files (filename VARCHAR(100)) ENGINE=INNODB;\n’;
logger.info(sql);
try {
logger.info(‘connection established’);
connection.setAutoCommit(false);
logger.info('AutoCommit: ’ + connection.getAutoCommit());
var statement = connection.prepareStatement(sql);
statement.executeUpdate();
connection.commit();
}
catch (e) {
logger.error(e);
connection.rollback();
}finally {
connection.close()
}

my query executes fine in MySQL Qorkbench

Does PReS Connect expect anything additionally in the MySQL syntax?
Can anyone please advise?

[Phil]

It looks like the jdbc driver balks at the use of the USE filemanager statement.
Try splitting your statement in two like this:

var errorEncountered = false;    
// First, create the DB if it doesn't exist
var connection = db.connect('jdbc:mysql://localhost:3306', 'root', 'password');
var sql = 'CREATE DATABASE IF NOT EXISTS filemanager;\n';
try {
	connection.setAutoCommit(false);
    var statement = connection.prepareStatement(sql);
	statement.executeUpdate();
} catch(e) {
	logger.error(e);
	connection.rollback();
    errorEncountered = true;
} finally {
	connection.close()
}

if(errorEncountered) return;

// Second, create the table if it doesn't exist
connection = db.connect('jdbc:mysql://localhost:3306/filemanager', 'root', 'password');
sql = 'CREATE TABLE IF NOT EXISTS files (filename VARCHAR(100)) ENGINE=INNODB;';
try {
	connection.setAutoCommit(false);
	statement = connection.prepareStatement(sql);
	statement.executeUpdate();
	connection.commit();
} catch (e) {
	logger.error(e);
	connection.rollback();
}finally {
	connection.close()
}

Note how the second db.connect() statement establishes a connection directly to the schema created in the preceding chunk of code, thereby eliminating the need for a USE statement…

[robyger]

Thank you Phil. I will test it but I think it will work.

[robyger]

I am testing it right now and I think the issue is down to the fact that combine multiple statement in the same query.

The files table alone is created with:

sql = 'CREATE TABLE IF NOT EXISTS files (filename VARCHAR(100)) ENGINE=INNODB;';

BUT if I attempt to create two tables in one go like this:
sql = 'CREATE TABLE IF NOT EXISTS files (filename VARCHAR(100)) ENGINE=INNODB;';
sql  += 'CREATE TABLE IF NOT EXISTS users (username VARCHAR(100)) ENGINE=INNODB;';

Then I get the same error around the second CREATE TABLE statement.

Is there a limitation in Connect, because it would be really cumbersome to send one statement at a time?

[Phil]

Google is my friend…

I checked and apparently, the jdbc driver’s connection string requires you to specify that multiple queries are allowed using the allowMultiQueries parameter. I tried the following on my system and it worked as expected:

var sql = '';
var connection = db.connect('jdbc:mysql://localhost:3306?allowMultiQueries=true', 'root', 'password');

sql += 'CREATE DATABASE IF NOT EXISTS filemanager;';
sql += 'USE filemanager;';
sql += 'CREATE TABLE IF NOT EXISTS files (filename VARCHAR(100)) ENGINE=INNODB;';
try {
	connection.setAutoCommit(false);
    var statement = connection.prepareStatement(sql);
	statement.executeUpdate();
} catch(e) {
	logger.error(e);
	connection.rollback();
} finally {
	connection.close()
}

[robyger]

You are simply the best!

[Phil]

Please send the above comment to:

• my boss
• my wife

[robyger]

I am pretty sure she knows this !

[jim3108]

Are they not the same person?

[Phil]

Not gonna risk answering that one…