Working with mysql BIT data type using Ballerina

This example is written using Ballerina MYSQL Client which is used to access and manipulate data in MYSQL databases.

1. First, you need to install MySQL. 
2. Download and install Ballerina 0.992.0.
3. Copy the MySQL JDBC driver(mysql-connector-java-5.1.39.jar) to the BALLERINA_HOME/bre/lib folder and change the DB connection properties as required.

Sample

	import ballerina/io;
	import ballerina/mysql;
	import ballerina/sql;
	mysql:Client testDB = new({
	host: "localhost",
	port: 3306,
	name: "",
	username: "root",
	password: "root",
	poolOptions: { maximumPoolSize: 5 },
	dbOptions: { useSSL: false }
	});
	type BitTypes record {
	int id;
	boolean bitVal1;
	byte[] bitVal2;
	};
	public function main(string... args) {
	createDatabase();
	var ret = testDB->update("CREATE TABLE testdb.bittypes(
	id INT,
	bitVal1 BIT(1),
	bitVal2 BIT(64),
	PRIMARY KEY (id)
	)");
	handleUpdate(ret, "Create bittypes table");
	ret = testDB->update("INSERT INTO testdb.bittypes(
	id, bitVal1, bitVal2
	) VALUES (
	1, b'1', b'01110' )");
	handleUpdate(ret, "Update bittypes table");
	int id = 2;
	boolean bitVal1 = true;
	byte[] bitVal2 = [1, 2, 3];
	sql:Parameter para1 = { sqlType: sql:TYPE_INTEGER, value: id };
	sql:Parameter para2 = { sqlType: sql:TYPE_BOOLEAN, value: bitVal1 };
	sql:Parameter para3 = { sqlType: sql:TYPE_BINARY, value: bitVal2};
	ret = testDB->update("INSERT INTO testdb.bittypes(
	id,
	bitVal1,
	bitVal2
	) VALUES (
	?,
	?,
	?
	)", para1, para2, para3);
	handleUpdate(ret, "Update bittypes table");
	var selectRet = testDB->select("Select id, bitVal1, bitVal2 from testdb.bittypes", BitTypes);
	if (selectRet is table<BitTypes>) {
	int index = 0;
	io:println("===================================");
	foreach var row in selectRet {
	io:println(row.id);
	io:println(row.bitVal1);
	io:println(row.bitVal2);
	}
	io:println("===================================");
	} else {
	io:print("ERROR OCCURED!!!:");
	io:println(selectRet);
	}
	dropDatabase();
	}
	function createDatabase() {
	var ret = testDB->update("CREATE DATABASE testdb");
	handleUpdate(ret, "Create database testdb");
	}
	function dropDatabase() {
	var ret = testDB->update("DROP DATABASE testdb");
	handleUpdate(ret, "Drop Database testdb");
	}
	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 workingWithBitData.bal hosted with ❤ by GitHub

How to run:

ballerina run workingWithBitData.bal

Output:

Create database testdb status: 1
Create bittypes table status: 0
Update bittypes table status: 1
Update bittypes table status: 1
===================================
1
true
[0, 0, 0, 0, 0, 0, 0, 14]
2
true
[0, 0, 0, 0, 0, 1, 2, 3]
===================================
Drop Database testdb status: 1