[BUG] JSQLParser 5.0: Alter alter = (Alter) CCJSqlParserUtil.parse(sqlStr); Table table = alter.getTable(); for ALTER TABLE the_needed_db.the_table ADD [...] not getting the needed DB

[patpatpat123]

Hello team,

Just wanted to reach out with a bug. If not a bug, could you please consider this as feature request?

I have a very simple query in which I would like to extract both the DB name and the table name.
Looking at the doc, I found this construct: CCJSqlParserUtil.parse and alter.getTable
However, it seems getTable cannot take into account the DB name.

For instance, on this simple statement: String sqlStr = "ALTER TABLE the_needed_db.the_table ADD [...]

I have this piece of code:

 try {
//            Set<String> tableNames = TablesNamesFinder.findTables(sqlStr); why java.lang.UnsupportedOperationException: Finding tables from Alter is not supported ...
                    Alter alter = (Alter) CCJSqlParserUtil.parse(sqlStr);
                    Table table = alter.getTable();
            System.out.println("the table name for analyze statement " + table.getName());
        } catch (Exception e) {
            System.err.println("issue with analyze statement " + sqlStr);
            e.printStackTrace();
        }

Expected result: the table name for analyze statement the_needed_db.the_table

Actual result: the table name for analyze statement the_table

Could you please help on this bug?

In the event this has been thought over and is per design.
Would it be possible to consider this ticket as enhancement request, giving the user who cares about the DB name to have some API that can extract both the DB name and the table name please?

Business justification, table names can be the same across two distinct DBs.
Example:

ALTER TABLE the_cool_db.the_table ADD [...]
ALTER TABLE the_needed_db.the_table ADD [...]

Without DB name, the result execution of both would be the same, while it might be interesting to the user to distinguish the actual DB.

Thank you

[patpatpat123]

Thanks! May I ask in which version this will be available? We will be glad to bump to that version

[manticore-projects]

Greetings.

please use Snapshot artifacts or wait for the next Stable release (which may take a few months). You can understand JSQLParser as a rolling release software.

[patpatpat123]

Hello there,

Sorry to keep bumping this up.
I found the same issue for INSERT statement.
Would you like me to reopen a new issue?
Or would you like me to keep all similar issues in one single thread?

[manticore-projects]

Greetings, the following succeeds for me:

    @Test
    void testInsertTableIssue() throws JSQLParserException {
        String sqlStr = "INSERT INTO  the_cool_db.the_table\n"
                        + "    VALUES ( 'something' ) \n"
                        + ";";
        Set<String> tables = TablesNamesFinder.findTablesOrOtherSources(sqlStr);
        assertThat(tables).containsExactlyInAnyOrder("the_cool_db.the_table");

        tables = TablesNamesFinder.findTables(sqlStr);
        assertThat(tables).containsExactlyInAnyOrder("the_cool_db.the_table");
    }

What exactly is the challenge please?

[patpatpat123]

public class InsertIssue {

    public static void main(String[] args) {
        String sqlStr = "INSERT INTO  the_cool_db.the_table\n"
                + "    VALUES ( 'something' ) \n"
                + ";";
        try {
            Insert insert = (Insert) CCJSqlParserUtil.parse(sqlStr);
            Table table = insert.getTable();
            System.out.println("the table name for insert statement " + table.getName());
        } catch (Exception e) {
            System.err.println("issue with analyze statement " + sqlStr);
            e.printStackTrace();
        }
    }
}

Prints:

the table name for insert statement the_table

I was hoping it would print

the table name for insert statement the_cool_db.the_table

[manticore-projects]

Use table.getFullyQualifiedName(); for getting the fully qualified name including optional catalog name and schema name.