"Hans Müller" <[email protected]> writes:
> Hello,
>
> i have a problem with a database which is stored a jar.
>
> The SQL-Queries work well, when the database in not inside a jar.
> But if i store the database in a jar, then some SQL-Queries do not work and
> throw an exception.
>
> I have set the properties for the derby.storage.tempDirectory, befor i am
> loading the EmbeddedDriver of Derby.
>
> The database is large. It contains some lists with more than 1500, 3400,
> 3900, 5500 and 8000 lines.
>
> This are the Exceptions:
>
> java.sql.SQLException: Ein Feature ist nicht implementiert.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
> at Datenbankanbindung.mAbfrageElementSonstiges(Datenbankanbindung.java:8758)
> ...
>
> and
>
> Caused by: java.sql.SQLException: Ein Feature ist nicht implementiert.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 14 more
> Caused by: ERROR XSAI3: Ein Feature ist nicht implementiert.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.log.ReadOnly.checkVersion(Unknown Source)
> at org.apache.derby.impl.store.raw.RawStore.checkVersion(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.checkVersion(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown
> Source)
> at org.apache.derby.iapi.store.access.DiskHashtable.<init>(Unknown Source)
> at
> org.apache.derby.iapi.store.access.BackingStoreHashtable.spillToDisk(Unknown
> Source)
> at
> org.apache.derby.iapi.store.access.BackingStoreHashtable.add_row_to_hash_table(Unknown
> Source)
> at org.apache.derby.iapi.store.access.BackingStoreHashtable.putRow(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchSet(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.UnionResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
> at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 8 more
>
>
> This is an SQL-Querie, which works well with a database which is not stored
> in a jar and which throw an exception, if you store the database in a jar:
>
> SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment
> FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr =
> SpracheTab.IdentifikationsNr INNER JOIN FassadenartenGruppeTab ON
> ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr
> WHERE SpracheTab.Sprache = 'de'
> AND ArtikelTab.Baugruppe = 'asdf'
> AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL)
> AND ArtikelTab.Lage LIKE '%Vertikal%'
> AND FassadenartenGruppeTab.Fassadenart = 'xyz'
> AND ArtikelTab.Sortiment = 's'
> ORDER BY ArtikelNr
>
> If you put the "ORDER BY"-line away or if you use "ORDER BY
> IdentifikationsNr" then it works.
> But it is not only a problem with the "ORDER BY". I have some other
> SQL-Queries which do not work too and who have no "ORDER BY".
>
> The following SQL-Querie works well:
>
> SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment
> FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr =
> SpracheTab.IdentifikationsNr INNER JOIN FassadenartenGruppeTab ON
> ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr
> WHERE SpracheTab.Sprache = 'de'
> AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL)
> AND FassadenartenGruppeTab.Fassadenart = 'xyz'
> ORDER BY ArtikelNr
>
> It seems, that this problem is similar to the following:
> http://old.nabble.com/ResultSet.next%28%29-throws-ERROR-XSAI3%3A-Feature-not-implemented.-td20957328.html
> and
> https://issues.apache.org/jira/browse/DERBY-2354
>
>
> Could it be, that Derby can only handle little databases, if you put these in
> a jar (read-only mode)?
> Is this a Bug?
> How can i fix the problem?
Hi Hans,
It looks like the root cause of this problem is the same as DERBY-2354,
that Derby isn't prepared for handling hash tables that spill to disk in
read-only databases.
You may be able to work around this problem by using optimizer overrides
to force Derby's optimizer to pick nested loop joins instead of hash
joins. That should eliminate the use of hash scans and prevent this bug
from getting triggered, I think. In the failing query above, you would
need to do something like this:
SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment
FROM ArtikelTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
INNER JOIN SpracheTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
ON ArtikelTab.ArtikelNr = SpracheTab.IdentifikationsNr
INNER JOIN FassadenartenGruppeTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
ON ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr
WHERE SpracheTab.Sprache = 'de'
AND ArtikelTab.Baugruppe = 'asdf'
AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL)
AND ArtikelTab.Lage LIKE '%Vertikal%'
AND FassadenartenGruppeTab.Fassadenart = 'xyz'
AND ArtikelTab.Sortiment = 's'
ORDER BY ArtikelNr
More on optimizer overrides can be found here:
http://db.apache.org/derby/docs/10.7/tuning/ctunoptimzoverride.html
Hope this helps,
--
Knut Anders
