== 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.3.3</version>
</dependency>
----

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

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

== 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
    ResultSet 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]
----
List<String> columnNames = resultSet.getColumnNames();

List<JsonArray> results = resultSet.getResults();

for (JsonArray row: results) {

  String id = row.getString(0);
  String fName = row.getString(1);
  String lName = row.getString(2);
  int shoeSize = row.getInteger(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]
----
List<JsonObject> rows = resultSet.getRows();

for (JsonObject row: rows) {

  String id = row.getString("ID");
  String fName = row.getString("FNAME");
  String lName = row.getString("LNAME");
  int shoeSize = row.getInteger("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]
----
String query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?";
JsonArray params = new JsonArray().add("Fox").add(9);

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

  if (res.succeeded()) {
    // Get the result set
    ResultSet 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()) {

    UpdateResult result = res.result();
    System.out.println("Updated no. of rows: " + result.getUpdated());
    System.out.println("Generated keys: " + result.getKeys());

  } 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]
----
String update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?";
JsonArray params = new JsonArray().add("Fox");

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

  if (res.succeeded()) {

    UpdateResult updateResult = res.result();

    System.out.println("No. of rows updated: " + updateResult.getUpdated());

  } 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]
----
String func = "{ call one_hour_ago() }";

connection.call(func, res -> {

  if (res.succeeded()) {
    ResultSet 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]
----
String func = "{ call new_customer(?, ?) }";

connection.callWithParams(func, new JsonArray().add("John").add("Doe"), null, res -> {

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

However you can also return values like this:

[source,java]
----
String func = "{ call customer_lastname(?, ?) }";

connection.callWithParams(func, new JsonArray().add("John"), new JsonArray().addNull().add("VARCHAR"), res-> {

  if (res.succeeded()) {
    ResultSet 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]
----
List<String> batch = new ArrayList<>();
batch.add("INSERT INTO emp (NAME) VALUES ('JOE')");
batch.add("INSERT INTO emp (NAME) VALUES ('JANE')");

connection.batch(batch, res -> {
  if (res.succeeded()) {
    List<Integer> 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]
----
List<JsonArray> batch = new ArrayList<>();
batch.add(new JsonArray().add("joe"));
batch.add(new JsonArray().add("jane"));

connection.batchWithParams("INSERT INTO emp (name) VALUES (?)", batch, res -> {
  if (res.succeeded()) {
    List<Integer> 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]
----
String 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()) {
    System.out.println("Table created !");
  } else {
    // Failed!
  }
});
----

=== 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]
----
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]`.