FireDAC connection to Firebird DB open but SQL qry doesn't recognise tables

I have a simple test application using FireDAC.  I have a TFDConnection connected to a Firebird database. I can open the connect to the database. I have a query linked to the FDCOnnection with a simple SQL statement
SELECT * FROM CUSTOMERS. When I open the query I get the error message Table unknown.
I have connected to the database using RazorSQL. I can see the tables and the data with no problem. So I know CUSTOMERS is a valid table. So why the error message?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
make the query "select count(*) from mon$transactions;" that is an internal table that must exist in any firebird database
RodbachAuthor Commented:
I tried SELECT COUNT(*) FROM mon$transactions and again it tells me it is an unknown table.
Yet if I use my original query IBExpert I don't have this problem as long as I surround the table name with double quotes.
I can query all the tables.
I also tried the double quotes in the SQL string of my query, same result. The full error message is:
[FireDAC][Phys][FB]Dynamic SQL error
SQL error code = -204
Table unknown
At line1 column 10.

I get the same message with a change of name when I try querying mon$transactions.
Nick UpsonPrincipal Operations EngineerCommented:
what are the connection parameters in the 2 circumstances, although the message is no what I'd expect it could be a permission issue. Ensure you connect as sysdba or the database owner if different and see if that changes things
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

RodbachAuthor Commented:
Using sysdba and masterkey both with IBExpert and from my Delphi app.
I can connect using a TFDConnection but when I open the TFDQuery I get the unknown table message.
Nick UpsonPrincipal Operations EngineerCommented:
sorry, the problem seems to be the delphi end, which is not my area, the database seems fine. I would check ALL the connection parameters but thats a long shot
RodbachAuthor Commented:
Thanks for the suggestions though. I'll continue investigating. If I find out anything I'll post it here.
RodbachAuthor Commented:
I found the answer. I noticed that when checking with IBExpert the table name was 'Customers' and not 'CUSTOMERS' and that IBExpert surrounded it with double quotes. Tried that and it worked.
Not that that is mentioned in any of the notes. Nor did I see that mentioned in Cary jensen's white paper.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick UpsonPrincipal Operations EngineerCommented:

firebird names (tables, fields, etc) are case-insensitive by default, whatever you put its treated as the same, if you surround with quotes then it is taken as a literal and must be specified the same way whenever referenced.

sounds like you created "Customers" when you wanted to create customers or CUSTOMERS no quotes so the same thing
RodbachAuthor Commented:
Could be. I'll see if that's the case. Can't imagine why I'd create a table name with quotes though. And it was the same with every table name in the database. I'll cetainly bear that in mind from now on. Thanks for the info
Nick UpsonPrincipal Operations EngineerCommented:
if you typed it as Customer in IBExpert you may not have noticed
RodbachAuthor Commented:
Because I found a workaround that did what I wanted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.