Clarion with Postgre SQL

[tonyl...@gmail.com]

Hi All,

Clarion should be able to connect to Postgre SQL throught ODBC I believe. However, I tried to create a simple app to test with it, but seems that Clarion is not able to create a table with binary BLOB field. An error occured:

ERROR: type "lo" does not exists;
Error while executing the query (42704)) creating Customer. Press OK to end this application

Any ideas to figure out this?

Thanks

Tony Lam

[Sean H]

Don't let clarion create the table!
It's not too bad, but in some cases it doesn't do what you would expect.
Blobs are a case in point.

explicitly create the table using an external SQL script or use prop:SQL

Other than that all waorks well. I use postgres quite a lot.


Sean H

[sand...@yahoo.com]

Hello Tony.
Clarion is able to connect with postgreSQL server.
Here are some advices:
1. Install Postgre's psqlodbc (I've installed psqlodbc_08_03_0400 for example)
2. Create appropriate connection string (in DCT or in init() procedure using variables created in DCT - glo:sql_owner for example) with name of driver and database, with localhost or IP address of server, username and password, port ... If you use variable, in DCT you should fill - Owner name: !glo:sql_owner
3. Let the server create tables (uncheck create table in DCT)
4. Let the server create unique id for every table in db (uncheck autoincrement for unique key in DCT for every table)
- set EMULATEAUTONUMKEY (true) in table properties in DCT, in options (Boolean)
- set Driver options: /AUTOINC='SELECT currval(''try_me_id_seq'')'
(well, server will create nextval, but from Clarion side, program use currval! Do not get confused)
- set IsIdentity (true) for id field in options (Boolean)
5. Some people create tables on server and import them in DCT.
On the contrary, if you create tables in DCT, use templates which create scripts you have to use on server side (I use Artigas templates for this)
If you set "options" in point 4. this will create unique key (see constraints) and also ''try_me_id_seq'' sequence for autoincrementing on server side :)
6. Change relational constraints from "Cascade" to "Cascade (on Server)" in DCT (same for Restrict)
7. Change Owner and priviledges on server side (assuming you have created user on server)
8. Blob in Clarion is Bytea on PostgreSQL server :)
9. Use string (8) for date & time. In addition but only in Clarion DCT (not on server!) use Group of fields: MyDate (DATE), MyTime (TIME) for example, and set Over attribute for this group = over string (8) variable, so these variables will use same memory space. Use these two variables for displaying date and time in your APP.

Hope it helps.
Which version of postgreSQL do you use?
Regards from Zoran.

[Douglas Johnson]

Great list, Zoran.

I recommend adding one more item.

10) Set NESTING=True for all tables. This can easily be done in the
FileManager.Init method with a simple template to generate the line:
SEND(SELF.File,'/NESTING=True')
This significantly increases performance by allowing the ODBC driver to generate SQL outer joins for multi-table browses (unfortunately not the default setting last time I checked).

[sand...@yahoo.com]

Thank You, Douglas.
Do you in Clarion dictionary set "Case Sensitive" property for Keys?
Zoran.

[Douglas Johnson]

Zoran,

Yes, for Postgresql I always set case sensitive keys, whereas for tps files, I rarely did. I find that users quickly adapt.

BTW - Where are you located?

[sand...@yahoo.com]

Thank you Douglas. BTW, I am from Serbia.

[Douglas Johnson]

Beograd?

[sand...@yahoo.com]

On Thursday, March 6, 2014 4:53:36 PM UTC+1, Douglas Johnson wrote:
> Beograd?
>
>
>
> > Thank you Douglas. BTW, I am from Serbia.

Novi Sad.

[Douglas Johnson]

On Monday, March 10, 2014 3:54:59 AM UTC-6, sand...@yahoo.com wrote:

> Novi Sad.

Too crazy during the EXIT Festival or worth the journey?

[sand...@yahoo.com]

Hello. Yes, Novi Sad is famous for Exit festival. Two years ago Gun 'n' Roses were very good. This year Festival will worth two journeys: organizers will combine Exit Festival in Novi Sad with Sea Dance Festival in Budva (Montenegro) at Adriatic Seaside. Bon voyage :)