Insert byte[] as a direct value to MySQL update operation in Ballerina

Ballerina version used: 0.992.0

	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 }
	});
	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");
	int id = 1;
	boolean bitVal1 = true;
	byte[] bitVal2 = [1, 2];
	var ret = testDB->update("INSERT INTO testdb.bittypes(
	row_id,
	bit_val_1,
	bit_val_2
	) VALUES (
	?,
	?,
	?
	)", id, bitVal1, bitVal2);
	handleUpdate(ret, "Update bittypes table");
	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 insertByteArrayAsDirectValue.bal hosted with ❤ by GitHub

Insert byte[] into BIT data type in MySQL using Java

First, you need to create a database and table as below to work with the following sample.

	mysql> create database testdb;
	Query OK, 1 row affected (0.01 sec)
	mysql> CREATE TABLE testdb.bittypes(
	-> row_id INT,
	-> bit_val_1 BIT(1),
	-> bit_val_2 BIT(64),
	-> PRIMARY KEY (row_id)
	-> );
	Query OK, 0 rows affected (0.10 sec)
	mysql>
	mysql> describe testdb.bittypes;
	+-----------+---------+------+-----+---------+-------+
	| Field | Type | Null | Key | Default | Extra |
	+-----------+---------+------+-----+---------+-------+
	| row_id | int(11) | NO | PRI | NULL | |
	| bit_val_1 | bit(1) | YES | | NULL | |
	| bit_val_2 | bit(64) | YES | | NULL | |
	+-----------+---------+------+-----+---------+-------+
	3 rows in set (0.02 sec)
	mysql>

view raw createDatabaseTable.sql hosted with ❤ by GitHub

Sample:

	import java.io.ByteArrayInputStream;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.Statement;
	import java.util.Arrays;
	public class InsertByteArray {
	public static void main(String[] args) {
	String JdbcURL="jdbc:mysql://localhost:3306/testdb?useSSL=false";
	String Username="root";
	String password="root";
	try {
	Connection connection = DriverManager.getConnection(JdbcURL, Username, password);
	String sql = "INSERT INTO bittypes(id, bitVal1, bitVal2) VALUES (4, b'1', ?)";
	PreparedStatement pstmt = connection.prepareStatement(sql);
	byte[] buffer = new byte[] {1, 2, 5, 40};
	pstmt.setBinaryStream(1, new ByteArrayInputStream(buffer), buffer.length);
	System.out.println("pstmt");
	System.out.println(pstmt);
	pstmt.executeUpdate();
	pstmt.close();
	Statement stmt = connection.createStatement();
	ResultSet resultSet = stmt.executeQuery("SELECT * FROM bittypes");
	System.out.println("========================");
	while (resultSet.next()) {
	byte[] bytes = resultSet.getBytes("bitVal2");
	System.out.println(Arrays.toString(bytes));
	}
	} catch(Exception e) {
	e.printStackTrace();
	}
	}
	}

view raw InsertByteArray.java hosted with ❤ by GitHub

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

Working with Postgresql database using Ballerina

1. First, install Postgresql database.
2. Download the postgresql driver jar and copy the Postgresql JDBC driver jar into $BALLERINA_HOME/bre/lib.
3. Connect to your postgresql server and create a database:



postgres=# create database testdb;

view raw createDatabase.sql hosted with ❤ by GitHub

Sample

This is the sample to create a table using the Ballerina jdbc client, then insert a BIT data type record to the table.

Ballerina version used: 0.992.0

	import ballerinax/jdbc;
	import ballerina/io;
	import ballerina/sql;
	jdbc:Client testDB = new({
	url: "jdbc:postgresql://localhost:13800/testdb",
	username: "postgres",
	password: "docker",
	poolOptions: { maximumPoolSize: 5 }
	});
	public function main(string... args) {
	var ret = testDB->update("CREATE TABLE BitTypes(ID SERIAL, bit_val BIT VARYING(64));");
	handleUpdate(ret, "Create BitTypes table");
	var insertRet = testDB->update("INSERT INTO BitTypes(bit_val) VALUES(B'101')");
	handleUpdate(insertRet, "Update BitTypes table");
	}
	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 workingWithBitDataPostgresql.bal hosted with ❤ by GitHub

Running the sample:

ballerina run workingWithBitDataPostgresql.bal

Output:

Create BitTypes table status: 0
Update BitTypes table status: 1

Working with an Oracle database using Ballerina

Ballerina version used: 0.992.0
Oracle database version used: Oracle-xe-11g (Follow these instructions explained here to install Oracle-xe-11g with Docker).


This Sample is written using ballerinax/jdbc module. Before running this sample you need to copy the Oracle JDBC driver jar into $BALLERINA_HOME/bre/lib.

First, create the table using the following command:

CREATE TABLE oracledb(id NUMBER, byte_array RAW(10))

view raw createTable.sql hosted with ❤ by GitHub

This is a sample to insert byte array into oracle database:

	import ballerina/io;
	import ballerinax/jdbc;
	import ballerina/sql;
	jdbc:Client testDB = new({
	url: "jdbc:oracle:thin:@localhost:49161:xe",
	username: "system",
	password: "oracle",
	poolOptions: { maximumPoolSize: 1 }
	});
	byte[] bitVal2 = [1, 2];
	public function main() {
	var dt1 = testDB->update("INSERT INTO oracledb(id, byte_array) VALUES (1, ?)", bitVal2);
	handleUpdate(dt1, "Update oracledb table");
	}
	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 insertByteArray.bal hosted with ❤ by GitHub

Running the sample:

ballerina run insertByteArray.bal

Output:

Update oracledb table status: 1

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

Install SQL*Plus on Ubuntu 16.04

1. First, you need to download Instant Client Downloads.


2. Install alien software.



sudo apt-get install alien



3. Navigate to the folder where the downloaded rpm files are located and execute the following commands:



sudo alien -i oracle-instantclient*-basic*.rpm
sudo alien -i oracle-instantclient*-sqlplus*.rpm
sudo alien -i oracle-instantclient*-devel*.rpm



4. Type following command to install libaio.so



sudo apt-get install libaio1



5. Create an Oracle configuration file:



sudo nano /etc/ld.so.conf.d/oracle.conf



6. Put the following line in the created file:



/usr/lib/oracle/<your oracle version>/client64/lib/



7. Update the configuration by running the following command:



sudo ldconfig



8. Try to connect using the following command:



sqlplus system/oracle@localhost:49161/xe

Installing Oracle XE 11g on Ubuntu

Installing Oracle XE with Docker

Docker is an application container for Linux. It is based on LXC and gives you the ability to package complete application including their dependencies to a self-containing file called an image. These images can be exchanged and run on every Linux machine where Docker is installed.  It is the easiest way to install Oracle XE. Docker images are also shared around the community on https://index.docker.io.

1. First, you need to install Docker on Ubuntu. Follow the instructions given in this doc.


2. Pull the image to your machine using the following command :



docker pull arahman/docker-oracle-xe-11g



3. Run the image using the following command :



docker run -d -p 49160:22 -p 49161:1521 -p 49162:8080 arahman/docker-oracle-xe-11g /sbin/my_init



4. Connect to the database with the following setting:



hostname: localhost
port: 49161
sid: xe
username: system
password: oracle



5. You can connect to the database using SQL*Plus