prepareSQL for insert operation

[JajaComp]

Hi! Can i get raw sql code without execute request? I try use Insert.prepareSQL(this), but is call execute request. Latest version Postgresql

[Tapac]

https://github.com/JetBrains/Exposed/issues/371#issuecomment-413988731

[JajaComp]

@Tapac This result SQL plain text not contain values:

object TestTable : Table("test") {
    val name = text("name")
    val soName = text("soName")
    val phone = text("phone").nullable()
}

fun test(): Unit = transaction {
        val result = TestTable.insertQuery {
            it[name] = "Name"
            it[soName] = "SoName"
        }.prepareSQL(this@transaction)
        println("Request =  $result")
    }

fun <T: Table> T.insertQuery(body: T.(InsertStatement<Number>)->Unit) = InsertStatement<Number>(this).apply {
    body(this)
}

Result is : INSERT INTO test ("name", phone, "soName") VALUES (?, ?, ?)

[Tapac]

Please replace this@transaction with QueryBuilder(prepared = false) if you need the complete query with values.

[JajaComp]

@Tapac I can't. This function exist just for select request (override fun prepareSQL(builder: QueryBuilder): String). Insert/update have only override fun prepareSQL(transaction: Transaction): String

[micHar]

@JajaComp but you can easily copy it from the Exposed source. E.g. for a delete statement the original is

override fun prepareSQL(transaction: Transaction): String =
        transaction.db.dialect.functionProvider.delete(isIgnore, table, where?.let { QueryBuilder(true).append(it).toString() }, limit, transaction)

So to have a version with prepared=false all you need is

private fun DeleteStatement.prepareSQL(transaction: Transaction, prepared: Boolean): String =
    transaction.db.dialect.functionProvider.delete(
        isIgnore,
        table,
        where?.let { QueryBuilder(prepared).append(it).toString() },
        limit,
        transaction
    )

and then you can use it like this

fun <T : Table> T.deleteWhereQuery(
    isIgnore: Boolean = false,
    limit: Int? = null,
    offset: Long? = null,
    op: SqlExpressionBuilder.() -> Op<Boolean>
) = DeleteStatement(this, SqlExpressionBuilder.op(), isIgnore, limit, offset)
    .prepareSQL(TransactionManager.current(), prepared = false)

[holdbetter]

One more hacky way is using addLogger + transaction.rollback() or addLogger and throwing exception in transaction to get rollback.

In my scenario: I'm using batchInsert and it doesn't have prepareSql method, because it's not Statement like single insert

[shalaga44]

No updates on this in 2024? I'm doing it this way:

class SqlPlainTest {
    object Users : UUIDTable() {
        val name = text("name")
        val age = integer("age")
    }

    fun <T : Table> T.insertQuery(body: T.(InsertStatement<Number>) -> Unit): InsertStatement<Number> =
        InsertStatement<Number>(this).apply {
            body(this)
        }

    @Test
    fun exposedTest() {

        Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL")
        transaction { SchemaUtils.createMissingTablesAndColumns(Users) }
        val query = Users.insertQuery {
            it[name] = "John Doe"
            it[age] = 30
        }

        val sql = transaction {
            query.prepareSQL(this, false)
        }
        println("Generated SQL: $sql")
        transaction {
            Users.selectAll().also { println(it.toList()) }
        }
    }
}

So will this API change?