Home Server
New Blog Posts: Merging Reports - Part 1 and Part 2

Extendig DADE and Firebird table name in query

edited October 2005 in Server
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

Comments

  • edited October 2005

    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



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited October 2005
    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
This discussion has been closed.