The `sql` property of `DryRunResult` does include ...

# komapper

[Toshihiro Nakamura]

The sql property of DryRunResult does include ?. If the sql property does not contain all the ? placeholders that would actually be issued, it might be because the dry run is not using the correct database configuration. You can easily retrieve all the values to be bound as well.
Please try the following code.

val query = QueryDsl.insert(a).single(Address(street = "street A"))
val result = query.dryRun(db.config) // or `db.dryRunQuery(query)`
// sql with all the ?
val sql = result.sql
// all the params to bind with the sql
val allArgs = result.args.map { it.any }

[dave08]

This was my implementation:

class SqlClient2(private val conn: SuspendingConnection) {
    val dbConfig = R2dbcDatabase("r2dbc:<postgresql://user:pass@dummy/dummy>").config

    suspend fun runQuery(query: Query<*>): QueryResult {
        val sqlT = query.dryRun(dbConfig)

        return conn.sendPreparedStatement(sqlT.sql, sqlT.args.map { it.any })
    }

    suspend fun <T> runQueryScalar(query: Query<*>): T? {
        return runQuery(query).rows.singleOrNull()?.getAs<T>(0)
    }
}

but it seems that in sql there's a $1 instead of a ?

[dave08]

Also, there seems to be a bug in sqlWithArgs... when a String field contains ' it doesn't escape them... @Toshihiro Nakamura

[dave08]

I had to implement it like this to work...:

class SqlClient2(private val conn: SuspendingConnection) {
    val dbConfig = R2dbcDatabase("r2dbc:<postgresql://user:pass@dummy/dummy>").config
    val logger = KotlinLogging.logger {  }

    val argPlaceholder = """\$\d+""".toRegex()

    suspend fun runQuery(query: Query<*>): QueryResult {
        val sqlT = query.dryRun(dbConfig)

        return catch({
            if (sqlT.args.isEmpty())
                conn.sendQuery(sqlT.sql)
            else {
                conn.sendPreparedStatement(sqlT.sql.replace(argPlaceholder, "?"), sqlT.args.map { it.any })
            }
        }) {
            logger.error { "Sql error: $sqlT, ${it.message}" }

            throw it
        }
    }

    suspend fun <T> runQueryScalar(query: Query<*>): T? {
        return runQuery(query).rows.singleOrNull()?.getAs<T>(0)
    }
}

[dave08]

But I hope that if I had the same field in the request multiple times it still adds them to args multiple times. I suspect that if the current placeholders are $1 and $2 , then it'll just re-use the arg at position 0 for all the $1 occurances, which would break this implementation...

[dave08]

It seems like Postgres supports the $1 etc syntax, but jasync doesn't... so I ended up with something not too clean:

class SqlClient2(private val conn: SuspendingConnection) {
    val dialect = object : PostgreSqlR2dbcDialect {
        override fun createBindVariable(index: Int, value: StatementPart.Value) = "?"
    }
    val dbConfig = R2dbcDatabase("r2dbc:<postgresql://user:pass@dummy/dummy>", dialect = dialect).config
    val logger = KotlinLogging.logger {  }

    suspend fun runQuery(query: Query<*>): QueryResult {
        val sqlT = query.dryRun(dbConfig)

        return catch({
            if (sqlT.args.isEmpty())
                conn.sendQuery(sqlT.sql)
            else {
                conn.sendPreparedStatement(sqlT.sql, sqlT.args.map { it.any })
            }
        }) {
            logger.error { "Sql error: $sqlT, ${it.message}" }

            throw it
        }
    }

    suspend fun <T> runQueryScalar(query: Query<*>): T? {
        return runQuery(query).rows.singleOrNull()?.getAs<T>(0)
    }
}

[dave08]

But this probably won't cover something like this:

fun getUserOrManager(connectionFactory: ConnectionFactory, userId: Int): Mono<Void> {
    val sql = "SELECT * FROM users WHERE id = $1 OR manager_id = $1"
    return Mono.from(connectionFactory.create())
        .flatMapMany { connection ->
            connection.createStatement(sql)
                .bind(0, userId) // Bind the value once for $1
                .execute()
                .flatMap(PostgresqlResult::getRowsUpdated)
                .doFinally { connection.close() }
        }
        .then()
}

[dave08]

Since maybe the dialect only returns $1 in args one time... so it's the same situation as before...

[Toshihiro Nakamura]

The choice between using ? or $1 as placeholders depends on the driver. Komapper uses the appropriate placeholders for each driver through its Dialect. If you perform a dry run using MySqlR2dbcDialect, you’ll get SQL with ? placeholders.

Also, there seems to be a bug in sqlWithArgs... when a String field contains a ', it doesn’t escape them.

Escaping single quotes is the responsibility of the driver’s PreparedStatement.

[dave08]

> Escaping single quotes is the responsibility of the driver’s PreparedStatement.
It didn't seem to work for the postgres r2dbc driver...

[dave08]

The choice between using ? or $1 as placeholders depends on the driver

But if the driver decides on $1 and there are a few of them in the same sql statement, will it generate a $2 for the next one and repeat the argument, or will it use the same argument for the second occurance?

[Toshihiro Nakamura]

it generate a $2 for the next one and repeat the argument

Komapper takes this approach.