Free Blocks

Calling UDFs

In situations where you need to use a SQL UDF that is available directly on the database, or when you need to use custom SQL syntax that is not supported by ExoQuery, you can use a free block.

val q = sql {
  Table<Person>().filter { p -> free("mySpecialDatabaseUDF(${p.name})") == "Joe" }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE mySpecialDatabaseUDF(p.name) = 'Joe'

You can pass param-calls into the free-block as well.

val myRuntimeVar = ...
val q = sql {
  Table<Person>().filter { p -> p.name == free("mySpecialDatabaseUDF(${param(myRuntimeVar)})") }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = mySpecialDatabaseUDF(?)

Enriching Queries

Free blocks are also useful for adding information before/after an entire query. For example:

val q = sql {
  free("${Table<Person>().filter { p -> p.name == "Joe" }} FOR UPDATE").asPure<SqlQuery<Person>>()
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.name = 'Joe' FOR UPDATE

This is technique is quite powerful when combined with SQL Fragment Functions to abstract out logic:

@SqlFragment
fun <T : Person> forUpdate(v: SqlQuery<T>) = sql {
    free("${v} FOR UPDATE").asPure<SqlQuery<T>>()
  }

val q = sql {
  forUpdate(Table<Person>().filter { p -> p.age > 21 })
}
//> (SELECT p.id, p.name, p.age FROM Person p WHERE p.age > 21) FOR UPDATE

Enriching Actions

Free blocks can even be used with Action (i.e. insert, update, delete) statements:

val q = sql {
  insert<Person> { setParams(Person(1, "Joe", "Bloggs", 111)) }
}
val qq = sql {
  free("SET IDENTITY_INSERT Person ON ${q} SET IDENTITY_INSERT Person OFF").asPure<SqlAction<Person, Long>>()
}
qq.build<SqlServerDialect>().runOn(ctx)
//> SET IDENTITY_INSERT Person ON  INSERT INTO Person (id, name, age, companyId) VALUES (?, ?, ?, ?) SET IDENTITY_INSERT Person OFF