March 12th, 2004, 02:03 PM
Sql Dialects ???
I am just starting out with FB1.5 and I ran into this.
INSERT INTO "user" ("last_name", "first_name") VALUES ('Doe', 'John')
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.
March 12th, 2004, 06:59 PM
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?
March 16th, 2004, 07:47 AM
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.
Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle