[jrf-devel] Re: JRF/Firebird
Brought to you by:
joncrlsn
|
From: Mandip S. S. <ma...@su...> - 2002-02-06 16:35:15
|
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
|