[jrf-devel] Re: JRF/Firebird

[Mandip S. S. <ma...@su...>]

Hi Jonathan

I have been using JRF to connect to a Firebird database using the JDBC type 4 
driver.  There are several areas I would like to discuss and they are as 
follows: 

1)  FirebirdDatabasePolicy.
I have completed the FirebirdDatabasePolicy and found one issue:- Firebird 
uses generators as opposed to sequence objects which are fairly similar apart 
from the fact that generators are global to the database where they are 
declared.  This means that when you drop a table that has an associated 
generator this generator will not be deleted.  At the moment I am manually 
deleting generators before dropping tables with the following code:

delete from rdb$generators where rdb$generator_name='GEN_CapitalisedTableName'

I think it would be nice if we could automate this process.  Could this be 
coded into the DatabasePolicy or would we need to look further into JRF?  
Once we decide what needs to be done I would be happy to code the changes 
myself.

2)  Transactions.
When I began using the FirebirdDatabasePolicy I found that I could not even 
create a table and would get the following error:

ERROR com.is.jrf.DatabaseException :: com.is.jrf.DatabaseException is 
wrapping java.sql.SQLException: javax.resource.ResourceException: no local 
transaction active: can't commit: Exception occurred while ending transaction 
in JDBCHelper:
 javax.resource.ResourceException: no local transaction active: can't commit
java.sql.SQLException: javax.resource.ResourceException: no local transaction 
active: can't commit
        at org.firebirdsql.jdbc.FBConnection.commit(Unknown Source)
        at com.is.util.sql.JDBCHelper.commit(Unknown Source)
        at com.is.util.sql.JDBCHelper.close(Unknown Source)
        at com.is.jrf.AbstractDomain.endTransaction(Unknown Source)
        at com.is.jrf.AbstractDomain.createTable(Unknown Source)
        at com.is.jrf.AbstractDomain.createTable(Unknown Source)
        at test.DomainTEST.<init>(Unknown Source)

After inspecting the code I found that the AbstractDomain.endTransaction() 
method contains two calls:
        aJDBCHelper.endTransaction();
        aJDBCHelper.close();
The problem seems to arise from the fact that both methods try to commit and 
the first passes okay but as is obvious from the above error the second 
doesn't.  To overcome this problem I set the i_shouldCommitOnClose parameter 
to false with the following code:
	boolean commitOnClose = false;
	JDBCHelper jdbchelper = (JDBCHelper)i_personDomain.retJDBCHelper();
            jdbchelper.setShouldCommitOnClose(commitOnClose);
But then I found that when I tried to do any sort of find operation I got 
this error:

ERROR com.is.jrf.AbstractDomain :: RuntimeException in 
AbstractDomai#executeSQLQuery.
java.lang.IllegalStateException: Can't destroy managed connection  with 
active transaction
        at org.firebirdsql.jca.FBManagedConnection.destroy(Unknown Source)
        at 
org.firebirdsql.jca.FBStandAloneConnectionManager.connectionClosed(Unknown 
Source)
        at org.firebirdsql.jca.FBManagedConnection.notify(Unknown Source)
        at org.firebirdsql.jca.FBManagedConnection.close(Unknown Source)
        at org.firebirdsql.jdbc.FBConnection.close(Unknown Source)
        at com.is.util.sql.JDBCHelper.close(Unknown Source)
        at com.is.jrf.AbstractDomain.executeSQLQuery(Unknown Source)
        at com.is.jrf.AbstractDomain.findCustom(Unknown Source)
        at com.is.jrf.AbstractDomain.find(Unknown Source)
        at com.is.jrf.AbstractDomain.findWhere(Unknown Source)
        at com.is.jrf.AbstractDomain.finalFind(Unknown Source)
        at com.is.jrf.AbstractDomain.find(Unknown Source)
        at com.is.jrf.AbstractDomain.find(Unknown Source)
        at test.DomainTEST.test030UpdateSequencedKey(Unknown Source)

I then had to set i_shouldCommitOnClose back to true.  Although this solved 
the problem I am now constantly having to change the value of 
i_shouldCommitOnClose when I go from an operation that explicitly begins and 
ends a transaction (save, createtable...) to one that doesn't (find, 
findAll...) and vice-versa.  I would really appreciate you looking into this 
problem.

3)  Default Column Values.
Refering to the columnDefinitionString(DatabasePolicy) method in the 
AbstractColumnSpec class, Firebird requires that a primary key must be 
defined with a NOT NULL token whereas the above method does not place a such 
a token for a primary key.  Also Firebird doesn't seem to like the NULL 
token, so that when a column value is supposed to default to null you have to 
make sure the NULL token is NOT specified and then Firebird will assume the 
column value will default to null.  The method now looks like this:

public String columnDefinitionString(DatabasePolicy dbPolicy) {
    StringBuffer buffer = new StringBuffer();
    buffer.append(i_columnName);
    buffer.append(" ");
    buffer.append(getSQLColumnType(dbPolicy));
    if (this.isRequired()){
        buffer.append(" NOT NULL");
    }       
    if (this.isSequencedPrimaryKey()) {
        if (dbPolicy.autoIncrementIdentifier() == null) {
            buffer.append(" NOT NULL");
        }
        else {
            buffer.append(" ");
            buffer.append(dbPolicy.autoIncrementIdentifier());
        }
    }  
    return buffer.toString();
}

The method in the above state might not work for other databases because they 
might require NULL tokens, so would it be possible to code this change into 
the DatabasePolicy or would we need to look further into JRF.  Again I would 
be happy to code any changes.

4)  Booleans Stored As Numeric Values.
Having created a table with a boolean column (actually stored as data type 
NUMERIC(1)), I tried to do an insert and got the following error:

ERROR com.is.jrf.DatabaseException :: com.is.jrf.DatabaseException is 
wrapping java.sql.SQLException: can't interpret value as boolean: 0:  SELECT 
Person.PersonId, Person.Name, Person.Age, Person.Wealthy, Person.LastUpdated 
FROM Person WHERE Person.PersonId=1:
 can't interpret value as boolean: 0
java.sql.SQLException: can't interpret value as boolean: 0
        at org.firebirdsql.jdbc.FBResultSet.getBoolean(Unknown Source)
        at org.firebirdsql.jdbc.FBResultSet.getBoolean(Unknown Source)
        at com.is.util.sql.JDBCHelper.getBoolean(Unknown Source)
        at com.is.jrf.BooleanColumnSpec.getColumnValueFrom(Unknown Source)
        at 
com.is.jrf.AbstractColumnSpec.copyColumnValueToPersistentObject(Unknown 
Source)
        at com.is.jrf.AbstractDomain.convertToPersistentObject(Unknown Source)
        at com.is.jrf.AbstractDomain$1.handleRow(Unknown Source)
        at com.is.jrf.AbstractDomain.executeSQLQuery(Unknown Source)
        at com.is.jrf.AbstractDomain.findCustom(Unknown Source)
        at com.is.jrf.AbstractDomain.find(Unknown Source)
        at com.is.jrf.AbstractDomain.findWhere(Unknown Source)
        at com.is.jrf.AbstractDomain.finalFind(Unknown Source)
        at com.is.jrf.AbstractDomain.save(Unknown Source)
        at com.is.jrf.AbstractDomain.save(Unknown Source)
        at test.DomainTEST.test010InsertSequencedKey(Unknown Source)

This was caused by the JDBCHelper.getBoolean(String column) method making the 
following call:

    Boolean returnValue = new Boolean(i_resultSet.getBoolean(column));

I have changed this method so that rather than cast the numeric value as a 
boolean straightaway, the code inspects the numeric value and returns the 
corresponding boolean as follows:

public Boolean getBoolean(String column)
          throws SQLException {
    i_columnName = column;
    Boolean returnValue = null;
    int intValue = i_resultSet.getInt(column);
    if (intValue == 0) {
        returnValue = Boolean.FALSE;
     }
    else if (intValue == 1) {
        returnValue = Boolean.TRUE;
    }
    i_columnName = "";
    return returnValue;
}

I would be happy to upload this, however I would like your opinion on this 
matter first and whether the other getBoolean() methods need to be changed 
aswell.

Finally, I'm using Firebird v1.0 RC2, also I have uploaded the 
FirebirdDatabasePolicy, so you can replicate the above problems if required 

Regards
Mandip