Actions

The MSSQL connector is distributed across three libraries:

• ballerinax/mssql
• ballerinax/mssql.driver
• ballerinax/mssql.cdc.driver

Available clients:

Client	Purpose
Client	Connects to a Microsoft SQL Server database and executes SQL queries, DML statements, batch operations, and stored procedures.

For event-driven integration, see the Trigger Reference.

---

Client

Connects to a Microsoft SQL Server database and executes SQL queries, DML statements, batch operations, and stored procedures.

Configuration

Field	Type	Default	Description
host	string	"localhost"	Hostname of the MSSQL server.
user	string?	"sa"	Database username.
password	string?	()	Database password.
database	string?	()	Name of the database to connect to.
port	int	1433	Port number of the MSSQL server.
instance	string	""	Named instance of SQL Server (for example, "SQLEXPRESS").
options	Options?	()	MSSQL-specific connection options (SSL, timeouts, XA).
connectionPool	sql:ConnectionPool?	()	Connection pool configuration. If not provided, the global shared pool is used.

Initializing the client

import ballerinax/mssql;
import ballerinax/mssql.driver as _;

configurable string host = ?;
configurable string user = ?;
configurable string password = ?;
configurable string database = ?;
configurable int port = 1433;

mssql:Client dbClient = check new (
    host = host,
    user = user,
    password = password,
    database = database,
    port = port
);

Operations

Query operations

query

Executes a parameterized SQL query and returns a stream of results.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	The SQL query to execute, using Ballerina's parameterized query syntax.
rowType	typedesc<record {}>	No	The expected record type for each row in the result set.

Returns: stream<rowType, sql:Error?>

Sample code:

type Employee record {|
    int id;
    string first_name;
    string last_name;
    decimal salary;
|};

stream<Employee, sql:Error?> employees = dbClient->query(
    `SELECT id, first_name, last_name, salary FROM Employees WHERE salary > ${50000}`
);
check from Employee emp in employees
    do {
        // process each employee record
    };

Sample response:

{"id": 1, "first_name": "John", "last_name": "Doe", "salary": 75000.00}
{"id": 2, "first_name": "Jane", "last_name": "Smith", "salary": 82000.00}

queryRow

Executes a parameterized SQL query that returns at most one row.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	The SQL query to execute.
returnType	typedesc<anydata>	No	The expected return type for the single result row.

Returns: returnType|sql:Error

Sample code:

type Employee record {|
    int id;
    string first_name;
    string last_name;
    decimal salary;
|};

Employee employee = check dbClient->queryRow(
    `SELECT id, first_name, last_name, salary FROM Employees WHERE id = ${1}`
);

Sample response:

{"id": 1, "first_name": "John", "last_name": "Doe", "salary": 75000.00}

DML execution

execute

Executes a parameterized SQL statement (INSERT, UPDATE, DELETE, or DDL).

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedQuery	Yes	The SQL statement to execute.

Returns: sql:ExecutionResult|sql:Error

Sample code:

sql:ExecutionResult result = check dbClient->execute(
    `INSERT INTO Employees (first_name, last_name, salary)
     VALUES (${"Peter"}, ${"Parker"}, ${65000.00})`
);

Sample response:

{"affectedRowCount": 1, "lastInsertId": 3}

batchExecute

Executes a batch of parameterized SQL statements for high-throughput operations.

Parameters:

Name	Type	Required	Description
sqlQueries	sql:ParameterizedQuery	Yes	An array of parameterized SQL statements to execute as a batch.

Returns: sql:ExecutionResult[]|sql:Error

Sample code:

sql:ParameterizedQuery[] insertQueries = [
    `INSERT INTO Employees (first_name, last_name, salary)
     VALUES (${"Alice"}, ${"Johnson"}, ${70000.00})`,
    `INSERT INTO Employees (first_name, last_name, salary)
     VALUES (${"Bob"}, ${"Williams"}, ${68000.00})`
];

sql:ExecutionResult[] results = check dbClient->batchExecute(insertQueries);

Sample response:

[{"affectedRowCount": 1, "lastInsertId": 4}, {"affectedRowCount": 1, "lastInsertId": 5}]

Stored procedures

call

Calls a stored procedure with IN, OUT, and INOUT parameters using the MSSQL exec syntax.

Parameters:

Name	Type	Required	Description
sqlQuery	sql:ParameterizedCallQuery	Yes	The stored procedure call query using exec ProcName syntax.
rowTypes	typedesc<record {}>[]	No	Expected record types for result sets returned by the procedure.

Returns: sql:ProcedureCallResult|sql:Error

Sample code:

sql:IntegerOutParameter totalCount = new;

sql:ProcedureCallResult result = check dbClient->call(
    `exec GetEmployeeCount ${totalCount}`
);
int? count = totalCount.get(int);

Sample response:

{"executionResult": {"affectedRowCount": -1}, "queryResult": null}

Connection management

close

Closes the client connection and releases all associated database resources.

Returns: sql:Error?

Sample code:

check dbClient.close();