= Reactive MSSQL Client
:PREPARED_PARAMS: `@p1`, `@p2`, etc…​

The Reactive MSSQL Client is a client for Microsoft SQL Server 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*

* Event driven
* Lightweight
* Built-in connection pooling
* Direct memory to object without unnecessary copies
* Java 8 Date and Time
* RxJava API
* SSL/TLS
* Cursor
* Row streaming

*Not supported yet*

* Prepared queries caching

== Usage

To use the Reactive MSSQL Client add the following dependency to the _dependencies_ section of your build descriptor:

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

[source,xml]
----
<dependency>
  <groupId>${maven.groupId}</groupId>
  <artifactId>${maven.artifactId}</artifactId>
  <version>${maven.version}</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

[source,groovy]
----
dependencies {
  compile '${maven.groupId}:${maven.artifactId}:${maven.version}'
}
----

== Getting started

Here is the simplest way to connect, query and disconnect

[source,$lang]
----
{@link examples.MSSQLClientExamples#gettingStarted()}
----

== Connecting to SQL Server

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

[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting01}
----

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:

[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting02}
----

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

[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting03}
----

When you need to execute several operations on the same connection, you need to use a client
{@link io.vertx.mssqlclient.MSSQLConnection connection}.

You can easily get one from the pool:

[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting04}
----

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

== Configuration

=== Data Object

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

[source,$lang]
----
{@link examples.MSSQLClientExamples#configureFromDataObject(io.vertx.core.Vertx)}
----

=== Connection URI

As an alternative to configuring the client with a {@link io.vertx.mssqlclient.MSSQLConnectOptions} data object, you can use a connection URI:

[source,$lang]
----
{@link examples.MSSQLClientExamples#configureFromUri(io.vertx.core.Vertx)}
----

The connection URI format is defined by the client in an idiomatic way:

----
sqlserver://[user[:[password]]@]host[:port][/database][?<key1>=<value1>[&<key2>=<value2>]]
----

Currently, the client supports the following parameter keys:

* `host`
* `port`
* `user`
* `password`
* `database`

NOTE: Configuring parameters in connection URI will override the default properties.

== Connect retries

You can configure the client to retry when a connection fails to be established.

[source,$lang]
----
{@link examples.MSSQLClientExamples#reconnectAttempts}
----

include::queries.adoc[leveloffset=1]

== Working with `identity` columns

You can retrieve the value of an `identity` column after inserting new data using the `OUTPUT` clause:

[source,$lang]
----
{@link examples.MSSQLClientExamples#identityColumn}
----

include::connections.adoc[]

include::transactions.adoc[]

include::cursor.adoc[]

== Tracing queries

include::tracing.adoc[]

== Data types supported

Currently, the client supports the following SQL Server types:

* TINYINT(`java.lang.Short`)
* SMALLINT(`java.lang.Short`)
* INT(`java.lang.Integer`)
* BIGINT(`java.lang.Long`)
* BIT(`java.lang.Boolean`)
* REAL(`java.lang.Float`)
* DOUBLE(`java.lang.Double`)
* NUMERIC/DECIMAL(`{@link java.math.BigDecimal}`)
* CHAR/VARCHAR(`java.lang.String`)
* NCHAR/NVARCHAR(`java.lang.String`)
* DATE(`java.time.LocalDate`)
* TIME(`java.time.LocalTime`)
* SMALLDATETIME(`java.time.LocalDateTime`)
* DATETIME(`java.time.LocalDateTime`)
* DATETIME2(`java.time.LocalDateTime`)
* DATETIMEOFFSET(`java.time.OffsetDateTime`)
* BINARY/VARBINARY(`io.vertx.core.buffer.Buffer`)
* MONEY (`{@link java.math.BigDecimal}`)
* SMALLMONEY (`{@link java.math.BigDecimal}`)
* GUID (`{@link java.util.UUID}`)

Tuple decoding uses the above types when storing values.

=== Using Java `enum` types

SQL Server does not have `ENUM` data type, but the client can map the retrieved string/numeric data type to enum.

You can encode Java enums like this:

[source,$lang]
----
{@link examples.MSSQLClientExamples#enumeratedType01Example}
----

You can decode a Java enum like this:

[source,$lang]
----
{@link examples.MSSQLClientExamples#enumeratedType02Example}
----

=== Handling `NULL`

If you modify a `Tuple` with one of the `addXXX` methods, `null` values are handled transparently.

The client can infer the right SQL type when executing a prepared query:

[source,$lang]
----
{@link examples.MSSQLClientExamples#transparentNullHandling}
----

Otherwise, you should declare the type explicitly using one of the {@link io.vertx.sqlclient.data.NullValue} constants or the {@link io.vertx.sqlclient.data.NullValue#of} method:

[source,$lang]
----
{@link examples.MSSQLClientExamples#explicitNullHandling}
----

== Collector queries

You can use Java collectors with the query API:

[source,$lang]
----
{@link examples.MSSQLClientExamples#collector01Example(io.vertx.sqlclient.SqlClient)}
----

The collector processing must not keep a reference on the {@link io.vertx.sqlclient.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:

[source,$lang]
----
{@link examples.MSSQLClientExamples#collector02Example(io.vertx.sqlclient.SqlClient)}
----

== Information messages

SQL Server can send information messages to the client as part of responses to queries.
By default, they are logged at `WARN` level.

You can set a handler on a connection to catch them and do something useful with them.

[source,$lang]
----
{@link examples.MSSQLClientExamples#infoHandler}
----

== Using SSL/TLS

=== Encryption level negotiation

When a connection is established, the client and the server negotiate the encryption level.

The negotiated level depends on the client config in {@link io.vertx.mssqlclient.MSSQLConnectOptions} and the server config:

* no encryption: if `ssl` is set to `false` in client options, and the server does not support encryption
* encrypt login packet only: if `ssl` is set to `false` in client options, and the server supports encryption
* encrypt entire connection: if `ssl` is set to `true` in client options, or the server requires encryption

[NOTE]
====
The negotiation fails if `ssl` is set to `true` in client options, and the server does not support encryption.
In this case, the client terminates the connection.
====

=== Configuration

To configure `ssl` in client options, use the {@link io.vertx.mssqlclient.MSSQLConnectOptions#setSsl} method.
By default, `ssl` is set to `false`.

[source,$lang]
----
{@link examples.MSSQLClientExamples#setSsl}
----

When `ssl` is set to `false`, the client trusts all server certificates.
Otherwise, it performs hostname validation.

If `ssl` is set to true in client options, and the server uses a self-signed certificate, hostname validation can be disabled:

[source,$lang]
----
{@link examples.MSSQLClientExamples#disableHostnameValidation}
----

Alternatively, the client can be configured to trust the server certificate with {@link io.vertx.core.net.TrustOptions}.
For example, {@link io.vertx.core.net.PemTrustOptions} can be used if a PEM file contains the server certificate:

[source,$lang]
----
{@link examples.MSSQLClientExamples#usingTrustOptions}
----

For further details about SSL support in Vert.x, please refer to the https://vertx.io/docs/vertx-core/java/#ssl[Vert.x Core documentation].

== Pool sharing

include::pool_sharing.adoc[]

== Advanced pool configuration

include::pool_config.adoc[]

