Using Oracle Dialect in HSQLDB

HSQLDB supports Oracle dialect starting version 2.0 onwards. However, there is not much documentation or examples around this feature.

In this post, I will explain the usage of this feature with a simple example.

To enable SQL dialect, two steps are required:

1. Set the property "sql.syntax_ora=true" in your connection string.

Ex.

String connectURL="jdbc:hsqldb:file:C:/projects/mxdb/mxStoreDb;sql.syntax_ora=true";

2. Set the below execute to execute before any SQL statements:

"SET DATABASE SQL SYNTAX ORA TRUE"

Below is a simple Java code which explains the usage:

package com.jm.hsqlDemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HSQLConnect {

	static Connection conn;

	public static void main (String [] args) throws SQLException{
    	String driver="org.hsqldb.jdbcDriver";
    	String connectURL="jdbc:hsqldb:file:C:/projects/mxdb/mxStoreDb;sql.syntax_ora=true";

    	String username="sa";
    	String password="";

    	try {

    		Class.forName(driver);
    		conn = DriverManager.getConnection(connectURL,username, "");

			Statement st = null;
            ResultSet rs = null;

    		st= conn.createStatement();
    		st.execute("SET DATABASE SQL SYNTAX ORA TRUE");
    		st.execute("DROP TABLE MX_NAMES");
    		st.execute("CREATE TABLE MX_NAMES(NAME VARCHAR2(35))");
    		st.execute("INSERT INTO MX_NAMES VALUES('Hello World12')");
    		st.execute("INSERT INTO MX_NAMES VALUES('Hello World31')");
    		conn.commit();
    		rs= st.executeQuery("SELECT  * FROM MX_NAMES");

    		while(rs.next()){
    			System.out.println(rs.getString(1));
    		}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.commit();
			conn.close();
			System.out.println("Connection closed");
		}

	}
}
Advertisements
This entry was posted in Java, Oracle, Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s