ballerina/sql package
Package overview
This package provides the common record types and constants required for other data management packages such as jdbc
, mysql
, and h2
.
PoolOptions
The PoolOptions type the properties that are used to configure DB connection pool. This is used with jdbc
, mysql
, and h2
endpoints to configure the connection pool associated with the endpoint.
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. IN parameters are used to send values to stored procedures or pass parameters to other functions like 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 and retrieve values from stored procedures.
Parameter
The Parameter type represents a parameter for the SQL actions when a variable needs to be passed into the action.
Type Definitions
Type | Values | Description | |
---|---|---|---|
Direction | OUT | INOUT | 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 |
|
Param | The parameter passed into the operations. |
||
SQLType | VARCHAR | VARBINARY | TINYINT | TIMESTAMP | TIME | STRUCT | SMALLINT | REFCURSOR | REAL | NVARCHAR | NUMERIC | NCLOB | NCHAR | LONGVARCHAR | LONGVARBINARY | LONGNVARCHAR | INTEGER | FLOAT | DOUBLE | DECIMAL | DATETIME | DATE | CLOB | CHAR | BOOLEAN | BLOB | BIT | BINARY | BIGINT | 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 | ||
---|---|---|---|
Parameter | Parameter represents a parameter for the SQL actions when a variable parameter needs to pass to the action. |
||
PoolOptions | Represents the properties which are used to configure DB connection pool. |
Objects Summary
Object | Description | ||
---|---|---|---|
CallerActions | The Caller actions for SQL databases. |
Functions Summary
Return Type | Function and Description | ||
---|---|---|---|
close(sql:CallerActions callerActions) An internal function used by clients to shutdown the connection pool. |
Global Variables
Name | Data Type | Description | |
---|---|---|---|
DIRECTION_IN | Direction | ||
DIRECTION_INOUT | Direction | ||
DIRECTION_OUT | Direction | ||
TYPE_ARRAY | SQLType | ||
TYPE_BIGINT | SQLType | ||
TYPE_BINARY | SQLType | ||
TYPE_BIT | SQLType | ||
TYPE_BLOB | SQLType | ||
TYPE_BOOLEAN | SQLType | ||
TYPE_CHAR | SQLType | ||
TYPE_CLOB | SQLType | ||
TYPE_DATE | SQLType | ||
TYPE_DATETIME | SQLType | ||
TYPE_DECIMAL | SQLType | ||
TYPE_DOUBLE | SQLType | ||
TYPE_FLOAT | SQLType | ||
TYPE_INTEGER | SQLType | ||
TYPE_LONGNVARCHAR | SQLType | ||
TYPE_LONGVARBINARY | SQLType | ||
TYPE_LONGVARCHAR | SQLType | ||
TYPE_NCHAR | SQLType | ||
TYPE_NCLOB | SQLType | ||
TYPE_NUMERIC | SQLType | ||
TYPE_NVARCHARR | SQLType | ||
TYPE_REAL | SQLType | ||
TYPE_REFCURSOR | SQLType | ||
TYPE_SMALLINT | SQLType | ||
TYPE_STRUCT | SQLType | ||
TYPE_TIME | SQLType | ||
TYPE_TIMESTAMP | SQLType | ||
TYPE_TINYINT | SQLType | ||
TYPE_VARBINARY | SQLType | ||
TYPE_VARCHAR | SQLType |
public type Parameter
Parameter represents a parameter for the SQL actions when a variable parameter needs to pass to the action.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
sqlType | BIGINT|REAL|INTEGER|DATE|NVARCHAR|LONGVARCHAR|SMALLINT|DOUBLE|REFCURSOR|TIMESTAMP|VARBINARY|BIT|STRUCT|CLOB|DATETIME|FLOAT|BINARY|VARCHAR|DECIMAL|LONGNVARCHAR|TINYINT|TIME|BLOB|CHAR|BOOLEAN|NCLOB|NUMERIC|ARRAY|LONGVARBINARY|NCHAR | The data type of the corresponding SQL parameter |
|
value | any | Value of paramter pass into the SQL query |
|
direction | INOUT|OUT|IN | Direction of the SQL Parameter 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.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
connectionInitSql | string | SQL statement that will be executed after every new connection creation before adding it to the pool |
|
dataSourceClassName | string | Name of the DataSource class provided by the JDBC driver |
|
autoCommit | boolean | true | Auto-commit behavior of connections returned from the pool |
isXA | boolean | false | Whether Connections are used for a distributed transaction |
maximumPoolSize | int | 10 | Maximum size that the pool is allowed to reach, including both idle and in-use connections |
connectionTimeout | int | 30000 | Maximum number of milliseconds that a client will wait for a connection from the pool. Default is 30 seconds |
idleTimeout | int | 600000 | Maximum amount of time that a connection is allowed to sit idle in the pool. Default is 10 minutes |
minimumIdle | int | -1 | Minimum number of idle connections that pool tries to maintain in the pool. Default is same as maximumPoolSize |
maxLifetime | int | 1800000 | Maximum lifetime of a connection in the pool. Default is 30 minutes |
validationTimeout | int | 5000 | Maximum amount of time that a connection will be tested for aliveness. Default 5 seconds |
public function close(sql:CallerActions callerActions)
An internal function used by clients to shutdown the connection pool.
Parameter Name | Data Type | Default Value | Description |
---|---|---|---|
callerActions | sql:CallerActions | The CallerActions object which represents the connection pool. |
public type CallerActions object
The Caller actions for SQL databases.
-
<CallerActions> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|sql:Parameter[] parameters) returns (table[] | null | error)
The call action implementation for SQL connector to invoke stored procedures/functions.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
recordType typedesc[]? Array of record types of the returned tables if there is any
parameters string|int|boolean|float|sql:Parameter[] Return Type Description table[] | null | error A
table[]
if there are tables returned by the call action and else nil,error
will be returned if there is any error -
<CallerActions> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|sql:Parameter[] parameters) returns (table | error)
The select action implementation for SQL connector 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|sql:Parameter[] Return Type Description table | error A
table
returned by the sql query statement elseerror
will be returned if there is any error -
<CallerActions> update(string sqlQuery, string|int|boolean|float|sql:Parameter[] parameters) returns (int | error)
The update action implementation for SQL connector to update data and schema of the database.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
parameters string|int|boolean|float|sql:Parameter[] Return Type Description int | error int
number of rows updated by the statement and elseerror
will be returned if there is any error -
<CallerActions> batchUpdate(string sqlQuery, sql:Parameter[][] parameters) returns (int[] | error)
The batchUpdate action implementation for SQL connector to batch data insert.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
parameters sql:Parameter[][] Return Type Description int[] | error An
int[]
array of updated row count by each of statements in batch and elseerror
will be returned if there is any error -
<CallerActions> updateWithGeneratedKeys(string sqlQuery, string[]? keyColumns, string|int|boolean|float|sql:Parameter[] parameters) returns ((int,string[]) | error)
The updateWithGeneratedKeys action implementation for SQL connector which returns the auto generated keys during the update action.
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|sql:Parameter[] Return Type Description (int,string[]) | error A
Tuple
will be returned and would represent updated row count during the query exectuion, aray of auto generated key values during the query execution, in order. Elseerror
will be returned if there is any error. -
<CallerActions> getProxyTable(string tableName, typedesc recordType) returns (table | error)
The getProxyTable action implementation for SQL connector which acts as a proxy for a database table that allows performing select/update operations over the actual database table.
Parameter Name Data Type Default Value Description tableName string The name of the table to be retrieved
recordType typedesc The record type of the returned table
Return Type Description table | error A
table
returned by the sql query statement elseerror
will be returned if there is any error