ballerina/mysql module
Module overview
This module provides the functionality required to access and manipulate data stored in a MySQL database.
Endpoint
To access a database, you must first create a client
object. Create a client of the MySQL Client type (i.e., mysql:Client
) and provide the necessary connection parameters. This will create a pool of connections to the given MySQL database. A sample for creating a MySQL client can be found below.
Database operations
Once the client is created, database operations can be executed through that client. This module provides support for creating tables and executing stored procedures. It also supports selecting, inserting, deleting, updating, and batch updating data. For more details on the supported remote functions refer to the sql
module. Also the details of the SQL data types and query parameters relevant to these database operations can be found in the documentation of the sql
module.
Samples
Creating an endpoint
mysql:Client testDB = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
poolOptions: { maximumPoolSize: 5 },
dbOptions: { "useSSL": false }
});
The full list of client properties can be found in the sql:PoolOptions
type.
Records Summary
Record | Description | ||
---|---|---|---|
ClientEndpointConfig | The Client endpoint configuration for mysql databases. |
Endpoints Summary
Endpoint | Description | ||
---|---|---|---|
Client | Represents an MySQL client endpoint. |
public type ClientEndpointConfig record
The Client endpoint configuration for mysql databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
host | string | The host name of the database to connect |
|
port | int | 3306 | The port of the database to connect |
name | string | The name of the database to connect |
|
username | string | Username for the database connection |
|
password | string | Password for the database connection |
|
poolOptions | sql:PoolOptions | {} | Properties for the connection pool configuration. Refer |
dbOptions | map | {} | A map of DB specific properties |
Endpoint Client
Represents an MySQL client endpoint.
-
<Client> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns (table<$anonType$0>[] | null | error<>)
The call operation implementation for MySQL 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|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 -
<Client> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns (table<$anonType$1> | error<>)
The select operation implementation for MySQL 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|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 -
<Client> update(string sqlQuery, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns (int | error<>)
The update operation implementation for MySQL Client 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 The parameters to be passed to the update query. The number of parameters is variable
Return Type Description int | error<> int
number of rows updated by the statement and elseerror
will be returned if there is any error -
<Client> batchUpdate(string sqlQuery, string|int|boolean|float|byte[][][]|sql:Parameter parameters) returns (int[] | error<>)
The batchUpdate operation implementation for MySQL Client 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 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 -
<Client> updateWithGeneratedKeys(string sqlQuery, string[]? keyColumns, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns ((int,string[]) | error<>)
The updateWithGeneratedKeys operation implementation for MySQL Client which returns the auto generated keys during the update remote function.
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 The parameters to be passed to the update query. The number of parameters is variable
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.