/************************************************************************
    AUTHOR: Speedy Mercer 
   WEBSITE: http://www.herohog.com
    E-MAIL: code AT herohog.com
     LEGAL: This program is provided for FREE.
            There is NO warranty either written or implied.
*************************************************************************/
   /* Temp Table Include for SQL.p */
   {SQL.i}   
    
   /* Variables used as parameters to call SQL-pro.p */
   DEFINE VARIABLE cDSN      AS CHARACTER NO-UNDO. /* The ODBC DSN */
   DEFINE VARIABLE cServer   AS CHARACTER NO-UNDO. /* server hosting the SQL DB and DSN */
   DEFINE VARIABLE cUser     AS CHARACTER NO-UNDO. /* The userid for the SQL Database */
   DEFINE VARIABLE cPassword AS CHARACTER NO-UNDO. /* Password required by above user-id */
   DEFINE VARIABLE cQuery    AS CHARACTER NO-UNDO. /* The Query to use */
   DEFINE VARIABLE cError    AS CHARACTER NO-UNDO. /* Any error messages to be returned */
   DEFINE VARIABLE cFields   AS CHARACTER NO-UNDO. /* Names of fields to be returned by SQL.p */
   DEFINE VARIABLE iRecords  AS INTEGER   NO-UNDO. /* Number of records returned */   

   /* Local Variables */
   DEFINE VARIABLE iStart    AS INTEGER   NO-UNDO.
   DEFINE VARIABLE cID       AS CHARACTER NO-UNDO.
   DEFINE VARIABLE cFname    AS CHARACTER NO-UNDO.
   DEFINE VARIABLE cLname    AS CHARACTER NO-UNDO.
   DEFINE VARIABLE iCnt      AS INTEGER   NO-UNDO.
   
   /* allow for 800x600 display */
   ASSIGN DEFAULT-WINDOW:HEIGHT-PIXELS = 600
          DEFAULT-WINDOW:WIDTH-PIXELS  = 800.

   /* Name the fields that you are getting back from SQL.p */
   ASSIGN 
      cFields = "SessID,SessTime,SessDate,UserName":U /* Names of fields to return by SQL.p */
      iStart  = TIME.
   
   /* Define the ODBC information to access the SQL data */
   ASSIGN
     cDSN      = "SalesSystem":U /* The ODBC DSN */
     cServer   = "SQL1":U        /* server hosting the SQL DB and DSN */
     cUser     = "WebSpeed":U    /* The userid for the SQL Database */
     cPassword = "foobar":U      /* Password required by above user-id */
     cQuery    = "SELECT * FROM SessData":U.

   /* Run the SQL procedure */
   RUN SQL.p 
     (INPUT  cDSN,              /* The ODBC DSN */
      INPUT  cServer,           /* server hosting the SQL DB and DSN */
      INPUT  cUser,             /* The userid for the SQL Database */
      INPUT  cPassword,         /* Password required by above user-id */
      INPUT  cQuery,            /* The Query to use */
      INPUT  cFields,           /* Names of fields to be returned by SQL.p */
      OUTPUT TABLE ttSQLfields, /* Temp Table containing returned fields & data */
      OUTPUT cError,            /* Any error messages to be returned */
      OUTPUT iRecords).         /* Number of records returned */
    
   /* Display any error returned by SQL.p */
   IF cError <> ""
   THEN DO:
      MESSAGE cError VIEW-AS ALERT-BOX ERROR BUTTONS OK.

      RETURN.
   END.
   ELSE DO:
     /* Show how many records were found */
     MESSAGE 
         TRIM(STRING(iRecords,">>>,>>>,>>9")) + " records fetched in "
         + TRIM(STRING(TIME - iStart,">>>>>>9")) + " seconds."
         VIEW-AS ALERT-BOX INFO BUTTONS OK.
       
     /* time how long it taked to display the temp table records */
     ASSIGN iStart = TIME.
   
     IF iRecords > 0
     THEN DO:
        /* Display the returned data in the temp table */
        FOR EACH ttSQLfields NO-LOCK
        BREAK BY ttSQLfields.Record:
           /* Display Record n of n: */
           IF FIRST-OF(ttSQLfields.Record)
           THEN PUT UNFORMATTED "Record " 
              + TRIM(STRING(ttSQLfields.Record,">>>,>>>,>>9"))
              + " of " + TRIM(STRING(iRecords,">>>,>>>,>>9")) 
              + ":" SKIP.

           /* Skip a couple of lines between each record */
           IF LAST-OF(ttSQLfields.Record)
           THEN PUT UNFORMATTED SKIP(2).
         
           /* Display fieldname: fieldvalue */
           PUT UNFORMATTED
              ENTRY(ttSQLfields.Seq, cFields) + ": " 
              + ttSQLfields.Val FORMAT "x(100)" SKIP.
        END.
         
        /* Let them know we are done */
        PUT UNFORMATTED SKIP "End of SQL recordset." SKIP.
     END.
   
     /* Show how many records and how long it took to display them */
     PUT UNFORMATTED
        TRIM(STRING(iRecords,">>>,>>>,>>9")) + " records displayed in "
        + TRIM(STRING(TIME - iStart,">>>>>>9")) + " seconds." SKIP.
   END.