/*
   PROGRAM NAME: SQL.p
         AUTHOR: Speedy Mercer
   DATE WRITTEN: 03-16-2004
    DESCRIPTION: Universal SQL Query Procedure that
                 passes back the number of data fields =
                 to NUM-ENTRIES(cFields).
ENHANCEMENTS BY: 9/15/2006: Jeff Pilant came up with the 
                 temp table and loop that freed us up 
                 from the old 50 field restristion in 
                 SQL.*.
*/

/* Temp Table Include for SQL.p */
{SQL.i}   

DEFINE INPUT  PARAMETER cDSN      AS CHARACTER NO-UNDO.
DEFINE INPUT  PARAMETER cServer   AS CHARACTER NO-UNDO.
DEFINE INPUT  PARAMETER cUser     AS CHARACTER NO-UNDO.
DEFINE INPUT  PARAMETER cPassword AS CHARACTER NO-UNDO.
DEFINE INPUT  PARAMETER cQuery    AS CHARACTER NO-UNDO.
DEFINE INPUT  PARAMETER cFields   AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER TABLE     FOR ttSQLfields.
DEFINE OUTPUT PARAMETER cError    AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER iRecords  AS INTEGER   NO-UNDO.

DEFINE VARIABLE chObjRecordSet  AS COM-HANDLE  NO-UNDO.
DEFINE VARIABLE chObjConnection AS COM-HANDLE  NO-UNDO.
DEFINE VARIABLE chObjCommand    AS COM-HANDLE  NO-UNDO.
DEFINE VARIABLE cODBC-DSN       AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cODBC-SERVER    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cODBC-USERID    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cODBC-PASSWD    AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cODBC-QUERY     AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iODBC-RECCOUNT  AS INTEGER     NO-UNDO.
DEFINE VARIABLE cODBC-NULL      AS CHARACTER   NO-UNDO.
DEFINE VARIABLE iODBC-CURSOR    AS INTEGER     NO-UNDO.
DEFINE VARIABLE iNdx            AS INTEGER     NO-UNDO.

/* Create the connection object for the link to SQL */
CREATE "ADODB.Connection":U chObjConnection.

/* Create a recordset object ready to return the data */
CREATE "ADODB.RecordSet":U chObjRecordSet.

/* Create a command object for sending the SQL statement */
CREATE "ADODB.Command":U   chObjCommand.

/* Set connection variable values */
ASSIGN
  cODBC-DSN    = cDSN       /* The ODBC DSN */
  cODBC-SERVER = cServer    /* server hosting the SQL DB and DSN */
  cODBC-USERID = cUser      /* The userid for the SQL Database */
  cODBC-PASSWD = cPassword. /* Password required by above user-id */

/* Set the query string */
ASSIGN cODBC-QUERY  = cQuery.

/* Open up the connection to the ODBC Layer */
chObjConnection:OPEN ("data source=":U
  + cODBC-DSN 
  + ";server=":U
  + cODBC-SERVER, cODBC-USERID, cODBC-PASSWD, 0) NO-ERROR.

/* Check for connection errors */
IF (error-status:NUM-MESSAGES > 0 )
THEN DO:
   ASSIGN 
      cError = "Error: Could not establish connection to ~"":U
             + cODBC-DSN + ";server=":U + cODBC-SERVER
             + "~" as ~"" + cODBC-USERID + "~" using password ~""
             + cODBC-PASSWD + "~".".
   
   RETURN.
END.
ELSE DO:
  /* Run the query */    
  ASSIGN
    chObjCommand:ActiveConnection  = chObjConnection
    chObjCommand:CommandText       = cODBC-QUERY
    chObjConnection:CursorLocation = 3 /* adUseClient */
    chObjRecordSet                 = chObjCommand:EXECUTE(OUTPUT cODBC-NULL,"",32).

   /* Scott Auge sauge@amduus.com - UPDATES, etc. puke on recordcount */
   /* so we only do this on SELECT statements.                        */
   IF cODBC-QUERY BEGINS "SELECT" 
   THEN ASSIGN iODBC-RECCOUNT = chObjRecordSet:RecordCount.

  /* Have we returned any rows ? */
  IF (iODBC-RECCOUNT > 0) 
  AND NOT (iODBC-RECCOUNT = ?) 
  THEN DO:
    iRecords = iODBC-RECCOUNT.
    
    /* Goto The first record */
    chObjRecordSet:MoveFirst NO-ERROR.

    /* Loop through all returned records */
    DO WHILE iODBC-CURSOR < iODBC-RECCOUNT:
      /* increment the counter */
      ASSIGN iODBC-CURSOR = iODBC-CURSOR + 1.
      
      CREATE ttSQLfields.
      
      ASSIGN ttSQLfields.Record = iODBC-CURSOR.
      
      /* 9/15/2006: Jeff Pilant - Begin */
      DO iNdx = 1 TO NUM-ENTRIES(cFields):
        CREATE ttSQLfields.

        ASSIGN ttSQLfields.Record = iODBC-CURSOR
               ttSQLfields.Seq    = iNdx
               ttSQLfields.Val    = chObjRecordSet:FIELDS(ENTRY(iNdx, cFields)):VALUE.
      END.
      /* 9/15/2006: Jeff Pilant - End */

      /* Goto the next record */
      chObjRecordSet:MoveNext NO-ERROR.
    END. /* retrieved a single data row */
  END. /* retrieved all data rows */
  ELSE cError = "No records found.":U.

  RELEASE OBJECT chObjRecordSet  NO-ERROR.
END.

/* Close the ADO connection */
chObjConnection:CLOSE NO-ERROR.

/* Don't forget to release the memory!! */
RELEASE OBJECT chObjConnection NO-ERROR.
RELEASE OBJECT chObjCommand    NO-ERROR.
RELEASE OBJECT chObjRecordSet  NO-ERROR.

/* Clear the vars */
ASSIGN
  chObjConnection = ?
  chObjCommand    = ?
  chObjRecordSet  = ?.