DBQuery errors on a multi-statement query. ErrorMsg() returns that there is an error in the syntax for the query, specifically the beginning of the 2nd statement.
Numbered Steps to Reproduce Problem:
Create a MySQL database called TestDB on localhost.
Create a user called DMTest with the password of testing with create privileges on that database.
Make a new DBConnection to the database and a new DBQuery from the connection and a multi-statement sql statement. Execute the query and use DBQuery.ErrorMsg() to read the error returned. I suggest using/modifying the snippet below.
Code Snippet (if applicable) to Reproduce Problem:
mob/verb/DBTest()
var/DBConnection/DBCon = new("dbi:mysql:TestDB:localhost:3306","DMTest","testing")
//change TestDB to your DB name, localhost to the server name, and 3306 to your server's port
//change DMTest to a user with create permissions on the DB, and testing to that user's password
if(!DBCon.IsConnected()) DBCon.Connect()
var/DBQuery/query = DBCon.NewQuery({"
DROP TABLE IF EXISTS `TestDB`.`TestTable`;
CREATE TABLE `TestDB`.`TestTable` (
`field1` INT(10),
`field2` VARCHAR(255),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;"})
query.Execute()
usr<<query.ErrorMsg()
Expected Results:
Successful execution of the query.
Actual Results:
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 'CREATE TABLE `TestDB`.`TestTable` (
`field1` INT(10),
`field2` VARCHAR(25' at line 2
Does the problem occur:
Every time, without fail.
When does the problem NOT occur?
Never, without workarounds (see blow).
Workarounds:
I have started pre-parsing my queries and generating new DBQuery objects for each statement with the assumption the the ";" character signifies the end of a statement.
-------------
The query will work fine if run through the MySQL Query Browser. I have not tried the command line but I imagine that if I were to save the query to a file and try it would work just fine. I don't know if DM's built in DB handling strips queries of the ";" character or what. It seems the most logical conclusion based on the present evidence and the error message returned. The only way that the query would work in a usual MySQL environment but give the syntax error for DM is if DM is changing the syntax in some way. Perhaps this is done because of the way ErrorMsg() handles the error reports?
