firebird&jboss - entity-command an pk generation

[petrkalina]

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

[petrkalina]

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..

[petrkalina]

oh, sorry about the PostgreSQL comment..