== Vert.x Common SQL interface

The common SQL interface is used to interact with Vert.x SQL services.

You obtain a connection to the database via the service interface for the specific SQL service that
you are using (e.g. JDBC/MySQL/PostgreSQL).

To use this project, add the following dependency to the _dependencies_ section of your build descriptor:

* Maven (in your `pom.xml`):

[source,xml,subs="+attributes"]
----
<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-sql-common</artifactId>
  <version>3.4.2</version>
</dependency>
----

* Gradle (in your `build.gradle` file):

[source,groovy,subs="+attributes"]
----
compile 'io.vertx:vertx-sql-common:3.4.2'
----

== The SQL Connection

A connection to the database is represented by `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html[SQLConnection]`.

=== Auto-commit

When you obtain a connection auto commit is set to `true`. This means that each operation you perform will effectively
execute in its own transaction.

If you wish to perform multiple operations in a single transaction you should set auto commit to false with
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#setAutoCommit-boolean-io.vertx.core.Handler-[setAutoCommit]`.

When the operation is complete, the handler will be called:

[source,java]
----
connection.setAutoCommit(false, { res ->
  if (res.succeeded()) {
    // OK!
  } else {
    // Failed!
  }
})

----

=== Executing queries

To execute a query use `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#query-java.lang.String-io.vertx.core.Handler-[query]`

The query string is raw SQL that is passed through without changes to the actual database.

The handler will be called with the results, represented by `link:../../apidocs/io/vertx/ext/sql/ResultSet.html[ResultSet]` when the query has
been run.

[source,java]
----
connection.query("SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE", { res ->
  if (res.succeeded()) {
    // Get the result set
    def resultSet = res.result()
  } else {
    // Failed!
  }
})

----

The `link:../../apidocs/io/vertx/ext/sql/ResultSet.html[ResultSet]` instance represents the results of a query.

The list of column names are available with `link:../../apidocs/io/vertx/ext/sql/ResultSet.html#getColumnNames--[getColumnNames]`, and the actual results
available with `link:../../apidocs/io/vertx/ext/sql/ResultSet.html#getResults--[getResults]`

The results are a list of `link:../../apidocs/io/vertx/core/json/JsonArray.html[JsonArray]` instances, one for each row of the results.

[source,java]
----

def columnNames = resultSet.columnNames

def results = resultSet.results

results.each { row ->

  def id = row[0]
  def fName = row[1]
  def lName = row[2]
  def shoeSize = row[3]

}


----

You can also retrieve the rows as a list of Json object instances with `link:../../apidocs/io/vertx/ext/sql/ResultSet.html#getRows--[getRows]` -
this can give you a somewhat simpler API to work with, but please be aware that SQL results can contain duplicate
column names - if that's the case you should use `link:../../apidocs/io/vertx/ext/sql/ResultSet.html#getResults--[getResults]` instead.

Here's an example of iterating through the results as Json object instances:

[source,java]
----

def rows = resultSet.rows

rows.each { row ->

  def id = row.ID
  def fName = row.FNAME
  def lName = row.LNAME
  def shoeSize = row.SHOE_SIZE

}


----

=== Prepared statement queries

To execute a prepared statement query you can use
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#queryWithParams-java.lang.String-io.vertx.core.json.JsonArray-io.vertx.core.Handler-[queryWithParams]`.

This takes the query, containing the parameter place holders, and a `link:../../apidocs/io/vertx/core/json/JsonArray.html[JsonArray]` or parameter
values.

[source,java]
----

def query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?"
def params = [
  "Fox",
  9
]

connection.queryWithParams(query, params, { res ->

  if (res.succeeded()) {
    // Get the result set
    def resultSet = res.result()
  } else {
    // Failed!
  }
})


----

=== Executing INSERT, UPDATE or DELETE

To execute an operation which updates the database use `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#update-java.lang.String-io.vertx.core.Handler-[update]`.

The update string is raw SQL that is passed through without changes to the actual database.

The handler will be called with the results, represented by `link:../../apidocs/io/vertx/ext/sql/UpdateResult.html[UpdateResult]` when the update has
been run.

The update result holds the number of rows updated with `link:../../apidocs/io/vertx/ext/sql/UpdateResult.html#getUpdated--[getUpdated]`, and
if the update generated keys, they are available with `link:../../apidocs/io/vertx/ext/sql/UpdateResult.html#getKeys--[getKeys]`.

[source,java]
----

connection.update("INSERT INTO PEOPLE VALUES (null, 'john', 'smith', 9)", { res ->
  if (res.succeeded()) {

    def result = res.result()
    println("Updated no. of rows: ${result.updated}")
    println("Generated keys: ${result.keys}")

  } else {
    // Failed!
  }
})



----

=== Prepared statement updates

To execute a prepared statement update you can use
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#updateWithParams-java.lang.String-io.vertx.core.json.JsonArray-io.vertx.core.Handler-[updateWithParams]`.

This takes the update, containing the parameter place holders, and a `link:../../apidocs/io/vertx/core/json/JsonArray.html[JsonArray]` or parameter
values.

[source,java]
----

def update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?"
def params = [
  "Fox"
]

connection.updateWithParams(update, params, { res ->

  if (res.succeeded()) {

    def updateResult = res.result()

    println("No. of rows updated: ${updateResult.updated}")

  } else {

    // Failed!

  }
})


----

=== Callable statements

To execute a callable statement (either SQL functions or SQL procedures) you can use
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#callWithParams-java.lang.String-io.vertx.core.json.JsonArray-io.vertx.core.json.JsonArray-io.vertx.core.Handler-[callWithParams]`.

This takes the callable statement using the standard JDBC format `{ call func_proc_name() }`, optionally including
parameter place holders e.g.: `{ call func_proc_name(?, ?) }`, a `link:../../apidocs/io/vertx/core/json/JsonArray.html[JsonArray]` containing the
parameter values and finally a `link:../../apidocs/io/vertx/core/json/JsonArray.html[JsonArray]` containing the
output types e.g.: `[null, 'VARCHAR']`.

Note that the index of the output type is as important as the params array. If the return value is the second
argument then the output array must contain a null value as the first element.

A SQL function returns some output using the `return` keyword, and in this case one can call it like this:

[source,java]
----
// Assume that there is a SQL function like this:
//
// create function one_hour_ago() returns timestamp
//    return now() - 1 hour;

// note that you do not need to declare the output for functions
def func = "{ call one_hour_ago() }"

connection.call(func, { res ->

  if (res.succeeded()) {
    def result = res.result()
  } else {
    // Failed!
  }
})

----

When working with Procedures you and still return values from your procedures via its arguments, in the case you do
not return anything the usage is as follows:

[source,java]
----
// Assume that there is a SQL procedure like this:
//
// create procedure new_customer(firstname varchar(50), lastname varchar(50))
//   modifies sql data
//   insert into customers values (default, firstname, lastname, current_timestamp);

def func = "{ call new_customer(?, ?) }"

connection.callWithParams(func, [
  "John",
  "Doe"
], null, { res ->

  if (res.succeeded()) {
    // Success!
  } else {
    // Failed!
  }
})

----

However you can also return values like this:

[source,java]
----
// Assume that there is a SQL procedure like this:
//
// create procedure customer_lastname(IN firstname varchar(50), OUT lastname varchar(50))
//   modifies sql data
//   select lastname into lastname from customers where firstname = firstname;

def func = "{ call customer_lastname(?, ?) }"

connection.callWithParams(func, [
  "John"
], [
  null,
  "VARCHAR"
], { res ->

  if (res.succeeded()) {
    def result = res.result()
  } else {
    // Failed!
  }
})

----

Note that the index of the arguments matches the index of the `?` and that the output parameters expect to be a
String describing the type you want to receive.

To avoid ambiguation the implementations are expected to follow the following rules:

* When a place holder in the `IN` array is `NOT NULL` it will be taken
* When the `IN` value is NULL a check is performed on the OUT
  * When the `OUT` value is not null it will be registered as a output parameter
  * When the `OUT` is also null it is expected that the IN value is the `NULL` value.

The registered `OUT` parameters will be available as an array in the result set under the output property.

=== Batch operations

The SQL common interface also defines how to execute batch operations. There are 3 types of batch operations:

* Batched statements `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#batch-java.util.List-io.vertx.core.Handler-[batch]`
* Batched prepared statements `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#batchWithParams-java.lang.String-java.util.List-io.vertx.core.Handler-[batchWithParams]`
* Batched callable statements `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#batchCallableWithParams-java.lang.String-java.util.List-java.util.List-io.vertx.core.Handler-[batchCallableWithParams]`

A batches statement will exeucte a list of sql statements as for example:

[source,java]
----
// Batch values
def batch = []
batch.add("INSERT INTO emp (NAME) VALUES ('JOE')")
batch.add("INSERT INTO emp (NAME) VALUES ('JANE')")

connection.batch(batch, { res ->
  if (res.succeeded()) {
    def result = res.result()
  } else {
    // Failed!
  }
})

----

While a prepared or callable statement batch will reuse the sql statement and take an list of arguments as for example:

[source,java]
----
// Batch values
def batch = []
batch.add([
  "joe"
])
batch.add([
  "jane"
])

connection.batchWithParams("INSERT INTO emp (name) VALUES (?)", batch, { res ->
  if (res.succeeded()) {
    def result = res.result()
  } else {
    // Failed!
  }
})

----

=== Executing other operations

To execute any other database operation, e.g. a `CREATE TABLE` you can use
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#execute-java.lang.String-io.vertx.core.Handler-[execute]`.

The string is passed through without changes to the actual database. The handler is called when the operation
is complete

[source,java]
----

def sql = "CREATE TABLE PEOPLE (ID int generated by default as identity (start with 1 increment by 1) not null,FNAME varchar(255), LNAME varchar(255), SHOE_SIZE int);"

connection.execute(sql, { execute ->
  if (execute.succeeded()) {
    println("Table created !")
  } else {
    // Failed!
  }
})


----

=== Multiple ResultSet responses

In some cases your query might return more than one result set, in this case and to preserve the compatibility when
the returned result set object is converted to pure json, the next result sets are chained to the current result set
under the property `next`. A simple walk of all result sets can be achieved like this:

[source,groovy]
----
// do something with the result set...

// next step
rs = rs.next


----

=== Streaming

When dealing with large data sets, it is not advised to use API just described but to stream data since it avoids
inflating the whole response into memory and JSON and data is just processed on a row by row basis, for example:

[source,groovy]
----
connection.queryStream("SELECT * FROM large_table", { stream ->
  if (stream.succeeded()) {
    stream.result().handler({ row ->
      // do something with the row...
    })
  }
})

----

You still have full control on when the stream is pauses, resumed and ended. For cases where your query returns
multiple result sets you should use the result set ended event to fetch the next one if available. If there is more
data the stream handler will receive the new data, otherwise the end handler is invoked.

[source,groovy]
----
connection.queryStream("SELECT * FROM large_table; SELECT * FROM other_table", { stream ->
  if (stream.succeeded()) {
    def sqlRowStream = stream.result()

    sqlRowStream.resultSetClosedHandler({ v ->
      // will ask to restart the stream with the new result set if any
      sqlRowStream.moreResults()
    }).handler({ row ->
      // do something with the row...
    }).endHandler({ v ->
      // no more data available...
    })
  }
})

----

=== Using transactions

To use transactions first set auto-commit to false with `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#setAutoCommit-boolean-io.vertx.core.Handler-[setAutoCommit]`.

You then do your transactional operations and when you want to commit or rollback use
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#commit-io.vertx.core.Handler-[commit]` or
`link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#rollback-io.vertx.core.Handler-[rollback]`.

Once the commit/rollback is complete the handler will be called and the next transaction will be automatically started.

[source,java]
----

// Do stuff with connection - updates etc

// Now commit

connection.commit({ res ->
  if (res.succeeded()) {
    // Committed OK!
  } else {
    // Failed!
  }
})


----

=== Closing connections

When you've done with the connection you should return it to the pool with `link:../../apidocs/io/vertx/ext/sql/SQLConnection.html#close-io.vertx.core.Handler-[close]`.