Extendig DADE and Firebird table name in query

rbuser

Hi, All.

I'm now testing my DADE plug-in against a Firebird database that has
tables with mixed case name and fieldnames, so I need some hints, please.

Some background.
Firebird ( as Interbase does ) converts all metadata names to uppercase, so
1) CREATE TABLE mytable ( ...
2) create table MYTABLE ( ...
3) create TABLE MyTable ( ...
are three equivalent statements to create a table MYTABLE.

You can SELECT from the above tables in whichever mixed case you want,
naming it mytable, MYTABLE, MyTable, mYtABLE: doesn't care.

Creating tables with (note double quotes around tablename)
4) CREATE TABLE "MyTable" ( ...

you can only
select ... from "MyTable"

Disabling DataDictionary in the connection definition form, DADE plug-in
correctly collect mixed-case names, but query generation converts it to
uppercase.

Same problem with field names: field "Facolta" of table
"Corsi_Integrati" must be precisely "Corsi_Integrati"."Facolta" in queries.

In GetTableNames I can identify names and correct tablenames, but
ppStripOffTableName() raise exception on table "Corsi_Integrati".
If I leave uncorrected tablenames, Firebird raise "Table unknown
CORSI_INTEGRATI" getting fieldsnames for table.

Any hints?
Overriding some methods is not a problem.

Thanks in advance.

Dott. Umberto Masotti
Università "G.d'Annunzio"
Chieti-Pescara

nardmoseley

I do not think we have had any users create this type of database before.

I researched this by looking at the source code. Try the following:

1. In your DADE plug-in override the TdaSession.IsSQLReservedWord method to
return True if the name passed to the function is one of your table names.

When IsSQLReservedWord returns True, then the name will be enclosed in
quotes.

2. Override TdaSession.GetTableNames to return the names in the appropriate
case


3. You migh also need to override some of the TdaDataSet methods to force
the field names to be properly cased.

Some databases have a setting in which you can configure them to be Case
Sensitive. Does Firebird have such a setting?




--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Nard Moseley (Digital Metaphors) wrote:

Surely it's not frequently used; but sintatictally every mixed-case name
(table, view or field and IIRC procedure names) must be double-quote
rounded for an exact match: Firebird 1.5.x, Firebird 2.0 and Vulcan
works in this manner, otherwise they complain with "NOT FOUND" error.
IIRC Yaffil and Interbase 7.* also do.
Normally every name is created without surrounding it and so defaulted
to uppercase or, better, "ignore case".


Ok. I'll go developing ASAP that code: i think i'll have no problem.
Now i'm stuck with compatibility problems of various version of jvUIB:
may be i'll give a cut soon.


IIRC, only DIALECT 1 databases are forced to all uppercase names, so
double-quote are rejected as errors. Obviously DIALECT 1 is only a
compatibility Interbase 5 mode, and has neither BIGINT, nor a lot of
other goodies.

Thanks a lot.
Regards.

Dott. Umberto Masotti
Università "G.d'Annunzio"
Chieti-Pescara