Hi Charles
What if you leave a space after (and before) '$$' and add the package
(java.math) before the BigInteger class name?
create alias next_prime as $$ String nextPrime(String value) { return new
java.math.BigInteger(value).nextProbablePrime().toString(); } $$;
Or if you leave out the semi colon at the end?
create alias next_prime as $$ String nextPrime(String value) { return new
java.math.BigInteger(value).nextProbablePrime().toString(); } $$
Regards,
Christian
On Tuesday, December 4, 2018 at 2:04:32 PM UTC+1, Charles Berger wrote:
>
> My Spring Boot (v1.4.3.RELEASE) project uses H2 database as a persistence
> layer in its Java unit tests. The target database is Oracle 11g R2.
>
>
> I'm trying to create a user defined function in H2 to simulate an Oracle
> packaged procedure that I call in a DAO class.
>
>
> In the SQL script that builds the test database I try to create a user
> defined function as follows (the actual function I am trying to create is
> not the one below, but this example (taken from the H2 documentation) also
> does not work):
>
>
> create alias next_prime as $$String nextPrime(String value) { return new
> BigInteger(value).nextProbablePrime().toString(); }$$;
>
>
> When my unit test class runs the test fails with an error creating the
> application context:
>
>
> Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement
> "create alias next_prime as [*]$$String nextPrime(String value) { return new
> BigInteger(value).nextProbablePrime().toString()" [42000-193]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.message.DbException.get(DbException.java:179)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.message.DbException.get(DbException.java:155)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.message.DbException.getSyntaxError(DbException.java:191)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcConnection.translateGetEnd(JdbcConnection.java:1218)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcConnection.translateSQL(JdbcConnection.java:1395)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:169)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
> ~[h2-1.4.193.jar:1.4.193]
> at
> org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473)
> ~[spring-jdbc-4.3.5.RELEASE.jar:4.3.5.RELEASE]
>
>
> The test database is created and initialised as follows:
>
>
> @Bean(name = "dataSource")
> @Autowired
> public EmbeddedDatabase dataSource() {
> return new
> EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).addScript("db-c12-schema.sql")
> .addScript("db-c12-test-data.sql").build();
> }
>
>
> I've tried running the create alias SQL statement in the H2 console and it
> works fine but it doesn't in the Spring Boot unit test.
>
>
> The H2 version provided by default by Spring Boot 1.4.3 is 1.4.193 but I
> have also tried overriding that with version 1.4.197 but neither version
> works.
>
>
> Please can anyone help me get past this problem?
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
