Ballerina version: 0.990.3
Oracle database version used: Oracle-xe-11g

Note: You need to copy the Oracle JDBC driver jar into $BALLERINA_HOME/bre/lib

Table creation query:

Sample:

How to run:

ballerina run oracle-0.990.3.bal

Output:

1.0|Harry|Potter|1000.0
2.0|Ron|Weasley|1000.0

Interact with an Oracle database with Ballerina was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

So, here’s a sample for you to create a Docker image with MongoDB module installed.

Dockerfile:

Please replace the versions as required.

Docker image build command:

docker build -t mongodb-ballerina .

Once built when you execute docker images command you should be able to see the image named mongodb-ballerina.

You can find a Balleria program below which uses the image we just built in order to create a containerized service that uses MongoDB module.

Command to build the Ballerina program

ballerina build mongodb-service-docker.bal

Once you build an image will be created which encapsulates the service, on top of the mongodb-ballerina image we created. Also you will be presented with the docker command similar to below, to create a container and run.

docker run -d -p 9090:9090 ballerina.guides.io/ballerina_mongodb_service:v1.0

Sample request:

curl -v http://localhost:9090/dummyService/getData

That’s about it! Enjoy!

Containerized Ballerina service with a MongoDB data backend was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

Ballerina is a programming language designed to fill the gap between integration focused products and general purpose programming languages. Read more here. MongoDB Atlas is a fully managed cloud database service developed by the MongoDB folks. Lets see how to connect to MongoDB Atlas cluster and perform some operations, using MongoDB package provided by WSO2.

1. First install Ballerina! I used Ballerina 0.982.0 version. You can download the installer from here.

2. Then download MongoDB package from here, and install it using the installation script.

Now I assume you have a MongoDB Atlas account. Its quick and easy to create one. Visit their website and create a free account if you don’t have one.

Then, make sure to add your IP into the whitelist in your MongoDB atlas cloud configuration. There is an option which detects your public IP and adds it, as well.

Also you’ll have to create a user as well. The user interface is quite intuitive. I don’t think you need instructions for that. :)

Now you should be able to view your clusters as below.

Now we want to get the connection string.

To do that,

1. Click on the connect button.
2. Then you will see the following UI. Click on Connect Your Application option.

3. Then you’ll see the UI below. There will be two options for the connection string. You can use either of them.

Now you can try out the following Ballerina program which demonstrates creating the endpoint and performing all available operations provided by the WSO2 MongoDB package.

Note: WSO2 MongoDB package allows you to either connect with direct URL string or by filling in required properties (host, port,.. etc) separately. The example demonstrates both.

This program inserts 3 documents to the collection “Books”, queries for them, and deletes one of them. So after running the program, you should be able to see two documents being inserted in your MongoDB Atlas cluster as follows.

That’s about it. Enjoy!

Let’s connect to MongoDB Atlas with Ballerina was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

First, I’m going to assume you have, Connection pool size of the database itself and the ballerina database are set to meet the needs. This article is only about how you could have caused connection leaks due to the lack of best practices in the ballerina code itself.

First, the culprit is almost always a table returned by a select action or a call action! It can never be an update action.

1. Do you not fully iterate the table?

2. Are you having a break statement in the middle of an iteration?

2. Are you returning from a function in the middle of an iteration?

These are possible scenarios that could have caused the connection pool exhaustion. Let me explain why.

When you obtain a table from a select/call operation, the complete result set will not be loaded to memory. The table just keeps a pointer to the database result set. Records will be retrieved on demand. This is because ballerina treats streaming behavior as a first class citizen. Therefore, connection will be closed when the table is fully iterated or “close” function is called on the table explicitly!

So, in each of the above mentioned scenarios, table is neither fully iterated or explicitly closed, causing a connection leak.

Now I guess its obvious to you how to fix this.

You have to either,

1. Fully iterate the table

Or,

2. Close the table explicitly before you return in the middle of an iteration, break etc.

[Ballerina] Database connection pool exhausted? was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

Let me explain what select iterable operation does.

Assume you have a table constrained with “Person” type. Here is the Ballerina record type for “Person”.

type Person record {
    int id,
    float salary,
    string name,
};

And you have another type “Salary”.

type Salary record {
    int id,
    float salary,
};

Also, you have following function which would output a “Salary” record given a “Person” record!

function getSalary(Person p) returns Salary {
    Salary s = { id: p.id, salary: p.salary };
    return s;
}

If you want to get a “Salary” table which contains a record for each “Person” in the Person table, you’d have to iterate the Person table and call the above function on each Person record and add it to a new table like below.

table<Salary> salaryTable;
foreach person in personTable {
    _ = salaryTable.add(getSalary(person));
}

But wait! Ballerina provides a shortcut for this! That is select iterable operation.

You can simple do like follows.

table<Salary> salaries = dt.select(getSalary);

Saved a few lines of code, didn’t we?

For the people who want to copy paste a program and see it running, I have put the complete example below that you can simply run! Enjoy!

“select” iterable operation on Ballerina tables was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

We have two resource managers, a mysql database and an Oracle database. Here we have two functions to demonstrate a successful transaction and a failing one.

Here goes the program!

XA transaction example with Ballerina — Oracle and MySQL as resource managers was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

In Ballerina language there is a type called table. Table type is analogous to a database table. One use case of these tables is acting as a proxy for an actual database table. This is an operation available in Ballerina generic jdbc client and other database specific jdbc clients such as mysql and h2 clients.

The operation is getProxyTable.

So let’s write a Ballerina program that creates a “proxy” table for an actual database table, inserts some data to it and iterates the database table through the Ballerina table!

Here goes the program.

1. First you need to have postgresql installed.
2. Also download and install Ballerina 0.981.0
3. Download the postgresql driver (I used the version 42.2.1)

Now connect to your postgresql server and create a database called “customerdb”. That is the one we are going to connect through our Ballerina program.

Here goes the Ballerina program. We are simply creating a table using the Ballerina jdbc client, then insert a record to the table and retrieve the data from that table and iterate!

Interact with a Postgresql database with Ballerina! was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

Following are the prerequisites.

1. Have Oracle installed. I used 11g.
2. Install Ballerina 0.981.0.
3. Copy Oracle driver (ojdbc6.jar) to ${BALLERINA_HOME}/bre/lib/

First, let’s look at the stored procedure.

This stored procedure simply takes in a parameter of NUMBER type and returns a refcursor for the result of a select query called on the following table.

Hope the above stored procedure is simple enough!

Let’s create a table.

Here goes the Ballerina program to call the above mentioned stored procedure and iterate the results and print them to console.

When you run this program as follows,

ballerina run refcursor-sample.bal

You should see a result similar to below. Please note that you should have some data inserted to your table corresponding to the SQL query.

1.0|Jim|Young|217.5

So, that is it!

Oracle RefCursor Example with Ballerina was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

1. First, let’s get Oracle set up. I simply followed [1] and got a docker container with oracle 11g running.
2. Then installed SQLPlus with the help of [2]. Then connected to my oracle instance in the docker
   container with the following command.
   sqlplus system/oracle@localhost:49161/xe
3. Now we need to get Ballerina installed as well. You can download Ballerina from [3].

Now let’s create a PL/SQL package.

Connect to the Oracle instance with SQLPlus as I have mentioned above and run the following statements.

Here we are first declaring a package. This package has 2 functions and one procedure.

Now we are going to define the functions/procedures in the package.

Following is the table used in the above procedures/functions.

Here is the ballerina program.

Now start the Ballerina service with the following command.

ballerina run oracle_plsql_service.bal

If service was successfully started you should see an output similar to the following.

ballerina: initiating service(s) in ‘oracle_plsql_service.bal’
ballerina: started HTTP/WS endpoint 0.0.0.0:9090

Following are sample cURL requests you can send to this service.

Request:

curl -v http://localhost:9090/dataService/getInfoById/2

Output:

<results><result><first_name>Manuri</first_name><last_name>Perera</last_name><id>2.0</id><salary>20.0</salary></result></results>

Request:

curl -v http://localhost:9090/dataService/getSalary/2

Output:

<Salary><id>2</id><value>20.0</value></Salary>

Request:

curl -v http://localhost:9090/dataService/adjustSalary/1/20

Output:

<Salary><id>2</id><value>24.0</value></Salary>

[1] https://tuhrig.de/3-ways-of-installing-oracle-xe-11g-on-ubuntu/
[2] https://gm.matias.ph/internet-technology/linux-unix/running-oracles-sql-plus-in-linux-ubuntu/
[3] https://ballerina.io/downloads/

Oracle PL/SQL example with Ballerina was originally published in Ballerina Swan Lake Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.