I have been using ogr_fdw to connect PostgreSQL/PostGIS databases to FireBird databases via ODBC connection on Windows machines. The connections have been working great, and are immensely useful for transferring data between the databases - I am using the foreign data wrapper to keep data synced as we transition from FireBird to PostgreSQL.
FireBird is not spatially enabled. On one of the databases, I had created my own "GEOMETRY_COLUMNS" table in FireBird and used SQL to create views and tables with WKT and WKB geometries. This was partly an experiment, but at the same time shoe-horned some spatial functionality into the FireBird database that we needed.
On all of the other databases, I can use "import foreign schema" to connect to all tables, including non-spatial. However, on this one database I am limited to only the tables in the geometry_columns table. Based on this post Can't connect to foreign SQL Server table from PosgreSQL using OGR_FDW, it seems that I need to set variables to list_all_tables and use_geometry columns. However, the post was for MSSQL and I am using ODBC with FireBird. I suspect these settings tie into GDAL: https://gdal.org/drivers/vector/pg.html. I've been trying to create a foreign server that will ignore the GEOMETRY_COLUMNS table and show all tables, not just spatial ones, but have not been successful.
This is my typical create server SQL:
CREATE SERVER fb_db_svr FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource 'ODBC:FB_DB')
CREATE SCHEMA IF NOT EXISTS firebird;
I then import schema for individual tables as follows:
IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("TBL_NAME") FROM SERVER fb_db_svr INTO firebird;
I tried taking a guess at a different foreign server SQL statement based on this https://pgxn.org/dist/ogr_fdw/
CREATE SERVER fb_db_svr FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (datasource 'ODBC:ODBC:FB_DB', config_options 'LIST_ALL_TABLES=YES USE_GEOMETRY_COLUMNS=NO');
I also tried setting a few environment variables on the server:
Still no luck. Is this possible, or is my only option to delete the GEOMETRY_COLUMNS table in the FireBird database?
FIREBIRD_GEOMETRY_COLUMNS
or something similar to it ?