Kbase P116614: How to access an Oracle Database from 4GL using ADO?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/29/2009 |
|
Status: Unverified
GOAL:
How to access an Oracle Database from 4GL using ADO?
GOAL:
How to read information from an Oracle Database using ADO?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
Windows
Oracle
FIX:
This code runs against the SCOTT schema in an Oracle sample database.
An ODBC DSN created against the ODBC driver for the specific Oracle instance (for example, the driver "Oracle in OraHome92") is required.
/* Sample Code */
DEFINE VARIABLE ObjRecordSet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjConnection AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjCommand AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ODBC-DSN AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-SERVER AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-USERID AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-PASSWD AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-QUERY AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-STATUS AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-RECCOUNT AS INTEGER NO-UNDO.
DEFINE VARIABLE ODBC-NULL AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-CURSOR AS INTEGER NO-UNDO.
/* Definition matches DEPT table in SCOTT schema */
DEFINE TEMP-TABLE tt
FIELD deptno AS INTEGER
FIELD dname AS CHARACTER
FIELD loc AS CHARACTER.
DEFINE QUERY q1 FOR tt SCROLLING.
DEFINE BROWSE b1 QUERY q1 NO-LOCK
DISPLAY deptno dname loc
WITH NO-ROW-MARKERS SEPARATORS SIZE 70 BY 12.62 EXPANDABLE.
DEFINE FRAME f1 b1 WITH NO-BOX.
CREATE "ADODB.Connection" ObjConnection. /* Create the connection object for the link to Oracle */
CREATE "ADODB.RecordSet" ObjRecordSet. /* Create a recordset object ready to return the data */
CREATE "ADODB.Command" ObjCommand. /* Create a command object for sending the SQL statement */
/* Change the below values as necessary */
ASSIGN ODBC-DSN = "OraDB" /* Oracle ODBC DSN */
ODBC-SERVER = "localhost" /* The name of the server hosting the Oracle DB and DSN */
ODBC-USERID = "SCOTT" /* The user id for access to the Oracle Database */
ODBC-PASSWD = "tiger" /* Password required by above user-id */
ODBC-QUERY = "SELECT * FROM dept".
/* Open up the connecti.on to the ODBC Layer */
ObjConnection:OPEN ( "data source=" + ODBC-DSN + ";server=" + ODBC-SERVER, ODBC-USERID, ODBC-PASSWD, 0 ) NO-ERROR.
/* Check for connection errors */
If ( ERROR-STATUS:NUM-MESSAGES > 0 ) THEN
ODBC-STATUS = "Error: Could not establish connection.".
ELSE
DO:
ASSIGN ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandText = ODBC-QUERY
ObjCommand:CommandType = 1 /* adCmdText */
ObjConnection:CursorLocation = 3 /* adUseClient */
ObjRecordSet:CursorType = 3 /* adOpenStatic */
ObjRecordSet = ObjCommand:EXECUTE ( OUTPUT ODBC-NULL, "", 32 )
ODBC-RECCOUNT = ObjRecordSet:RecordCount.
/* Have we returned any rows ? */
IF ( ODBC-RECCOUNT > 0 ) AND NOT ( ODBC-RECCOUNT = ? ) THEN
DO:
ObjRecordSet:MoveFirst NO-ERROR.
DO WHILE ODBC-CURSOR < ODBC-RECCOUNT:
/* Display the data from the query (or create a Progress temp-table for future use) */
/* Display ObjRecordSet:Fields ("name"):Value format "x(20)". */
CREATE tt.
&n.bsp; ASSIGN tt.deptno = ObjRecordSet:FIELDS ("deptno"):VALUE
tt.dname = ObjRecordSet:FIELDS ("dname"):VALUE
tt.loc = ObjRecordSet:FIELDS ("loc"):VALUE.
ASSIGN ODBC-CURSOR = ODBC-CURSOR + 1.
ObjRecordSet:MoveNext NO-ERROR.
END. /* retrieved a single data row */
END. /* retrieved all data rows */
ELSE
ASSIGN ODBC-STATUS = "No records found.".
/* Close the ADO connection */
ObjConnection:CLOSE NO-ERROR.
END. /* The connection opened correctly */
/* Release the memory */
RELEASE OBJECT ObjConnection NO-ERROR.
RELEASE OBJECT ObjCommand NO-ERROR.
RELEASE OBJECT ObjRecordSet NO-ERROR.
ASSIGN ObjConnection = ?
ObjCommand = ?
ObjRecordSet = ?.
OPEN QUERY q1 FOR EACH tt.
ENABLE ALL WITH FRAME f1.
WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW..