7 Replies Latest reply on Jul 11, 2003 7:21 AM by viz

    Use MS-Access as EJB datasource

    Tsuki Kawahara Newbie

      What will be the <datasource-mapping> name for MS-Access in the jbosscmp-jdbc.xml?
      Someone told me that I cannot use MS-Access database to develop CMP Entity Bean and I rather need to use BMP. Is it true? Any suggestion is greatly appreciated.

        • 1. Re: Use MS-Access as EJB datasource
          viz Newbie

          There is an msaccess-ds.xml in the \docs\examples\jca directory - which would imply that it is supported?

          • 2. Re: Use MS-Access as EJB datasource
            Pranav Kapil Newbie

            Yes...it indeed is supported... I have tested it, and it works fine...

            Cheers...

            • 3. Re: Use MS-Access as EJB datasource
              Juha Lindfors Master

              wow... access has transactions?

              • 4. Re: Use MS-Access as EJB datasource
                viz Newbie

                Humm.... I did wonder why one might choose MS Access - not exactly the most robust, scalable datasource solution ;-)

                • 5. Re: Use MS-Access as EJB datasource
                  Tsuki Kawahara Newbie

                  Does anyone a working example of standardjbosscmp-jdbc.xml with the addition of the mapping to MS-Access? I have added the following mapping but it gives me an error.

                  <type-mapping>
                  MS-Access
                  <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE</row-locking-template>
                  <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (key)</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>TRUE</true-mapping>
                  <false-mapping>FALSE</false-mapping>


                  <java-type>java.lang.Boolean</java-type>
                  <jdbc-type>BIT</jdbc-type>
                  <sql-type>BOOLEAN</sql-type>


                  <java-type>java.lang.Byte</java-type>
                  <jdbc-type>TINYINT</jdbc-type>
                  <sql-type>SMALLINT</sql-type>


                  <java-type>java.lang.Short</java-type>
                  <jdbc-type>SMALLINT</jdbc-type>
                  <sql-type>SMALLINT</sql-type>


                  <java-type>java.lang.Integer</java-type>
                  <jdbc-type>INTEGER</jdbc-type>
                  <sql-type>INTEGER</sql-type>


                  <java-type>java.lang.Long</java-type>
                  <jdbc-type>BIGINT</jdbc-type>
                  <sql-type>BIGINT</sql-type>


                  <java-type>java.lang.Float</java-type>
                  <jdbc-type>REAL</jdbc-type>
                  <sql-type>FLOAT</sql-type>


                  <java-type>java.lang.Double</java-type>
                  <jdbc-type>DOUBLE</jdbc-type>
                  <sql-type>DOUBLE</sql-type>


                  <java-type>java.lang.Character</java-type>
                  <jdbc-type>CHAR</jdbc-type>
                  <sql-type>CHAR</sql-type>


                  <java-type>java.lang.String</java-type>
                  <jdbc-type>VARCHAR</jdbc-type>
                  <sql-type>VARCHAR(64)</sql-type>


                  <java-type>java.sql.Date</java-type>
                  <jdbc-type>DATE</jdbc-type>
                  <sql-type>DATE</sql-type>


                  <java-type>java.sql.Time</java-type>
                  <jdbc-type>TIME</jdbc-type>
                  <sql-type>TIME</sql-type>


                  <java-type>java.sql.Timestamp</java-type>
                  <jdbc-type>TIMESTAMP</jdbc-type>
                  <sql-type>TIMESTAMP</sql-type>


                  <java-type>java.lang.Object</java-type>
                  <jdbc-type>JAVA_OBJECT</jdbc-type>
                  <sql-type>JAVA_OBJECT</sql-type>

                  </type-mapping>

                  And this is the error:

                  2003-07-08 13:09:30,262 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.StudentInfoWriter] Executing SQL: CREATE TABLE studentInfo (key INTEGER, id VARCHAR(64), firstName VARCHAR(64), lastName VARCHAR(64), itemNumber VARCHAR(64), response VARCHAR(64), respondTime VARCHAR(64), reviewTime VARCHAR(64), score VARCHAR(64), finalScore DOUBLE, section VARCHAR(64), startTime TIMESTAMP, endTime TIMESTAMP, testName VARCHAR(64), ipAddress VARCHAR(64), CONSTRAINT pk_studentInfo PRIMARY KEY (key))
                  2003-07-08 13:09:30,362 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.StudentInfoWriter] Could not create table studentInfo
                  org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.)

                  I know the MS-Access is not very good db for web application but our client strongly requests to use it... Thank you for all your help!

                  • 6. Re: Use MS-Access as EJB datasource
                    viz Newbie

                    You shouldn't need to touch the standardjbosscmp-jdbc.xml

                    Simply copy the msaccess-ds.xml to the /deploy folder then create a jbosscmp-jdbc.xml to use ms access. I've not tried this, but recently struggled with mySQL and this very issue.

                    --
                    Marc

                    • 7. Re: Use MS-Access as EJB datasource
                      viz Newbie

                      What I mean by 'I haven't tried this' is that I've not tried this with MS Access :-)

                      BTW the jbosscmp-jdbc.xml should be located in you META-INF directory.

                      Regards,
                      --
                      Marc