Working with JDBC Client Call Procedures with BIT data type using Ballerina

This sample explains how to execute stored procedures using JDBC Client with BIT data type. Before running the sample, copy the MySQL JDBC driver to the BALLERINA_HOME/bre/lib folder.

Sample:
Ballerina version used: 0.992.0

	import ballerina/io;
	import ballerina/sql;
	import ballerinax/jdbc;
	jdbc:Client testDB = new({
	url: "jdbc:mysql://localhost:3306/testdb",
	username: "root",
	password: "root",
	poolOptions: { maximumPoolSize: 5 },
	dbOptions: { useSSL: false }
	});
	public function main() {
	// Create a table using the `update` remote function. If the DDL
	// statement execution is successful, the `update` remote function returns 0.
	io:println("Creating table and procedures:");
	var ret = testDB->update("CREATE TABLE testdb.bittypes(
	row_id INT,
	bit_val_1 BIT(1),
	bit_val_2 BIT(64),
	PRIMARY KEY (row_id)
	)");
	handleUpdate(ret, "Create bittypes table");
	ret = testDB->update("INSERT INTO testdb.bittypes(
	row_id, bit_val_1, bit_val_2
	) VALUES (
	1, b'1', b'01110' )");
	handleUpdate(ret, "Update bittypes table");
	// Insert byte[] as a sql:Parameter.
	int rowId = 2;
	boolean bitValue1 = true;
	byte[] bitValue2 = [1, 2];
	sql:Parameter para1 = { sqlType: sql:TYPE_INTEGER, value: rowId };
	sql:Parameter para2 = { sqlType: sql:TYPE_BOOLEAN, value: bitValue1};
	sql:Parameter para3 = { sqlType: sql:TYPE_BINARY, value: bitValue2};
	ret = testDB->update("INSERT INTO testdb.bittypes(row_id, bit_val_1, bit_val_2
	) VALUES (?, ?, ?)", para1, para2, para3);
	handleUpdate(ret, "Update bittypes table using sql:Parameter.");
	// Insert byte[] as a direct parameter
	rowId = 3;
	bitValue2 = [1, 2, 4];
	ret = testDB->update("INSERT INTO testdb.bittypes(row_id, bit_val_1, bit_val_2
	) VALUES (?, ?, ?)", rowId, bitValue1, bitValue2);
	handleUpdate(ret, "Update bittypes table by passing as direct parameter.");
	//Create the stored procedure with IN and OUT parameters.
	ret = testDB->update("CREATE PROCEDURE TestBitOutParam (IN id INT, OUT bitVal1 BOOLEAN, OUT bitVal2 BIT(64))
	READS SQL DATA
	BEGIN
	SELECT bit_val_1 INTO bitVal1 from bittypes WHERE row_id = id;
	SELECT bit_val_2 INTO bitVal2 from bittypes WHERE row_id = id;
	END");
	handleUpdate(ret, "Stored procedure with IN and OUT param creation");
	sql:Parameter id = { sqlType: sql:TYPE_INTEGER, direction: sql:DIRECTION_IN, value: 2 };
	sql:Parameter bitVal1 = { sqlType: sql:TYPE_BOOLEAN, direction: sql:DIRECTION_OUT };
	sql:Parameter bitVal2 = { sqlType: sql:TYPE_BINARY, direction: sql:DIRECTION_OUT };
	// The remote function `call` is used to invoke a stored procedure.
	// Here stored procedure with IN and OUT parameters is invoked.
	var retCall = testDB->call("{call TestBitOutParam (?,?,?)}",(), id, bitVal1, bitVal2);
	if (retCall is ()|table<record {}>[]) {
	io:println("Call operation with OUT params successful");
	io:println("=============bitVal1============= ", bitVal1.value);
	io:println("=============bitVal2============= ", bitVal2.value);
	} else {
	io:println("Stored procedure call failed: " + <string>retCall.detail().message);
	}
	//Drop the procedures.
	ret = testDB->update("DROP PROCEDURE TestBitOutParam");
	handleUpdate(ret, "Drop stored procedure TestBitOutParam");
	}
	// Function to handle the return value of the `update` remote function.
	function handleUpdate(sql:UpdateResult|error returned, string message) {
	if (returned is sql:UpdateResult) {
	io:println(message + " status: " + returned.updatedRowCount);
	} else {
	io:println(message + " failed: " + <string>returned.detail().message);
	}
	}

view raw callProcedureWithBitDataType.bal hosted with ❤ by GitHub

How to run:

ballerina run callProcedureWithBitDataType.bal

Output:

Creating table and procedures:
Create bittypes table status: 0
Update bittypes table status: 1
Update bittypes table using sql:Parameter. status: 1
Update bittypes table by passing as direct parameter. status: 1
Stored procedure with IN and OUT param creation status: 0
Call operation with OUT params successful
=============bitVal1============= true
=============bitVal2============= 258
Drop stored procedure TestBitOutParam status: 0