MS Access in JBoss 3.0.8

[Bhaskar Chaganti]

Hello everybody!!
I want to configure my MS ACCESS JDBC Driver to work with JBoss 3.0.8 on running on Win'98. I've modified msaccess-service.xml file accordingly and stuck with creating jdbc-cmp.xml file.
What entry should I fill in for <datasource-mapping> in the xml file? Is it compulsory? If so could any one pls. help me about this. I've been around this problem since last one day.

[Jason Stull]

Hi Bhaskar,
As long as the MS Access DataSource is bound to JNDI, this example should get you going:

public class AccessTesterBean implements SessionBean {
......
public void testAccessDS() {
    Connection con = null;
    Statement s = null;
    try {
      Context ctx = new InitialContext();
      DataSource msAccessDS = (DataSource)ctx.lookup("java:/MSACCESSDS");
      con = msAccessDS.getConnection();
      s = con.createStatement();
      ResultSet rs = s.executeQuery("SELECT * FROM CD");
      while(rs.next()){
        System.out.println("CD INFO: ID = " + rs.getInt("CD_ID") + " NAME = " + rs.getString("CD_Name"));
      }
    }
    catch(Exception e){
      throw new EJBException("Failed executing public void testAccessDS(): ", e);
    }
    finally {
      try{s.close();}catch(Exception e2){}//try to close statement
      try{con.close();}catch(Exception e3){}//try to close connection
    }
  }
}
.....
}

[Bhaskar Chaganti]

Hi!!
Thanx for your peice of information. But my JBoss App server refuses to recognize the data source and persistently saying that <datasource-mapping> field is necessary and it could not find it. I am using the complete setup feature of JBoss and the error I get is in msaccess-service.xml file.
If you can provide me any valuable info regarding this I will be thankful.
Bye .Expecting a +ve reply

[Jason Stull]

"Expecting a +ve reply", whahuh???
Could you be more specific about what you are trying to accomplish? MBean configuration from msaccess-service.xml should look like:

<mbean code="org.jboss.resource.connectionmanager.LocalTxConnectionManager" name="jboss.jca:service=LocalTxCM,name=MSACCESSDS">
    <depends optional-attribute-name="ManagedConnectionFactoryName">
      <!--embedded mbean-->
      <mbean code="org.jboss.resource.connectionmanager.RARDeployment" name="jboss.jca:service=LocalTxDS,name=MSACCESSDS">
        <attribute name="JndiName">MSACCESSDS</attribute>
        <attribute name="ManagedConnectionFactoryProperties">
          <properties>
            <!-- format of URL is "jdbc :-o	 dbc :mrgreen: SNNAME" -->
            <config-property name="ConnectionURL" type="java.lang.String">jdbc :-o	 dbc:MyAccessDB</config-property>
            <config-property name="DriverClass" type="java.lang.String">sun.jdbc.odbc.JdbcOdbcDriver</config-property>
          </properties>
        </attribute>
<!--Below here are advanced properties -->
        <!--hack-->
        <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper</depends>
      </mbean>
    </depends>
    <depends optional-attribute-name="ManagedConnectionPool">
      <!--embedded mbean-->
      <mbean code="org.jboss.resource.connectionmanager.JBossManagedConnectionPool" name="jboss.jca:service=LocalTxPool,name=MSACCESSDS">
        <attribute name="MinSize">0</attribute>
        <attribute name="MaxSize">50</attribute>
        <attribute name="BlockingTimeoutMillis">5000</attribute>
        <attribute name="IdleTimeoutMinutes">15</attribute>
        <!--criteria indicates if Subject (from security domain) or app supplied
            parameters (such as from getConnection(user, pw)) are used to distinguish
            connections in the pool. Choices are 
            ByContainerAndApplication (use both), 
            ByContainer (use Subject),
            ByApplication (use app supplied params only),
            ByNothing (all connections are equivalent, usually if adapter supports
              reauthentication)-->
        <attribute name="Criteria">ByContainer</attribute>
      </mbean>
    </depends>
    <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager</depends>
    <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager</depends>
    <attribute name="TransactionManager">java:/TransactionManager</attribute>
    <!--make the rar deploy! hack till better deployment-->
    <depends>jboss.jca:service=RARDeployer</depends>
  </mbean>

[Bhaskar Chaganti]

Thanx.
Could you forward me the format for jbosscmp-jdbc.xml file for this? If all goes well my application should start running.
Bye

[Jason Stull]

ejb-jar.xml

<entity>
            <display-name>Cd</display-name>
            <ejb-name>Cd</ejb-name>
            <local-home>org.jas.CdHome</local-home>
            <local>org.jas.Cd</local>
            <ejb-class>org.jas.CdBean</ejb-class>
            <persistence-type>Container</persistence-type>
            <prim-key-class>java.lang.Integer</prim-key-class>
            <reentrant>False</reentrant>
            <cmp-version>2.x</cmp-version>
            <abstract-schema-name>Cd</abstract-schema-name>
            <cmp-field>
                <field-name>cdId</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>cdName</field-name>
            </cmp-field>
            <cmp-field>
                <field-name>cdArtist</field-name>
            </cmp-field>
            <primkey-field>cdId</primkey-field>
</entity>

jboss-cmpjdbc.xml

<jbosscmp-jdbc>
    <defaults>
        <datasource>java:/MSACCESSDS</datasource>
        <datasource-mapping>MSACCESS2000</datasource-mapping>
        <create-table>false</create-table>
        <remove-table>false</remove-table>
        <read-only>false</read-only>
    </defaults>
    <enterprise-beans>
        <entity>
            <ejb-name>Cd</ejb-name>
            <table-name>CD</table-name>
            <cmp-field>
                <field-name>cdId</field-name>
                <column-name>CD_ID</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>cdName</field-name>
                <column-name>CD_NAME</column-name>
            </cmp-field>
            <cmp-field>
                <field-name>cdArtist</field-name>
                <column-name>CD_ARTIST</column-name>
            </cmp-field>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

jboss.xml

<jboss>
    <enterprise-beans>
        <entity>
            <ejb-name>Cd</ejb-name>
            <local-jndi-name>Cd</local-jndi-name>
        </entity>
    </enterprise-beans>
</jboss>

add to standardjbosscmp-jdbc.xml (Not pretty, but actually works):

<type-mapping>
         <name>MSACCESS2000</name>
         <row-locking-template>SELECT ?1 FROM ?2 with (rowlock) WHERE ?3</row-locking-template>
         <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
         <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
         <alias-header-prefix>t</alias-header-prefix>
         <alias-header-suffix>_</alias-header-suffix>
         <alias-max-length>32</alias-max-length>
         <subquery-supported>true</subquery-supported>
         <true-mapping>1</true-mapping>
         <false-mapping>0</false-mapping>
         <mapping> 
            <java-type>java.lang.Integer</java-type> 
            <jdbc-type>INTEGER</jdbc-type> 
            <sql-type>NUMBER</sql-type> 
         </mapping>
         <mapping> 
            <java-type>java.lang.String</java-type> 
            <jdbc-type>VARCHAR</jdbc-type> 
            <sql-type>TEXT</sql-type> 
         </mapping> 
     </type-mapping>

[ April 13, 2004: Message edited by: Jason Stull ]