ParserImpl, used by DDLDatabase for codegen, fails on trailing newline

[jo...@quizlet.com]

Hi. I've been trying to use jOOQ's code generation feature, using a DDL file rather than a JDBC connection to obtain the table definitions, as described in the manual. I kept getting a parsing error on what seemed like valid SQL. I think I've narrowed it down to jOOQ's parser (org.jooq.impl.ParserImpl) failing when the DDL string contains trailing whitespace, such as a newline (as is common in a text file, even added automatically by many text editors and IDEs).

Here's some sample code to demonstrate this, which I modeled on the DDLDatabase.create0() implementation:

package com.example.experiment;

import org.jooq.Parser;
import org.jooq.Queries;
import org.jooq.impl.DSL;
import org.jooq.impl.ParserException;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ExperimentMain {

    public static void main(String[] args) {
        Properties info = new Properties();
        info.put("user", "sa");
        info.put("password", "");

        String ddl = "CREATE TABLE table_1\n( id NUMBER(7)\nNOT NULL PRIMARY KEY, name VARCHAR(20) );\nCREATE TABLE table_2\n( id NUMBER(10)\nNOT NULL PRIMARY KEY, other_name VARCHAR(100) );";

        System.out.println(String.format("DDL=###%s###", ddl));
        System.out.println("Parsing...");

        try {
            Connection connection = new org.h2.Driver().connect("jdbc:h2:mem:jooq-meta-extensions", info);
            Parser parser = DSL.using(connection).parser();
            Queries queries = parser.parse(ddl);
            System.out.println("Success. Queries: " + queries);
        } catch (ParserException e) {
            System.out.println("ParserException!");
            e.printStackTrace(System.out);
        } catch (SQLException e) {
            System.out.println("SQLException! code=" + e.getErrorCode() + ", sqlState=" + e.getSQLState() + ", message=" + e.getMessage());
            e.printStackTrace(System.out);
        }
    }
}

I compiled this with org.jooq:jooq:3.10.1 and org.jooq:jooq-meta-extensions:3.10.1. Note the String ddl, which I've peppered with newlines and other whitespace in the middle of the statements, but I'm ending with a semi-colon (no whitespace). The output indicates it gets parsed successfully:

DDL=###CREATE TABLE table_1

( id NUMBER(7)

NOT NULL PRIMARY KEY, name VARCHAR(20) );

CREATE TABLE table_2

( id NUMBER(10)

NOT NULL PRIMARY KEY, other_name VARCHAR(100) );###

Parsing...

Success. Queries: create table table_1(

  id number(7, 0) not null,

  name varchar(20) null,

  primary key (id)

);

create table table_2(

  id number(10, 0) not null,

  other_name varchar(100) null,

  primary key (id)

);

But when I add a newline (or a space) to that ddl String, like this:

        // note final newline
        String ddl = "CREATE TABLE table_1\n( id NUMBER(7)\nNOT NULL PRIMARY KEY, name VARCHAR(20) );\nCREATE TABLE table_2\n( id NUMBER(10)\nNOT NULL PRIMARY KEY, other_name VARCHAR(100) );\n";

...then it fails:

DDL=###CREATE TABLE table_1

( id NUMBER(7)

NOT NULL PRIMARY KEY, name VARCHAR(20) );

CREATE TABLE table_2

( id NUMBER(10)

NOT NULL PRIMARY KEY, other_name VARCHAR(100) );

###

Parsing...

ParserException!

org.jooq.impl.ParserException: C42000_NO_SUBCLASS: Unsupported query type: 

NOT NULL PRIMARY KEY, other_name VARCHAR(100) );

[*]

at org.jooq.impl.ParserImpl$ParserContext.exception(ParserImpl.java:5787)

at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:550)

at org.jooq.impl.ParserImpl.parse(ParserImpl.java:345)

at org.jooq.impl.ParserImpl.parse(ParserImpl.java:337)

at com.example.experiment.ExperimentMain.main(ExperimentMain.java:27)

The [*] in the exception output indicates that the parser was at the end of the string when it failed.

Correct me if I'm wrong, but this seems like a bug.

[Lukas Eder]

Hi Josh,

Thanks for your message. Indeed, there had been such an issue. It's fixed in 3.10.2:

https://github.com/jOOQ/jOOQ/issues/6710 (3.10.2)

https://github.com/jOOQ/jOOQ/issues/6707 (3.11.0)

Thanks

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jo...@quizlet.com]

Ah, I should have checked that. Thanks, Lukas!

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