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
The Result Xml formatSQL queries return there results as Xml in the followning format:
COLUMN_A
COLUMN_2
cell value a |
cell value 2 |
cell value b |
cell value 3 |
- 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 |
|