#522 Skipping quoting escape in table dropping.

Snapshot
closed-fixed
Rob Manning
5
2015-01-08
2007-06-23
Saverio Miroddi
No

If I create a table with escaped quotes:

CREATE TABLE "foo""bar" (someid int);

And then drop it via GUI (_tableName_ ->refactoring->table->drop table), Squirrel uses this command:

DROP TABLE _schemaName_."foo"foo2[*]"

which is wrong because it hasn't quote escaping.

The RDBMS used is H2, but it's the same for all RDBMSs.

Bye!
Saverio

Discussion

  • Rob Manning
    Rob Manning
    2007-06-23

    • assigned_to: nobody --> manningr
    • milestone: --> snapshot
    • labels: --> Refactoring Plugin
    • status: open --> open-accepted
     
  • Rob Manning
    Rob Manning
    2007-06-23

    test case exposing bug in H2

     
  • Rob Manning
    Rob Manning
    2007-06-23

    Logged In: YES
    user_id=1287991
    Originator: NO

    File Added: H2IdentifierQuoteTest.java

     
  • Rob Manning
    Rob Manning
    2007-06-23

    • status: open-accepted --> closed-rejected
     
  • Rob Manning
    Rob Manning
    2007-06-23

    Logged In: YES
    user_id=1287991
    Originator: NO

    I believe it's a bug in the H2 driver. The driver reports the name of the table as "foo"bar" instead of "foo""bar".
    See the attached test case. I'll file a bug report against H2.

    Rob

     
  • Logged In: YES
    user_id=1638817
    Originator: YES

    for some (many?) implentations (H2, SmallSQL and PostgreSQL for sure), to insert a double quote inside the table name you use a double-double quote, so

    create table "foo""bar"

    correctly creates a table called:

    "foo"bar".

    other implementations:
    - Oracle doesn't accepts double quotes in table names (but i have to check);
    - MySQL doesn't accepts double quotes neither as wrapping ("foobar").

    Saverio

     
  • Rob Manning
    Rob Manning
    2007-06-24

    • status: closed-rejected --> open
     
  • Rob Manning
    Rob Manning
    2007-06-24

    Logged In: YES
    user_id=1287991
    Originator: NO

    I see. That seems right from an SQL perspective ('' means ' when embedded in a character string). I guess I saw this as a bug because you cannot just take the table names that you get back from DatabaseMetaData.getTables() as something that can be used in SQL statements. JDBC specification doesn't seem to address this, though I could be wrong. The question that has to be answered is - can I always assume that a identifier quote string, when found to be embedded in an identifier such as a table name or column name, should always be duplicated when constructing scripts? I'll see what Thomas Mueller has to say about it in the "bug" report that I added to the discussion group.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-07-02

    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2007-07-02

    Logged In: YES
    user_id=1287991
    Originator: NO

    Fixed in CVS. Thomas Mueller (H2) contributed code which was used to interpret the identifiers read from DatabaseMetaData.getTables() correctly.

    Rob