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