Sometimes I feel like the things I write on this blog are so obscure they'll never be read. But I suppose if I ran into this issue, chances are someone else will too.
I recently had a great experience with the support team from FrontBase regarding their ODBC driver. While their documentation included with the driver was lacking, they made up for it with the excellent support.
I had a problem selecting columns from a table using their FrontBase ODBC driver. I could select * from a table and get results, but if I tried to select a single column it failed. Also I could browse the tables and views, but couldn't browse the columns of the tables without receiving an error: Failed to Execute SQL Columns() statement
If you are using the FrontBase ODBC driver (I'm using version 1.20.0.92), and receive this error, there are a few solutions. First off, the error is caused by a permission issue. The driver needs to create a special view appropriately called VIEW_NEEDED_BY_FBODBC. If you aren't the db owner, or if you've set the ODBC connection in read-only transaction mode, then you'll run into this error. I'm told you can correct this by simply making the connection as the owner, and the driver will handle creating the view the first time you run a query.
You can also create the view yourself via FBManager or RazorSQL or whatever querying tool you have. For this you'll obviously need another way to connect to the database, and I used RazorSQL and the FrontBase JDBC driver.
Run this query to build the view:
CREATE VIEW VIEW_NEEDED_BY_FBODBC AS SELECT COLUMN_PK, TABLE_PK, "COLUMN_NAME", IS_NULLABLE, ORDINAL_POSITION, COLUMN_DEFAULT FROM DEFINITION_SCHEMA.COLUMNS;
GRANT SELECT ON VIEW_NEEDED_BY_FBODBC TO "_PUBLIC";
You'll just need to run this code once and the ODBC driver will work as expected, allowing you to browse the table columns and select individual columns.
