## 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 `module.ceylon`:

    shared import io.vertx.ceylon.sql "3.3.2";

## The SQL Connection

A connection to the database is represented by `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 `setAutoCommit`.

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

    connection.setAutoCommit(false, (Throwable? res) {
      if (!exists res) {
        // OK!
      } else {
        // Failed!
      };
    });

### Executing queries

To execute a query use `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 `ResultSet` when the query has been run.

    connection.query("SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE", (ResultSet|Throwable res) {
      if (is ResultSet res) {
        // Get the result set
        value resultSet = res;
      } else {
        // Failed!
      };
    });

The `ResultSet` instance represents the results of a query.

The list of column names are available with `getColumnNames`, and the actual results available with `getResults`

The results are a list of `JsonArray` instances, one for each row of the results.

    value columnNames = resultSet.columnNames;

    value results = resultSet.results;

    for (row in results) {

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

    };

You can also retrieve the rows as a list of Json object instances with `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 `getResults` instead.

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

    value rows = resultSet.rows;

    for (row in rows) {

      value id = rowget("ID";
      value fName = rowget("FNAME";
      value lName = rowget("LNAME";
      value shoeSize = rowget("SHOE_SIZE";

    };

### Prepared statement queries

To execute a prepared statement query you can use `queryWithParams`.

This takes the query, containing the parameter place holders, and a `JsonArray` or parameter values.

    value query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?";
    value params = JsonArray {"Fox" , 9 };

    connection.queryWithParams(query, params, (ResultSet|Throwable res) {

      if (is ResultSet res) {
        // Get the result set
        value resultSet = res;
      } else {
        // Failed!
      };
    });

### Executing INSERT, UPDATE or DELETE

To execute an operation which updates the database use `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 `UpdateResult` when the update has been run.

The update result holds the number of rows updated with `getUpdated`, and if the update generated keys, they are available with `getKeys`.

    connection.update("INSERT INTO PEOPLE VALUES (null, 'john', 'smith', 9)", (UpdateResult|Throwable res) {
      if (is UpdateResult res) {

        value result = res;
        print("Updated no. of rows: ``result.updated``");
        print("Generated keys: ``result.keys``");

      } else {
        // Failed!
      };
    });

### Prepared statement updates

To execute a prepared statement update you can use `updateWithParams`.

This takes the update, containing the parameter place holders, and a `JsonArray` or parameter values.

    value update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?";
    value params = JsonArray {"Fox" };

    connection.updateWithParams(update, params, (UpdateResult|Throwable res) {

      if (is UpdateResult res) {

        value updateResult = res;

        print("No. of rows updated: ``updateResult.updated``");

      } else {

        // Failed!

      };
    });

### Callable statements

To execute a callable statement (either SQL functions or SQL procedures) you can use `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 `JsonArray` containing the parameter values and finally a `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:

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

    connection.call(func, (ResultSet|Throwable res) {

      if (is ResultSet res) {
        value result = res;
      } 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:

    // 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);

    value func = "{ call new_customer(?, ?) }";

    connection.callWithParams(func, JsonArray {"John" , "Doe" }, null, (ResultSet|Throwable res) {

      if (is ResultSet res) {
        // Success!
      } else {
        // Failed!
      };
    });

However you can also return values like this:

    // 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;

    value func = "{ call customer_lastname(?, ?) }";

    connection.callWithParams(func, JsonArray {"John" }, JsonArray {null , "VARCHAR" }, (ResultSet|Throwable res) {

      if (is ResultSet res) {
        value result = res;
      } 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 `batch`

- Batched prepared statements `batchWithParams`

- Batched callable statements `batchCallableWithParams`

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

    // Batch values
    value batch = renderNewList;
    renderListAdd;
    renderListAdd;

    connection.batch(batch, (List<Integer>|Throwable res) {
      if (is List<Integer> res) {
        value result = res;
      } else {
        // Failed!
      };
    });

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

    // Batch values
    value batch = renderNewList;
    renderListAdd;
    renderListAdd;

    connection.batchWithParams("INSERT INTO emp (name) VALUES (?)", batch, (List<Integer>|Throwable res) {
      if (is List<Integer> res) {
        value result = res;
      } else {
        // Failed!
      };
    });

### Executing other operations

To execute any other database operation, e.g. a `CREATE TABLE` you can use `execute`.

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

    value 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, (Throwable? execute) {
      if (!exists execute) {
        print("Table created !");
      } else {
        // Failed!
      };
    });

### Using transactions

To use transactions first set auto-commit to false with `setAutoCommit`.

You then do your transactional operations and when you want to commit or rollback use `commit` or `rollback`.

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

    // Do stuff with connection - updates etc

    // Now commit

    connection.commit((Throwable? res) {
      if (!exists res) {
        // Committed OK!
      } else {
        // Failed!
      };
    });

### Closing connections

When you’ve done with the connection you should return it to the pool with `close`.