Page 1 of 1
Won't allow Mixed-case Table Names - converts them to CAPS
Posted: Thu 27 Aug 2009 19:47
by rfwoolf
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
Posted: Fri 28 Aug 2009 07:29
by Plash
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).
Posted: Fri 28 Aug 2009 07:52
by rfwoolf
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!!!!
Posted: Fri 28 Aug 2009 08:52
by rfwoolf
This is actually very frustrating.
I am now obligated to rename all my tables to capital letters !! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!! !!!
Posted: Fri 28 Aug 2009 08:52
by Plash
This problem is also fixed in the next IBDAC build.
Posted: Fri 28 Aug 2009 08:56
by rfwoolf
Good to know. Unfortunately this is very urgent. I can't really wait. When's the next build?
Posted: Fri 28 Aug 2009 09:39
by rfwoolf
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'
Posted: Fri 28 Aug 2009 09:54
by rfwoolf
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?
Posted: Fri 28 Aug 2009 11:51
by Plash
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.
Posted: Fri 28 Aug 2009 12:07
by rfwoolf
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)
Posted: Fri 28 Aug 2009 12:37
by rfwoolf
Okay I solved that problem - I had created the view on the wrong database.
Now it works. It's a very clever workaround.