Failed to parse SQL Server ddl

[Vipin-Sharma]

Steps to Reproduce the Problem:

String ddl = """
CREATE TABLE [Person].[Person]
(
      [BusinessEntityID] [int] NOT NULL,
      [PersonType] [nchar](2) NOT NULL,
      [NameStyle] [dbo].[NameStyle] NOT NULL,
      [Title] [nvarchar](8) NULL,
      [FirstName] [dbo].[Name] NOT NULL,
      [MiddleName] [dbo].[Name] NULL,
      [LastName] [dbo].[Name] NOT NULL,
      [Suffix] [nvarchar](10) NULL,
      [EmailPromotion] [int] NOT NULL,
      [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
      [Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
);
""";
CCJSqlParserUtil.parse(ddl);

CCJSqlParserUtil.parse(ddl) throws error:

Encountered unexpected token: "CREATE" "CREATE"
    at line 1, column 1.

Was expecting one of:

    "("
    "ALTER"
    "CALL"
    "COMMENT"
    "COMMIT"
    "DECLARE"
    "DELETE"
    "DESCRIBE"
    "DROP"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "GRANT"
    "INSERT"
    "MERGE"
    "PURGE"
    "RENAME"
    "RESET"
    "ROLLBACK"
    "SAVEPOINT"
    "SET"
    "SHOW"
    "TRUNCATE"
    "UPDATE"
    "UPSERT"
    "USE"
    "VALUES"
    "WITH"
    <K_SELECT>

Java version: 17 JSQLParser version: 4.4

[Vipin-Sharma]

Tried below as well CCJSqlParserUtil.parse(ddl, parser -> parser.withSquareBracketQuotation(true));

Now Exception is:

Encountered unexpected token: "[Person]" <S_QUOTED_IDENTIFIER>
    at line 1, column 372.

Was expecting one of:

    ")"
    ","
    "CHAR"
    <S_CHAR_LITERAL>
    <S_IDENTIFIER>
    <S_LONG>

[Vipin-Sharma]

This code works fine, here I have removed columns having custom types from DDL. This DDL is part of famous adventureworks database.

String a = """
CREATE TABLE [Person].[Person]
(
      [BusinessEntityID] [int] NOT NULL,
      [PersonType] [nchar](2) NOT NULL,
      [Title] [nvarchar](8) NULL,
      [Suffix] [nvarchar](10) NULL,
      [EmailPromotion] [int] NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
);
        """;

CCJSqlParserUtil.parse(a, parser -> parser.withSquareBracketQuotation(true));

[Vipin-Sharma]

Tried with previous version of JSQLParser 4.3 and similar behavior.

[manticore-projects]

Greetings,

the challenge is about the XML declaration and without it, everything works fine.

If you like to send a PR or sponsor an implementation, we surely will look into it. Otherwise, I am not sure if there will be enough interest in those rather exotic syntax.

[Vipin-Sharma]

Good to know this is a limitation in JSQLParser, not an error in my code. PR is good idea, I am afraid I can not start coding immediately as this is completely new project for me, It would be helpful if you can share some link to read about design/architecture of JSQLParser. That will make it easy for me to contribute.