PeopleSoft Open Query ODBC API Example

The following example shows the ODBC API calls needed to execute a PeopleSoft query using the PeopleSoft Open Query ODBC driver. The following query requires two bind variables: business unit and department ID. The sample query returns an answer set of three columns: employee ID, name, and monthly rate.

/***********************************************************************
* Function:     OpenQuerySample                                        *
*                                                                      *
* Description:  Sample program illustrating the usage of PeopleSoft  *
*               Open Query ODBC API.                                   *
*               Sample code uses basic PeopleSoft Query ODBC interface *
*               functions.  Most error checking is excluded to make    *
*               code easier to follow; in a typical application,       *
*               every return code would be checked.                    *
*                                                                      *
* Returns:      TRUE if successful                                     *
***********************************************************************/
 
 
BOOL WINAPI     OpenQuerySample(HWND hWnd)
{
HENV            hEnv;                   // Environment handle for application
HDBC            hDbc;                   // Connection handle
HSTMT           hStmt;                  // Statement handle
RETCODE         retcode;                // Return code
char            szConnectString[] = 
"DSN=PeopleSoft PeopleTools;DBTYPE=ORACLE;DBNAME=PTDMO7;UID=PTDMO;PWD=PTDMO;";
char            szConnStringOut[256];   // completed connection string
SWORD           nConnStringLen;         // length of completed connect string
 
 
// Allocate environment, database connection
retcode = SQLAllocEnv(&hEnv);
if ((retcode = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)
    // error--this would normally abort program with message
    return(FALSE);
 
// Connect to the database
retcode = SQLDriverConnect(hDbc, hWnd, szConnectString,
        strlen(szConnectString), szConnStringOut, sizeof(szConnStringOut),
        &nConnStringLen, SQL_DRIVER_COMPLETE);
 
retcode = SQLAllocStmt(hDbc, &hStmt);
 
ProcessQuery(hStmt);
 
// Close the connection, release resources
retcode = SQLFreeStmt(hStmt);
retcode = SQLDisconnect(hDbc);
retcode = SQLFreeConnect(hDbc);
retcode = SQLFreeEnv(hEnv);
 
return(TRUE);
}
 
 
/***********************************************************************
* Function:     ProcessQuery                                           *
*                                                                      *
* Description:  Run a query and retrieve answer set.                   *
*                                                                      *
* Returns:      TRUE if successful, else FALSE                         *
***********************************************************************/
 
BOOL            ProcessQuery(HSTMT hStmt)
{
RETCODE         retcode;                // Return code
char            szSelect[] = "{call query.myquery(?,?)";
 
// binding of input variables must occur before statement execution
for (i = 0; i < 2; i++)
    retcode = SQLBindCol(hStmt, i, datatype, &value, sizeof(value), &valuelen);
 
retcode = SQLExecDirect(hStmt, szSelect, strlen(szSelect));
 
while (retcode = SQLFetch(hStmt) == SQL_SUCCESS)
    // process data for a fetched row....
 
return(retcode == SQL_NO_DATA_FOUND);
}