<show-structure for="chapter,procedure" depth="2"/>

# Building SQL Statements

Exposed DSL provides various functions to perform database operations, such as [](DSL-CRUD-operations.topic).
If you need to access the SQL generated by this DSL without executing it,
Exposed provides this functionality through [`Statement.prepareSQL()`](https://jetbrains.github.io/Exposed/api/exposed-core/org.jetbrains.exposed.v1.core.statements/-statement/prepare-s-q-l.html).

The following examples refer to the [`StarWarsFilmsTable`](DSL-Table-Types.topic) defined previously, and any generated
SQL is based on output from the H2 database.

<note>
This functionality still requires a connection and transaction context, since Exposed adapts its statement and query
building dynamically based on the underlying database, primarily for identifier and syntax specifics.
</note>

## Read operations

<tldr>
    <p>API References: <a href="https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc/-query/index.html"><code>Query</code> (JDBC)</a>, 
    <a href="https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc/-query/index.html"><code>Query</code> (R2DBC)</a>  
    </p>
</tldr>

An instance of a `Query` is not executed until its results are consumed, for example through iteration.
For this reason, queries can be built and stored for later use:

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="filmQuery"/>

You can then call `.prepareSQL()` to check the SQL string representation of the query that would be sent to the database:

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="querySql"/>

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-lines="27-29"/>

By default, a parameterized SQL string is prepared and returned. To generate a SQL string without parameter placeholders,
set the `prepared` argument to `false`:

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="fullQuerySql"/>

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-lines="38-40"/>

## Other operations

When calling a function like [`.insert()`](DSL-CRUD-operations.topic#insert) on a table, Exposed automatically sends the generated SQL
to the database to create a new row. To avoid automatic execution, you can instantiate the underlying statement class directly, namely
[`InsertStatement`](https://jetbrains.github.io/Exposed/api/exposed-core/org.jetbrains.exposed.v1.core.statements/-insert-statement/index.html).

Since version 1.0.0, you can create instances of these underlying statements without automatic execution, by using
the same DSL inside a [`buildStatement {}`](https://jetbrains.github.io/Exposed/api/exposed-core/org.jetbrains.exposed.v1.core.statements/build-statement.html) block:

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="insertFilm"/>

As for [queries](#read-operations), you can access the SQL string to be executed using `.prepareSQL()`:

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="preparedSql"/>

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-lines="57"/>

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-symbol="fullSql"/>

<code-block lang="kotlin"
    src="exposed-dsl/src/main/kotlin/org/example/examples/BuildStatementExamples.kt"
    include-lines="64"/>

### Executing a statement

<tldr>
    <p>API References: <a href="https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc/-jdbc-transaction/exec.html"><code>exec</code> (JDBC)</a>, 
    <a href="https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc/-r2dbc-transaction/exec.html"><code>exec</code> (R2DBC)</a>  
    </p>
</tldr>

A stored `Statement` can be sent to the database by first passing it to an executable class, either a subclass
of [`BlockingExecutable`](https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc.statements/-blocking-executable/index.html) (JDBC)
or [`SuspendExecutable`](https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc.statements/-suspend-executable/index.html) (R2DBC).
The executable can then be sent to the database using `exec()` in a transaction block.

This can be done manually, if the appropriate class is known or a custom statement or executable class is being used:

```kotlin
exec(InsertBlockingExecutable(insertFilm))
```

Alternatively, if the statement is created using the Exposed API, you can use `Statement.toExecutable()`
([JDBC](https://jetbrains.github.io/Exposed/api/exposed-jdbc/org.jetbrains.exposed.v1.jdbc.statements/to-executable.html),
[R2DBC](https://jetbrains.github.io/Exposed/api/exposed-r2dbc/org.jetbrains.exposed.v1.r2dbc.statements/to-executable.html))
to resolve the appropriate executable class instance for the calling statement type:

```kotlin
exec(insertFilm.toExecutable())
```
