Value of parameter passed into the SQL statement

value

TypedValue

Value of parameter passed into the SQL statement


ReadableCharacterChannel

string

init

NClobValue

Represents Array Out Parameter used in procedure calls.


Parses returned SQL value to ballerina value.



Type description of the data that need to be converted


typeDesc

ArrayOutParameter

Value of parameter passed into the SQL statement
# Deprecated
This `ArrayValue` class deprecated by introducing the a new `ArrayValueType` type.


ArrayValue

Represents BigInt Array Out Parameter used in procedure calls


BigIntArrayOutParameter

BigIntArrayValue

Represents BigInt Out Parameter used in procedure calls.


BigIntOutParameter

BigIntValue

Represents Binary Array Out Parameter used in procedure calls.


BinaryArrayOutParameter

BinaryArrayValue

Represents Binary Out Parameter used in procedure calls.


BinaryOutParameter

ReadableByteChannel

BinaryValue

Represents Bit Array Out Parameter used in procedure calls.


BitArrayOutParameter

BitArrayValue

Represents Bit Out Parameter used in procedure calls.


BitOutParameter

boolean

BitValue

Represents Blob Out Parameter used in procedure calls.


BlobOutParameter

BlobValue

Represents Boolean Array Out Parameter used in procedure calls.


BooleanArrayOutParameter

BooleanArrayValue

Represents Boolean Out Parameter used in procedure calls.


BooleanOutParameter

BooleanValue

Represents Char Array Out Parameter used in procedure calls


Parses returned Char SQL value to ballerina value.



CharArrayOutParameter

CharArrayValue

Represents Char Out Parameter used in procedure calls.


CharOutParameter

CharValue

Represents Clob Out Parameter used in procedure calls.


ClobOutParameter

ClobValue

Represents Date Array Out Parameter used in procedure calls.


DateArrayOutParameter

DateArrayValue

Represents Date Out Parameter used in procedure calls.


DateOutParameter

Represents DateTime Array Out Parameter used in procedure calls.


DateTimeArrayOutParameter

DateTimeArrayValue

Represents DateTime Out Parameter used in procedure calls.


DateTimeOutParameter

Civil

DateTimeValue

Date

DateValue

Represents Decimal Out Parameter used in procedure calls.


DecimalArrayOutParameter

DecimalArrayValue

DecimalOutParameter

decimal

DecimalValue

Represents Double Array Out Parameter used in procedure calls.


DoubleArrayOutParameter

DoubleArrayValue

Represents Double Out Parameter used in procedure calls.


DoubleOutParameter

float

DoubleValue

Represents Float Array Out Parameter used in procedure calls.


FloatArrayOutParameter

FloatArrayValue

Represents Float Out Parameter used in procedure calls.


FloatOutParameter

FloatValue

Represents SQL InOutParameter used in procedure calls.


Value

InOutParameter

Represents Integer Array Out Parameter used in procedure calls.


IntegerArrayOutParameter

IntegerArrayValue

Represents Integer Out Parameter used in procedure calls.


IntegerOutParameter

IntegerValue

Represents NChar Out Parameter used in procedure calls.


NCharOutParameter

NCharValue

Represents NClob Out Parameter used in procedure calls.


NClobOutParameter

Represents Numeric Array Out Parameter used in procedure calls.


NumericArrayOutParameter

NumericArrayValue

Represents Numeric Out Parameter used in procedure calls.


NumericOutParameter

NumericValue

Represents NVarchar Array Out Parameter used in procedure calls.


NVarcharArrayOutParameter

Represents Varchar NVarchar  SQL field.



NVarcharArrayValue

Represents NVarchar Out Parameter used in procedure calls.


NVarcharOutParameter

NVarcharValue

Object that is used to return stored procedure call results.



Summary of the execution of DML/DLL query


executionResult

ExecutionResult

queryResult

Error

stream

The instance of the custom Ballerina class that is structurally equivalent to
the `customResultIterator` object type. This instance includes a custom implementation
of the `getNextQueryResult` method


customResultIterator

CustomResultIterator

Updates `executionResult` or `queryResult` with the next result in the result. This will also close the current
results by default.



getNextQueryResult

True if the next result is `queryResult`


Closes the `sql:ProcedureCallResult` object and releases the associated resources.



close

An `sql:Error` if any error occurred while closing


ProcedureCallResult

Represents Real Array Out Parameter used in procedure calls.


RealArrayOutParameter

RealArrayValue

Represents Real Out Parameter used in procedure calls.


RealOutParameter

RealValue

Represents Ref Out Parameter used in procedure calls.


RefOutParameter

RefValue

The result iterator object that is used to iterate through the results in the event stream.



The instance of the custom Ballerina class that is structurally equivalent to
the `customResultIterator` object type. This instance includes a custom implementation
of the `nextResult` method


ResultIterator

Represents Row Out Parameter used in procedure calls.


RowOutParameter

byte

RowValue

Represents SmallInt Array Out Parameter used in procedure calls.


SmallIntArrayOutParameter

SmallIntArrayValue

Represents SmallInt Out Parameter used in procedure calls.


SmallIntOutParameter

SmallIntValue

Represents Struct Out Parameter used in procedure calls.


StructOutParameter

StructValue

Represents Text Out Parameter used in procedure calls.


TextOutParameter

TextValue

Represents Time Array Out Parameter used in procedure calls.


TimeArrayOutParameter

TimeArrayValue

Represents Time Out Parameter used in procedure calls.


TimeOutParameter

Represents Timestamp Array Out Parameter used in procedure calls.


TimestampArrayOutParameter

TimestampArrayValue

Represents Timestamp Out Parameter used in procedure calls.


TimestampOutParameter

TimestampValue

Represents Timestamp with Timezone Array Out Parameter used in procedure calls.


TimestampWithTimezoneArrayOutParameter

Represents Timestamp with Timezone Out Parameter used in procedure calls.


TimestampWithTimezoneOutParameter

TimeOfDay

TimeValue

Represents Time With Timezone Array Out Parameter used in procedure calls.


TimeWithTimezoneArrayOutParameter

Represents Time With Timezone Out Parameter used in procedure calls.


TimeWithTimezoneOutParameter

Represents VarBinary Array Out Parameter used in procedure calls.


VarBinaryArrayOutParameter

VarBinaryArrayValue

Represents VarBinary Out Parameter used in procedure calls.


VarBinaryOutParameter

VarBinaryValue

Represents Varchar Array Out Parameter used in procedure calls.


VarcharArrayOutParameter

VarcharArrayValue

Represents Varchar Out Parameter used in procedure calls.


VarcharOutParameter

Constant indicating that the specific batch statement failed.


EXECUTION_FAILED

Constant indicating that the specific batch statement executed successfully
but that no count of the number of rows it affected is available.


SUCCESS_NO_INFO

## Overview

This module provides the common interface and functionality to interact with a database. The corresponding database
clients can be created by using specific database modules such as `mysql` or using the Java Database Connectivity 
module `jdbc`.

### List of Database Modules
Ballerina now has the [`jdbc` module](https://docs.central.ballerina.io/ballerinax/java.jdbc/latest) as the generic DB connector module to connect to any relational database by simply providing the JDBC URL and the other related properties.
Ballerina also provides specially designed various database-specific DB connectors so that you can work with different databases and you can access their DB-specific functionalities.

### Client

The database client should be created using any of the above-listed database modules and once it is created, the operations and functionality explained below can be used. 

#### Connection Pool Handling

All database modules share the same connection pooling concept and there are three possible scenarios for 
connection pool handling.  For its properties and possible values, see the [`sql:ConnectionPool`](https://docs.central.ballerina.io/ballerina/sql/latest/records/ConnectionPool).  

1. Global, shareable, default connection pool

    If you do not provide the `poolOptions` field when creating the database client, a globally-shareable pool will be 
    created for your database unless a connection pool matching with the properties you provided already exists. 
    The JDBC module sample below shows how the global connection pool is used. 

    ```ballerina
    jdbc:Client|sql:Error dbClient = 
                               new ("jdbc:mysql://localhost:3306/testdb", 
                                "root", "root");
    ```

2. Client-owned, unsharable connection pool

    If you define the `connectionPool` field inline when creating the database client with the `sql:ConnectionPool` type, 
    an unsharable connection pool will be created. The JDBC module sample below shows how the global 
    connection pool is used.

    ```ballerina
    jdbc:Client|sql:Error dbClient = 
                            new (url = "jdbc:mysql://localhost:3306/testdb",
                            connectionPool = { maxOpenConnections: 5 });
    ```

3. Local, shareable connection pool

    If you create a record of the `sql:ConnectionPool` type and reuse that in the configuration of multiple clients, 
    for each set of clients that connects to the same database instance with the same set of properties, a shared 
    connection pool will be created. The JDBC module sample below shows how the global connection pool is used.

    ```ballerina
    sql:ConnectionPool connPool = {maxOpenConnections: 5};
    
    jdbc:Client|sql:Error dbClient1 =       
                            new (url = "jdbc:mysql://localhost:3306/testdb",
                            connectionPool = connPool);
    jdbc:Client|sql:Error dbClient2 = 
                            new (url = "jdbc:mysql://localhost:3306/testdb",
                            connectionPool = connPool);
    jdbc:Client|sql:Error dbClient3 = 
                            new (url = "jdbc:mysql://localhost:3306/testdb",
                            connectionPool = connPool);
    ```
    
#### Closing the Client

Once all the database operations are performed, you can close the database client you have created by invoking the `close()`
operation. This will close the corresponding connection pool if it is not shared by any other database clients. 

```ballerina
error? e = dbClient.close();
```
Or
```ballerina
check dbClient.close();
```

### Database Operations

Once the client is created, database operations can be executed through that client. This module defines the interface 
and common properties that are shared among multiple database clients.  It also supports querying, inserting, deleting, 
updating, and batch updating data.  

#### Parameterized Query

The `sql:ParameterizedQuery` is used to construct the SQL query to be executed by the client.
You can create a query with constant or dynamic input data as follows.

*Query with constant values*

```ballerina
sql:ParameterizedQuery query = `SELECT * FROM students 
                                WHERE id < 10 AND age > 12`;
```

*Query with dynamic values*

```ballerina
int[] ids = [10, 50];
int age = 12;
sql:ParameterizedQuery query = `SELECT * FROM students 
                                WHERE id < ${ids[0]} AND age > ${age}`;
```

Moreover, the SQL package has `sql:queryConcat()` and `sql:arrayFlattenQuery()` util functions which make it easier
to create a dynamic/constant complex query.

The `sql:queryConcat()` is used to create a parameterized query by concatenating a set of parameterized queries.
The sample below shows how to concatenate queries.

```ballerina
int id = 10;
int age = 12;
sql:ParameterizedQuery query = `SELECT * FROM students`;
sql:ParameterizedQuery query1 = ` WHERE id < ${id} AND age > ${age}`;
sql:ParameterizedQuery sqlQuery = sql:queryConcat(query, query1);
```

The query with the `IN` operator can be created using the `sql:ParameterizedQuery` like below. Here you need to flatten the array and pass each element separated by a comma.

```ballerina
int[] ids = [1, 2, 3];
sql:ParameterizedQuery query = `SELECT count(*) as total FROM DataTable 
                                WHERE row_id in (${ids[0]}, ${ids[1]}, ${ids[2]})`;
```

The util function `sql:arrayFlattenQuery()` is introduced to make the array flatten easier. It makes the inclusion of varying array elements into the query easier by flattening the array to return a parameterized query. You can construct the complex dynamic query with the `IN` operator by using both functions like below.

```ballerina
int[] ids = [1, 2];
sql:ParameterizedQuery sqlQuery = 
                         sql:queryConcat(`SELECT * FROM DataTable WHERE id IN (`, 
                                          sql:arrayFlattenQuery(ids), `)`);
```

#### Creating Tables

This sample creates a table with two columns. One column is of type `int` and the other is of type `varchar`.
The `CREATE` statement is executed via the `execute` remote function of the client.

```ballerina
// Create the ‘Students’ table with the  ‘id’, 'name', and ‘age’ fields.
sql:ExecutionResult result = 
                check dbClient->execute(`CREATE TABLE student (
                                           id INT AUTO_INCREMENT,
                                           age INT, 
                                           name VARCHAR(255), 
                                           PRIMARY KEY (id)
                                         )`);
//A value of the sql:ExecutionResult type is returned for 'result'. 
```

#### Inserting Data

These samples show the data insertion by executing an `INSERT` statement using the `execute` remote function 
of the client.

In this sample, the query parameter values are passed directly into the query statement of the `execute` 
remote function.

```ballerina
sql:ExecutionResult result = check dbClient->execute(`INSERT INTO student(age, name)
                                                        VALUES (23, 'john')`);
```

In this sample, the parameter values, which are in local variables are used to parameterize the SQL query in 
the `execute` remote function. This type of a parameterized SQL query can be used with any primitive Ballerina type 
like `string`, `int`, `float`, or `boolean` and in that case, the corresponding SQL type of the parameter is derived 
from the type of the Ballerina variable that is passed in. 

```ballerina
string name = "Anne";
int age = 8;

sql:ParameterizedQuery query = `INSERT INTO student(age, name)
                                  VALUES (${age}, ${name})`;
sql:ExecutionResult result = check dbClient->execute(query);
```

In this sample, the parameter values are passed as a `sql:TypedValue` to the `execute` remote function. Use the 
corresponding subtype of the `sql:TypedValue` such as `sql:VarcharValue`, `sql:CharValue`, `sql:IntegerValue`, etc., when you need to 
provide more details such as the exact SQL type of the parameter.

```ballerina
sql:VarcharValue name = new ("James");
sql:IntegerValue age = new (10);

sql:ParameterizedQuery query = `INSERT INTO student(age, name)
                                  VALUES (${age}, ${name})`;
sql:ExecutionResult result = check dbClient->execute(query);
```

#### Inserting Data With Auto-generated Keys

This sample demonstrates inserting data while returning the auto-generated keys. It achieves this by using the 
`execute` remote function to execute the `INSERT` statement.

```ballerina
int age = 31;
string name = "Kate";

sql:ParameterizedQuery query = `INSERT INTO student(age, name)
                                  VALUES (${age}, ${name})`;
sql:ExecutionResult result = check dbClient->execute(query);
//Number of rows affected by the execution of the query.
int? count = result.affectedRowCount;
//The integer or string generated by the database in response to a query execution.
string|int? generatedKey = result.lastInsertId;
```

#### Querying Data

These samples show how to demonstrate the different usages of the `query` operation to query the
database table and obtain the results.

This sample demonstrates querying data from a table in a database.
First, a type is created to represent the returned result set. This record can be defined as an open or a closed record
according to the requirement. If an open record is defined, the returned stream type will include both defined fields
in the record and additional database columns fetched by the SQL query which are not defined in the record.
Note the mapping of the database column to the returned record's property is case-insensitive if it is defined in the
record(i.e., the `ID` column in the result can be mapped to the `id` property in the record). Additional Column names
added to the returned record as in the SQL query. If the record is defined as a close record, only defined fields in the
record are returned or gives an error when additional columns present in the SQL query. Next, the `SELECT` query is executed
via the `query` remote function of the client. Once the query is executed, each data record can be retrieved by looping 
the result set. The `stream` returned by the select operation holds a pointer to the actual data in the database and it 
loads data from the table only when it is accessed. This stream can be iterated only once.

```ballerina
// Define an open record type to represent the results.
type Student record {
    int id;
    int age;
    string name;
};

// Select the data from the database table. The query parameters are passed 
// directly. Similar to the `execute` samples, parameters can be passed as
// sub types of `sql:TypedValue` as well.
int id = 10;
int age = 12;
sql:ParameterizedQuery query = `SELECT * FROM students
                                WHERE id < ${id} AND age > ${age}`;
stream<Student, sql:Error?> resultStream = dbClient->query(query);

// Iterating the returned table.
error? e = resultStream.forEach(function(Student student) {
   //Can perform operations using the record 'student' of type `Student`.
});
```

Defining the return type is optional and you can query the database without providing the result type. Hence, 
the above sample can be modified as follows with an open record type as the return type. The property name in the open record 
type will be the same as how the column is defined in the database. 

```ballerina
// Select the data from the database table. The query parameters are passed 
// directly. Similar to the `execute` samples, parameters can be passed as 
// sub types of `sql:TypedValue` as well.
int id = 10;
int age = 12;
sql:ParameterizedQuery query = `SELECT * FROM students
                                WHERE id < ${id} AND age > ${age}`;
stream<record{}, sql:Error?> resultStream = dbClient->query(query);

// Iterating the returned table.
error? e = resultStream.forEach(function(record{} student) {
    // Can perform operations using the record 'student'.
    io:println("Student name: ", student.value["name"]);
});
```

There are situations in which you may not want to iterate through the database and in that case, you may decide
to use the `sql:queryRow()` operation. If the provided return type is a record, this method returns only the first row 
retrieved by the query as a record.

```ballerina
int id = 10;
sql:ParameterizedQuery query = `SELECT * FROM students WHERE id = ${id}`;
Student retrievedStudent = check dbClient->queryRow(query);
```

The `sql:queryRow()` operation can also be used to retrieve a single value from the database (e.g., when querying using 
`COUNT()` and other SQL aggregation functions). If the provided return type is not a record (i.e., a primitive data type)
, this operation will return the value of the first column of the first row retrieved by the query.

```ballerina
int age = 12;
sql:ParameterizedQuery query = `SELECT COUNT(*) FROM students WHERE age < ${age}`;
int youngStudents = check dbClient->queryRow(query);
```

#### Updating Data

This sample demonstrates modifying data by executing an `UPDATE` statement via the `execute` remote function of 
the client.

```ballerina
int age = 23;
sql:ParameterizedQuery query = `UPDATE students SET name = 'John' WHERE age = ${age}`;
sql:ExecutionResult result = check dbClient->execute(query);
```

#### Deleting Data

This sample demonstrates deleting data by executing a `DELETE` statement via the `execute` remote function of 
the client.

```ballerina
string name = "John";
sql:ParameterizedQuery query = `DELETE from students WHERE name = ${name}`;
sql:ExecutionResult result = check dbClient->execute(query);
```

#### Batch Updating Data

This sample demonstrates how to insert multiple records with a single `INSERT` statement that is executed via the 
`batchExecute` remote function of the client. This is done by creating a `table` with multiple records and 
parameterized SQL query as same as the above `execute` operations.

```ballerina
// Create the table with the records that need to be inserted.
var data = [
  { name: "John", age: 25  },
  { name: "Peter", age: 24 },
  { name: "jane", age: 22 }
];

// Do the batch update by passing the batches.
sql:ParameterizedQuery[] batch = from var row in data
                                 select `INSERT INTO students ('name', 'age')
                                           VALUES (${row.name}, ${row.age})`;
sql:ExecutionResult[] result = check dbClient->batchExecute(batch);
```

#### Execute SQL Stored Procedures

This sample demonstrates how to execute a stored procedure with a single `INSERT` statement that is executed via the 
`call` remote function of the client.

```ballerina
int uid = 10;
sql:IntegerOutParameter insertId = new;

sql:ProcedureCallResult result = 
                         check dbClient->call(`call InsertPerson(${uid}, ${insertId})`);
stream<record{}, sql:Error?>? resultStr = result.queryResult;
if resultStr is stream<record{}, sql:Error?> {
    sql:Error? e = resultStr.forEach(function(record{} result) {
      // Can perform operations using the record 'result'.
    });
}
check result.close();
```
Note that you have to invoke the close operation explicitly on the `sql:ProcedureCallResult` to release the connection resources and avoid a connection leak as shown above.

>**Note:** The default thread pool size used in Ballerina is: `the number of processors available * 2`. You can configure the thread pool size by using the `BALLERINA_MAX_POOL_SIZE` environment variable.


Represents an error originating from application-level causes.


ApplicationError

Represents an error occurred when a batch execution is running.


error

BatchExecuteError

This module provides the common interface and functionality to interact with a database. The corresponding database


Class: NClobValue

Fields