## 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).

## 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, res -> {
      if (res.succeeded()) {
        // 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", res -> {
      if (res.succeeded()) {
        // Get the result set
        ResultSet resultSet = res.result();
      } 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.

    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 `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:

    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 `queryWithParams`.

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

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

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

    }

### 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.

    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!

      }
    });

### 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

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

    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 `close`.