Example: Visual C++ - Access and return data by calling a procedure
This example illustrates using Visual C++ to access and return data by a call to a DB2® for IBM® i procedure.
Only the code relevant to the procedure call has been included here. This code assumes the connection has already been established. See the Examples: RPG - Host code for ODBC procedures topic for the source code for the procedure.
Creating the procedure
//* Drop the old Procedure
strcpy(szDropProc,"drop procedure apilib.partqry2");
rc = SQLExecDirect(m_hstmt, (unsigned char *)szDropProc, SQL_NTS);
// This statement is used to create a procedure
// Unless the
// procedure is destroyed, this statement need never be run again
strcpy(szCreateProc,"CREATE PROCEDURE APILIB.PARTQRY2 (INOUT P1 INTEGER," );
strcat(szCreateProc,"INOUT P2 INTEGER)");
strcat(szCreateProc,"EXTERNAL NAME APILIB.SPROC2 LANGUAGE RPG GENERAL")
//' Create the new Procedure
rc = SQLExecDirect(m_hstmt, (unsigned char *)szCreateProc, SQL_NTS);
if (rc != SQL_SUCCESS &&; rc != SQL_SUCCESS_WITH_INFO) {
DspSQLError(m_henv, m_hdbc, SQL_NULL_HSTMT);
return APIS_INIT_ERROR;
}
if(rc != SQL_SUCCESS) {
DspSQLError(m_henv, m_hdbc, SQL_NULL_HSTMT);
return APIS_INIT_ERROR;
}
Preparing the statements to call the procedure
// Prepare the procedure call
strcpy(szStoredProc, "call partqry2(?, ?)");
// Prepare the CALL statement
rc = SQLPrepare(m_hstmt, (unsigned char *) szStoredProc, strlen(szStoredProc));
if(rc != SQL_SUCCESS &&; rc != SQL_SUCCESS_WITH_INFO) {
DspSQLError(m_henv, m_hdbc, m_hstmt);
return APIS_INIT_ERROR;
}
Binding the parameters
// Bind the parameters for the procedure
rc = SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG,
SQL_INTEGER, sizeof(m_lOption), 0, &m_lOption, sizeof(m_lOption), &lcbon),
&lcbOption);
rc |= SQLBindParameter(m_hstmt, 2, SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG,
SQL_INTEGER, sizeof(m_lPartNo), 0, &m_lPartNo, sizeof(m_lPartNo), &lcbon),
&lcbOption);
// Bind the Columns
rc = SQLBindCol(m_hstmt, 1, SQL_C_SLONG, &m_lSPartNo,
sizeof(m_lSPartNo), &lcbBuffer);
rc |= SQLBindCol(m_hstmt, 2, SQL_C_CHAR, &m_szSPartDesc,
26, &lcbBuffer);
rc |= SQLBindCol(m_hstmt, 3, SQL_C_SLONG, &m_lSPartQty,
sizeof(m_lSPartQty), &lcbBuffer);
rc |= SQLBindCol(m_hstmt, 4, SQL_C_DOUBLE, &m_dSPartPrice,
sizeof(m_dSPartPrice), &lcbBuffer);
rc |= SQLBindCol(m_hstmt, 5, SQL_C_DATE, &m_dsSPartDate,
10, &lcbBuffer);
Calling the procedure
// Request a single record
m_lOption = ONE_RECORD;
m_lPartNo = PartNo;
// Run the procedure
rc = SQLExecute(m_hstmt);
if (rc != SQL_SUCCESS) {
DspSQLError(m_henv, m_hdbc, m_hstmt);
return APIS_SEND_ERROR;
}
// (Try to) fetch a record
rc = SQLFetch(m_hstmt);
if (rc == SQL_NO_DATA_FOUND) {
// Close the cursor for repeated processing
rc = SQLCloseCursor(m_hstmt);
return APIS_PART_NOT_FOUND;
}
else if (rc != SQL_SUCCESS) {
DspSQLError(m_henv, m_hdbc, m_hstmt);
return APIS_RECEIVE_ERROR;
}
// If we are still here we have some data, so map it back
// Format and display the data
.
.
.