Won't allow Mixed-case Table Names - converts them to CAPS

rfwoolf · Post by **rfwoolf** » Thu 27 Aug 2009 19:47

Hi guys.

I drop a TIBCTable onto my form, connect it to my connection, and choose a table from the drop-down list. If I choose for example TblEmployees I get this message when the dataset tries to open:
---------------------------
Error
---------------------------

Dynamic SQL Error
SQL error code = -204
Table unknown
TBLEMPLOYEES
At line 1, column 15.
---------------------------

I have tried putting quotes "" and square brackets []
..but nothing works

Plash · Post by **Plash** » Fri 28 Aug 2009 07:29

You should do one of the following:

1) Set the QuoteNames option to True.
2) Take the table name in quotes (this way does not work in the current IBDAC build, but we will fix this problem in the next build).

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 07:52

I see QuotedNames now. Thanks. In any case I only had two TZTables, so I just converted them to TIBCQueries.

But then I still have some serious peroblsm with the TIBCQueries that I do have ...
I had a TIBCQuery with table name "TblCredits". I used it as table by saying SELECT * FROM "TblCredits" which returns a read-writable result set.
I then fiddled with the GeneratorMode gmInsert and KeyFields and KeyGenerator.
I then INSERT into the field.
It works but when I try to post, it posts using SQL and the INSERT statement reads
INSERT INTO
TBLCREDITS

Everything else is fine in the INSERT statements, for example all the fields will have quotes around them and will be in mixed case, but the table name, will not

..and yes, QuotedNames was on!!!!

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 08:52

This is actually very frustrating.
I am now obligated to rename all my tables to capital letters !! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!!

Plash · Post by **Plash** » Fri 28 Aug 2009 08:52

This problem is also fixed in the next IBDAC build.

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 08:56

Good to know. Unfortunately this is very urgent. I can't really wait. When's the next build?

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 09:39

Is there perhaps a way I could intercept the SQL before it's executed?

I just need to replace the first line:

INSERT INTO "TBLFLIGHTSCHEDULE"
("ID", "FlightName", "Origin", "DepartTimeScheduled", "Destination", "ArriveTimeScheduled", "AircraftRegistration", "Status", "EconomyPriceIfEnabled", "BusinessPriceIfEnabled", "FirstPriceIfEnabled", "AirportTaxes", "BookingOpen", "CheckingInOpen", "BoardingOpen", "IsRouteOnly")
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

:"ID"(INTEGER,IN)=29
:"FlightName"(VARCHAR[7],IN)='another'
:"Origin"(CHAR[3],IN)='ERE'
:"DepartTimeScheduled"(TIMESTAMP,IN)=2009/08/28
:"Destination"(CHAR[3],IN)='AFW'
:"ArriveTimeScheduled"(TIMESTAMP,IN)=2009/08/28 12:00:00 PM
:"AircraftRegistration"(VARCHAR[6],IN)='JJJLGP'
:"Status"(VARCHAR[4],IN)='Open'
:"EconomyPriceIfEnabled"(FLOAT,IN)=45000
:"BusinessPriceIfEnabled"(FLOAT,IN)=0
:"FirstPriceIfEnabled"(FLOAT,IN)=0
:"AirportTaxes"(FLOAT,IN)=333
:"BookingOpen"(CHAR[1],IN)='T'
:"CheckingInOpen"(CHAR[1],IN)='F'
:"BoardingOpen"(CHAR[1],IN)='F'
:"IsRouteOnly"(CHAR[1],IN)='F'

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 09:54

Like when I set Debug to True it shows me the SQL it is about to execute.
However when I try to access the TIBCQuery.SQL property I don't get the SQL it jus showed me in the Debug window.
How do I fetch the SQL like it is in the Debug window?

Plash · Post by **Plash** » Fri 28 Aug 2009 11:51

There is no way to change the generated INSERT statement. I recommend to rename your tables to the upper case.

We are planning to release the next IBDAC build next week.

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 12:07

Someone in the firebird support group suggested I create a VIEW of the uppercase table name, as in:
CREATE VIEW TBLFLIGHTSCHEDULE AS SELECT * FROM "TblFlightSchedule"
In my tests this worked., but for some reason I still get the error if the debug SQL is like my post above:

INSERT INTO "TBLFLIGHTSCHEDULE"
("ID", "FlightName", "Origin", "DepartTimeScheduled", "Destination", "ArriveTimeScheduled", "AircraftRegistration", "Status", "EconomyPriceIfEnabled", "BusinessPriceIfEnabled", "FirstPriceIfEnabled", "AirportTaxes", "BookingOpen", "CheckingInOpen", "BoardingOpen", "IsRouteOnly")
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

:"ID"(INTEGER,IN)=29
:"FlightName"(VARCHAR[7],IN)='another'
:"Origin"(CHAR[3],IN)='ERE'
:"DepartTimeScheduled"(TIMESTAMP,IN)=2009/08/28
:"Destination"(CHAR[3],IN)='AFW'
:"ArriveTimeScheduled"(TIMESTAMP,IN)=2009/08/28 12:00:00 PM
:"AircraftRegistration"(VARCHAR[6],IN)='JJJLGP'
:"Status"(VARCHAR[4],IN)='Open'
:"EconomyPriceIfEnabled"(FLOAT,IN)=45000
:"BusinessPriceIfEnabled"(FLOAT,IN)=0
:"FirstPriceIfEnabled"(FLOAT,IN)=0
:"AirportTaxes"(FLOAT,IN)=333
:"BookingOpen"(CHAR[1],IN)='T'
:"CheckingInOpen"(CHAR[1],IN)='F'
:"BoardingOpen"(CHAR[1],IN)='F'
:"IsRouteOnly"(CHAR[1],IN)='F'

Do you have any idea what it is not working? The error is
First chance exception at $7C812AEB. Exception class EIBCError with message '
Dynamic SQL Error
SQL error code = -204
Table unknown
TBLFLIGHTSCHEDULE
At line 1, column 8'. Process Project.exe (1520)

rfwoolf · Post by **rfwoolf** » Fri 28 Aug 2009 12:37

Okay I solved that problem - I had created the view on the wrong database.
Now it works. It's a very clever workaround.