sql - firebird isql: "there is no table XXXX in this database"

Question

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.

Answer 1

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";