The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Firebird - Technical Question

Just starting to migrate our application from an Access backend to Firebird. We have created a firebird database and can connect to it through our app however our SQL statements require objects to be quoted, i.e.

SELECT * FROM "Table"

We have another test database that does not require this, we can't understand why there is a difference.

Also, what character sets, font character set and dialect do you recommend for an international product.

Lastly ... (may as well fire off a few questions) we are developing in .NET and are using the Firebird ADO.NET Provider to connect to the database.

Has anyone connected to firebird using the native System.Data class?

Cheers
Xavier
Xavier
Friday, July 18, 2008
 
 
Hi,
you might want to check out
http://firebirdsql.org/dotnetfirebird/

It contains a lot of good stuff about connecting to Firebird via ADO.NET.
fh Send private email
Friday, July 18, 2008
 
 
the quotes thing is to do with which sql dialect you created the database with


http://mc-computing.com/Databases/Firebird/SQL_Dialect.html
bumperbox
Friday, July 18, 2008
 
 
The quotes might be required because you set up the table names as upper and lower case.  I had a problem with that for a while, but I changed all the database objects' names to all upper case and then I could refer to them with either upper or lower case.  I would like to be able to use upper/lower in naming tables, but I don't want to have to use the quotes and I don't want the database to be sensitive to my capitalization always being the same as originally set up.  So the closest I could get is to use all upper when naming and then I can use UpperLower case in queries, procedures, etc.
Jack Send private email
Friday, July 18, 2008
 
 
To expand on the above comments, if you define objects in Firebird with double quotes, then you must use them everywhere and you must also match the case.  There is an apparent exception to this rule however.  Even if objects are defined with double quotes but are in all uppercase, then it is possible to use the identifiers in SQL without the double quotes.  As stated before, the simple solution is to use all uppercase when you first define objects.
DaveW
Friday, July 18, 2008
 
 
I use SQL Manager 2005. I have read up about dialects and not that there is an option for dialect 3 databases for "Quoted Identifiers", I think I have this option turned on as the dialect is 3.

How can I turn this option off?
Xavier
Monday, July 21, 2008
 
 
Xavier,

The use of different dialects in Firebird is not an "option" as such.  It is possible to create new databases in Firebird using dialect 1 or 3 (not 2).  The dialect is a Firebird transition feature designed to allow Firebird to recognize and work with older legacy databases (dialect 1).  Dialect 2 is only intended to be used for transitioning a database in dialect 1 to dialect 3.  Also, dialect 1 is being depreciated.

Firebird's default dialect is 3 and that is what should be used for all new Firebird databases. Dialect 3 does support the ANSI SQL convention for optionally delimiting identifiers as you have found out.  Whether you choose to use delimiters is up to you.  However, don't "turn them off" by creating a database in dialect 1.  Use dialect 3 and just don't use delimiters.
DaveW
Tuesday, July 22, 2008
 
 
typedef "Quoted Identifiers" = delimiters
DaveW
Tuesday, July 22, 2008
 
 
Hello DaveW,

I wish it was as easy as "not using delimiters" in our SQL. We use SQL Manager 2005 to administer our FireBird database. If we type a simple SQL statement like

SELECT *
FROM Table1

We get an error;
Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Table unknown.

If we change the SQL to;

SELECT *
FROM "Table1"

We get results.

So as you can see, I cannot avoid using delimiters around object names. This was why I thought there was an option which can be selected.

I thought it may have been a setting wiht the GUI tool however when I query the database from within our C# application, we also need to use delimiters.

Your thoughts
Xavier
Saturday, July 26, 2008
 
 
Maybe my prior comments were not clear.  My suggestion was to not use delimiters when you create the database.  Once you create a database using delimiters then you will always have to use delimiters in your SQL.  They cannot be turned off as you would like.

The solution is to create the database without delimiters from the start.  Then you should never have to use quotes in your SQL.

I create all of my Firebird databases, tables etc. using all caps and no quotes.  Afterwards, I never use quotes in my SQL to reference tables or fields.

Hope that helps.
DaveW
Monday, July 28, 2008
 
 
Hello DaveW,

I created the database again, this time using all capitals for the object names. Now I don't need to use quotation marks.

Thanks for your help.

Cheers
Xavier
Friday, August 01, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz