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?
-
If OpenEdge can connect through an ODBC connection, then this should be possible as there is an ODBC driver for PostgreSQLuser330315– user3303152023-02-22 14:32:27 +00:00Commented Feb 22, 2023 at 14:32
Add a comment
|
2 Answers
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
2 Comments
Ezequiel
Hello FloW, I'm kind of ashamed but I don't know which value is to put on "Provider". My adapted version of your code give me an error in OPEN, saying "Provider not found". I've tried the DSN name and other strings.
FloW
first you should setup a odbc connection in your windowsOS, you need a driver for this and then you can use the same connection string you dont need the odbc setup, but it is the easyest way to test the connection
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:
"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.
1 Comment
Ezequiel
We don't have the license, but thank you, Mike.