Postgres connection string

1

Hello,

New question about Clarion and Postgres. :slight_smile:

When I install ODBC driver, and as connection string use “PostgreSQL ANSI” or “Unicode”. I can connect to database, but i cannot retrieve data and Clarion ODBC driver will not create Table. It will send correct SELECT statement, but it will fail to retrieve data. Off course table is created manually.

Connection string used: Driver={PostgreSQL ANSI};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

But when I add Postgre in System DSN section and fill all necessary data, I can retrieve data and Clarion ODBC will create table.

DSN connection string: Driver={PostgreSQL DSN}

Why is ODBC only working with DSN? Am I missing something?

2

Looks ok to me.
My Connection string is:
Driver={PostgreSQL UNICODE};Server=server;Port=5432;Database=phoenix;Uid=phoenix_prog;usedeclarefetch=1;

The useDeclareFetch increases performance on browses

3

Curious if there is more to consider regarding the app data?
My understanding of the setting is:

With this mode set, each fetch results in a client-server round trip and fetches one row from the cursor to the client.

Without this mode (the default is to not use UseDeclareFetch ) then the entire result set from the cursor is pushed to client memory before the first fetch is returned. This reduces network usage, but increases (sometimes greatly) the amount of client RAM required for a cursor result set.

4

Yes that’s the way it works. Note you can also include fetch=n in the string as well.
The setting can also mean that you get the first rows sent before the result set is completed being assembled on the server, which can mean lots of time saved, depending on table sizes.

basically because Clarion uses cursers a lot anyway, this setting just works really well.

5

Have you done any comparison testing? If the results for a browse are generally less than 1000 records (and small record sizes), I am wondering if one would see a benefit.

6

No, I haven’t done any testing, I just noticed a large difference in some browses and so always use it :slight_smile:

7

Just trying to get an idea of the character of the data and where to look for improvements before I start down the test road toward the cliff.

8

I always use Unicode, both with Clarion and python. No problems after well over 5 years.

9

I suggest using prop:sql to build the table like you want and not leave it to the driver to figure it out. I have zero table creation problems that way - for years.

The connection string looks right (tho I use Unicode). The DSN is probably matching a different driver dll.

10

Apologies for not answering before.

Problem was in Database= section, it was typed Datbase=.

Thank you all for help.