Hi Martin,
Here’s some insight based on my experience converting our application from TOPSPEED to PSQL about seven years ago. Below are the basics to get it working effectively:
- Connection String: Use a DSN-less connection string. The
OWNER() property in your dictionary should be set to this connection string (CONNECT in this case).
- ODBC Driver: Ensure the PostgreSQL ODBC driver (
psqlodbc) is installed on any machine that needs to access the database. No DSN setup is required.
- File Drivers: Add the
ODBC driver to your solution under “File Drivers.”
Here’s an example:
Connection String
! CONNECTION STRING FOR POSTGRESQL SERVER
! This DSN-less connection string specifies the driver, server, port, database, and user credentials.
CONNECT STRING( 'Driver={{PostgreSQL ANSI};Server=<HOST>;Port=5432;Database=<DATABASE>;Uid=<username>;Pwd=<passwd>;UseDeclareFetch=1;Fetch=100;' )
Table Definition
! TABLE DEFINITION IN THE DICTIONARY
! This structure matches the PostgreSQL table exactly, including column types and primary keys.
TEST FILE, DRIVER('ODBC'), RECLAIM, OWNER(CONNECT), PRE(TST), BINDABLE, CREATE, THREAD
ID_ID_K KEY( TST:ID ), PRIMARY ! Primary key for the table
RECORD RECORD
ID LONG ! Integer column in PostgreSQL
LINE1 STRING(20) ! String column, max 20 characters
ARTEST STRING(1024) ! String column, max 1024 characters
END
END
TEST::USED LONG, THREAD ! Thread-safe counter for open file usage
Main Program
TESTPROJECT PROGRAM
INCLUDE('KEYCODES.CLW'), ONCE
INCLUDE('EQUATES.clw'), ONCE
INCLUDE('ERRORS.CLW'), ONCE
MAP
END
! CONNECTION STRING FOR POSTGRESQL SERVER
CONNECT STRING( 'Driver={{PostgreSQL ANSI};Server=<HOST>;Port=5432;Database=<DATABASE>;Uid=<username>;Pwd=<passwd>;UseDeclareFetch=1;Fetch=100;' )
! TABLE DEFINITION
TEST FILE, DRIVER('ODBC'), RECLAIM, OWNER(CONNECT), PRE(TST), BINDABLE, CREATE, THREAD
ID_ID_K KEY( TST:ID ), PRIMARY
RECORD RECORD
ID LONG
LINE1 STRING(20)
ARTEST STRING(1024)
END
END
TEST::USED LONG, THREAD ! Counter for file usage
! QUEUE TO STORE DATA
! This queue mirrors the structure of the TEST table and is used for processing or display.
TEST_Q QUEUE, PRE(TSQ)
ID LONG
LINE1 STRING(20)
ARTEST STRING(1024)
END
! USER INTERFACE
Window WINDOW('PostgreSQL Test'), AT(,,234,117), CENTER, GRAY, FONT('Segoe UI',9)
BUTTON('&OK'), AT(185,98,41,14), USE(?OkButton), DEFAULT
BUTTON('&Cancel'), AT(8,98,42,14), USE(?CancelButton), STD(STD:Close)
LIST, AT(8,5,219,89), USE(?TEST_Q), FLAT, FROM(TEST_Q), FORMAT('20L(2)|M~ID~82L(2)|M~LINE1~20L(2)|M~ARTEST~')
BUTTON('Get Data'), AT(97,98), USE(?GETDATA)
END
CODE
OPEN(WINDOW)
ACCEPT
CASE FIELD()
OF ?GETDATA
CASE EVENT()
OF EVENT:ACCEPTED
! I forgot to add the OPEN() to actually connect to the db.
OPEN( TEST )
! Define the SQL query
TEST{PROP:SQL} = 'SELECT id, line1, artest FROM test'
! Fetch the first record
NEXT(TEST)
IF ERROR() THEN MESSAGE(FILEERROR()) END
! Loop through the records and populate the queue
LOOP UNTIL ERROR()
TEST_Q :=: TEST.RECORD ! Copy the record to the queue
! Perform any additional data manipulation here if needed
ADD(TEST_Q) ! Add the record to the queue
NEXT(TEST) ! Fetch the next record
END
END
END
END
Explanation:
- Connection String:
- The
CONNECT string specifies PostgreSQL server details, such as host, port, database, and credentials. Adjust <HOST>, <DATABASE>, <username>, and <passwd> to your setup.
- Dictionary Settings:
- The
TEST table is defined in the dictionary to match the PostgreSQL table schema exactly.
- The
OWNER(CONNECT) links the table to the connection string.
- Queue:
TEST_Q is a queue mirroring the TEST table structure. It holds data fetched from PostgreSQL.
- Fetching Data:
- The
PROP:SQL property runs a SQL query on the TEST table.
- The
NEXT(TEST) function fetches rows, and the loop adds them to the queue.