Having DbException Syntax error in SQL statement defining a H2 trigger

[Giulio Vito de Musso]

Hi,

I'm defining a H2 trigger with the following command

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON MY_TABLE AS $$
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {

            @Override
            public void remove() throws SQLException {
            }

            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }

            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }

            @Override
            public void close() throws SQLException {
            }
        };

    } $$;

I added the h2-1.3.173.jar archive to the CLASSPATH environment variable, but when I try to execute the command to actually create the trigger (I'm using DBeaver) I get this error.

SQL Error [90043] [90043]: Error creating or initializing trigger "MY_TRIGGER" object, class "..source..", cause: "org.h2.message.DbException: Syntax error in SQL statement ""
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } "" [42000-193]"; see root cause for details; SQL statement:


    [...]


    Syntax error in SQL statement "
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } " [42000-193]
          java.lang.NullPointerException:

Now, what could be the problem? I followed the syntax in the official guide regarding inline definition of trigger code.

Thank you

Giulio

[Noel Grandin]

what does the rest of the exception trace look like?

[Giulio Vito de Musso]

Hi Noel, thanks for the reply.

This is the full stacktrace of the error

SQL Error [90043] [90043]: Error creating or initializing trigger "MY_TRIGGER" object, class "..source..", cause: "org.h2.message.DbException: Syntax error in SQL statement ""
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } "" [42000-193]"; see root cause for details; SQL statement:

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON CURRENT_APPLICATION_PROPERTY AS $$
    org.h2.api.Trigger create() {

        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };

    } $$ [90043-193]

  Error creating or initializing trigger "MY_TRIGGER" object, class "..source..", cause: "org.h2.message.DbException: Syntax error in SQL statement ""
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } "" [42000-193]"; see root cause for details; SQL statement:

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON CURRENT_APPLICATION_PROPERTY AS $$
    org.h2.api.Trigger create() {

        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };

    } $$ [90043-193]

    Syntax error in SQL statement "
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } " [42000-193]

      Syntax error in SQL statement "
    org.h2.api.Trigger create() {
        return new org.h2.api.Trigger() {


            @Override
            public void remove() throws SQLException {
            }


            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }


            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }


            @Override
            public void close() throws SQLException {
            }
        };


    } " [42000-193]
       
          java.lang.NullPointerException:

[Thomas Mueller Graf]

Hi,

I'm not sure why, but it looks like inner classes are not supported right now. I get:

Caused by: java.lang.reflect.InvocationTargetException

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:498)

    at org.h2.schema.TriggerObject.loadFromSource(TriggerObject.java:101)

    ... 15 more

Caused by: java.lang.NoClassDefFoundError: org/h2/dynamic/trigger/MY_TRIGGER$Trig (wrong name: org/h2/dynamic/trigger/MY_TRIGGER)

    at java.lang.ClassLoader.defineClass1(Native Method)

    at java.lang.ClassLoader.defineClass(ClassLoader.java:763)

    at java.lang.ClassLoader.defineClass(ClassLoader.java:642)

    at org.h2.util.SourceCompiler$ClassFileManager$1.findClass(SourceCompiler.java:520)

    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON MY_TABLE AS $$

       org.h2.api.Trigger create() {

        return new Trig();

    }

    static class Trig implements  org.h2.api.Trigger {

            @Override

            public void remove() throws SQLException {

            }

            @Override

            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,

                int type) throws SQLException {

            }

            @Override

            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {

            }

            @Override

            public void close() throws SQLException {

            }

    }

     $$;

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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Giulio Vito de Musso]

Hi Thomas,

I tried your version but got another exception about syntax errors

SQL Error [90043] [90043]: Error creating or initializing trigger "MY_TRIGGER" object, class "..source..", cause:

"org.h2.message.DbException: Syntax error in SQL statement ""
       org.h2.api.Trigger create() {

        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

"" [42000-193]"; see root cause for details; SQL statement:

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON MY_TABLE AS $$
       org.h2.api.Trigger create() {
        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

$$ [90043-193]
  Error creating or initializing trigger "MY_TRIGGER" object, class "..source..", cause:

"org.h2.message.DbException: Syntax error in SQL statement ""
       org.h2.api.Trigger create() {

        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

"" [42000-193]"; see root cause for details; SQL statement:

CREATE TRIGGER MY_TRIGGER BEFORE INSERT ON MY_TABLE AS $$
       org.h2.api.Trigger create() {
        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

$$ [90043-193]
    Syntax error in SQL statement

"
       org.h2.api.Trigger create() {

        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

" [42000-193]

      Syntax error in SQL statement

"
       org.h2.api.Trigger create() {

        return new Trig();
 }
    static class Trig implements  org.h2.api.Trigger {
            @Override
            public void remove() throws SQLException {
            }
            @Override
            public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before,
                int type) throws SQLException {
            }
            @Override
            public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            }
            @Override
            public void close() throws SQLException {
            }
    }

" [42000-193]
       
          java.lang.NullPointerException:

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

[Giulio Vito de Musso]

HI all,

no one can help me solve this issue? It's so strange that no one tried defining and executing a trigger inline with H2...

Regards

[Stephan Hesse]

You are not alone :-)

I also tried and failed. I came to the conclusion, that defining a trigger in source code can (currently) only be used to return a class that is already available on the class path.

With this restriction it seems pretty useless for pure H2 servers where you do not want to add additional jars to the classpath.

However, I did not take the time to analyze and provide a patch - so I should not complain...

[iJava]

Hi all,

I am facing the same problem and asked question on SO here

https://stackoverflow.com/questions/61599071/how-to-create-trigger-in-h2-from-source-code

Could anyone say if the problem has been solved three years later?

Best regards

понедельник, 10 апреля 2017 г., 22:25:27 UTC+3 пользователь Stephan Hesse написал:

You are not alone :-)

I also tried and failed. I came to the conclusion, that defining a trigger in source code can (currently) only be used to return a class that is already available on the class path.

With this restriction it seems pretty useless for pure H2 servers where you do not want to add additional jars to the classpath.

However, I did not take the time to analyze and provide a patch - so I should not complain...

Am 10.04.2017 um 12:45 schrieb Giulio Vito de Musso:

HI all,

no one can help me solve this issue? It's so strange that no one tried defining and executing a trigger inline with H2...

Regards

--
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-da...@googlegroups.com.