[
https://issues.apache.org/jira/browse/CAMEL-23481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18084060#comment-18084060
]
Torsten Mielke commented on CAMEL-23481:
----------------------------------------
h2. Analysis of camel-sql component
Most unit tests in {{came-sql}} already use H2, however the tests around stored
procedures used Apache Derby so far. All of the stored procedure unit tests
were specifically designed to work with Apache Derby.
H2 does not support calling stored procedures with OUT parameters, hence
breaking many unit tests for stored procedures.
This is confirmed in https://github.com/h2database/h2database/issues/3798.
As there are many tests that are broken when using H2, this database no longer
seems a candidate to replace Apache Derby.
HSQL correctly supports stored procedure calls with OUT and INOUT parameters.
All these tests pass after respective adjustments.
However the unit tests for stored procedures also test SQL stored functions,
and this is where HSQL lacks support for the feature.
This is confirmed in https://sourceforge.net/p/hsqldb/feature-requests/280/ and
while that source is fairly old, the Camel unit tests still suffer from the
very same error message when testing SQL functions.
Now these SQL functions are only tested in two (pretty much identical) test
methods:
{{src/test/java/org/apache/camel/component/sql/stored/CallableStatementWrapperTest.java
method shouldExecuteStoredFunction()}}
{{src/test/java/org/apache/camel/component/sql/stored/SqlFunctionDataSourceTest.java
method shouldExecuteStoredProcedure()}}
So only a small subset of all tests still fail when being used with HSQL. But
these two tests can not be performed using HSQL.
This is where MariaDB is introduced. It supports SQL stored procedures and
functions and can also be run embedded in a JVM.
However MariaDB4j still spawns a new process for the database itself and does
not run entirely in memory only. It needs to create some files on the file
system ({{$java.io.tmpdir}}). The more complex startup routine has a direct
impact on the initialisation time going from a few milliseconds with Derby or
HSQL to 5-10 seconds for MariaDB4j.
Further MariaDB4J does not support calling Java methods from the SQL stored
procedure or function.
So an SQL function definition like
{code:sql}
CREATE FUNCTION SUBNUMBERS_FUNCTION(param1 INTEGER, param2 INTEGER)
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME
'CLASSPATH:org.apache.camel.component.sql.stored.TestStoredFunction.subnumbers';
{code}
… needs to to be replaced with the logic being defined in the function itself
{code:sql}
CREATE FUNCTION SUBNUMBERS_FUNCTION(param1 INT, param2 INT) RETURNS INT
DETERMINISTIC
RETURN param1 - param2$$
{code}
That alone is perhaps not a problem, as the main goal is to test an SQL
function call from Camel using the “sql-stored:” component.
Hence the test class {{SqlFunctionDataSourceTest}} now runs an embedded
MariaDB4j instance. It is the only class that uses MariaDB.
The method {{shouldExecuteStoredFunction()}} in class
{{CallableStatementWrapperTest}} is moved to {{SqlFunctionDataSourceTest}} as
it also requires the use of MariaDB4j.
The two disadvantages of using MariaDB are
- These SQL function tests will take multiple seconds to complete
- We cannot test the SQL function call against a Java defined method
> Remove Apache Derby dependencies
> --------------------------------
>
> Key: CAMEL-23481
> URL: https://issues.apache.org/jira/browse/CAMEL-23481
> Project: Camel
> Issue Type: Dependency upgrade
> Components: tests
> Affects Versions: 4.20.0
> Reporter: Torsten Mielke
> Assignee: Torsten Mielke
> Priority: Major
> Fix For: 4.x
>
>
> Multiple Camel components use Apache Derby in unit tests.
> Apache Derby is a retired project as of Oct 2025.
> https://db.apache.org/derby/
> As no further releases of Derby are planned, we should remove any
> dependencies on Derby from the Camel project.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
