XmlBlaster Logo

REQUIREMENT

engine.service.rdbms

XmlBlaster Logo


Type NEW
Priority LOW
Status CLOSED
Topic XmlBlaster provides a framework to send SQL statements to arbitrary databases simultaneously and receive query results xml encoded (JDK 1.5 or higher only)
Des
cription

XmlBlaster has a native service allowing querying relational databases (RDBMS). Every xmlBlaster client can address this service with any valid SQL statement. If the SQL statement was a 'SELECT' the result set is delivered xml encoded.

In the example section below we provide examples how to access Oracle and how to access dBase files from any xmlBlaster client.

You need to setup the xmlBlaster JDBC driver in xmlBlaster.properties to use the RDBMS service.
First the xmlBlaster driver has to be activated (which is default): 

ProtocolPlugin[JDBC][1.0]=org.xmlBlaster.protocol.jdbc.JdbcDriver
CbProtocolPlugin[JDBC][1.0]=org.xmlBlaster.protocol.jdbc.CallbackJdbcDriver
      
   <!-- xmlBlasterPlugins.xml: -->
   <plugin create='true' id='JDBC' className='org.xmlBlaster.protocol.jdbc.JdbcDriver'>
      <action do='LOAD' onStartupRunlevel='5' sequence='5' />
      <action do='STOP' onShutdownRunlevel='4' sequence='1'/>   
   </plugin>

Second, you have to activate the JDBC driver of your databases: 

JdbcDriver.drivers=sun.jdbc.odbc.JdbcOdbcDriver,\
                   ORG.as220.tinySQL.dbfFileDriver,\
                   oracle.jdbc.driver.OracleDriver,\
                   org.gjt.mm.mysql.Driver,\
                   postgresql.Driver

JdbcDriver.drivers is a comma delimited list of database drivers. Use as many as you like. The appropriate driver class files(jar) must be in your classpath.
Note that Sun's JDBC-ODBC bridge and tinySQL (a dBase driver) are activated as default.

To test it we have provided a command line based demo client XmlDBClient in the directory xmlBlaster/demo/javaclients/jdbc
See the example below how to use it.

Most exceptions are caught and returned in the appropriate xml message.

To invoke the client, try something similar to the following: 

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:postgresql://24.3.47.214/postgres" \
   -user postgres \
   -pass secret \
   -type query \
   -query "select * from foo_table" \
   -limit 50 \
   -confirm true

or to invoke an update: 

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" \
   -user marcel \
   -pass secret \
   -type update \
   -query "INSERT INTO myTable VALUES( '1', to_date('0816', 'hh24mi'), 'james', 'START')"

Options: 

   -url (any valid JDBC url)
   -user (valid DB username)
   -pass (valid DB password)
   -query (any valid SQL syntax)
   -type (query|update) query by default - determines the interaction with db
   -confirm (true|false) true by default - when set to true, you get an answer
   -limit (any integer) 50 by default - used to limit the number of rows returned

NOTE:

  1. If you are querying, set your -type to 'query'.
  2. If you are updating (insert, update, delete) set -type to 'update'.
  3. If you are updating and don't want a confirmation, set -confirm to 'false'.

More examples are in xmlBlaster/demo/javaclients/jdbc

If you want to do queries synchronously, for example if you don't want to establish a callback server in your client, see the example in 

xmlBlaster/demo/javaclients/jdbc/XmlDBClientSync.java

get() requests on key oid="__sys__jdbc" are handled by xmlBlaster (see RequestBroker.java) directly and the result set is delivered as the return value of the get() request.

Example
any

We demonstrate RDBMS access with a simple command line example, creating, inserting and querying a table 'cars'. Usually you wouldn't do this from command line but embed it into your xmlBlaster client. Each query is an xmlBlaster message sent.

This example uses tinySQL, a JDBC driver supporting dBase file formats. It is delivered with xmlBlaster to allow regression tests of our JDBC plugin, so you can instantly try the following commands or query one of your dBase files: 

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type update \
      -query "CREATE TABLE cars (name CHAR(25), id NUMERIC(4,0))"

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type update \
      -query "INSERT INTO cars (name, id) VALUES('Fiat', 1)"

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type update \
      -query "INSERT INTO cars (name, id) VALUES('Jeep', 7)"

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type query \
      -query "SELECT * from cars"

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type query \
      -query "SELECT * from cars where id > 0"

java javaclients.jdbc.XmlDBClient -url "jdbc:dbfFile:." -user joe -type update \
      -query "DROP table cars"

NOTE:

  1. The given user name is optional as tinySQL (dBase) does not support authentication, it is useful for nicer logging only.
  2. tinySQL can only access files on the host where xmlBlaster is running (it is no socket based JDBC driver).
  3. The given url "jdbc:dbfFile:." looks for or adds dBase files in the given directory ".", that is there where xmlBlaster was started. In the above example there will be created a dBase file cars.DBF in the directory where you started xmlBlaster.
    You can pass any path on your local harddisk like "jdbc:dbfFile:/home/marcel/test" or "jdbc:dbfFile:C:\testBlaster\db"
    If you have a dBase file C:\testBlaster\db\myData.DBF you access it like "jdbc:dbfFile:C:\testBlaster\db" and select * from myData
  4. Using xmlBlaster it is possible to query dBase files remotely from any programming language like C/C++, Java, PHP, perl etc.
  5. The query in the above example returns the cars entered formatted with XML. 
<?xml version="1.0" encoding="UTF-8"?>

<dbadapterresults>
  <desc>
    <numcolumns>2</numcolumns>
    <columnnames>
      <column>name</column>
      <column>id</column>
    </columnnames>
    <rownum>2</rownum>
  </desc>
  <results>
    <row>
      <name>
        Fiat
      </name>
      <id>
        1
      </id>
    </row>
    <row>
      <name>
         Jeep
      </name>
      <id>
        7
      </id>
    </row>
  </results>
</dbadapterresults>

Note that the result values are encapsulated by CDATA sections (not shown here).

Example
any

We demonstrate RDBMS access with a simple command line example, creating, inserting and querying a table 'person' with Oracle: 

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" -user mrf -pass mrf -type update \
   -query "CREATE TABLE person (name VARCHAR(32), age NUMBER)"

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" -user mrf -pass mrf -type update \
   -query "INSERT INTO person VALUES( 'Ben', '6' )"

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" -user mrf -pass mrf -type update \
   -query "INSERT INTO person VALUES( 'Tim', '8' )"

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" -user mrf -pass mrf -type update \
   -query "INSERT INTO person VALUES( 'Gesa', '10' )"

java javaclients.jdbc.XmlDBClient \
   -url "jdbc:oracle:thin:@localhost:1521:MARCEL" -user mrf -pass mrf \
   -query "SELECT * FROM person"

The result looks like this: 

   <?xml version="1.0" encoding="UTF-8"?>
   <dbadapterresults>
     <desc>
       <numcolumns>2</numcolumns>
       <columnnames>
         <column>NAME</column>
         <column>AGE</column>
       </columnnames>
       <rownum>3</rownum>
     </desc>
     <results>
       <row>
         <NAME>
           Ben
         </NAME>
         <AGE>
           6
         </AGE>
       </row>
       <row>
         <NAME>
           Tim
         </NAME>
         <AGE>
           8
         </AGE>
       </row>
       <row>
         <NAME>
           Gesa
         </NAME>
         <AGE>
           10
         </AGE>
       </row>
     </results>
   </dbadapterresults>

Note that the result values are encapsulated by CDATA sections (not shown here).

Example
any

As default Sun's JDBC-ODBC bridge is activated so you can instantly access any ODBC database over xmlBlaster.

Configure

Server side configuration

Property Default / Example Description Impl
JdbcDriver.loginName __sys__jdbc The login name of the jdbc proxy plugin yes
JdbcDriver.password secret The password for the jdbc proxy to login yes
JdbcDriver.drivers sun.jdbc.odbc.JdbcOdbcDriver The underlying JDBC protocol driver to use, you can specify any number of drivers separated by ':'. Example (all in one line):
org.hsqldb.jdbcDriver: oracle.jdbc.driver.OracleDriver: COM.ibm.db2.jdbc.app.DB2Driver
All listed databases are available simultaneously. 		yes

If you for example use the htpasswd plugin for authentication try this setting:

Create a $HOME/xmlBlaster.htpasswd file with this entry:

__sys__jdbc:yZ24stvIel1j6

Change your $HOME/xmlBlaster.properties file with this entry:

Security.Server.Plugin.htpasswd.secretfile=${user.home}${file.separator}xmlBlaster.htpasswd

Start the server:

java org.xmlBlaster.Main -JdbcDriver.password secret

NOTE: Configuration parameters are specified on command line (-someValue 17) or in the xmlBlaster.properties file (someValue=17). See requirement "util.property" for details.
Columns named Impl tells you if the feature is implemented.
Columns named Hot tells you if the configuration is changeable in hot operation.

Todo 
   INSERT and UPDATE are currently literally given, there should
   be a xml encoded variant allowing to use the Prepared statement.
   Binary data containing the CDATA end-token are not supported.
See REQ engine.service.rdbms.jdbcpool
See API org.xmlBlaster.protocol.jdbc.JdbcDriver
See API org.xmlBlaster.protocol.jdbc.XmlDBAdapterWorker
See API javaclients.jdbc.XmlDBClient
See xmlBlaster/demo/javaclients/jdbc/README
See TEST org.xmlBlaster.test.jdbc.TestJdbcAccess

This page is generated from the requirement XML file xmlBlaster/doc/requirements/engine.service.rdbms.xml

Back to overview