Introduction

XwikSqlPlugin is useful in situation, when we need connect from xwiki application to foreign relational databases. With help of xwikisql plugin we can use sql statements in velocity templates. In addition application programmer cand not care about connection management: xwiki sql plugin do this automatically. So, programming in xwiki become efficient alternative to LAMP, applicable in java-based environment.

Download:

You can download project artifacts from google project space or from our local site:

xwiki-plugin-sql-1.5.jar – just jar.
xwiki-plugin-sql-src-1.5.tar.gz – source distributive.

License is GPL v3.

Installation:

Configure JNDI data store in application container, where you xwiki instance is running.
Add xwiki sql plugin to you xwiki instance, i.e:
- copy xwiki-plugin-sql-1.1.jar to <webapp-root>/WEB-INF/lib;
- add ua.gradsoft.xwikisql.SqlPlugin to list of active plugins in <webapp-root>/WEB-INF/xwiki.cfg;
- restart xwiki;

Usage:

Now it is possible to use sql statements in velocity templates. Detailed reference you can find in XwikiSqlApi, below is few code snippets:

## connect to database.
#set  ($mydb=$xwiki.sql.getDabase(“MyDS”))


## print top 10 records from our table.
<table>
#foreach($row in $mydb.executeQuery(
                    “select id, name 
                          from (select id, name, rownum from test_table) 
                          where rownum < 10”
              ))
 <tr>
  <td> $row.getLong(“id”) </td>
  <td> $row.getString(“name”) </td>
 </tr>
#end
</table>

Of course better use bind variables:

##receive database
#set  ($mydb=$xwiki.sql.getDabase(“MyDS”))


## print n pages from our table.
## set ($lowRow=$currentPage*$nRowsInPage)
## set ($hightRow=($currentPage+1)*$nRowsInPage)
<table>
#foreach($row in $mydb.executeQuery(
                    “select id, name 
                          from (select id, name, rownum from test_table) 
                          where rownum > ? and rownum < ?”
                     , [ $lowRow, $hightRow ]
              ))
 <tr>
  <td> $row.getLong(“id”) </td>
  <td> $row.getString(“name”) </td>
 </tr>
#end
</table>

Example of update statements:

##receive database
#set  ($mydb=$xwiki.sql.getDabase(“MyDS”))


#set  ($result=$mydb.executeUpdate(
             “update test_table set name=? where id=?”,
             [$myname,$myid])

Sometimes we need explicit set type of binded parameters, in such case we can write pair from typename and value (instead value on the same place), where typename is name of appropriative constants in javax.sql.Types API.

Example:



#set  ($result=$mydb.executeUpdate(
             “update test_table set name=? when=? where id=?”,
             [$myname,[TIME “2008-01-01T12:00:00”],$myid])

will set value of when to midday of 01.01.2008. As you see, date and time values are mapped to strings according to IS0 86601.

Yet one note about types: for now supported only primitive data types (if you need more – contact us).

That's all in glance. API description is available (reference).

Yet one not about error handling: usually errors during exceptions are throwed to xwiki top-level, but sometimes we need handle sql errors in velocity code. It is possible by setting database property throwExceptions to false, as in next code snippet:

$mydb.setThrowExceptions(false)
#set  ($result=$mydb.executeQuery($sql))
#if($mydb.wasError())
 Error during evaluating $sql: $mydb.getLastException().getMessage()  
#else
 sql sentence $sql was executed without errors.  Received $rs.getNRows() rows.
#end

Configuration:

XwikiSqlPlugin can be configured or through set of variables in xwiki.cfg, prefixed by 'xwikisql.' or through own configuration file. (name of file can be set in xwikisql.configuration.fname).

Configurated variables are next:

throwExceptions -- determinate first-time behavior ofxwiki on errors: when set to true – plugin throws exceptions on error, otherwise – log exception to ua.gradsoft.xwikisql.SqlPlugin logger with error mark and return to velocity null variable; Note, that this can be programmatially changed by call of SqlDatabase.setThrowExceptions().
logSql – if true, log sql statements to ua.gradsoft.xwikisql.SqlPlugin logger with info priority;
logEnabled – if false then disable all logging.
maxRowsRetrieved – maximum number of rows, which can be retrieved through one select statement. (default – 1000). If select statement returns more rows, than only maxRowsRetrieved are actually retrieved and method isNotAllRows() of approriative SQL Result returns true;
initialContextPrefix – initial context prefix to database. Default is 'java:comp/env'. Can be useful during work with obsolete versions of server.

Appropriative section of xwik.cfg can look as:



xwikisql.logSql=1
xwikisql.throwExceptions=0
xwikisql.maxRowsRetrieved=200

Or, when we want to keep xwikisql configuration in separate file, than in xwiki.cfg we have:


 xwikisql.configuration.fname=<fname>

and in <fname> - serialized from of class ua.gradsoft.xwikisql.XWikiSqlPluginConfiguration, according to JSR57:

<?xml version="1.0" encoding="UTF-8"?>
<java version="1.5.0" class="java.beans. XMLDecoder">
 <object class="ua.gradsoft.xwikisql.XWikiSqlPluginConfiguration">
  <void property=”throwExceptions”>
    <boolean>true</boolean>
  </void>
  <void property=”logSql”>
    <boolean>true</boolean>
  </void>
  <void property=”maxRowsRetrieved”>
    <int>300</int>
  </void>
 </object>
</java>

Also logging can be configured from xwiki-wide log4j.properties – to enable debug just append next string to log4j configuration and restart xwiki.

log4j.logger.ua.gradsoft.xwikisql=debug

Changes:

2008-05-07 revision-1.5

fixed bug wih setting of Timestamp and Type in input-parameters before Date (issue#1);
fixed bug with retrieving of jsch library during ivy configuration;
simple-jndi dependency is now from published 11.3 version;

2008-04-05 revision-1.4

added logEnable writable property to SQLErrorable;
changed ivy configuration to build with newest xwiki release.
fixed bug with setting logSql from configuration.

2008-03-07 revision-1.3

added throwExceptions writable property to SQLErrorable.

2008-02-27 revision-1.2

added initialContextPrefix configuration parameter;
agged getInteger() family of methods;

2008-02-11 revision-1.1

2008-02-06 Initial revision.