Like statement does not support ESCAPE with parameter

[dondank]

the following sql fails to parse:

SELECT last_name FROM employees WHERE last_name LIKE ? ESCAPE ?

this one works:

SELECT last_name FROM employees WHERE last_name LIKE ? ESCAPE '\'

[wumpz]

Is the escape character realy providable by a parameter?

[dondank]

yes, this is code running on Oracle in production

[wumpz]

Ok. Do you want to make a pull request?

[dondank]

can it be fixed?

[wumpz]

Looking at production LikeExpression, JSqlParser does only accept a character literal at this position. So the fix should introduce a JdbcParameter as well or even an Expression.

[arcuri82]

hi,
ending up here, as having exactly the same issue, due to a query generated by the JDBC driver in H2.

From JdbcDatabaseMetadata.getColumns :

@Override
    public ResultSet getColumns(String catalogPattern, String schemaPattern,
            String tableNamePattern, String columnNamePattern)
            throws SQLException {
        try {
            if (isDebugEnabled()) {
                debugCode("getColumns(" + quote(catalogPattern)+", "
                        +quote(schemaPattern)+", "
                        +quote(tableNamePattern)+", "
                        +quote(columnNamePattern)+");");
            }
            checkClosed();
            boolean includeSynonyms = hasSynonyms();

            StringBuilder select = new StringBuilder(2432);
            if (includeSynonyms) {
                select.append("SELECT "
                        + "TABLE_CAT, "
                        + "TABLE_SCHEM, "
                        + "TABLE_NAME, "
                        + "COLUMN_NAME, "
                        + "DATA_TYPE, "
                        + "TYPE_NAME, "
                        + "COLUMN_SIZE, "
                        + "BUFFER_LENGTH, "
                        + "DECIMAL_DIGITS, "
                        + "NUM_PREC_RADIX, "
                        + "NULLABLE, "
                        + "REMARKS, "
                        + "COLUMN_DEF, "
                        + "SQL_DATA_TYPE, "
                        + "SQL_DATETIME_SUB, "
                        + "CHAR_OCTET_LENGTH, "
                        + "ORDINAL_POSITION, "
                        + "IS_NULLABLE, "
                        + "SCOPE_CATALOG, "
                        + "SCOPE_SCHEMA, "
                        + "SCOPE_TABLE, "
                        + "SOURCE_DATA_TYPE, "
                        + "IS_AUTOINCREMENT, "
                        + "IS_GENERATEDCOLUMN "
                        + "FROM ("
                        + "SELECT "
                        + "s.SYNONYM_CATALOG TABLE_CAT, "
                        + "s.SYNONYM_SCHEMA TABLE_SCHEM, "
                        + "s.SYNONYM_NAME TABLE_NAME, "
                        + "c.COLUMN_NAME, "
                        + "c.DATA_TYPE, "
                        + "c.TYPE_NAME, "
                        + "c.CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, "
                        + "c.CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, "
                        + "c.NUMERIC_SCALE DECIMAL_DIGITS, "
                        + "c.NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, "
                        + "c.NULLABLE, "
                        + "c.REMARKS, "
                        + "c.COLUMN_DEFAULT COLUMN_DEF, "
                        + "c.DATA_TYPE SQL_DATA_TYPE, "
                        + "ZERO() SQL_DATETIME_SUB, "
                        + "c.CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, "
                        + "c.ORDINAL_POSITION, "
                        + "c.IS_NULLABLE IS_NULLABLE, "
                        + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATALOG, "
                        + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_SCHEMA, "
                        + "CAST(c.SOURCE_DATA_TYPE AS VARCHAR) SCOPE_TABLE, "
                        + "c.SOURCE_DATA_TYPE, "
                        + "CASE WHEN c.SEQUENCE_NAME IS NULL THEN "
                        + "CAST(?1 AS VARCHAR) ELSE CAST(?2 AS VARCHAR) END IS_AUTOINCREMENT, "
                        + "CASE WHEN c.IS_COMPUTED THEN "
                        + "CAST(?2 AS VARCHAR) ELSE CAST(?1 AS VARCHAR) END IS_GENERATEDCOLUMN "
                        + "FROM INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.SYNONYMS s ON "
                        + "s.SYNONYM_FOR = c.TABLE_NAME "
                        + "AND s.SYNONYM_FOR_SCHEMA = c.TABLE_SCHEMA "
                        + "WHERE s.SYNONYM_CATALOG LIKE ?3 ESCAPE ?7 "
                        + "AND s.SYNONYM_SCHEMA LIKE ?4 ESCAPE ?7 "
                        + "AND s.SYNONYM_NAME LIKE ?5 ESCAPE ?7 "
                        + "AND c.COLUMN_NAME LIKE ?6 ESCAPE ?7 "
                        + "UNION ");
            }
            select.append("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "COLUMN_NAME, "
                    + "DATA_TYPE, "
                    + "TYPE_NAME, "
                    + "CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, "
                    + "CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, "
                    + "NUMERIC_SCALE DECIMAL_DIGITS, "
                    + "NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, "
                    + "NULLABLE, "
                    + "REMARKS, "
                    + "COLUMN_DEFAULT COLUMN_DEF, "
                    + "DATA_TYPE SQL_DATA_TYPE, "
                    + "ZERO() SQL_DATETIME_SUB, "
                    + "CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, "
                    + "ORDINAL_POSITION, "
                    + "IS_NULLABLE IS_NULLABLE, "
                    + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATALOG, "
                    + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_SCHEMA, "
                    + "CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_TABLE, "
                    + "SOURCE_DATA_TYPE, "
                    + "CASE WHEN SEQUENCE_NAME IS NULL THEN "
                    + "CAST(?1 AS VARCHAR) ELSE CAST(?2 AS VARCHAR) END IS_AUTOINCREMENT, "
                    + "CASE WHEN IS_COMPUTED THEN "
                    + "CAST(?2 AS VARCHAR) ELSE CAST(?1 AS VARCHAR) END IS_GENERATEDCOLUMN "
                    + "FROM INFORMATION_SCHEMA.COLUMNS "
                    + "WHERE TABLE_CATALOG LIKE ?3 ESCAPE ?7 "
                    + "AND TABLE_SCHEMA LIKE ?4 ESCAPE ?7 "
                    + "AND TABLE_NAME LIKE ?5 ESCAPE ?7 "
                    + "AND COLUMN_NAME LIKE ?6 ESCAPE ?7");
            if (includeSynonyms) {
                select.append(')');
            }
            PreparedStatement prep = conn.prepareAutoCloseStatement(
                    select.append(" ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION").toString());
            prep.setString(1, "NO");
            prep.setString(2, "YES");
            prep.setString(3, getCatalogPattern(catalogPattern));
            prep.setString(4, getSchemaPattern(schemaPattern));
            prep.setString(5, getPattern(tableNamePattern));
            prep.setString(6, getPattern(columnNamePattern));
            prep.setString(7, "\\");
            return prep.executeQuery();
        } catch (Exception e) {
            throw logAndConvert(e);
        }
    }

if making a pull request, which are the files that would be more like be involved? (I guess LikeExpression would be a starting point)

[manticore-projects]

Greetings!

Please consider the following minimum requirement for accepting the PR:

1. Amend the JavaCC Grammar Definition file
2. Amend the toString() method of the Java Object in order to reflect your new syntax or feature
3. Amend the Deparser Classes in order to reflect your new syntax or feature
4. Provide a Test Case for Parsing and Deparsing your new syntax or feature
5. Ensure that all existing Use Case Tests still succeed (Please try not to break anything.)
6. Ensure sufficient Test Coverage and Code Style compliance (The Maven/Gradle plugins would warn you.)

[manticore-projects]

A quicky during the lunch break, please give it a thorough testing as I did not spend much time on this.

[manticore-projects]

Works with JSQLParser 4.6. Snapshot.
Closed.