Declaring Functions as Source Code

[hans.j...@gmail.com]

Hi,

i try to declare a function as Source Code with a reference to the "org.h2.index.Index".
Here is the example sql statement:

DROP ALIAS "TEST_INDEX" IF EXISTS;

CREATE ALIAS "TEST_INDEX" AS $$
import org.h2.index.Index;
@CODE
String TEST_INDEX(Index idx,String test)
{
    return "test";
}
$$;


If the statement is executed this error occurs:


Syntax Fehler in SQL Befehl "org\h2\dynamic\TEST_INDEX.java:5: error: illegal start of type
    public static  import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:5: error: ';' expected
    public static  import org.h2.index.Index;
                         ^
org\h2\dynamic\TEST_INDEX.java:5: error: illegal start of type
    public static  import org.h2.index.Index;
                             ^
org\h2\dynamic\TEST_INDEX.java:5: error: ';' expected
    public static  import org.h2.index.Index;
                                ^
org\h2\dynamic\TEST_INDEX.java:5: error: <identifier> expected
    public static  import org.h2.index.Index;
                                            ^
5 errors
"
Syntax error in SQL statement "org\h2\dynamic\TEST_INDEX.java:5: error: illegal start of type
    public static  import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:5: error: ';' expected
    public static  import org.h2.index.Index;
                         ^
org\h2\dynamic\TEST_INDEX.java:5: error: illegal start of type
    public static  import org.h2.index.Index;
                             ^
org\h2\dynamic\TEST_INDEX.java:5: error: ';' expected
    public static  import org.h2.index.Index;
                                ^
org\h2\dynamic\TEST_INDEX.java:5: error: <identifier> expected
    public static  import org.h2.index.Index;
                                            ^
5 errors
"; SQL statement:
CREATE ALIAS "TEST_INDEX" AS $$
@CODE import org.h2.index.Index;

String TEST_INDEX(
Index idx,
String test
)
throws SQLException
{

return "test";
}
$$ [42000-170] 42000/42000 (Hilfe)



The problem is the reference to the "org.h2.index.Index",how can I resolve this problem?

Thanks in advance,
Hans

[Noel Grandin]

Do this:

CREATE ALIAS "TEST_INDEX" AS $$

String TEST_INDEX(org.h2.index.Index idx, String test)
{
return "test";
}
$$;

[Thomas Mueller]

Hi,

See also the documentation at http://h2database.com/html/features.html#user_defined_functions

Your example statement works for me, and it doesn't match the statement in your exception message...

Regards,

Thomas

--
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 h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Hans Hans]

Hi,

sorry, I have tested a little bit and have the wrong stack trace posted.
here is the right for my first example:

DROP ALIAS "TEST_INDEX" IF EXISTS;

Änderungen: 0
(1 ms)

CREATE ALIAS "TEST_INDEX" AS $$
import org.h2.index.Index;
@CODE
String TEST_INDEX(Index idx,String test)
{
    return "test";
}
$$;

Syntax Fehler in SQL Befehl "org\h2\dynamic\TEST_INDEX.java:3: error: package org.h2.index does not exist
import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:6: error: cannot find symbol
String TEST_INDEX(Index idx,String test)
                  ^
  symbol:   class Index
  location: class TEST_INDEX
2 errors
"
Syntax error in SQL statement "org\h2\dynamic\TEST_INDEX.java:3: error: package org.h2.index does not exist
import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:6: error: cannot find symbol
String TEST_INDEX(Index idx,String test)
                  ^
  symbol:   class Index
  location: class TEST_INDEX
2 errors
"; SQL statement:

CREATE ALIAS "TEST_INDEX" AS $$
import org.h2.index.Index;
@CODE
String TEST_INDEX(Index idx,String test)
{
    return "test";
}

$$ [42000-174] 42000/42000 (Hilfe)

Here is the exception from the example of noel:

CREATE ALIAS "TEST_INDEX" AS $$
String TEST_INDEX(org.h2.index.Index idx, String test)
{
     return "test";
}
$$;

Syntax Fehler in SQL Befehl "org\h2\dynamic\TEST_INDEX.java:7: error: package org.h2.index does not exist

String TEST_INDEX(org.h2.index.Index idx, String test)

                              ^
1 error
"
Syntax error in SQL statement "org\h2\dynamic\TEST_INDEX.java:7: error: package org.h2.index does not exist

String TEST_INDEX(org.h2.index.Index idx, String test)

                              ^
1 error
"; SQL statement:

CREATE ALIAS "TEST_INDEX" AS $$
String TEST_INDEX(org.h2.index.Index idx, String test)
{
     return "test";
}

$$ [42000-174] 42000/42000 (Hilfe)

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

That means the H2 classes are not in the classpath. I can't reproduce the problem. Could you post the complete stack trace please? Which JVM do you use, and how do you start the program (command line options / system properties / classpath)?

Regards,

Thomas

[Hans Hans]

Hi,

i use the Java version 1.8.0 - build 1.8.0-b132 (64-Bit) on a Windows 7 - 64 Bit. I have tested it also with Java 7.
The program is started with a bat file like this:
java -cp C:\Users\fhaerteis\Desktop\DB_Test\h2\bin\h2-1.3.174.jar; org.h2.tools.Server -tcpAllowOthers

Do you need more Information?

Here is the complete stack trace:

org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "org\h2\dynamic\TEST_INDEX.java:3: error: package org.h2.index does not exist

import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:6: error: cannot find symbol
String TEST_INDEX(Index idx,String test)
                  ^
  symbol:   class Index
  location: class TEST_INDEX
2 errors
"
Syntax error in SQL statement "org\h2\dynamic\TEST_INDEX.java:3: error: package org.h2.index does not exist
import org.h2.index.Index;
                   ^
org\h2\dynamic\TEST_INDEX.java:6: error: cannot find symbol
String TEST_INDEX(Index idx,String test)
                  ^
  symbol:   class Index
  location: class TEST_INDEX
2 errors
"; SQL statement:
CREATE ALIAS "TEST_INDEX" AS $$
import org.h2.index.Index;
@CODE
String TEST_INDEX(Index idx,String test)
{
    return "test";
}
$$ [42000-174]

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.message.DbException.get(DbException.java:149)
    at org.h2.util.SourceCompiler.throwSyntaxError(SourceCompiler.java:360)
    at org.h2.util.SourceCompiler.exec(SourceCompiler.java:316)
    at org.h2.util.SourceCompiler.javacProcess(SourceCompiler.java:293)
    at org.h2.util.SourceCompiler.javacCompile(SourceCompiler.java:216)
    at org.h2.util.SourceCompiler$1.findClass(SourceCompiler.java:149)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at org.h2.util.SourceCompiler.getClass(SourceCompiler.java:161)
    at org.h2.util.SourceCompiler.getMethod(SourceCompiler.java:175)
    at org.h2.engine.FunctionAlias.loadFromSource(FunctionAlias.java:123)
    at org.h2.engine.FunctionAlias.load(FunctionAlias.java:111)
    at org.h2.engine.FunctionAlias.init(FunctionAlias.java:98)
    at org.h2.engine.FunctionAlias.newInstanceFromSource(FunctionAlias.java:90)
    at org.h2.command.ddl.CreateFunctionAlias.update(CreateFunctionAlias.java:50)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:253)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:340)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:156)
    at java.lang.Thread.run(Unknown Source)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:589)
    at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:186)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:181)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1320)
    at org.h2.server.web.WebApp.query(WebApp.java:1009)
    at org.h2.server.web.WebApp$1.next(WebApp.java:971)
    at org.h2.server.web.WebApp$1.next(WebApp.java:958)
    at org.h2.server.web.WebThread.process(WebThread.java:167)
    at org.h2.server.web.WebThread.run(WebThread.java:94)
    at java.lang.Thread.run(Unknown Source)
   
   
Thanks in advance,
Hans

[Thomas Mueller]

Hi,

I can't reproduce the problem.

According to the stack trace you posted, the compiler within H2 is starting "javac" as an external process. This is the third option; first it tries to use "javax.tools.ToolProvider.getSystemJavaCompiler". If that's not available, it tries to use Class.forName("com.sun.tools.javac.Main"). If that is also not available, it tries to run "javac" as an external process.

In your case both javax.tools.ToolProvider.getSystemJavaCompiler and com.sun.tools.javac.Main don't seem to be available. This might be the reason: http://stackoverflow.com/questions/15513330/toolprovider-getsystemjavacompiler-returns-null-usable-with-only-jre-install 

I'm not sure what the best solution is in this case. It might be possible to support this case by explicitly adding the h2.jar file to the classpath when starting "javac" as an external process, but I'm not sure how to do that. Or maybe not support this option at all, and throwing a better exception message that explains what the problem is. Ideas and patches are welcome!

Regards,

Thomas