Exposed

Exposed copied to clipboard

Problem: i am trying to execute multi-statement transaction using exec function in mysql database

val str = "START TRANSACTION;\n" + "INSERT something\n" + "SELECT LAST_INSERT_ID() AS INSERTED_ID;\n" + "COMMIT;" TransactionManager.current().exec(str)

but get error

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 'SELECT LAST_INSERT_ID() AS INSERTED_ID; COMMIT' at line 2

Question: Manually executing the script successfully returns the result How to properly execute such transactions in exposed?

1. Please check that SO thread https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement maybe it helps you.
2. You should not call START TRANSACTION / COMMIT as jdbc driver will do the same thing for you (before first statement execution and after Exposed transaction block.

@Tapac No, unfortunately it didn't help.

I think the problem is here Transaction.kt

For example transaction: "SET @uid:=1; SELECT * from user_authorized where id=@uid;" defined as OTHER and executed but does not return a result

Explicitly specifying the SELECT type does not help java.sql.SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets.

Hi @AliceCarroll239 . Using current version 0.44.0, if allowMultiQueries=true is added to the JDBC url, the syntax exception goes away. Still, neither of the 2 multi-statement examples above will produce a result set to work with because the SELECT statement comes second and Exposed does not currently support retrieving multiple result sets.

For example, the following works as expected because the order is reversed and the result set from the first statement is returned for iteration:

object Tester : IntIdTable("tester") {
    val num1 = integer("num_1")
}

transaction {
    // ...
    Tester.insert { it[num1] = 99 }

    val result = exec(
        "SELECT LAST_INSERT_ID() AS INSERTED_ID; INSERT INTO tester (num_1) VALUES (100);"
    ) {
        it.next()
        it.getInt("inserted_id")
    }
    println(result) // 1

    println(Tester.selectAll().count()) // 2
}

Even if 2 SELECT statements are used instead, only the result set from the first will be retrieved.

So, a few questions to consider if supporting retrieval of multi-statement result sets is introduced:

1. How would you expect to receive the result set(s) for user-handling? As a List<ResultSet>, in the order of the provided statements? Something like this:

execMulti(
    "INSERT INTO tester (num_1) VALUES (100); SELECT LAST_INSERT_ID() AS INSERTED_ID;"
) { resultSets ->
    for (rs in resultSets ) {
        rs.next()
        println(rs.getObject(1))
    }
}

2. Would the expectation be that only result sets are returned in the transform block? Because in the example above, the insert statement technically returns a row update count of 1 as well.

3. Or is the expectation simply that 1 result set should be returned? As in, there will be only 1 SELECT statement preceded (and/or followed) by x other statements. If that's the case, we could potentially introduce a new StatementType.MULTI to be handled in the original exec() with a contract that only 1 statement producing a result set is included (as only the first would be retrieved).