Read PostgreSQL tables from an Openedge program

Question

I need to write an Openedge 11.6.4 ABL procedure that automatically:

• Connects to a PostgreSQL database.
• Read some info and write it in my OpenEdge DB.
• Disconnect from PostgreSQL.

My problem is in the connection part.

Is that possible?

Answer 1

You can do it with ADODB with a ODBC driver

Example:

DEFINE VARIABLE objAdoCon   AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE objAdoCmd   AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE objAdoRS    AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE cAdoSql AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cField AS CHARACTER   NO-UNDO.

/* ODBC Connection */
CREATE "ADODB.Connection" objAdoCon.

objAdoCon:ConnectionString = "Provider=PostgreSQL OLE DB;" +
                             "Data Source=myServerAddress;location=myDataBase;User ID=myUsername;password=myPassword".

objAdoCon:OPEN(,,,).  /* makes the connection to the datasource */

/* RecordSet */
CREATE "ADODB.RecordSet"  objAdoRS.
objAdoRS:CursorLocation = 3.  // Client
objAdoRS:CursorType     = 0.  // ForwardOnly
objAdoRS:locktype       = 3.  // LockOptimistic

cAdoSql = "SELECT * FROM postgreTable".

objAdoRS:OPEN (cAdoSql,objAdoCon,,,).

DO WHILE NOT objAdoRS:eof:

 cField = objAdoRS:FIELDS("FieldFromPostgreTable"):VALUE. 
 
 CREATE Customer.
 ASSIGN
    Customer.NAME = cField.
 
 objAdoRS:MoveNext.
 
END.
   

FINALLY:  
    

   objAdoRS:CLOSE NO-ERROR.
   objAdoCon:CLOSE NO-ERROR.
   RELEASE OBJECT objAdoCon NO-ERROR.
   RELEASE OBJECT objAdoRS  NO-ERROR.
   ASSIGN objAdoCon = ?.
   ASSIGN objAdoRS  = ?.     
          
END FINALLY. 

Some other code samples:

https://community.progress.com/s/question/0D74Q000007tLORSA2/detail https://www.w3schools.com/asp/ado_connect.asp

Answer 2

As you're still on OpenEdge 11.6 (very old) you can still use the OpenEdge DataServer for ODBC. This feature is retired from OpenEdge 11.7 on:

https://community.progress.com/s/article/Frequently-asked-questions-regarding-the-DataServer-for-ODBC-phaseout

"OpenEdge 11.6 is the last OpenEdge version with the DataServer for ODBC engine."

To use the Data Server you will need a license for this product from Progress Software.

Depending on the use-case and the frequency of the import, it might be easier to export from PostgresSQL into plain text files, XML or JSON and import that using ABL programs.

If you need a frequent and potentially bi-directional replication I would prefer to write a web service (in whatever language) that connects to your PostgreSQL database and exposes the data using REST.