How to set up ODBC for progress database on a windows server

Rob Fitzpatrick

ProgressTalk.com Sponsor
There is a lot that could be said about OpenEdge and SQL. What you need to know next depends on what you know already, which we don't know.

I'd suggest you download the documentation set for your OpenEdge version and look at the SQL manuals. Then read this KB article:
Progress KB - Basic Guide to Defining Progress SQL-92 Database Permissions & Security
Then read the other articles that it links to, if they interest you. Then post follow-up questions here.
 
Hello Huang,
One simple example with DSN-less connection to SQL DB.

Code:
/* 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.
 
Last edited by a moderator:
Top