Multi-statements are not supported

[nomad-software]

It seems multiple statements are not supported. These are multiple SQL statements in one string that can be executed in one call.

Here's an example which reports a false error:

import mysql.connection;
import std.stdio;

void main(string[] args)
{
    auto connection = new Connection("localhost", "user", "password", "database");
    auto command    = Command(connection);

    command.sql  = "CREATE DATABASE IF NOT EXISTS foo; USE foo;";

    ulong rowsAffected;

    auto result = command.execSQL(rowsAffected);

    writeln("Got results.");
}

output:

mysql.protocol.packets.MySQLReceivedException@/media/Data/Projects/D/third-party/mysql-native/source/mysql/protocol/commands.d(719): MySQL error: You have an error in your SQL s
yntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE foo' at line 1 

There is no error in the SQL. Are there plans to support multi statements?

[Abscissa]

I'd like to support it as I'd find it useful too. But unless there's some feature to support it in the protocol itself, the library might have to implement an SQL parser (not necessarily out of the question, but might get complicated, especially just to support multiple statements).

In some of my own projects, I have a whole database init script in SQL. For those, I just split the script on ; and foreach to send each to the server. But obviously that's error-prone if there's any statements or comments that happen to have a semicolon in them.

Maybe it would be good enough just detect \; vs ; and to parse out SQL comments? Or are there special cases where that still wouldn't be smart enough?

[nomad-software]

Yeah there are a lot of cases where delimiting on semi-colons won't work, for example when using stored procedures:

DELIMITER $$
CREATE PROCEDURE country_hos(IN con CHAR(20))
BEGIN
    SELECT Name, HeadOfState FROM Country WHERE Continent = con;
END $$
DELIMITER ;

Notice the delimiter changes.

[Abscissa]

Sometimes it can be strange being maintainer of a codebase that was originally somebody else's. Every once in a while you find things even you had no idea were there.

Case in point: Turns out there's already a feature for this, built into the MySQL communications protocol, and already exposed by mysql-native:

Connection.enableMultiStatements

SvrCapFlags.MULTI_STATEMENTS

However, I've yet to actually try it out, and it's completely untested in the unittests, so no guarantees. Plus, it seems to be specifically excluded from the defaultClientFlags - maybe there was a reason for that? In any case, it's worth a try.

[nomad-software]

Thanks for the update.