Pentaho PDI - Running multiple SQL statements

As you may already know, you can use Exec SQL step to execute multiple SQL statements either once during the entire transformation run or once per row.

But...

I was bitten by this issue and ended up wasting couple of hours trying to debug the cause. The reason... Error is really amgious, if you have multiple "syntactically correct" SQL statements separated by commas and try to execute it with default settings on the Connector/J and MySQL as DB you get
"You have an error in your SQL statement, Check the syntax..." error.
Checked my SQL statements several times and individually in SQL Query browser to make sure they were correct.

There are a couple of things you should note
1. In the debug log you will see an old log saying executing DDL statement when it actually can execute both DDL and DML statements. So you will need to ignore that log
2. Not all the drivers/databases allow running multiple comma separated SQL statements due to SQL Injection attach. Check the driver/db to make sure it allows execution of multiple SQL statements separated by commas in one shot.

For MySQL and Connector/J by default does not allow this, so to enable it you will have to add an option in your Database Connections dialog -> Options tab and add the parameter allowMultiQueries with the value of true.

3. Also make sure that you surround ?'s in your SQL statements with single quotes if you are passing in string values. If you are like me, you tend to think of the statement as your typical prepared statement in JDBC and using JDBC api to set the values so you dont need to worry about wrapping strings with quotes.

Hope that helps.