# PostgreSQL version 16 **URL:** \ **Category:** questions\ **Tags:** odbc, postgresql\ **Created:** [November 21, 2024, 7:45am UTC](https://clarionhub.com/t/postgresql-version-16/7678 "2024-11-21T07:45:51Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 21, 2024, 7:45am UTC](https://clarionhub.com/t/postgresql-version-16/7678/1 "2024-11-21T07:45:51Z")
Hi Guys, It is many years since I had anything to do with Postgres, and I never put it into a production app with a client. However, my recollection is that I did get it to work with Clarion, and I know several of you use it. I use Windows 10 Home 64 bit, and Clarion 11.13630 I have installed PostgreSQL 16 and PgAdmin 4. Unfortunately i have thrown away my old C7 or 8 or 9 in which I had used it, but some remnants of my old code remain (commented out). I am very happy with PGAdmin4. I can create tables, schemas, etc. and understand most of the sql statements to create, insert, alter, etc. My tables are set to use the ODBC driver and the ClaODB.dll is included in the project. I use variables for table owner and name. PgAdmin connects perfectly using my password. My old connection string looks like this ```clarion ConnectionString='DSN=PostgreSQL30;' & | 'DATABASE=postgres;' & | 'SERVER=localhost;' & | 'PORT=5432;' & | 'UID=postgres;' & | 'PWD=MyPwd;' ``` and I "post" like this ```auto mytable{PROP:SQL}=ConnectionString ``` Obviously the version changes, but I am battling to connect to the database in code. Am I missing something using prop:SQL like this ? I have tried so many variations, and used Mr Google extensively, but in the main I get the "Select Data Source" dialog window as an error, which does not help me much, except to point to the source of the problem. I have checked all the configuration files, but they do not show any reference to the path or version. Do I need the Postgres ODBC driver ? Is it a path issue ? (Yes, I tried !!) Can anyone suggest what my next step should be. Thanks so much, in advance. Regards Martin Hoddinott ---
**Author:** ![xxetfxx](https://clarionhub.com/user_avatar/clarionhub.com/xxetfxx/32/5215_2.png) [@xxetfxx](https://clarionhub.com/u/xxetfxx)\ **Post date:** [November 21, 2024, 9:48am UTC](https://clarionhub.com/t/postgresql-version-16/7678/2 "2024-11-21T09:48:52Z")
I have not tried postgres but I think yes you need the Postgres ODBC driver 32 bit. In my experience we use mariadb, So in order to connect it to the database we install mariadb odbc driver or mysql odbc driver. ---
**Author:** ![blue](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/3bc359/32.png) [@blue](https://clarionhub.com/u/blue)\ **Post date:** [November 21, 2024, 11:01am UTC](https://clarionhub.com/t/postgresql-version-16/7678/3 "2024-11-21T11:01:13Z")
As xxetfxx wrote, you need ODBC driver. At the beginning of the string `Driver=PostgreSQL ANSI;`, and at the end `UseDeclareFetch=1;KeepAliveTime=120;KeepAliveInterval=1;`. ---
**Author:** ![FominTools](https://clarionhub.com/user_avatar/clarionhub.com/fomintools/32/3634_2.png) [@FominTools](https://clarionhub.com/u/FominTools)\ **Post date:** [November 21, 2024, 1:04pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/4 "2024-11-21T13:04:59Z")
> [@Martin\_Hoddinott](#): > > and I “post” like this > mytable{PROP:SQL}=ConnectionString You want to use **table{Prop:OWNER} = ConnectionString** property assignent or better **OWNER(ConnectionString)** in the FILE declaration. and [PostgreSQL ODBC Driver (psqlODBC) Connection Strings - ConnectionStrings.com](https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/) **DSN** keyword in the connection string typically implies you have configured a **D** ata **S** ource **N** ame in Windows ODBC Data Sources. This is not necessary and not recommended, unless you want to add some extra headache for customer support. ---
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 21, 2024, 2:50pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/5 "2024-11-21T14:50:11Z")
Thank you, I will download drivers and keep trying. ---
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 21, 2024, 2:50pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/6 "2024-11-21T14:50:41Z")
Thank you, I will try this out. ---
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 21, 2024, 2:51pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/7 "2024-11-21T14:51:39Z")
Thank you, I appreciate your reply. ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [November 21, 2024, 2:54pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/8 "2024-11-21T14:54:57Z")
I would put !ConnectionString as the owner on the postgres table, make it a global variable in your program, and put the values in it more or less like you have, except rather than than DSN =, you should have Driver = \< whatever the 32 bit driver looks like in your 32bit ODBC administrator\>, possibly in braces {}. As Oleg says, DSN= implies that you have set up a data source name (either user or machine) using the postgres driver. If you are looking at just using this yourself, that is fine, but if you think people on other machines could use your program then the DSN route is a headache. And yes you need the Postgres (32 bit) ODBC driver. And don’t use PROP:SQL to open a file/make the connection. Use Open(file) or the relationmanager/filemanager open ---
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 21, 2024, 2:57pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/9 "2024-11-21T14:57:14Z")
Thanks JonW, lots of homework !! ---
**Author:** ![streppa](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/a3d4f5/32.png) [@streppa](https://clarionhub.com/u/streppa)\ **Post date:** [November 21, 2024, 9:09pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/10 "2024-11-21T21:09:52Z")
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: 1. **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). 2. **ODBC Driver** : Ensure the PostgreSQL ODBC driver (`psqlodbc`) is installed on any machine that needs to access the database. No DSN setup is required. 3. **File Drivers** : Add the `ODBC` driver to your solution under “File Drivers.” Here’s an example: * * * #### Connection String ```auto ! 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=;Port=5432;Database=;Uid=;Pwd=;UseDeclareFetch=1;Fetch=100;' ) ``` #### Table Definition ```auto ! 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 ```auto 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=;Port=5432;Database=;Uid=;Pwd=;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: 1. **Connection String** : - The `CONNECT` string specifies PostgreSQL server details, such as host, port, database, and credentials. Adjust ``, ``, ``, and `` to your setup. 1. **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. 1. **Queue** : - `TEST_Q` is a queue mirroring the `TEST` table structure. It holds data fetched from PostgreSQL. 1. **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. ---
**Author:** ![Martin\_Hoddinott](https://clarionhub.com/user_avatar/clarionhub.com/martin_hoddinott/32/5153_2.png) [@Martin\_Hoddinott](https://clarionhub.com/u/Martin_Hoddinott)\ **Post date:** [November 22, 2024, 6:04am UTC](https://clarionhub.com/t/postgresql-version-16/7678/11 "2024-11-22T06:04:11Z")
Thanks so much for that. It seems my sticking to the DSN route was not the best option. Really appreciate your help. ---
**Author:** ![streppa](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/a3d4f5/32.png) [@streppa](https://clarionhub.com/u/streppa)\ **Post date:** [November 22, 2024, 4:10pm UTC](https://clarionhub.com/t/postgresql-version-16/7678/12 "2024-11-22T16:10:19Z")
Glad I could help! I realized I forgot the `OPEN()` call in my example, so I’ve updated the project to include it. Regarding DSNs or DSN files—they are essentially text files that contain the same information as the `CONNECT` string but in a slightly different format. If you set the `CONNECT` variable to `STRING(20)` instead of directly using the DSN-less connection string, it will open the DSN dialog box. This allows you to choose a DSN you’ve already set up. I believe the selected configuration is then saved in the Windows registry.