2 Replies Latest reply on Mar 29, 2006 11:34 AM by p k

    firebird&jboss - entity-command an pk generation

    p k Newbie

      hi,

      I'm trying to find out how to declare autogeneration of primary keys in jbosscmp-jdbc.xml.

      I know how to do this with the databases where this option is supported in standardjboss-cmp.xml (like postgre..):

      jbosscmp-jdbc:

       <entity>
       <ejb-name>Item</ejb-name>
       <table-name>item</table-name>
      
       <cmp-field>
       <field-name>pk</field-name>
       <column-name>pk</column-name>
       <auto-increment/>
       </cmp-field>
      ...
       <entity-command name="postgresql-fetch-seq">
       </entity-command>
      </entity>
      


      However, for firebird there's no entity-command defined in standardjboss-cmp.xml.

      My first guess was, that I should use the generic pk-sql command and insert the code to obtain the pk in firberd:

      standardjboss-cmp.xml:
      <entity-command name="pk-sql" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPkSqlCreateCommand">
       <attribute name="pk-sql">GEN_ID(generator_name, 1)</attribute>
      </entity-command>
      


      but obviously there's a catch in the fact I don't know the name of the generator to use.

      I looked at he code of the other generating methods, and they have a code similar to this somewhere inside
      sequence = entity.getQualifiedTableName()
      + '_' + SQLUtil.getColumnNamesClause(pkField, new StringBuffer(20))
      + "_seq";
      


      So my second quess is, that I might have to write the new entity command by hand based upon the code similar as above, or I might create a procedure and entity command for each table and call these commands.

      Is there someone who could help me to find a neater solution?

      Thanks a lot,
      -- peter kalina

        • 1. Re: firebird&jboss - entity-command an pk generation
          p k Newbie

          I solved the problem myself.

          The simpliest solution for me seemed indeed to create my own entity command:

          public class JDBCFirebirdCreateCommand extends JDBCInsertPKCreateCommand
          {
           private String generator;
           private String generatorSQL;
          
           protected JDBCCMPFieldBridge pkField;
          
           public JDBCFirebirdCreateCommand() {
           super();
           }
          
           protected void initGeneratedFields() throws DeploymentException
           {
           super.initGeneratedFields();
           pkField = getGeneratedPKField();
           }
          
           public void init(JDBCStoreManager manager) throws DeploymentException
           {
           super.init(manager);
           }
          
           protected void initEntityCommand(JDBCEntityCommandMetaData entityCommand) throws DeploymentException
           {
           super.initEntityCommand(entityCommand);
          
           generator = entityCommand.getAttribute("generator");
          
           String pkFieldName;
           JDBCFieldBridge[] pkFields = entity.getPrimaryKeyFields();
           if ( pkFields.length != 1 )
           throw new DeploymentException("More htan one or missing primary key field");
           pkFieldName = pkFields[0].getFieldName();
          
           if (generator == null) {
           generator = entity.getQualifiedTableName()
           + "_" + pkFieldName.toUpperCase()
           + "_GEN";
           }
          
           generatorSQL = "SELECT GEN_ID("+generator+",1) FROM RDB$DATABASE;";
           if (debug) {
           log.debug("SEQUENCE SQL is :"+generatorSQL);
           }
           }
           protected void generateFields(EntityEnterpriseContext ctx) throws CreateException
           {
           super.generateFields(ctx);
          
           Connection con = null;
           Statement s = null;
           ResultSet rs = null;
           try
           {
           if(debug)
           {
           log.debug("Executing SQL: " + generatorSQL);
           }
          
           DataSource dataSource = entity.getDataSource();
           con = dataSource.getConnection();
           s = con.createStatement();
          
           rs = s.executeQuery(generatorSQL);
           if(!rs.next())
           {
           throw new CreateException("Error fetching next primary key value: result set contains no rows");
           }
           pkField.loadInstanceResults(rs, 1, ctx);
           }
           catch(SQLException e)
           {
           log.error("Error fetching the next primary key value", e);
           throw new CreateException("Error fetching the next primary key value:" + e);
           }
           finally
           {
           JDBCUtil.safeClose(rs);
           JDBCUtil.safeClose(s);
           JDBCUtil.safeClose(con);
           }
           }
          }
          

          the "path" to the command has to be added to standardjbosscmp-jdbc.xml or to some other place that siuts you (jbosscmp-jdbc.xml..)
           <!-- retrieves the currval of the the sequence associated with a PostgreSQL SERIAL column -->
           <entity-command name="firebird-generate-pk"
           class="cz.jlabs.bookstore.ejb.integration.JDBCFirebirdCreateCommand">
           <!-- uncomment to change the name of the sequence; default is ${table}_${pkColumn}_seq
           <attribute name="sequence"></attribute>
           -->
           </entity-command>
          

          in a jbosscmp-jdbc.xml the bean definition could look like this:
          <jbosscmp-jdbc>
           <defaults>
           <datasource>java:/FirebirdDS</datasource>
           <datasource-mapping>Firebird</datasource-mapping>
           <create-table>false</create-table>
           </defaults>
          
           <enterprise-beans>
          
           <!--
           To add beans that you have deployment descriptor info for, add
           a file to your XDoclet merge directory called jbosscmp-jdbc-beans.xml
           that contains the <entity></entity> markup for those beans.
           -->
          
           <entity>
           <ejb-name>Book</ejb-name>
           <table-name>books</table-name>
          
           <cmp-field>
           <field-name>pk</field-name>
           <column-name>pk</column-name>
           <auto-increment/>
           </cmp-field>
           <cmp-field>
           <field-name>author</field-name>
           <column-name>author</column-name>
           </cmp-field>
           <cmp-field>
           <field-name>title</field-name>
           <column-name>title</column-name>
           </cmp-field>
          
           ....
          
           <entity-command name="firebird-generate-pk">
           </entity-command>
           </entity>
          
          ....
          

          and that's all there is..

          • 2. Re: firebird&jboss - entity-command an pk generation
            p k Newbie

            oh, sorry about the PostgreSQL comment..