Multi tool use

Copying specific tables of a MySQL database to a MariaDB database in JDBC

I am trying to create a Java programm that copies specific tables from a MySQL database on one server to a MariaDB on another. I know there is mysqldump but the purpose of the program is to be running on different machines, even if they don't habe mysql installed.

What I created seems to work well, as the resulting SQL does look correct and executes for example when I connect to the MariaDB with HeidiSQL. But when I execute the sql using .executeQuery, I get an error as response that there is a problem with my SQL.

.executeQuery

The code is basically:

import java.sql.*

public class JdbcTest {
Connection conn = null;
Connection conn_2 = null;
Statement stmt = null;
Statement stmt_2 = null;

//MySQL
Class.forName("com.mysql.cj.jdbc.Driver");
//MariaDB
Class.forName("org.mariadb.jdbc.Driver");

System.out.println("Connecting to DB1");
conn = DriverManager.getConnection(jdbc:mysql://server1/database,USER,PASS);

System.out.println("Connecting to DB2");
conn_2 = DriverManager.getConnection(jdbc:mariadb://server2/database,USER_2,PASS_2);

List<String> ConnToTableNames = new ArrayList<String>();
ConnToTableNames.add("table1");
ConnToTableNames.add("table2");

//Create queries for all tables in ConnToTableNames
stmt = conn.createStatement();
String exportSql = "";
for(String table : ConnToTableNames) {
//getting table structure
exportSql += "n" +
"-- ----------------------------n" +
"-- Table structure for `" + table+
"`n-- ----------------------------n"+
"DROP TABLE IF EXISTS `"+table+"`;n";
ResultSet rs2 = stmt.executeQuery("SHOW CREATE TABLE `" + table + "`");
rs2.next();
exportSql += rs2.getString(2) + ";n" +
"-- ----------------------------n";
rs2.close();

//adding the content
ResultSet rss = stmt.executeQuery("select * from "+table);
while (rss.next()) {
int colCount = rss.getMetaData().getColumnCount();
if (colCount > 0) {
exportSql += "INSERT INTO `" + table +"` VALUES(";

for (int i = 0; i < colCount; i++) {
if (i > 0) {
exportSql += ",";
}
String s = "";
try {
s += "'";
s += rss.getObject(i + 1).toString();
s += "'";
} catch (Exception e) {
s = "NULL";
}
exportSql += s;
}
exportSql += ");n";
}
}
rss.close();
}

//putting everything into the VM
stmt_2 = conn_2.createStatement();
stmt_2.executeQuery(exportSql);

stmt.close();
conn.close();
stmt_2.close();
conn_2.close();
}


and the error that I get is:

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE `supplier` (
`NUMMER` int(10) NOT NULL DEFAULT '0',
`PRIO` int(' at line 5


What really confuses me is: If I dump exportSql to the terminal or a file and execute that via HeidiSQL it works...

exportSql

An ideas what I did wrong?

Thanks in advance and have a great weekend

executeQuery()

?allowMultiQueries=true

1 Answer
1

To solve my problem I added ?allowMultiQueries=true to the connection string and used execute() instead of executeQuery().

?allowMultiQueries=true

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.