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 is 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 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.

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

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 be passed 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 record

Parameter represents a parameter for the SQL actions when a variable parameter needs to be passed to the action.

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

The data type of the corresponding SQL parameter

value any

Value of paramter pass into the SQL statement

direction INOUT|OUT|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.

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. 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.

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|byte[][]|sql:Parameter parameters) returns (table[] | null | error)

    The call operation 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|byte[][]|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|byte[][]|sql:Parameter parameters) returns (table | error)

    The select operation 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|byte[][]|sql:Parameter
    Return Type Description
    table | error

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

  • <CallerActions> update(string sqlQuery, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns (int | error)

    The update operation 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|byte[][]|sql:Parameter
    Return Type Description
    int | error

    int number of rows updated by the statement and else error will be returned if there is any error

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

    The batchUpdate operation implementation for SQL connector to batch data insert.

    Parameter Name Data Type Default Value Description
    sqlQuery string

    SQL statement to execute

    parameters string|int|boolean|float|byte[][][]|sql:Parameter
    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

  • <CallerActions> updateWithGeneratedKeys(string sqlQuery, string[]? keyColumns, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns ((int,string[]) | error)

    The updateWithGeneratedKeys operation 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|byte[][]|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. Else error will be returned if there is any error.

  • <CallerActions> getProxyTable(string tableName, typedesc recordType) returns (table | error)

    The getProxyTable operation 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 operation or else error will be returned if there is any error