Kbase P117484: How to access Oracle Packages via ADO using 4GL
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
24/07/2006 |
|
Status: Unverified
GOAL:
How to access Oracle Packages via ADO using 4GL
GOAL:
How to pass a parameter to an Oracle Package via ADO using 4GL
GOAL:
How to retrieve data from Oracle Packages with 4GL and Microsoft ActiveX Data Objects
FACT(s) (Environment):
Oracle
FIX:
This example code is based on the DEPT table in the SCOTT schema. The sample Package and Package Body need to be created before running the code. This code is provided "as-is" and is not intended to be a complete solution.
/* Sample Package for DEPT */
CREATE OR REPLACE PACKAGE deptlist
AS
TYPE tdeptno is TABLE of NUMBER(2)
INDEX BY BINARY_INTEGER;
TYPE tdname is TABLE of VARCHAR2(14)
INDEX BY BINARY_INTEGER;
TYPE tloc is TABLE of VARCHAR2(13)
INDEX BY BINARY_INTEGER;
PROCEDURE alldept
(deptno OUT tdeptno,
dname OUT tdname,
loc OUT tloc);
PROCEDURE onedept
(onedeptno IN NUMBER,
deptno OUT tdeptno,
dname OUT tdname,
loc OUT tloc);
END deptlist;
/* Sample Package Body for DEPT */
CREATE OR REPLACE PACKAGE BODY deptlist
AS
PROCEDURE alldept
(deptno OUT tdeptno,
dname OUT tdname,
loc OUT tloc)
IS
CURSOR dept_cur IS
SELECT deptno, dname, loc
FROM dept;
deptcount NUMBER DEFAULT 1;
BEGIN
FOR singledept IN dept_cur
LOOP
deptno(deptcount) := singledept.deptno;
dname(deptcount) := singledept.dname;
loc(deptcount) := singledept.loc;
deptcount := deptcount + 1;
END LOOP;
END;
PROCEDURE onedept
(onedeptno IN NUMBER,
deptno OUT tdeptno,
dname OUT tdname,
loc OUT tloc)
IS
CURSOR dept_cur IS
SELECT deptno, dname, loc
FROM dept
WHERE deptno = onedeptno;
deptcount NUMBER DEFAULT 1;
BEGIN
FOR singledept IN dept_cur
LOOP
deptno(deptcount) := singledept.deptno;
&.nbsp; dname(deptcount) := singledept.dname;
loc(deptcount) := singledept.loc;
deptcount := deptcount + 1;
END LOOP;
END;
END;
/* Sample 4GL Code */
DEFINE VARIABLE ObjConnection AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjCommand AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjRecordSet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjParameter AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ODBC-STATUS AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-RECCOUNT AS INTEGER 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 FORMAT "x(20)"
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.
DEFINE VARIABLE QSQL AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER LABEL "Leave as 0, or enter 10,20,30 or 40" NO-UNDO.
UPDATE i.
/* Setup ADODB.Connection */
CREATE "ADODB.Connection" ObjConnection. /* Create the connection object for the link to Oracle */
ASSIGN ObjConnection:ConnectionString = "Provider=MSDAORA;Data Source=ORA92;User Id=scott;Password=tiger;" /* Modify parameters to match local Oracle configuration */
ObjConnection:CursorLocation = 3. /* adUseClient */
/* Open Connection */
ObjConnection:OPEN(,,,) NO-ERROR.
/* Check for connection errors */
If ( ERROR-STATUS:NUM-MESSAGES > 0 ) THEN
ODBC-STATUS = "Error: Could not establish connection.".
ELSE
DO:
/* Setup ADODB.Command */
CREATE "ADODB.Command" ObjCommand. /* Create a command object for sending the SQL statement */
ASSIGN ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandType = 1. /* adCmdText */
/* Setup ADODB.RecordSet */
CREATE "ADODB.RecordSet" ObjRecordSet. /* Create a RecordSet Object ready to return the data */
ASSIGN ObjRecordSet:CursorType = 3 /* adOpenStatic */
ObjRecordSet:LockType = 1. /* adLockReadOnly */
/* Determine Package to execute based on user-input */
IF i > 0 THEN
DO:
/* Execute Package to retrieve 1 record */
ObjCommand:CommandText = "~{call deptlist.onedept(?,~{resultset 2, deptno, dname, loc})}".
/* Add and set Parameter for Command Object */
ObjParameter = ObjCommand:CreateParameter("ID",3,1,,). /* adInteger, adParamInput */
ObjCommand:Parameters:APPEND(ObjParameter).
&nbs.p; ObjParameter:VALUE = i.
END.
ELSE
/* Execute Package to retrieve all records */
ObjCommand:CommandText = "~{call deptlist.alldept(~{resultset 9, deptno, dname, loc})}".
/* Execute Query and Populate RecordSet */
ObjRecordSet = ObjCommand:EXECUTE (,,).
/* Check Record Count */
ODBC-RECCOUNT = ObjRecordSet:RecordCount NO-ERROR.
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.
ASSIGN tt.deptno = ObjRecordSet:FIELDS ("deptno"):VALUE
tt.dname = ObjRecordSet:FIELDS ("dname"):VALUE
tt.loc = ObjRecordSet:FIELDS ("loc"):VALUE.
ODBC-CURSOR = ODBC-CURSOR + 1.
ObjRecordSet:MoveNext NO-ERROR.
END. /* Retrieved a single data row */
END. /* Retrieved all data rows */
ELSE
ODBC-STATUS = "No records found.".
/* Close the ADO connection */
ObjConnection:CLOSE NO-ERROR.
/* Release the memory for ObjCommand, ObjRecordSet */
RELEASE OBJECT ObjCommand NO-ERROR.
RELEASE OBJECT ObjRecordSet NO-ERROR.
ASSIGN ObjCommand = ?
ObjRecordSet = ?.
END. /* The connection opened correctly */
/* Release the memory for ObjConnection*/
RELEASE OBJECT ObjConnection NO-ERROR.
ASSIGN ObjConnection = ?.
OPEN QUERY q1 FOR EACH tt.
ENABLE ALL WITH FRAME f1.
WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW..