[jboss-user] [JBoss Messaging] - FYI: mssql-persistence.xml uses reserved words for column n

[chip_schoch]

I noticed in the mssql-persistence.xml file that came with JBM1.2 the word CLUSTERED is used for a column name.  This is a reserved word.  Changing the script to IS_CLUSTERED fixes the exception thrown when creating the tables:

<?xml version="1.0" encoding="utf-8"?>
  | 
  | <!--
  |      MS SQL Server non-clustered persistence deployment descriptor.
  | 
  |      Tested with SQL Server 2005
  | 
  |      $Id$
  |  -->
  | 
  | <server>
  | 
  |    <mbean code="org.jboss.messaging.core.plugin.JDBCPersistenceManagerService"
  |          name="jboss.messaging:service=PersistenceManager"
  |          xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
  |          <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
  |          <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
  |          <attribute name="DataSource">java:/DefaultDS</attribute>
  |          <attribute name="CreateTablesOnStartup">true</attribute>
  |          <attribute name="UsingBatchUpdates">true</attribute>
  |          <attribute name="SqlProperties"><![CDATA[
  |    CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (CHANNEL_ID BIGINT, MESSAGE_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INT, SCHED_DELIVERY BIGINT, PRIMARY KEY(CHANNEL_ID, MESSAGE_ID))
  |    CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID)
  |    CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JBM_MSG_REF_ORD ON JBM_MSG_REF (ORD)
  |    CREATE_IDX_MESSAGE_REF_PAGE_ORD=CREATE INDEX JBM_MSG_REF_PAGE_ORD ON JBM_MSG_REF (PAGE_ORD)
  |    CREATE_IDX_MESSAGE_REF_MESSAGE_ID=CREATE INDEX JBM_MSG_REF_MESSAGE_ID ON JBM_MSG_REF (MESSAGE_ID)
  |    CREATE_IDX_MESSAGE_REF_SCHED_DELIVERY=CREATE INDEX JBM_MSG_REF_SCHED_DELIVERY ON JBM_MSG_REF (SCHED_DELIVERY)
  |    CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, HEADERS IMAGE, PAYLOAD IMAGE, CHANNEL_COUNT INT, TYPE TINYINT, PRIMARY KEY (MESSAGE_ID))
  |    CREATE_TRANSACTION=CREATE TABLE JBM_TX (TRANSACTION_ID BIGINT, BRANCH_QUAL VARBINARY(254), FORMAT_ID INT, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID))
  |    CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME))
  |    INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  |    DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
  |    UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
  |    UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
  |    COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
  |    COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
  |    ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
  |    ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
  |    LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
  |    LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
  |    LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
  |    UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
  |    SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
  |    SELECT_EXISTS_REF=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND MESSAGE_ID = ?
  |    SELECT_EXISTS_REF_MESSAGE_ID=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE MESSAGE_ID = ?
  |    UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE CHANNEL_ID = ? AND MESSAGE_ID = ?
  |    UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
  |    LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
  |    INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, CHANNEL_COUNT, TYPE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  |    INC_CHANNEL_COUNT=UPDATE JBM_MSG SET CHANNEL_COUNT = CHANNEL_COUNT + 1 WHERE MESSAGE_ID=?
  |    DEC_CHANNEL_COUNT=UPDATE JBM_MSG SET CHANNEL_COUNT = CHANNEL_COUNT - 1 WHERE MESSAGE_ID=?
  |    DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID=? AND CHANNEL_COUNT=0
  |    MESSAGE_ID_COLUMN=MESSAGE_ID
  |    MESSAGE_EXISTS=SELECT MESSAGE_ID FROM JBM_MSG WITH (HOLDLOCK, ROWLOCK) WHERE MESSAGE_ID = ?
  |    INSERT_TRANSACTION=INSERT INTO JBM_TX (TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?)
  |    DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE TRANSACTION_ID = ?
  |    SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX
  |    SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
  |    SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
  |    UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
  |    SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WITH (HOLDLOCK, ROWLOCK) WHERE NAME=?
  |    INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
  |    SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
  |          ]]></attribute>
  |          <attribute name="MaxParams">500</attribute>
  |    </mbean>
  | 
  |    <mbean code="org.jboss.messaging.core.plugin.DefaultPostOfficeService"
  |       name="jboss.messaging:service=PostOffice"
  |       xmbean-dd="xmdesc/DefaultPostOffice-xmbean.xml">
  |       <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
  |       <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
  |       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
  |       <attribute name="PostOfficeName">JMS</attribute>
  |       <attribute name="DataSource">java:/DefaultDS</attribute>
  |       <attribute name="CreateTablesOnStartup">true</attribute>
  |       <attribute name="SqlProperties"><![CDATA[
  | CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID SMALLINT, QUEUE_NAME VARCHAR(1023), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID INTEGER, IS_CLUSTERED CHAR(1))
  | INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, IS_CLUSTERED) VALUES (?, ?, ?, ?, ?, ?, ?)
  | DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
  | LOAD_BINDINGS=SELECT NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, IS_CLUSTERED FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME  = ?
  |       ]]></attribute>
  |    </mbean>
  | 
  |    <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
  |       name="jboss.messaging:service=JMSUserManager"
  |       xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
  |       <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
  |       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
  |       <attribute name="DataSource">java:/DefaultDS</attribute>
  |       <attribute name="CreateTablesOnStartup">true</attribute>
  |       <attribute name="SqlProperties"><![CDATA[
  | CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID))
  | CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID))
  | SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
  | POPULATE.TABLES.1=INSERT INTO JBM_USER (USER_ID,PASSWD,CLIENTID) VALUES ('dilbert','dogbert','dilbert-id')
  |       ]]></attribute>
  |    </mbean>
  | 
  | </server>
  | 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4035745#4035745

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4035745