XmlBlaster Logo

REQUIREMENT

queue.jdbc.sqlserver

XmlBlaster Logo


Type NEW
Priority MIDDLE
Status CLOSED
Topic XmlBlaster has an advanced queuing facility. Among others one tested persistence layer is offered by communicating with Microsoft SQL Server 2000 and 2005 database.
Des
cription

The generic plugin for jdbc based queues has been tested against Microsoft SQL Server by Zhang Zhi Wei (zzwzyz@163.com) and Marcel Ruff.

SQL Server is available from http://www.microsoft.com/sql/ where you can find all necessary information and instructions on how to make Microsoft SQL Server run.

XmlBlaster supports SQL Server 2000, 2005 and 2008. It is recommended to use the latest JDBC driver from Microsoft for all SQL Server versions, currently this is JDBC driver 1.2

Once you have a running database you can configure xmlBlaster to make it run against it. Before you need to download the jdbc driver. You must put these files in the CLASSPATH (for example you can put them into $XMLBLASTER_HOME/lib and add them to the CLASSPATH.

The tests where made with the type 4 JDBC driver of Microsoft with this extended CLASSPATH: 

old driver:
set CLASSPATH=\tmp\msbase.jar;\tmp\mssqlserver.jar;\tmp\msutil.jar;%CLASSPATH%

new driver 2005:
set CLASSPATH=\tmp\sqljdbc.jar;%CLASSPATH%

Setup SQL Server

  1. Setup xmlBlaster.properties as described in the example section below
  2. Create database in SQL Server, as for this demo ,it is xmlBlaster
  3. xmlBlaster creates on startup automatically the needed table

Note on temporary memory consumption

Expect the Microsoft JDBC driver to temporary allocate the 5 fold of the real message size during writing to persistence. If your persistent message is of size 40 MByte, xmlBlaster may for a short period of time allocate 240 MBytes of RAM (200 MBytes are allocated by the JDBC driver and 40 MBytes by xmlBlaster for the message itself). This is no JDBC driver memory leak as it is only temporary. The amount is depending on the quality of the used JDBC driver.

Example
Java

Setup SQL Server 2005

This is a configuration example from xmlBlaster.properties, please adjust it with your password:

See http://msdn2.microsoft.com/en-us/library/ms378428.aspx for a description of the URL syntax!

JdbcDriver.drivers=com.microsoft.sqlserver.jdbc.SQLServerDriver

JdbcStorage[MicrosoftSQLServer2005]=org.xmlBlaster.util.queue.jdbc.JdbcQueueCommonTablePlugin,\
                  url=jdbc:sqlserver://localhost:1433;databaseName=xmlBlaster,\
                  user=sa,\
                  password=xxx,\
                  connectionPoolSize=5,\
                  connectionBusyTimeout=90000,\
                  maxWaitingThreads=300,\
                  tableNamePrefix=XB_,\
                  entriesTableName=ENTRIES,\
                  colNamePrefix=XB_,\
                  dbAdmin=true
StoragePlugin[JDBC][1.0]=${JdbcStorage[MicrosoftSQLServer2005]}
QueuePlugin[JDBC][1.0]=${JdbcStorage[MicrosoftSQLServer2005]}
JdbcDriver.mapping[MicrosoftSQLServer2005]=string=VARCHAR(128),longint=numeric (19),boolean=char(1),blob=image

After starting xmlBlaster (without errors) you can query a table:

MS-Dos Command line:

osql -E
1> use  xmlBlaster
2> go
1> select * from XB_ENTRIES
2> go
Example
Java

Setup for old SQL Server driver (deprecated)

This is a configuration example from xmlBlaster.properties, please adjust it with your password: 

JdbcDriver.drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver

JdbcStorage[MicrosoftSQLServer]=\
  org.xmlBlaster.util.queue.jdbc.JdbcQueueCommonTablePlugin,\
  url=jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=xmlBlaster,\
  user=sa,\
  password=,\
  connectionPoolSize=5,\
  connectionBusyTimeout=90000,\
  maxWaitingThreads=300,\
  tableNamePrefix=XB_,\
  entriesTableName=ENTRIES,\
  colNamePrefix=XB_,\
  dbAdmin=true

StoragePlugin[JDBC][1.0]=${JdbcStorage[MicrosoftSQLServer]}

QueuePlugin[JDBC][1.0]=${JdbcStorage[MicrosoftSQLServer]}

JdbcDriver.mapping[MicrosoftSQLServer]=\
  string=CHAR(128),longint=numeric (19),boolean=char(1),blob=image

After starting xmlBlaster (without errors) you can query a table:

MS-Dos Command line:

osql -E
1> use  xmlBlaster
2> go
1> select * from XB_ENTRIES
2> go
Example
Java

Before the first usage you have to create a database 'xmlBlaster' in SQL Server. You can use the SQL Server Enterprise Manager GUI tool or start this script file 'xmlBlaster.sql' from a MS-DOS box: 

-- File:    xmlBlaster.sql
--
-- Create 'xmlBlaster' database for MS-SQLServer
--
-- Invoke:  osql -E -i xmlBlaster.sql
--

use master 

if exists (select * from sysdatabases where name='xmlBlaster')
begin
  raiserror('Dropping existing xmlBlaster database ....',0,1)
  DROP database xmlBlaster
end
go

create database xmlBlaster
go

The final step is to create a user with a password and add this to xmlBlaster.properties.

Configure

These parameters allow to configure a cache queue running with Microsoft SQL Server

Property Default Description Hot Impl
QueuePlugin[CACHE][1.0] org.xmlBlaster.util.queue.
cache.CacheQueueInterceptorPlugin		 specifies the cache implementing class to be used for storing the queue entries yes yes
StoragePlugin[CACHE][1.0] org.xmlBlaster.util.queue.
cache.CacheQueueInterceptorPlugin		 specifies the cache implementing class to be used for storing the message content yes yes
queue/maxEntries 1000 specifies the maximum number of entries allowed in this queue yes yes
queue/maxBytes 10485760 (10MB) specifies the maximum total size allowed in this queue yes yes
queue/persistent/url jdbc:microsoft:sqlserver://localhost:1433 the url which specifies where to find the DB. yes yes
queue/persistent/user xmlblaster The db user under which to run yes yes
queue/persistent/password secret The password used together with the user 'xmlblaster' yes yes
Additionally the configuration parameters for the ram queue and the jdbc queue must be defined.

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.

See API org.xmlBlaster.util.queue.I_Queue
See API org.xmlBlaster.util.queue.jdbc.CacheQueuePlugin
See API org.xmlBlaster.util.queue.QueuePluginManager
See API org.xmlBlaster.util.queue.I_QueueEntry
See API org.xmlBlaster.util.qos.storage.QueuePropertyBase
See API org.xmlBlaster.util.I_Plugin
See API org.xmlBlaster.util.Global
See REQ queue
See Download Microsoft JDBC driver
See SQLServer tutorial
See TEST org.xmlBlaster.test.classtest.queue.I_QueueTest
See TEST org.xmlBlaster.test.classtest.queue.JdbcQueueTest
See TEST org.xmlBlaster.test.classtest.queue.CacheQueueTest

This page is generated from the requirement XML file xmlBlaster/doc/requirements/queue.jdbc.sqlserver.xml

Back to overview