I am using an adapted version of <this MySQL dump ...

# exposed

[Stephcraft]

I am using an adapted version of this MySQL dump class using Kotlin & Exposed to create a .sql file backup from my database. However when trying to run one of these:

exec("SHOW TABLE STATUS FROM $databaseName") { result -> /* ... */ }

exec("SHOW CREATE TABLE $table") { result -> /* ... */ }

I get the following error:

[16:33:56 WARN]: Transaction attempt #0 failed: java.sql.SQLException: Can not issue executeUpdate() or executeLargeUpdate() with statements that produce result sets. Statement(s): SHOW TABLE STATUS FROM s6244_Exposed
org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLException: Can not issue executeUpdate() or executeLargeUpdate() with statements that produce result sets

Is this a bug? Am I doing something wrong? Is there a way around it? Thank you 🙏

[Stephcraft]

Here is my full MySQLDump class adapted from the article using Kotlin & Exposed for reference:

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.name
import org.jetbrains.exposed.sql.transactions.transaction
import java.sql.SQLException
import java.sql.Types
import java.text.SimpleDateFormat
import java.util.*

/**
 * <https://www.programmersought.com/article/32454724653/>
 */
class MySQLDump private constructor(private val db: Database) {
    // private val databaseName: String

    /**
     * Create a MysqlExport object
     *
     * @param conn database connection object
     * @param databaseName database library name
     * @param saveFolder save directory
     */
    init {
        // this.connection = co
    }

    /**
     * Get the names of all tables in the current database
     *
     * @return List all table names
     */
    private fun getAllTables(): List<String> {
        val databaseName = db.name
        val tables: MutableList<String> = ArrayList()
        transaction(db) {
            exec("SHOW TABLE STATUS FROM $databaseName") { result ->
                tables.add(result.getString("Name"))
            }
        }
        return tables
        // return transaction(db) { db.dialect.allTablesNames() }
    }

    /**
     * Generate create statement
     *
     * @param table table name
     * @return String
     */
    private fun getTableInsertStatement(table: String): String {
        val sql = StringBuilder()

        transaction(db) {
            exec("SHOW CREATE TABLE $table") { result ->
                val qtbl: String = result.getString(1)
                var query: String = result.getString(2)
                query = query.trim { it <= ' ' }.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")
                sql.append("\n\n--")
                sql.append("\n").append(SQL_START_PATTERN).append("  table dump : ").append(qtbl)
                sql.append("\n--\n\n")
                sql.append(query).append(";\n\n")
            }
        }

        sql.append("\n\n--\n").append(SQL_END_PATTERN).append("  table dump : ").append(table).append("\n--\n\n")
        return sql.toString()
    }

    /**
     * Generate insert statement
     *
     * @param table the table to get inserts statement for
     * @return String generated SQL insert
     */
    private fun getDataInsertStatement(table: String): String {
        val sql = StringBuilder()

        transaction(db) {
            exec("SELECT * FROM $table") { results ->
                try {
                    results.last()
                    sql.append("\n--").append("\n-- Inserts of ").append(table).append("\n--\n\n")
                    sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` DISABLE KEYS */;\n")
                    sql.append("\n--\n").append(SQL_START_PATTERN).append(" table insert : ").append(table).append("\n--\n")
                    sql.append("INSERT INTO `").append(table).append("`(")
                    val metaData = results.metaData
                    val columnCount = metaData.columnCount
                    for (i in 0 until columnCount) {
                        sql.append("`").append(metaData.getColumnName(i + 1)).append("`, ")
                    }
                    sql.deleteCharAt(sql.length - 1).deleteCharAt(sql.length - 1).append(") VALUES \n")
                    results.beforeFirst()
                    while (results.next()) {
                        sql.append("(")
                        for (i in 0 until columnCount) {
                            val columnType = metaData.getColumnType(i + 1)
                            val columnIndex = i + 1
                            if (Objects.isNull(results.getObject(columnIndex))) {
                                sql.append("").append(results.getObject(columnIndex)).append(", ")
                            } else if (columnType == Types.INTEGER || columnType == Types.TINYINT || columnType == Types.BIT) {
                                sql.append(results.getInt(columnIndex)).append(", ")
                            } else {
                                val `val` = results.getString(columnIndex).replace("'", "\\'")
                                sql.append("'").append(`val`).append("', ")
                            }
                        }
                        sql.deleteCharAt(sql.length - 1).deleteCharAt(sql.length - 1)
                        sql.append(if (results.isLast) ")" else "),\n")
                    }
                } catch (e: SQLException) {
                    e.printStackTrace()
                }
            }
        }

        sql.append(";\n--\n").append(SQL_END_PATTERN).append(" table insert : ").append(table).append("\n--\n")

        // enable FK constraint
        sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` ENABLE KEYS */;\n")
        return sql.toString()
    }

    /**
     * Export the structure and data of all tables
     *
     * @return String
     */
    private fun exportToSql(): String {
        val sql = StringBuilder()
        sql.append("--\n-- Generated by AJAXJS-Data")
        sql.append("\n-- Date: ").append(now()).append("\n--")

        // these declarations are extracted from HeidiSQL
        sql.append("\n\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;")
            .append("\n/*!40101 SET NAMES utf8 */;\n/*!50503 SET NAMES utf8mb4 */;")
            .append("\n/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;")
            .append("\n/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;")
        val tables = getAllTables()
        for (s in tables) {
            sql.append(getTableInsertStatement(s.trim { it <= ' ' }))
            sql.append(getDataInsertStatement(s.trim { it <= ' ' }))
        }
        sql.append("\n/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;").append(
            "\n/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;"
        )
            .append("\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;")
        return sql.toString()
    }

    companion object {

        private const val SQL_START_PATTERN = "-- start"
        private const val SQL_END_PATTERN = "-- end"

        private fun now(): String {
            val cal = Calendar.getInstance()
            val sdf = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
            return sdf.format(cal.time)
        }

        /**
         * Perform export
         */
        fun dump(database: Database) = MySQLDump(database).exportToSql()
    }
}

[AdamW]

Try passing in explicitStatementType = StatementType.SHOW

[Chantal Loncle]

Hi @Stephcraft Are you getting that error even when running an isolated exec("SHOW ...")?
Exposed should auto-detect a simple SHOW statement and run the expected executeQuery, so it's certainly an odd exception that you're getting. For me, the code throws a different exception because some java.sql.ResultSet cursor movements have been left out.

getAllTables is missing a while (result.next()) {} in the exec lambda.
getTableInsertStatement is missing result.next() before the first call to getString().
I also removed results.last() and results.beforeFirst() in getDataInsertStatement.
With those changes, running dump() produces the expected String on my end with no issues.

[Stephcraft]

I was on v0.41 (current v0.49), I should of checked that first. StatementType.SHOW was added in between these versions. My appologies. 🤦

[Stephcraft]

@Chantal Loncle Thank you for this. Its really appreciated 🙏