Validation with SQL with Brackets

[rider87]

Hi all,

Is there a possibility to validate the SQL also with brackets?

I was trying this, but it´s not working:
` String expression = "Select year([x.test]) FROM x";

	Statement statement = CCJSqlParserUtil.parse(expression, parser -> parser.withSquareBracketQuotation(true));
	Select selectStatement = (Select) statement;

	// validate statement if it's valid for all given databases.
	Validation validation = new Validation(Arrays.asList(DatabaseType.MYSQL, DatabaseType.MARIADB,
	                DatabaseType.POSTGRESQL, DatabaseType.H2), selectStatement.toString());
	List<ValidationError> errors = validation.validate();`

Any suggestions?

[manticore-projects]

Greetings.

please double check your Validation first: In my limited understanding this query would parse for DatabaseType MSSQL only (because you use SquaredBracketQuotation for columns instead of Arrays).

I would expected, that all your set DatabaseTypes would not validate that statement.

[rider87]

Thanks for your quick reply.
I think you mean MYSQL (not MSSQL)?

I´m not 100% sure what you mean:
If I change it to:
Validation validation = new Validation(Arrays.asList(DatabaseType.MYSQL), selectStatement.toString());

I´ve got the same error message.
It would be fine also to check against only MYSQL

[manticore-projects]

A correct validation for your example would look like this

String expression = "Select year([x.test]) FROM x";

    Statement statement =
        CCJSqlParserUtil.parse(expression, parser -> parser.withSquareBracketQuotation(true));
    Select selectStatement = (Select) statement;

    // validate statement if it's valid for all given databases.
    Validation validation =
        new Validation(Arrays.asList(DatabaseType.SQLSERVER), selectStatement.toString());
    List<ValidationError> errors = validation.validate();
    assertEquals(0, errors.size());

Pay attention to the DatabaseType.SQLSERVER which is crucial for SquaredBracketQuotation.

Although this still fails. @wumpz would you please like to clarify how this is supposed to work correctly?

[manticore-projects]

This works:

  @Test
  public void test() throws JSQLParserException {
    String sqlStr = "Select year([x].[test]) FROM x";

    FeatureConfiguration features = new FeatureConfiguration();
    features.setValue(Feature.allowSquareBracketQuotation, true);
    
    Validation validation =
        new Validation(features, Arrays.asList(DatabaseType.SQLSERVER), sqlStr);
    
    List<ValidationError> errors = validation.validate();
    assertEquals(0, errors.size());
  }

[wumpz]

Thx for finding the solution.

[rider87]

oh, great. Thank you so much