/* A sample procedure to test an ADO connection */
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-PROVIDER AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-SERVER AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-DATABASE 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.
/* If not executing against a sports2000 like database this temp table will need to be redefined */
DEFINE TEMP-TABLE tt
FIELD NUM_SEQ_MOVTO AS CHARACTER
FIELD NUM_PROCES AS CHARACTER
FIELD COD_EMPRESA AS CHARACTER
FIELD COD_TAREFA AS CHARACTER .
DEFINE QUERY q1 FOR tt SCROLLING.
DEFINE BROWSE b1 QUERY q1 NO-LOCK
DISPLAY
NUM_SEQ_MOVTO
NUM_PROCES
COD_EMPRESA
COD_TAREFA
WITH NO-ROW-MARKERS SEPARATORS SIZE 70 BY 12.62 EXPANDABLE.
DEFINE FRAME f1 b1 WITH NO-BOX.
/* Create the connection object for the link to SQL */
CREATE "ADODB.Connection" ObjConnection.
/* Create a recordset object ready to return the data */
CREATE "ADODB.RecordSet" ObjRecordSet.
/* Create a command object for sending the SQL statement */
CREATE "ADODB.Command" ObjCommand.
/* Change the below values as necessary */
ASSIGN
//ODBC-DSN = "ECM" /* The ODBC DSN */
ODBC-SERVER = "CA0DBP02" /* The name of the server hosting the SQL DB and DSN */
ODBC-PROVIDER = "SQLOLEDB"
ODBC-DATABASE = "WEBDESK"
ODBC-USERID = "WebDAccess" /* The user id for access to the SQL Database */
ODBC-PASSWD = "MVDPUMAztnMz" /* Password required by above user-id */
ODBC-QUERY = "SELECT top 10000 * from TAR_WORKFLOW".
ODBC-QUERY = "SELECT * from TAR_WORKFLOW where NUM_SEQ_MOVTO = 17".
/* Open up the connecti.on to the ODBC Layer */
// Connection with DSN parameters from MS Windows ODBC interface
//ObjConnection:OPEN ( "data source=" + ODBC-DSN + ";server=" + ODBC-SERVER, ODBC-USERID, ODBC-PASSWD, 0 ) .
// Parameter for a DSN-less connection, where you don´t need to do any parametrization on ODBC Interface
ObjConnection:OPEN ( 'Provider=' + ODBC-PROVIDER +
';Server=' + ODBC-SERVER +
';Database=' + ODBC-DATABASE +
';Uid=' + ODBC-USERID +
';Pwd=' + ODBC-PASSWD ,
"",
"",
0).
/* 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.
ASSIGN
tt.NUM_SEQ_MOVTO = ObjRecordSet:FIELDS ("NUM_SEQ_MOVTO"):VALUE
tt.NUM_PROCES = ObjRecordSet:FIELDS ("NUM_PROCES"):VALUE
tt.COD_EMPRESA = ObjRecordSet:FIELDS ("COD_EMPRESA"):VALUE
tt.COD_TAREFA = ObjRecordSet:FIELDS ("COD_TAREFA"):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 */
/* Don't forget to 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.