ballerina/sql module

Module overview

This module provides the common record types and constants required for other data management modules such as jdbc, mysql, and h2.

PoolOptions

The PoolOptions type is the properties that are used to configure DB connection pool. This is used with jdbc, mysql, and h2 clients to configure the connection pool associated with the client.

SQLType

The SQLType represents the SQL data type of a given parameter. When using a parameter, use the same SQL type as the actual database table column type. Otherwise data loss can occur.

Direction

The Direction type represents the direction of the parameter used in call operation. IN parameters are used to send values to stored procedures or pass parameters to other operations such as select, update, etc. This is the default direction of a parameter. The OUT parameters are used to get values from stored procedures. The INOUT parameters are used to send values to stored procedures and retrieve values from stored procedures.

Parameter

The Parameter type represents a parameter for the SQL operations when a variable needs to be passed into the sql statement given in the operation.

UpdateResult

The UpdateResult type represents the output of the update remote function. It contains the updated row count and auto generated column values.

Module Detail

Records

Record Description
DatabaseErrorData
Parameter Parameter represents a parameter for the SQL remote functions when a variable parameter needs to be passed to the remote function.
PoolOptions Represents the properties which are used to configure DB connection pool. Default values of the fields can be set through the configuration API.
UpdateResult Result represents the output of the `update` remote function.

Objects

Object Description
GlobalPoolConfigContainer

Endpoints

Endpoint Description
AbstractSQLClient

The abstract SQL Client object for SQL databases.

Client

Represents the base SQL Client

Functions

Function Description
close

An internal function used by clients to shutdown the connection pool.

getGlobalPoolConfigContainer

Retrieves the final GlobalPoolConfigContainer object.

Type Definitions

Type Values Description
Direction DIRECTION_OUT | DIRECTION_INOUT | DIRECTION_IN

The direction of the parameter.

IN - IN parameters are used to send values to stored procedures OUT - OUT parameters are used to get values from stored procedures INOUT - INOUT parameters are used to send values and get values from stored procedures

SQLType TYPE_VARCHAR | TYPE_VARBINARY | TYPE_TINYINT | TYPE_TIMESTAMP | TYPE_TIME | TYPE_STRUCT | TYPE_SMALLINT | TYPE_REFCURSOR | TYPE_REAL | TYPE_NVARCHAR | TYPE_NUMERIC | TYPE_NCLOB | TYPE_NCHAR | TYPE_LONGVARCHAR | TYPE_LONGVARBINARY | TYPE_LONGNVARCHAR | TYPE_INTEGER | TYPE_FLOAT | TYPE_DOUBLE | TYPE_DECIMAL | TYPE_DATETIME | TYPE_DATE | TYPE_CLOB | TYPE_CHAR | TYPE_BOOLEAN | TYPE_BLOB | TYPE_BIT | TYPE_BINARY | TYPE_BIGINT | TYPE_ARRAY

The SQL Datatype of the parameter.

VARCHAR - Small, variable length character string CHAR - Small, fixed length character string LONGVARCHAR - Large, variable length character string NCHAR - Small, fixed length character string with unicode support LONGNVARCHAR - Large, variable length character string with unicode support

BIT - Single bit value that can be zero or one, or nil BOOLEAN - Boolean value either True or false TINYINT - 8-bit integer value which may be unsigned or signed SMALLINT - 16-bit signed integer value which may be unsigned or signed INTEGER - 32-bit signed integer value which may be unsigned or signed BIGINT - 64-bit signed integer value which may be unsigned or signed

NUMERIC - Fixed-precision and scaled decimal values DECIMAL - Fixed-precision and scaled decimal values REAL - Single precision floating point number FLOAT - Double precision floating point number DOUBLE - Double precision floating point number

BINARY - Small, fixed-length binary value BLOB - Binary Large Object LONGVARBINARY - Large, variable length binary value VARBINARY - Small, variable length binary value

CLOB - Character Large Object. NCLOB - Character large objects in multibyte national character set

DATE - Date consisting of day, month, and year TIME - Time consisting of hours, minutes, and seconds DATETIME - Both DATE and TIME with additional a nanosecond field TIMESTAMP - Both DATE and TIME with additional a nanosecond field

ARRAY - Composite data value that consists of zero or more elements of a specified data type STRUCT - User defined structured type, consists of one or more attributes REFCURSOR - Cursor value

public type DatabaseErrorData

Field Name Data Type Default Value Description
message string

public type Parameter

Parameter represents a parameter for the SQL remote functions when a variable parameter needs to be passed to the remote function.

Field Name Data Type Default Value Description
sqlType VARCHAR|CHAR|LONGVARCHAR|NCHAR|LONGNVARCHAR|NVARCHAR|BIT|BOOLEAN|TINYINT|SMALLINT|INTEGER|BIGINT|NUMERIC|DECIMAL|REAL|FLOAT|DOUBLE|BINARY|BLOB|LONGVARBINARY|VARBINARY|CLOB|NCLOB|DATE|TIME|DATETIME|TIMESTAMP|ARRAY|STRUCT|REFCURSOR

The data type of the corresponding SQL parameter

value any ()

Value of paramter passed into the SQL statement

direction IN|OUT|INOUT DIRECTION_IN

Direction of the SQL Parameter IN, OUT, or INOUT - Default value is IN

recordType typedesc

In case of OUT direction, if the sqlType is REFCURSOR, this represents the record type to map a result row

public type PoolOptions

Represents the properties which are used to configure DB connection pool. Default values of the fields can be set through the configuration API.

Field Name Data Type Default Value Description
connectionInitSql string config:getAsString(b7a.sql.connection.init.sql, defaultValue=)

SQL statement that will be executed after every new connection creation before adding it to the pool. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.init.sql"

dataSourceClassName string config:getAsString(b7a.sql.datasource.class.name, defaultValue=)

Name of the DataSource class provided by the JDBC driver. This is used on following scenarios. 1. In JDBC client when DB specific properties are required (which are given with dbOptions) 2. In any data client in which XA transactions enabled by isXA property and need to provide a custom XA implementation. Default value of this field can be set through the configuration API with the key "b7a.sql.datasource.class.name"

autoCommit boolean config:getAsBoolean(b7a.sql.connection.auto.commit, defaultValue=true)

Auto-commit behavior of connections returned from the pool. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.auto.commit"

isXA boolean config:getAsBoolean(b7a.sql.xa.enabled, defaultValue=false)

Whether Connections are used for a distributed transaction. Default value of this field can be set through the configuration API with the key "b7a.sql.xa.enabled"

maximumPoolSize int config:getAsInt(b7a.sql.max.pool.size, defaultValue=15)

Maximum size that the pool is allowed to reach, including both idle and in-use connections. Default value of this field can be set through the configuration API with the key "b7a.sql.max.pool.size"

connectionTimeout int config:getAsInt(b7a.sql.connection.time.out, defaultValue=30000)

Maximum number of milliseconds that a client will wait for a connection from the pool. Default is 30 seconds. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.time.out"

idleTimeout int config:getAsInt(b7a.sql.connection.idle.time.out, defaultValue=600000)

Maximum amount of time that a connection is allowed to sit idle in the pool. Default is 10 minutes. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.idle.time.out"

minimumIdle int config:getAsInt(b7a.sql.connection.min.idle.count, defaultValue=15)

Minimum number of idle connections that pool tries to maintain in the pool. Default is same as maximumPoolSize. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.min.idle.count"

maxLifetime int config:getAsInt(b7a.sql.connection.max.life.time, defaultValue=1800000)

Maximum lifetime of a connection in the pool. Default is 30 minutes. Default value of this field can be set through the configuration API with the key "b7a.sql.connection.max.life.time"

validationTimeout int config:getAsInt(b7a.sql.validation.time.out, defaultValue=5000)

Maximum amount of time that a connection will be tested for aliveness. Default 5 seconds Default value of this field can be set through the configuration API with the key "b7a.sql.validation.time.out"

public type UpdateResult

Result represents the output of the `update` remote function.

Field Name Data Type Default Value Description
updatedRowCount int

The updated row count during the sql statement exectuion

generatedKeys map<anydata>

A map of auto generated key values during the sql statement execution

public function close(sql:Client sqlClient) returns (error?<>)

An internal function used by clients to shutdown the connection pool.

Parameter Name Data Type Default Value Description
sqlClient sql:Client

The Client object which represents the connection pool.

Return Type Description
error?<>

Possible error during closing

public function getGlobalPoolConfigContainer() returns (GlobalPoolConfigContainer)

Retrieves the final GlobalPoolConfigContainer object.

Return Type Description
GlobalPoolConfigContainer

The final GlobalPoolConfigContainer object

public type GlobalPoolConfigContainer object

  • <GlobalPoolConfigContainer> getGlobalPoolConfig() returns (PoolOptions)

    Return Type Description
    PoolOptions

Endpoint AbstractSQLClient

The abstract SQL Client object for SQL databases.

  • <AbstractSQLClient> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$0>[]|null|error<>)

    The call remote function implementation for SQL Client to invoke stored procedures/functions.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    The SQL stored procedure to execute

    recordType typedesc[]?

    Array of record types of the returned tables if there is any

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the procedure/function call. The number of parameters is variable

    Return Type Description
    table<$anonType$0>[]|null|error<>

    A table[] if there are tables returned by the call remote function and else nil, error will be returned if there is any error

  • <AbstractSQLClient> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$1>|error<>)

    The select remote function implementation for SQL Client to select data from tables.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL query to execute

    recordType typedesc?

    Type of the returned table

    loadToMemory boolean false

    Indicates whether to load the retrieved data to memory or not

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the select query. The number of parameters is variable

    Return Type Description
    table<$anonType$1>|error<>

    A table returned by the sql query statement else error will be returned if there is any error

  • <AbstractSQLClient> update(string sqlQuery, string[]? keyColumns, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (UpdateResult|error<>)

    The update remote function implementation for SQL Client to update data and schema of the database.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL statement to execute

    keyColumns string[]? ()

    Names of auto generated columns for which the auto generated key values are returned

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the update query. The number of parameters is variable

    Return Type Description
    UpdateResult|error<>

    A sql:UpdateResult with the updated row count and key column values, else error will be returned if there is any error

  • <AbstractSQLClient> batchUpdate(string sqlQuery, string|int|boolean|float|decimal|byte[][][]|sql:Parameter? parameters) returns (int[]|error<>)

    The batchUpdate remote function implementation for SQL Client to batch data insert.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL statement to execute

    parameters string|int|boolean|float|decimal|byte[][][]|sql:Parameter?

    Variable number of parameter arrays each representing the set of parameters of belonging to each individual update

    Return Type Description
    int[]|error<>

    An int[] - The elements in the array returned by the operation may be one of the following or else anerror will be returned if there is any error. A number greater than or equal to zero - indicates that the command was processed successfully and is an update count giving the number of rows A value of -2 - Indicates that the command was processed successfully but that the number of rows affected is unknown A value of -3 - Indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails

Endpoint Client

Represents the base SQL Client

  • <Client> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$2>[]|null|error<>)

    The call remote function implementation for SQL connector to invoke stored procedures/functions.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    The SQL stored procedure to execute

    recordType typedesc[]?

    Array of record types of the returned tables if there is any

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the procedure/function call. The number of parameters is variable

    Return Type Description
    table<$anonType$2>[]|null|error<>

    A table[] if there are tables returned by the call remote function and else nil, error will be returned if there is any error

  • <Client> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$3>|error<>)

    The select remote function implementation for SQL Client to select data from tables.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL query to execute

    recordType typedesc?

    Type of the returned table

    loadToMemory boolean false

    Indicates whether to load the retrieved data to memory or not

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the select query. The number of parameters is variable

    Return Type Description
    table<$anonType$3>|error<>

    A table returned by the sql query statement else error will be returned if there is any error

  • <Client> update(string sqlQuery, string[]? keyColumns, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (UpdateResult|error<>)

    The update remote function implementation for SQL Client to update data and schema of the database.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL statement to execute

    keyColumns string[]? ()

    Names of auto generated columns for which the auto generated key values are returned

    parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter?

    The parameters to be passed to the update query. The number of parameters is variable

    Return Type Description
    UpdateResult|error<>

    A sql:UpdateResult with the updated row count and key column values, else error will be returned if there is any error

  • <Client> batchUpdate(string sqlQuery, string|int|boolean|float|decimal|byte[][][]|sql:Parameter? parameters) returns (int[]|error<>)

    The batchUpdate remote function implementation for SQL Client to batch data insert.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL statement to execute

    parameters string|int|boolean|float|decimal|byte[][][]|sql:Parameter?

    Variable number of parameter arrays each representing the set of parameters of belonging to each individual update

    Return Type Description
    int[]|error<>

    An int[] - The elements in the array returned by the operation may be one of the following or else anerror will be returned if there is any error. A number greater than or equal to zero - indicates that the command was processed successfully and is an update count giving the number of rows A value of -2 - Indicates that the command was processed successfully but that the number of rows affected is unknown A value of -3 - Indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails