# Reactive DB2 Client

The Reactive DB2 Client is a client for DB2 with a straightforward API focusing on scalability and low overhead.

The client is reactive and non blocking, allowing to handle many database connections with a single thread.

Features

- Support for DB2 on Linux, Unix, and Windows

- Limited support for DB2 on z/OS

- Event driven

- Lightweight

- Built-in connection pooling

- Prepared queries caching

- Batch and cursor

- Row streaming

- RxJava 1 and RxJava 2

- Direct memory to object without unnecessary copies

- Java 8 Date and Time

- SSL/TLS

- HTTP/1.x CONNECT, SOCKS4a or SOCKS5 proxy support

Current limitations

- No stored procedures support

- Some column types (e.g. BLOB and CLOB) are not supported

## Usage

To use the Reactive DB2 Client add the following dependency to the dependencies section of your build descriptor:

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

    <dependency>
     <groupId>io.vertx</groupId>
     <artifactId>vertx-lang-ruby</artifactId>
     <version>3.9.13</version>
    </dependency>

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

    dependencies {
     compile 'io.vertx:vertx-lang-ruby:3.9.13'
    }

## Getting started

Here is the simplest way to connect, query and disconnect

    require 'vertx-db2-client/db2_pool'

    # Connect options
    connectOptions = {
      'port' => 50000,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret"
    }

    # Pool options
    poolOptions = {
      'maxSize' => 5
    }

    # Create the client pool
    client = VertxDb2Client::DB2Pool.pool(connectOptions, poolOptions)

    # A simple query
    client.query("SELECT * FROM users WHERE id='julien'").execute() { |ar_err,ar|
      if (ar_err == nil)
        result = ar
        puts "Got #{result.size()} rows "
      else
        puts "Failure: #{ar_err.get_message()}"
      end

      # Now close the pool
      client.close()
    }

## Connecting to DB2

Most of the time you will use a pool to connect to DB2:

    require 'vertx-db2-client/db2_pool'

    # Connect options
    connectOptions = {
      'port' => 50000,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret"
    }

    # Pool options
    poolOptions = {
      'maxSize' => 5
    }

    # Create the pooled client
    client = VertxDb2Client::DB2Pool.pool(connectOptions, poolOptions)

The pooled client uses a connection pool and any operation will borrow a connection from the pool to execute the operation and release it to the pool.

If you are running with Vert.x you can pass it your Vertx instance:

    require 'vertx-db2-client/db2_pool'

    # Connect options
    connectOptions = {
      'port' => 50000,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret"
    }

    # Pool options
    poolOptions = {
      'maxSize' => 5
    }
    # Create the pooled client
    client = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

You need to release the pool when you don’t need it anymore:

    # Close the pool and all the associated resources
    pool.close()

When you need to execute several operations on the same connection, you need to use a client `connection`.

You can easily get one from the pool:

    require 'vertx-db2-client/db2_pool'

    # Connect options
    connectOptions = {
      'port' => 50000,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret"
    }

    # Pool options
    poolOptions = {
      'maxSize' => 5
    }

    # Create the pooled client
    client = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

    # Get a connection from the pool
    client.get_connection() { |ar1_err,ar1|

      if (ar1_err == nil)

        puts "Connected"

        # Obtain our connection
        conn = ar1

        # All operations execute on the same connection
        conn.query("SELECT * FROM users WHERE id='julien'").execute() { |ar2_err,ar2|
          if (ar2_err == nil)
            conn.query("SELECT * FROM users WHERE id='emad'").execute() { |ar3_err,ar3|
              # Release the connection to the pool
              conn.close()
            }
          else
            # Release the connection to the pool
            conn.close()
          end
        }
      else
        puts "Could not connect: #{ar1_err.get_message()}"
      end
    }

Once you are done with the connection you must close it to release it to the pool, so it can be reused.

## Configuration

There are several alternatives for you to configure the client.

### data object

A simple way to configure the client is to specify a `DB2ConnectOptions` data object.

    require 'vertx-db2-client/db2_pool'

    # Data object
    connectOptions = {
      'port' => 50000,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret"
    }

    # Pool Options
    poolOptions = {
      'maxSize' => 5
    }

    # Create the pool from the data object
    pool = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

    pool.get_connection() { |ar_err,ar|
      # Handling your connection
    }

You can also configure the generic properties with the `setProperties` or `addProperty` methods. Note `setProperties` will override the default client properties.

### connection uri

Apart from configuring with a `DB2ConnectOptions` data object, We also provide you an alternative way to connect when you want to configure with a connection URI:

    require 'vertx-db2-client/db2_pool'
    require 'vertx-db2-client/db2_connection'

    # Connection URI
    connectionUri = "db2://dbuser:secretpassword@database.server.com:50000/mydb"

    # Create the pool from the connection URI
    pool = VertxDb2Client::DB2Pool.pool(connectionUri)

    # Create the connection from the connection URI
    VertxDb2Client::DB2Connection.connect(vertx, connectionUri) { |res_err,res|
      # Handling your connection
    }

The URI format for a connection string is:

    db2://<USERNAME>:<PASSWORD>@<HOSTNAME>:<PORT>/<DBNAME>

Currently the client supports the following parameter key words in connection uri

- host

- port

- user

- password

- dbname

Note: configuring properties in connection URI will override the default properties.

## Running queries

When you don’t need a transaction or run single queries, you can run queries directly on the pool; the pool will use one of its connection to run the query and return the result to you.

Here is how to run simple queries:

    client.query("SELECT * FROM users WHERE id='andy'").execute() { |ar_err,ar|
      if (ar_err == nil)
        result = ar
        puts "Got #{result.size()} rows "
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

### Prepared queries

You can do the same with prepared queries.

The SQL string can refer to parameters by position, using the database syntax `?`​

    require 'vertx-sql-client/tuple'
    client.prepared_query("SELECT * FROM users WHERE id=$1").execute(VertxSqlClient::Tuple.of("andy")) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts "Got #{rows.size()} rows "
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

Query methods provides an asynchronous `RowSet` instance that works for SELECT queries

    client.prepared_query("SELECT first_name, last_name FROM users").execute() { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        rows.each do |row|
          puts "User #{row.get_string(0)} #{row.get_string(1)}"
        end
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

or UPDATE/INSERT queries:

    require 'vertx-sql-client/tuple'
    client.prepared_query("INSERT INTO users (first_name, last_name) VALUES ($1, $2)").execute(VertxSqlClient::Tuple.of("Andy", "Guibert")) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts rows.row_count()
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

The `Row` gives you access to your data by index

    puts "User #{row.get_string(0)} #{row.get_string(1)}"

or by name

    puts "User #{row.get_string("first_name")} #{row.get_string("last_name")}"

The client will not do any magic here and the column name is identified with the name in the table regardless of how your SQL text is.

You can access a wide variety of of types

    firstName = row.get_string("first_name")
    male = row.get_boolean?("male")
    age = row.get_integer("age")

    # ...

You can use cached prepared statements to execute one-shot prepared queries:

    require 'vertx-sql-client/tuple'

    # Enable prepare statements caching
    connectOptions['cachePreparedStatements'] = true
    client.prepared_query("SELECT * FROM users WHERE id = ?").execute(VertxSqlClient::Tuple.of("julien")) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts "Got #{rows.size()} rows "
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

You can create a `PreparedStatement` and manage the lifecycle by yourself.

    require 'vertx-sql-client/tuple'
    sqlConnection.prepare("SELECT * FROM users WHERE id= ?") { |ar_err,ar|
      if (ar_err == nil)
        preparedStatement = ar
        preparedStatement.query().execute(VertxSqlClient::Tuple.of("julien")) { |ar2_err,ar2|
          if (ar2_err == nil)
            rows = ar2
            puts "Got #{rows.size()} rows "
            preparedStatement.close()
          else
            puts "Failure: #{ar2_err.get_message()}"
          end
        }
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

### Batches

You can execute prepared batch

    require 'vertx-sql-client/tuple'

    # Add commands to the batch
    batch = Array.new
    batch.push(VertxSqlClient::Tuple.of("julien", "Julient Viet"))
    batch.push(VertxSqlClient::Tuple.of("emad", "Emad Alblueshi"))
    batch.push(VertxSqlClient::Tuple.of("andy", "Andy Guibert"))

    # Execute the prepared batch
    client.prepared_query("INSERT INTO USERS (id, name) VALUES ($1, $2)").execute_batch(batch) { |res_err,res|
      if (res_err == nil)

        # Process rows
        rows = res
      else
        puts "Batch failed #{res_err}"
      end
    }

You can fetch generated keys by wrapping your query in `SELECT <COLUMNS> FROM FINAL TABLE ( <SQL> )`, for example:

    require 'vertx-sql-client/tuple'
    client.prepared_query("SELECT color_id FROM FINAL TABLE ( INSERT INTO color (color_name) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of("white", "red", "blue")) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts "Inserted #{rows.row_count()} new rows."
        rows.each do |row|
          puts "generated key: #{row.get_integer("color_id")}"
        end
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

## Using connections

When you need to execute sequential queries (without a transaction), you can create a new connection or borrow one from the pool:

    Code not translatable

Prepared queries can be created:

    require 'vertx-sql-client/tuple'
    connection.prepare("SELECT * FROM users WHERE first_name LIKE $1") { |ar1_err,ar1|
      if (ar1_err == nil)
        pq = ar1
        pq.query().execute(VertxSqlClient::Tuple.of("andy")) { |ar2_err,ar2|
          if (ar2_err == nil)
            # All rows
            rows = ar2
          end
        }
      end
    }

## Using transactions

### Transactions with connections

You can execute transaction using SQL `BEGIN`/`COMMIT`/`ROLLBACK`, if you do so you must use a `SqlConnection` and manage it yourself.

Or you can use the transaction API of `SqlConnection`:

    pool.get_connection() { |res_err,res|
      if (res_err == nil)

        # Transaction must use a connection
        conn = res

        # Begin the transaction
        tx = conn.begin()

        # Various statements
        conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute() { |ar1_err,ar1|
          if (ar1_err == nil)
            conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute() { |ar2_err,ar2|
              if (ar2_err == nil)
                # Commit the transaction
                tx.commit() { |ar3_err,ar3|
                  if (ar3_err == nil)
                    puts "Transaction succeeded"
                  else
                    puts "Transaction failed #{ar3_err.get_message()}"
                  end
                  # Return the connection to the pool
                  conn.close()
                }
              else
                # Return the connection to the pool
                conn.close()
              end
            }
          else
            # Return the connection to the pool
            conn.close()
          end
        }
      end
    }

When the database server reports the current transaction is failed (e.g the infamous current transaction is aborted, commands ignored until end of transaction block), the transaction is rollbacked and the `abortHandler` is called:

    tx.abort_handler() { |v|
      puts "Transaction failed => rollbacked"
    }

### Simplified transaction API

When you use a pool, you can start a transaction directly on the pool.

It borrows a connection from the pool, begins the transaction and releases the connection to the pool when the transaction ends.

    # Acquire a transaction and begin the transaction
    pool.begin() { |res_err,res|
      if (res_err == nil)

        # Get the transaction
        tx = res

        # Various statements
        tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute() { |ar1_err,ar1|
          if (ar1_err == nil)
            tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute() { |ar2_err,ar2|
              if (ar2_err == nil)
                # Commit the transaction
                # the connection will automatically return to the pool
                tx.commit() { |ar3_err,ar3|
                  if (ar3_err == nil)
                    puts "Transaction succeeded"
                  else
                    puts "Transaction failed #{ar3_err.get_message()}"
                  end
                }
              end
            }
          else
            # No need to close connection as transaction will abort and be returned to the pool
          end
        }
      end
    }

Note | this code will not close the connection because it will always be released back to the pool when the transaction

## Cursors and streaming

By default prepared query execution fetches all rows, you can use a `Cursor` to control the amount of rows you want to read:

    require 'vertx-sql-client/tuple'
    connection.prepare("SELECT * FROM users WHERE age > ?") { |ar1_err,ar1|
      if (ar1_err == nil)
        pq = ar1

        # Create a cursor
        cursor = pq.cursor(VertxSqlClient::Tuple.of(18))

        # Read 50 rows
        cursor.read(50) { |ar2_err,ar2|
          if (ar2_err == nil)
            rows = ar2

            # Check for more ?
            if (cursor.has_more?())
              # Repeat the process...
            else
              # No more rows - close the cursor
              cursor.close()
            end
          end
        }
      end
    }

Cursors shall be closed when they are released prematurely:

    cursor.read(50) { |ar2_err,ar2|
      if (ar2_err == nil)
        # Close the cursor
        cursor.close()
      end
    }

A stream API is also available for cursors, which can be more convenient, specially with the Rxified version.

    require 'vertx-sql-client/tuple'
    connection.prepare("SELECT * FROM users WHERE age > ?") { |ar1_err,ar1|
      if (ar1_err == nil)
        pq = ar1

        # Fetch 50 rows at a time
        stream = pq.create_stream(50, VertxSqlClient::Tuple.of(18))

        # Use the stream
        stream.exception_handler() { |err|
          puts "Error: #{err.get_message()}"
        }
        stream.end_handler() { |v|
          puts "End of stream"
        }
        stream.handler() { |row|
          puts "User: #{row.get_string("last_name")}"
        }
      end
    }

The stream read the rows by batch of `50` and stream them, when the rows have been passed to the handler, a new batch of `50` is read and so on.

The stream can be resumed or paused, the loaded rows will remain in memory until they are delivered and the cursor will stop iterating.

## DB2 type mapping

Currently the client supports the following DB2 types

- BOOLEAN (`java.lang.Boolean`) (DB2 LUW only)

- SMALLINT (`java.lang.Short`)

- INTEGER (`java.lang.Integer`)

- BIGINT (`java.lang.Long`)

- REAL (`java.lang.Float`)

- DOUBLE (`java.lang.Double`)

- DECIMAL (`io.vertx.sqlclient.data.Numeric`)

- CHAR (`java.lang.String`)

- VARCHAR (`java.lang.String`)

- ENUM (`java.lang.String`)

- DATE (`java.time.LocalDate`)

- TIME (`java.time.LocalTime`)

- TIMESTAMP (`java.time.LocalDateTime`)

- BINARY (`byte[]`)

- VARBINARY (`byte[]`)

- ROWID (`io.vertx.db2client.impl.drda.DB2RowId` or `java.sql.RowId`) (DB2 z/OS only)

Some types that are currently NOT supported are:

- XML

- BLOB

- CLOB

- DBCLOB

- GRAPHIC / VARGRAPHIC

For a further documentation on DB2 data types, see the following resources:

- DB2 for LUW 11.5 data types

- DB2 for z/OS 12.0 data types

Tuple decoding uses the above types when storing values, it also performs on the fly conversion of the actual value when possible:

    pool.query("SELECT an_int_column FROM exampleTable").execute() { |ar_err,ar|
      rowSet = ar
      row = rowSet.iterator().next()

      # Stored as INTEGER column type and represented as java.lang.Integer
      value = row.get_value(0)

      # Convert to java.lang.Long
      longValue = row.get_long(0)
    }

## Using Java enum types

You can map Java enum types to these column types:

- Strings (VARCHAR, TEXT)

- Numbers (SMALLINT, INTEGER, BIGINT)

    require 'vertx-sql-client/tuple'
    client.prepared_query("SELECT day_name FROM FINAL TABLE ( INSERT INTO days (day_name) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of(:FRIDAY, :SATURDAY, :SUNDAY)) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts "Inserted #{rows.row_count()} new rows"
        rows.each do |row|
          puts "Day: #{row.get_values(:class, row.get_column_index("day_name"))}"
        end
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

    require 'vertx-sql-client/tuple'
    client.prepared_query("SELECT day_num FROM FINAL TABLE ( INSERT INTO days (day_num) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of(:FRIDAY.ordinal(), :SATURDAY.ordinal(), :SUNDAY.ordinal())) { |ar_err,ar|
      if (ar_err == nil)
        rows = ar
        puts "Inserted #{rows.row_count()} new rows"
        rows.each do |row|
          puts "Day: #{row.get_values(:class, row.get_column_index("day_num"))}"
        end
      else
        puts "Failure: #{ar_err.get_message()}"
      end
    }

The String type is matched with the Java enum’s name returned by the `name()` method.

Number types are matched with the Java enum’s ordinal returned by the `ordinal()` method and the row.get() method returns the corresponding enum’s `name()` value at the ordinal position of the integer value retrieved.

## Collector queries

You can use Java collectors with the query API:

    Code not translatable

The collector processing must not keep a reference on the `Row` as there is a single row used for processing the entire set.

The Java `Collectors` provides many interesting predefined collectors, for example you can create easily create a string directly from the row set:

    Code not translatable

## Using SSL/TLS

To configure the client to use SSL connection, you can configure the `DB2ConnectOptions` like a Vert.x `NetClient`.

    require 'vertx-db2-client/db2_connection'

    options = {
      'port' => 50001,
      'host' => "the-host",
      'database' => "the-db",
      'user' => "user",
      'password' => "secret",
      'ssl' => true,
      'trustStoreOptions' => {
        'path' => "/path/to/keystore.p12",
        'password' => "keystoreSecret"
      }
    }

    VertxDb2Client::DB2Connection.connect(vertx, options) { |res_err,res|
      if (res_err == nil)
        # Connected with SSL
      else
        puts "Could not connect #{res_err}"
      end
    }

More information can be found in the Vert.x documentation.

## Using a proxy

You can also configure the client to use an HTTP/1.x CONNECT, SOCKS4a or SOCKS5 proxy.

More information can be found in the Vert.x documentation.