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.
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 |
Records Summary
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 Summary
Object | Description | ||
---|---|---|---|
GlobalPoolConfigContainer |
Endpoints Summary
Endpoint | Description | ||
---|---|---|---|
AbstractSQLClient | The abstract SQL Client object for SQL databases. |
||
Client | Represents the base SQL Client |
Functions Summary
Return Type | Function and Description | ||
---|---|---|---|
error<>|null | close(sql:Client sqlClient) An internal function used by clients to shutdown the connection pool. |
||
GlobalPoolConfigContainer | getGlobalPoolConfigContainer() Retrieves the |
Constants
public type DatabaseErrorData record
Field Name | Data Type | Default Value | Description |
---|---|---|---|
message | string |
public type Parameter record
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 record
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 record
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 |
A map of auto generated key values during the sql statement execution |
public function close(sql:Client sqlClient) returns (error<>|null)
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<>|null | Possible error during closing |
public function getGlobalPoolConfigContainer() returns (GlobalPoolConfigContainer)
Retrieves the final
GlobalPoolConfigContainer
object.
Return Type | Description | ||
---|---|---|---|
GlobalPoolConfigContainer | The |
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 elseerror
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, elseerror
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 elseerror
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, elseerror
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