Integrating Relational Databases          
  
   
 


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:

  • createConnection(alias,url,user,password) - creates a connection to a database that can be accessed from any server side javascript
    • alias - The alias to be used to reference this connection
    • url - The url of the database to be accessed
    • user - The user name for the connection
    • password - The password for the connection
  • execute(alias, statement) - Executes an SQL statement against an aliased database
    • alias - The database alias
    • statement - The SQL statement to execute
  • query(alias, query) - Gets the result of a query for an aliased database
    • alias - The database alias
    • query - The SQL query to run
  • registerDriver(driverName,pageName,jarFileName) - Registers a JDBC driver for accessing databases
    • driverName - The fully qualified driver class name
    • pageName - The wiki page where the driver jar is attached
    • jarFileName - The name of the JDBC driver jar file

/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>
  • result - The result set tag name
  • columns - Contains all the column names
  • column - Represents a column and contains the column name
  • row - One is included for each row of the result set and contains a cell for each column
  • cell - Represents a rows cell and contains the cells value

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

by Admin on 21/04/2008 at 10:08 PM