CODEWITHSUNDEEP
ballerina
Igor
Igor

Reputation: 41

wso2 Ballerina using DB Connections with Services

I use ballerina 0.91 and try to do a REST GET Service for getting some data on an oracle databse. Following are the Oracle Info using on sql:ClientConnector.

service<http> MyService {

 @http:GET {}
 @http:Path {value: "/myService"}
 resource apiGET (message m) {

   string driverClass = "oracle.jdbc.driver.OracleDriver";
   string dbURL = "jdbc:oracle:thin:@xxx:1521:yyy";
   string username = "aaa";
   string password = "bbb";
   map propertiesMap = {"driverClassName":driverClass,"jdbcUrl":dbURL, "username":username, "password":password};
   sql:ClientConnector myConnection = create sql:ClientConnector(propertiesMap);

   <getting data from the database and prepare to send back to client>

   message response = {};
   messages:setJsonPayload(response, myData);        
   sql:ClientConnector.close(myConnection);        

   reply response;
 }
}

So using the DB Connection within each GET I have to open the connection to database and close it again at the end. This is time costly.

Alternatively I could open the DB Connection on the Service level, so outside the GET, this would open the DB Connection once and inside the GET I can always use the DB. This is very fast and in running the service inside Composer everything works fine even I use the GET with some clients in parallel several times, I do not run out of DB Connections. But when I compile the Service an I run the service as 

ballerina run -s myService.balx

so I run out of DB Connections and I get some exception.

How can I pool DB Connection in a way so that I can reuse a connection for each GET and before finishing the GET I send it back to the pool. How I can close a DB Connection when the Service is shutdown?

Any general design hint is welcome.

Upvotes: 1

Views: 267

Answers (1)

Tharik Kanaka
Tharik Kanaka

Reputation: 2510

The SQL Client Connector will create a Connection Pool size of 10 by default. If you need to change the pool size you can pass properties as below.

sql:ConnectionProperties properties = {maximumPoolSize:5};
sql:ClientConnector testDB = create sql:ClientConnector(sql:MYSQL, "localhost", 3306, "db", "sa", "root", properties);

Declaring the connector on the Service level is the correct way. Then when you do SQL operations within GET it will get a connection from the pool and execute.

Apparently there is an issue in the connector when using it in .balx files. This has been reported as an issue in Github repository [1].

[1] https://github.com/ballerinalang/ballerina/issues/3222

Upvotes: 2

Related Questions