HSQLDB Database Locking and Shutdown

I have had a number of questions on how to shutdown multiple HSQLDB databases running on one JVM. I have written an example application which will shutdown all of the databases attached to a single database engine in a JVM. The application uses only one test database, but the principal applies to multiple databases.

The software is released under the Apache 2.0 license. The project was developed in Netbeans 5.5

Here is the software: HSQLDBLocking.zip

Here is the source code if you prefer to see it instead of downloading the project:

/*
* TestHSQLDB.java
*
* Created on February 9, 2007, 10:04 PM
*
* $Id: TestHSQLDB.java 12 2007-03-29 21:19:48Z jyeary $
*
*/
/*
* Copyright (C) 2007 Blue Lotus Holdings, LLC. All Rights Reserved.
* Copyright (C) 2007 Blue Lotus Software. All Rights Reserved.
* Copyright (C) 2007 White Lotus Software. All Rights Reserved.
* Copyright (C) 2007 John Yeary. All Rights Reserved.
*
* THIS SOFTWARE IS PROVIDED "AS IS," WITHOUT A WARRANTY OF ANY KIND.
* ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES,
* INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A
* PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. BLUE LOTUS HOLDINGS, LLC
* AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES OR LIABILITIES
* SUFFERED BY LICENSEE AS A RESULT OF OR RELATING TO USE, MODIFICATION
* OR DISTRIBUTION OF THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL
* BLUE LOTUS HOLDINGS, LLC OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE,
* PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
* INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE
* THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE,
* EVEN IF BLUE LOTUS HOLDINGS, LLC HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
*
* You acknowledge that Software is not designed, licensed or intended
* for use in the design, construction, operation or maintenance of any
* nuclear facility.
*/

/*
* Copyright 2007 John Yeary
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.bluelotussoftware.db.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Vector;
import org.hsqldb.DatabaseManager;

/**
*
* @author John Yeary
* @version $Revision: 12 $
*/
public class TestHSQLDB {

/** Creates a new instance of TestHSQLDB */
public TestHSQLDB() {
}

/**
* @param args the command line arguments
*/

public static void main(String args[]) throws Exception {

// Use the class loader to load the JDBC driver
Class.forName("org.hsqldb.jdbcDriver");
Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:test", "sa", "");

// Fetch all of the known database URIs and display them
Vector v = DatabaseManager.getDatabaseURIs();

for (Object o : v) {
System.out.println(((String) o));
}

ResultSet r = connection.createStatement().executeQuery("SELECT * FROM INFORMATION_SCHEMA.SYSTEM_USERS");
r.next();
System.out.println(r.getString(1));

// Shutdown the database normally (0), or shutdown and script out using (2)
DatabaseManager.closeDatabases(0);

connection.close();
}
}

JDBC Tips and Tricks

Tip #1

A ResultSet is ALWAYS returned from an executeQuery() method. This often causes issues since developers often try to check to see if the result was null, and it will always return false.

For example:
...
Statement stmt = connection.createStatement();

ResultSet rs = stmt.exeuteQuery("SELECT * FROM X.Y");

// Check for null will be false
If(rs == null) {
//Do something
}
...

To check for results use next(). This will allow you to determine if there are results.

if(rs.next()) {
//Do something
}

Tip #2

Please note that generally a cursor returned by a query only moves forward through the ResultSet unless it is set explicitly. Some databases do not support anything other than forward only cursors. You need to set the cursor explicitly before executing a query.

To set the cursor to a read-only concurrency and scroll insensitive use the following:
...
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
...

Tip #3

To see native database errors try using Connection.nativeSQL("your query here") in the error messages. This will show you what the database thinks you are asking for.

try {
...
} catch (SQLException e ) {
info("An SQLException occurred. This is the native query sent to the database\n: "
+ con.nativeSQL("SELECT TRUNC(SYSDATE) DATE FROM DUAL"));
}
...

Tip #4

If you use a counter inside your next() method loop you can see how many results were processed, or report if nothing was processed.

...
int counter = 0;
while(rs.next()) {
//Do something
counter++;
}

if (counter == 0) {
System.out.println("No records processed.");
} else {
System.out.println(counter + " records processed.");
}
...

Sun Java System Application Server 9.x (glassfish) External JNDI LDAP Resource Part III

I have had a question posed to me about how to use this resource once it is setup. Indeed it would be quite limiting without an example. I have created a stateless session bean (SSB) using JEE5 resource injection to show how to use it. It also has @WebService capabilities for testing. Keep in mind that the application has NO security and is merely provided as an example.

Prerequisites:

• LDAP Server installed and configured
• Sun Java System Application Server (glassfish) 9.x
• External JNDI LDAP resource configured

License: Apache 2.0
Package: EJBLDAPModule.zip

This is a Netbeans 6 project.

Sun Java System Application Server 9.x (glassfish) External JNDI LDAP Resource Part II

In my recent blog entry on configuring an external JNDI LDAP entry, I showed how to connect to an LDAP server as a JNDI reference. This assumes that the LDAP server is on the local machine and that it allows anonymous authentication. Usually this is not the case. I will cover how to use a login to an LDAP server which requires a little more configuration.

Prerequisites:

• A working LDAP server
• A login which can browse the directory tree

Instructions:

Please follow the directions in my previous blog entry to set up the basic external JNDI resource. The Sun Java System Application Server 9.1 Administration Guide is incorrect on how to set the properties. It refers to using some properties that are defined for LDAP like PROVIDER-URL. Unfortunately, to use them you would need to prefix them with the appropriate class. So we will use an alternate tack and use them by their fully qualified names. Add the following properties to the entry.

PROVIDER-URL:
java.naming.provider.url

SECURITY_PRINCIPAL:
java.naming.security.principal

SECURITY_AUTHENTICATION:
java.naming.security.authentication

SECURITY_CREDENTIALS:
java.naming.security.credentials

Since we have the fully qualified names, we can use them to set the properties for our external JNDI resource. See the image below.


Once you have the properties set and saved, you will have a complete external JNDI LDAP connection.

Congratulations!

Sun Java System Application Server 9.x (glassfish) External JNDI LDAP Resource Part I

I read the SJSAS Administration Guide to determine how to set up an external JNDI Resource. In my case, I wanted to simply provide another method to talk to LDAP outside of the security context. The Administration Guide is helpful...but incorrect. I have included the steps required to connect to an external LDAP server below. This works with OpenLDAP and SunONE Directory Server 5.2. The syntax should be similar on other systems.

Prerequisites:

• An LDAP server. I use both OpenLDAP and SunONE Directory Server
• Sun Java System Application Server 9.x (Project Glassfish). I am using Glassfish V2 Build 37

Instructions:

1. Log into the glassfish administration console.
2. Navigate to the Resources --> JNDI --> External Resources tree.


3. Create a new JNDI External Resource (see image below)

• JNDI Name: pick a unique name for the resource. I prefix mine with ldap e.g. ldap/myldap
• Resource Type: javax.naming.ldap.LdapContext
• Factory Class: com.sun.jndi.ldap.LdapCtxFactory
• JNDI Lookup: this would be your BaseDN e.g. dc=bluelotusholdings,dc=com

4. Check to make sure that the resource was created correctly and that it appears in the server JNDI Browser. If it does, you have configured it correctly. To find the JNDI Browser go to Application Server --> JNDI Browsing.


5. You should see something that looks like the image below. Please note the organizationalUnit (ou) ou=people and ou=groups are displayed as nodes in the BaseDN.


Success!