Database support

[pengtuck]

Does anyone know how easy is it to get support for other Databases into Nukes and what kind of work is required?

I'm currently interested in Nukes but wish to use it with another db like SAPDB (http://www.sapdb.org)

[julien1]

the most important thing is :

either the driver is JDBC3.0 compliant

or sapdb supports auto generated keys.
there is a way to retrieve it after it has been inserted.
if so, you need to create an entity command for sapdb.
this command will enable retrieving of the key after it has been inserted by the CMP engine :

look at org/jboss/server/ejb/plugins/cmp/jdbc/$vendor/JDBC$vendorCreateCommand.java

where vendor is :hsqldb, informix, jdbc3, mssql, mysql, postgres, sybase.

each of these class is an implementation. so if you know how to fetch the PK from the vendor you can copy one of these and implement it.

usually there are two ways to do such :

either downcast the connection to the vendor connection and fetch it by a specific method. (mysql for instance)

or executes a specific sql query right after the insertion to fetch it. (hsqldb for instance)

after done that, the rest is plain vanilla : write the DDL files that contains the statements to setup nukes.

julien

[pengtuck]

> the most important thing is :
>
> either the driver is JDBC3.0 compliant
Fortunately their driver is :D

Hmm, I understand what you are saying. Should be doable with any of your suggestions. Thanks :)

[olutosin]

For those interested in using SapDB, here is code for JDBCSapDBCreateCommand.java, I can also send the ddl for nukes if any body is interested


package org.jboss.ejb.plugins.cmp.jdbc.keygen;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.ejb.EJBException;

import org.jboss.ejb.plugins.cmp.jdbc.JDBCIdentityColumnCreateCommand;
import org.jboss.ejb.plugins.cmp.jdbc.SQLUtil;
import org.jboss.ejb.plugins.cmp.jdbc.JDBCUtil;
import org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager;
import org.jboss.ejb.plugins.cmp.jdbc.metadata.JDBCEntityCommandMetaData;
import org.jboss.ejb.EntityEnterpriseContext;
import org.jboss.deployment.DeploymentException;

/**
* Create command for use with SapDB, it that uses a sequence
* and uses sequence.CURRVAL to return the generated pk value
*
* @author Tosin Faleye
*
*/
public class JDBCSapDBCreateCommand extends JDBCIdentityColumnCreateCommand
{
private String sequence;
private int pkIndex;
private int jdbcType;

public void init(JDBCStoreManager manager) throws DeploymentException
{
super.init(manager);
}

protected void initEntityCommand(JDBCEntityCommandMetaData entityCommand) throws DeploymentException
{
super.initEntityCommand(entityCommand);
sequence = entityCommand.getAttribute("sequence");
if (sequence == null) {
throw new DeploymentException("Sequence must be specified");
}
else{
//SELECT SEQUENCE_NAME.CURRVAL AS CURRENT_VAL FROM DUAL
pkSQL = "SELECT " + sequence+".CURRVAL AS CURRENT_VAL FROM DUAL";
}
}

protected void initInsertSQL()
{
pkIndex = 1 + insertFields.length;
jdbcType = pkField.getJDBCType().getJDBCTypes()[0];

StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO ").append(entity.getTableName());
sql.append(" (");
SQLUtil.getColumnNamesClause(pkField, sql)
.append(", ");

SQLUtil.getColumnNamesClause(insertFields, sql);

sql.append(")");
sql.append(" VALUES (");
sql.append(sequence+".NEXTVAL, ");
SQLUtil.getValuesClause(insertFields, sql);
sql.append(")");
insertSQL = sql.toString();

if (debug) {
log.debug("Insert Entity SQL: " + insertSQL);
}
}

protected PreparedStatement prepareStatement(Connection c, String sql, EntityEnterpriseContext ctx) throws SQLException
{
return c.prepareCall(sql);
}

protected int executeInsert(PreparedStatement ps, EntityEnterpriseContext ctx) throws SQLException
{
int rows = ps.executeUpdate();
Connection c;
Statement s = null;
ResultSet rs = null;
try {
c = ps.getConnection();
s = c.createStatement();
rs = s.executeQuery(pkSQL);
if (!rs.next()) {
throw new EJBException("ResultSet was empty");
}
pkField.loadInstanceResults(rs, 1, ctx);
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
// throw EJBException to force a rollback as the row has been inserted
throw new EJBException("Error extracting generated key", e);
} finally {
JDBCUtil.safeClose(rs);
JDBCUtil.safeClose(s);
}
return rows;
}

}