#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    1
    Rep Power
    0

    Question Sql Dialects ???


    I am just starting out with FB1.5 and I ran into this.

    This works:

    INSERT INTO "user" ("last_name", "first_name") VALUES ('Doe', 'John')

    This doesn't:

    INSERT INTO user (last_name, first_name) VALUES ('Doe', 'John')

    This is the only database I have seen where you have to quote table and field names. I have looked at SQL-92 standard INSERT statements, Oracle INSERT, MySQL INSERT, and SQLite INSERT. They all do not use the quotes.

    Now I did set the dialect to 3 and I am wondering if that did it.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    According to the ANSI SQL standard, using double quotes preserves case for any database object.

    If you do CREATE TABLE "MyTable" it will be stored with mixed case in the database and the comparison will be case sensitiv. Maybe you created the table using double quotes, and that's why you now have to use them again. This is true for Oracle, Postgres, HSQLDB and I think SQL Server as well (not sure about the last one)

    What does SELECT RDB$RELATION_NAME from RDB$RELATION return? Are the tables listed in lowercase or uppercase?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    249
    Rep Power
    14
    I would advise against using those double quotes. If you never use them, you will never run into problems.

    If you're using a GUI tool, try finding a setting to avoid these errors.

    For example, Database Workbench ( www.upscene.com ) simply upper-cases everything for you, and will warn you if you're using non-standard characters.
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com

IMN logo majestic logo threadwatch logo seochat tools logo