Can't retrieve any records from MS Access mdb file in postgres with where clause #238
Unanswered
clustermass
asked this question in
Q&A
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hi Paul,
I am in no way DB expert, and just trying to follow the manuals to make things work for me.
I need to incorporate old app mdb database into Web server that can create customer's orders posted online. To do this In my testing env I was able to move mdb file to the linux machine where I was thinking to connect it to postgresql, and then I could read customer's data with my Node JS app as well as create new records for processing in the native app by end-users.
So far I was able to install postgres 14, unixodbc driver, I also installed
odbc-mdbtools
mdbtools
pgsql-ogr-fdw
postgresql-14-postgis-3
packages and I was able to follow the short example where I entered all commands in psql shell and I finally was able to see the test data when I did SELECT:
postgres=# SET client_min_messages = debug2;
SET
postgres=# select fid,cust_id,lineno,type,custname,estpartsamt,esthours,PrintedDate from Status;
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR FDW processed 4 rows from OGR
fid | cust_id | lineno | type | custname | estpartsamt | esthours | printeddate
-----+---------+--------+------+-------------------+-------------+----------+---------------------
0 | 0 | 354 | EST | QuickEst Customer | 4 | 1 | 2022-08-05 02:08:05
1 | 0 | 356 | EST | QuickEst Customer | 2648 | 0 | 2022-08-05 02:13:05
2 | 0 | 357 | EST | QuickEst Customer | 0 | 0 | 2022-08-08 21:36:02
3 | 0 | 358 | EST | QuickEst Customer | 120 | 0 | 2022-08-08 21:37:17
(4 rows)
However, when I was trying to test WHERE clause to see if I can get specific records, I was not able to do so, no matter what column I used:
postgres=# select fid,cust_id,lineno,type,custname,estpartsamt,esthours,PrintedDate from Status where cust_id >= 2;
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR SQL: ("cust_ID" >= 2)
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR FDW processed 0 rows from OGR
fid | cust_id | lineno | type | custname | estpartsamt | esthours | printeddate
-----+---------+--------+------+----------+-------------+----------+-------------
(0 rows)
postgres=# select fid,cust_id,lineno,type,custname,estpartsamt,esthours,PrintedDate from Status where fid = 1;
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR SQL: (fid = 1)
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR FDW processed 0 rows from OGR
fid | cust_id | lineno | type | custname | estpartsamt | esthours | printeddate
-----+---------+--------+------+----------+-------------+----------+-------------
(0 rows)
postgres=# select fid,cust_id,lineno,type,custname,estpartsamt,esthours,PrintedDate from Status where type like '%EST%';
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR SQL: (type ILIKE '%EST%')
DEBUG: ogrReadColumnData matched 1 FID, 1 GEOM, 65 FIELDS out of 67 PGSQL COLUMNS
DEBUG: OGR FDW processed 0 rows from OGR
fid | cust_id | lineno | type | custname | estpartsamt | esthours | printeddate
-----+---------+--------+------+----------+-------------+----------+-------------
(0 rows)
postgres=# SELECT ogr_fdw_version();
ogr_fdw_version
OGR_FDW="1.1" GDAL="3.4.1"
(1 row)
postgres=# select version();
version
PostgreSQL 14.4 (Ubuntu 14.4-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)
Am I doing something wrong? Should I do some extra steps to make this thing working?
I apologize for (maybe) stupid questions :)
Regards,
Max
Beta Was this translation helpful? Give feedback.
All reactions