Error getting connection using Database Connection....

[geadon]

Hi,

I'm getting the following error randomly on one of out Ebase servers. The server appears to hang (doesn't respond) then the below error appears in the server log.

Could anybody give me some indication as to where I can start looking for the cause of this issue?

We are running Ebase v5.2.0 and Tomcat 8.0.26 with AJP Connector.

ERROR Error getting connection using Database Connection DBSERVER, SQLException in StandardPoolDataSource:getConnection exception: java.sql.SQLException: SQLException in StandardPoolDataSource:getConnection no connection available java.lang.Exception: GenericPool:checkOut ERROR impossible to obtain a new object from the pool

[Jon]

The message means that all the connections in the connection pool are in use. Here are some suggestions:

If the system recovers i.e. the error is transient, then the pool size is too small (not likely).
If the system has to be restarted to recover (more likely), then your system is leaking connections somewhere i.e. there not being properly closed.

To check for active connections, run the server admin app, click on the Monitor link, you will see a line "No. of active jdbc connections" under the System Information header. This number should be 0, I think if it's non zero, you can click on it to get more details, but not too sure of this. This might help you find the culprit.

Do you use the DatabaseServices API at all in your scripts e.g. services.database.xxx or DatabaseServices.xxx. There is a bug which can lead to connection leaks in some circumstances. This exists in V5.2 and will be fixed in the next release.

Do you use Java JDBC code anywhere in your scripts? If so, you should check that all statements, result sets and connections are being closed correctly. See the documentation for system.getDatabaseConnection() for an example.

[geadon]

Thank you Jon.

The cause was in fact a JDBC connection (which was used a lot) not being closed. I had failed to add the Finally, to the Try Catch statement.

Thank you for your help once more.

[Segi]

geadon

There's another way that you can do database queries that is easier in my opinion because it needs less code and doesn't require you to remember to close the DB connection. Its present in eBase 5.

The syntax looks like this for a select statement:

Code: Select all

services.database.executeSelectStatement("DBCONNECTIONNAME","SELECT Name,Address FROM Contacts",
     function (columnData) {
          print("Name is " + columnData.Name + " and address is " + columnData.Address);
     });

The function gets called for each row thats returned by the query. I've found that using this method, you sometimes have to validate the DB field to make sure the value isn't null or displaying the value on a page will show "undefined".

So for the line the above, I'd actually do something more like thi to "normalize" it:

Code: Select all

print("Name is " + (columnData.Name != null ? columnData.Name  : "") + " and address is " + (columnData.Address) != null ? columnData.Address)  : "")

If you want to to do an UPDATE,DELETE or INSERT, its even easier:

Code: Select all

services.database.executeGenericUpdateStatement("DBCONNECTIONNAME","UPDATE Contacts SET Name='Superman' WHERE Name='Clark Kent'");

[Steve James]

This is a good and easy approach but you have to protect from SQL injection.

[geadon]

Segi - thank you for the suggestion. Anything that reduces code is worth considering in my opinion. Just a few questions as I can't find a massive amount of documentation around it.

Can you use parameters in the same way as you can using JDBC. I'm assuming that using parameters in JDBC helps reduce the risk of SQL injection.

Here is the example code (where I previously forgot to add the 'finally' element to close the connection). How could I use the services.database.executeGenericUpdateStatement() and protect it from SQL injection?

Code: Select all

//Write to security log
function securityLog(Action,Description) {
	try {
		con = system.getDatabaseConnection('DBCON');
		
		stmt = con.prepareStatement("insert into tblName (Username, Date_Time, Action, Description, Form, Browser_IP, Browser_Type, Session_ID, Server)"
		+ "values(?,getdate(),?,?,?,?,?,?,?)");
		stmt.setString(1,system.variables.$USER.value);
		stmt.setString(2,Action);
		stmt.setString(3,Description);
		stmt.setString(4,system.variables.$FORMID.value);
		stmt.setString(5,system.variables.$BROWSER_IP_ADDRESS.value);
		stmt.setString(6,system.variables.$BROWSER_TYPE.value);
		stmt.setString(7,system.variables.$SESSION_ID.value);
		stmt.setString(8,system.variables.$UFS_SERVER_NAME.value);
		stmt.execute();
	}
	catch (e) {
		log("__**ERROR** - Error writing to the security log - " + e);
			log("User:" + system.variables.$USER.value);
			log("Action:" + Action);
			log("Description:" + Description);
			log("BrowserIP:" + system.variables.$BROWSER_IP_ADDRESS.value);
			log("BrowserType:" + system.variables.$BROWSER_TYPE.value);
			log("EbaseFormID:" + system.variables.$FORMID.value);
	}
	finally {
	  if(stmt) stmt.close();
	  if(con) con.close();
	}
}