4

I am trying to extract data from a Firebird 2.5 SQL database for migration. The data has been built up over a long period through software that has the Firebird 2.5 database embeded - and the software company is not being helpful in allowing access to our data in an easily migratable form ...

By changing the security2.fdb file I can access the database through isql using the administrator username SYSDBA and can list the tables in the database, but any further access to individual tables always throws the message: "there is no table XXXX in this database"

Here is an example of the Windows command prompt:

SQL> show tables;
        ....
        ....
        Customer
        ....
SQL> show table customer;
There is no table CUSTOMER in this database

I suspect that access to individual tables is controlled, but can't work out how to regain access, if this is possible at all.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dzseti
  • 447
  • 1
  • 7
  • 18
  • a long shot only: is it being case sensitive? have you tried exactly matching the case of the shown table? otherwise it's likely to be security which I can't help with – Paul Maxwell Oct 04 '14 at 13:43
  • not case sensitive - wish that this were the answer to my problems! – Dzseti Oct 04 '14 at 13:45
  • mmm, yes, sorry but that's it from me. Good luck. – Paul Maxwell Oct 04 '14 at 13:46
  • I've seen this type of response when I was querying in the wrong database. Are you not in the correct database? I doubt its your problem because you see it in your show tables command. Worth asking, I suppose... – Jim Clouse Oct 04 '14 at 14:01
  • 2
    @Dzseti Are you *sure* it is not being case sensitive? Unquoted names are transformed to all uppercase, that's why the error message complains about a table `CUSTOMER`, not a table `customer`. Quoted names should preserve case, though. –  Oct 04 '14 at 14:33
  • I'm sure I'm in the right database - both commands above were entered while connected to it ... – Dzseti Oct 04 '14 at 14:33
  • Ooops ... yes you were right @hvd7 ... I presumed that everything was being transformed to uppercase, but for comparison and that case didn't matter ... simple quotes solved it!! This must be the dumbest question I've asked here :( – Dzseti Oct 04 '14 at 14:37
  • BTW: You might want to consider a slightly more user-friendly tool than isql, for example Flamerobin, IB Expert or Database Workbench (to name a few) – Mark Rotteveel Oct 04 '14 at 15:07
  • Thanks @Mark Rotteveel - I'm trying Flamerobin, but because I'm essetntially accessing the database in an "unconventional" way by changing security2.dbf, I get the error "no permission for direct access to the security database" and I can't work out how to get round this one - isql is however getting me the raw data (now!) in a manageable form – Dzseti Oct 04 '14 at 15:13
  • Modifying the security database is slightly unconventional, usually people simply copy the application database to a system with its own security2.fdb (or replace the security2.fdb with one that has a known password). Note that security2.fdb is not the application database. – Mark Rotteveel Oct 04 '14 at 15:17
  • That's wht I did - I simply changed the security2.fdb to one with a known password... – Dzseti Oct 04 '14 at 15:26
  • Then I don't understand what your problem is with flamerobin: You should connect to the application database, not to security2.fdb (connecting to security2.fdb directly is not allowed). – Mark Rotteveel Oct 04 '14 at 16:45
  • Aha - the problem was that the embeded version of Firebird was using databases with different extensions to the normal .fdb or .gdb - when setting up I only found the security2.fdb file and thought (wrongly) that this is how to indirectly open the database - but it seems that Flamerobin could open other extensions too :) :) - thanks for your help on this one too!!! – Dzseti Oct 04 '14 at 17:07

1 Answers1

6

By default object names in Firebird (and most other databases) are case insensitive, but with a catch: If you create or reference a table (or other object) name without quotes, it is actually treated as if it is uppercased. Only when an object name is enclosed in quotes is it case sensitive and referenced as-is. This behavior is specified in the SQL Standard (see SQL:2011 Foundation, 5.2 <token> and <separator> together with 5.4 Names and Identifiers).

This means that customer, Customer, CUSTOMER, CuStOmEr and "CUSTOMER" all reference the same table, namely: CUSTOMER.

When you create a table "Customer" (note the quotes), it is stored in the metadata as Customer but it can only be referenced as "Customer", using Customer will still reference CUSTOMER as unquoted object names are case insensitive.

You try to display the table using 

show table Customer;

Note the uppercased use of CUSTOMER in the error message:

There is no table CUSTOMER in this database

The output of show tables shows you have a table Customer (and not CUSTOMER), so you need to reference it as "Customer". You need to use:

show table "Customer";
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 1
    It might worth noting that this is nothing Firebird specific. This behaviour is defined (and required) by the SQL standard many other DBMS behave the same way. –  Oct 04 '14 at 14:42
  • @a_horse_with_no_name You are right, I added a reference to the relevant sections of the SQL standard. – Mark Rotteveel Oct 04 '14 at 14:56