OpenForum comes with a JDBC API as standard. Access for any RDB that has a JDBC driver can be configured. As an example, integration of the free open source database HQSQL is included.

Drivers for other popular databases can be found on the google code project page.


Using the API you can execute SQL from within server side Javascript using a simple set of methods.

To get an instance of the API call the getApi method like this:

db = js.getApi("/OpenForum/JarManager/JDBC");

db makes available the following methods:

/OpenForum/JarManager/JDBC/HQSQL has an example of accessing a database and displaying the result of a search. The server side javascript is held in a file get.sjs attached to the page /OpenForum/JarManager/JDBC/HQSQL/Action and is as follows:

// Get the parameter SQL from the page request SQL = transaction.getParameter("SQL"); // If no parameter is found then the wiki page is displayed if(SQL==null) { transaction.setResult(transaction.SHOW_PAGE); } // If the SQL parameter is present else { type = transaction.getParameter("type"); if(type==null) { type = "query" } // Get a connection to the database with alias test db = js.getApi("/OpenForum/JarManager/JDBC"); // If the parameter type is query then query the database // and display the result as a wiki table if(type=="query") { // Run the query and get the resulting xml result set result = db.query("test",SQL); data = ""; columns = Number(result.getAttributeValueByName("columns")); // Render the column titles in a wiki table xColumns = result.getElementByName("columns"); for(column=0;column<columns;column++) { data+="|__"+xColumns.getElementByIndex(column).getValue()+"__"; } data+="\n"; // Render each row as a row in a wiki table rows = result.getElementsByName("row"); for(row=0;row<rows.size();row++) { for(column=0;column<columns;column++) { data+="|"+rows.elementAt(row).getElementByIndex(column).getValue(); } data+="\n"; } } //If the paramter type is not query then execute the query else { data = db.execute("test",SQL); } page = transaction.buildPage( "Result",data,true ); transaction.sendPage(page); }

The Result Xml format

SQL queries return there results as Xml in the followning format:

<result> <columns> <column>COLUMN_A</column> <column>COLUMN_2</column> </columns> <row> <cell>cell value a</cell> <cell>cell value 2</cell> </row> <row> <cell>cell value b</cell> <cell>cell value 3</cell> </row> </result>

OpenForum has an Xml API for working with Xml elements which is documented here