Solved

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

Posted on 2014-09-26
11
1,175 Views
Last Modified: 2014-10-08
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?
0
Comment
Question by:Rodbach
  • 6
  • 5
11 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 40347336
make the query "select count(*) from mon$transactions;" that is an internal table that must exist in any firebird database
0
 

Author Comment

by:Rodbach
ID: 40347661
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
CUSTOMERS
At line1 column 10.

I get the same message with a change of name when I try querying mon$transactions.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 40347813
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
0
 

Author Comment

by:Rodbach
ID: 40348190
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.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 40348423
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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Rodbach
ID: 40348694
Thanks for the suggestions though. I'll continue investigating. If I find out anything I'll post it here.
0
 

Accepted Solution

by:
Rodbach earned 0 total points
ID: 40348781
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.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 40348887
aaah

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
0
 

Author Comment

by:Rodbach
ID: 40349242
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
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 40349323
if you typed it as Customer in IBExpert you may not have noticed
0
 

Author Closing Comment

by:Rodbach
ID: 40367960
Because I found a workaround that did what I wanted.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now