[xmlblaster] RE: Port to Microsoft SQL Server

[Marcel Ruff <mr at marcelruff.info>]

Hi zhang zhi wei,

thanks for your port to Microsoft SQL Server!

>  Modifying XmlBlaster to Support Microsoft SQL Server
>
>1. about the Production name
>
>   the database plugin name in xmlblaster.properties must
>equals the name get in jdbc connection meta data. For example
>oracle, or postgres. as for Microsoft SQL Server, we cannot
>use Microsoft SQL Server as database plugin name because of the
>space between it.
>    so we must modify the source to code to solve this problem.
>
>
>  In JdbcConnectionPool.java modify parseMapping method to deal with
>the space problem:
>////////////////////////////////////////////////////////////
> parseMapping :
> try {
>         conn = this.getConnection();
>         productName = conn.getMetaData().getDatabaseProductName();
>
>    //only the following line was added
>    if (productName.equals("Microsoft SQL Server"))productName="sqlserver";
>
>    }
>      finally {
>         if (conn != null) releaseConnection(conn);
>      }
>///////////////////////////////////////////////////////

If have added this code (to keep it generic i just throw out
all blanks):

    try {
       conn = this.getConnection();
       productName = conn.getMetaData().getDatabaseProductName();
       // e.g. replace "Microsoft SQL Server" to "MicrosoftSQLServer"
       // blanks are not allowed, thanks to zhang zhi wei
       productName = StringHelper.replaceAll(productName, " ", "");
    }
    finally {
       ...


> 2.Conguration in XmlBlaster.properties
>
>   JdbcStorage[sqlserver]=org.inforMessaging.util.queue.jdbc.JdbcQueueCommonTablePlugin,\
> 
>url=jdbc:microsoft:sqlserver://192.168.80.3:1433;SelectMethod=cursor;DatabaseName=zzw,\
>                    user=sa,\
>                    password=,\
>                    connectionPoolSize=5,\
>                    connectionBusyTimeout=90000,\
>                    maxWaitingThreads=300,\
>                    tableNamePrefix=XB_,\
>                    nodesTableName=NODES,\
>                    queuesTableName=QUEUES,\
>                    entriesTableName=ENTRIES,\
>                    dbAdmin=true
>
>    StoragePlugin[JDBC][1.0]=${JdbcStorage[sqlserver]}
>
>    QueuePlugin[JDBC][1.0]=${JdbcStorage[sqlserver]}
>
>    JdbcDriver.mapping[sqlserver]=string=CHAR(128),longint=numeric 
>(19),boolean=char(1),blob=image,tables=pg_tables,tablename=tablename,connectionErrorCodes=1089:17002

OK, i have added this to

  xmlBlaster/config/xmlBlaster.properties.template


>3.create database in SQL Server, as for this demo ,it is zzw
>
>4.create view in SQL Server
>  Because of the difference of ORACLE and SQL Server in their storing the meta information of
> database , and the fact the xmlblaster kernel code use this meta info, we must create a viw
>in SQL Server to accomadate the kernel code in xmlblaster.
>
>
> create view "pg_table"in SQL Server
>
> SELECT name AS tablename
> FROM dbo.sysobjects
> WHERE (xtype = 'U')
>
>5.Modify the preparedQuery.java code
>  and the modification has been test ok for ORACLE. For postgres , we have not test.
>
>  public class PreparedQuery {
>
>  //Be careful the following line.
>  //private PreparedStatement st = null;
>  private Statement st = null;
>
>  .................................
>
>  public PreparedQuery(JdbcConnectionPool pool, String request, boolean isAutoCommit,
> LogChannel log, int fetchSize)      throws SQLException, InforMessagingException
>  {
> ...
>        //Notice the following line
>        //   this.st = conn.prepareStatement(request);
>          this.st = conn.createStatement();
>
Ok, done.



>   public final ResultSet inTransactionRequest(String request /*, int fetchSize */)
>      //Notice the following line
>      //  this.st = conn.prepareStatement(request);
>      this.st = conn.createStatement();
Ok, done.


> Then it will ok to work now !
>
>Good luck for you!
>
>zhang zhi wei

Thanks for this port, it is now available with cvs,
could you please check out and confirm that my changes are correct?

I have added a little requirement:
 http://www.xmlblaster.org/xmlBlaster/doc/requirements/queue.jdbc.sqlserver.html
Please mail me if there are mistakes in the document.

The testsuite passes for Postgres and Oracle on Linux here.

best regards,

Marcel