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);
}