ballerina.data.sql package
public struct ConnectionProperties
ConnectionProperties structs represents the properties which are used to configure DB connection pool
| Field Name | Data Type | Description | Default Value |
|---|---|---|---|
| url | string | Platform independent DB access URL | |
| dataSourceClassName | string | Name of the DataSource class provided by the JDBC driver | |
| connectionTestQuery | string | Query that will be executed to validate that the connection to the database is still alive | |
| poolName | string | User-defined name for the connection pool and appears mainly in logging | |
| catalog | string | Catalog of connections created by this pool | |
| connectionInitSql | string | SQL statement that will be executed after every new connection creation before adding it to the pool | |
| driverClassName | string | Fully qualified Java class name of the JDBC driver to be used | |
| transactionIsolation | string | Transaction isolation level of connections returned from the pool. The supported values are TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ and TRANSACTION_SERIALIZABLE | |
| autoCommit | boolean | Auto-commit behavior of connections returned from the pool | true |
| isolateInternalQueries | boolean | Determines whether HikariCP isolates internal pool queries, such as the connection alive test, in their own transaction | |
| allowPoolSuspension | boolean | Whether the pool can be suspended and resumed through JMX | |
| readOnly | boolean | Whether Connections obtained from the pool are in read-only mode by default | |
| isXA | boolean | Whether Connections are used for a distributed transaction | |
| maximumPoolSize | int | Maximum size that the pool is allowed to reach, including both idle and in-use connections | -1 |
| connectionTimeout | int | Maximum number of milliseconds that a client will wait for a connection from the pool | -1 |
| idleTimeout | int | Maximum amount of time that a connection is allowed to sit idle in the pool | -1 |
| minimumIdle | int | Minimum number of idle connections that pool tries to maintain in the pool | -1 |
| maxLifetime | int | Maximum lifetime of a connection in the pool | -1 |
| validationTimeout | int | Maximum amount of time that a connection will be tested for aliveness | -1 |
| leakDetectionThreshold | int | Amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak | -1 |
| datasourceProperties | map | Data source specific properties which are used along with the dataSourceClassName |
public struct Parameter
Parameter struct represents a query parameter for the SQL queries specified in connector actions
| Field Name | Data Type | Description | Default Value |
|---|---|---|---|
| sqlType | Type | The data type of the corresponding SQL parameter | |
| value | any | Value of paramter pass into the SQL query | |
| direction | Direction | Direction of the SQL Parameter IN, OUT, or INOUT |
public connector ClientConnector ( DB dbType , string hostOrPath , int port , string dbName , string username , string password , ConnectionProperties options )
The Client Connector for SQL databases.
| Parameter Name | Data Type | Description |
|---|---|---|
| dbType | DB | SQL database type |
| hostOrPath | string | Host name of the database or file path for file based database |
| port | int | Port of the database |
| dbName | string | Name of the database to connect |
| username | string | Username for the database connection |
| password | string | Password for the database connection |
| options | ConnectionProperties | ConnectionProperties for the connection pool configuration |
Actions:
-
< ClientConnector > batchUpdate ( string query , Parameter[][] parameters ) ( int[] )
The batchUpdate action implementation for SQL connector to batch data insert.
Parameter Name Data Type Description query string SQL query to execute parameters Parameter[][] Parameter array used with the SQL query Return Variable Data Type Description int[] -
< ClientConnector > call ( string query , Parameter[] parameters , type structType ) ( datatable )
The call action implementation for SQL connector to invoke stored procedures/functions.
Parameter Name Data Type Description query string SQL query to execute parameters Parameter[] Parameter array used with the SQL query structType type Return Variable Data Type Description datatable -
< ClientConnector > close ( )
The close action implementation for SQL connector to shutdown the connection pool.
-
< ClientConnector > select ( string query , Parameter[] parameters , type structType ) ( datatable )
The select action implementation for SQL connector to select data from tables.
Parameter Name Data Type Description query string SQL query to execute parameters Parameter[] Parameter array used with the SQL query structType type Return Variable Data Type Description datatable -
< ClientConnector > update ( string query , Parameter[] parameters ) ( int )
The update action implementation for SQL connector to update data and schema of the database.
Parameter Name Data Type Description query string SQL query to execute parameters Parameter[] Parameter array used with the SQL query Return Variable Data Type Description int -
< ClientConnector > updateWithGeneratedKeys ( string query , Parameter[] parameters , string[] keyColumns ) ( int , string[] )
The updateWithGeneratedKeys action implementation for SQL connector which returns the auto generated keys during the update action.
Parameter Name Data Type Description query string SQL query to execute parameters Parameter[] Parameter array used with the SQL query keyColumns string[] Names of auto generated columns for which the auto generated key values are returned Return Variable Data Type Description int string[]
public enum DB
The Databases which has direct parameter support.
| Name | Description |
|---|---|
| MYSQL | MySQL DB with connection url in the format of jdbc:mysql://[HOST]:[PORT]/[database] |
| SQLSERVER | SQL Server DB with connection url in the format of jdbc:sqlserver://[HOST]:[PORT];databaseName=[database] |
| ORACLE | Oracle DB with connection url in the format of jdbc:oracle:thin:[username/password]@[HOST]:[PORT]/[database] |
| SYBASE | Sybase DB with connection url in the format of jdbc:sybase:Tds:[HOST]:[PORT]/[database] |
| POSTGRES | PostgreSQL DB with connection url in the format of jdbc:postgresql://[HOST]:[PORT]/[database] |
| IBMDB2 | IBMDB2 DB with connection url in the format of jdbc:db2://[HOST]:[PORT]/[database] |
| HSQLDB_SERVER | HSQL Server with connection url in the format of jdbc:hsqldb:hsql://[HOST]:[PORT]/[database] |
| HSQLDB_FILE | HSQL Server with connection url in the format of jdbc:hsqldb:file:[path]/[database] |
| H2_SERVER | H2 Server DB with connection url in the format of jdbc:h2:tcp://[HOST]:[PORT]/[database] |
| H2_FILE | H2 File DB with connection url in the format of jdbc:h2:file://[path]/[database] |
| DERBY_SERVER | DERBY server DB with connection url in the format of jdbc:derby://[HOST]:[PORT]/[database] |
| DERBY_FILE | Derby file DB with connection url in the format of jdbc:derby://[path]/[database] |
| GENERIC | Custom DB connection with given connection url |
public enum Direction
The direction of the parameter
| Name | Description |
|---|---|
| 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 |
public enum Type
The SQL Datatype of the parameter
| Name | Description |
|---|---|
| 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 |
| NVARCHAR | VARCHAR: Small, variable-length character string |
| BIT | Single bit value that can be zero or one, or null |
| 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 |