Re: Retrieving result sets from Stored Procedures using ODBC

From: support email <msftmail_at_us.oracle.com>
Date: Mon, 24 May 1999 16:37:59 GMT
Message-ID: <374b801d.8414329@newshost.us.oracle.com>

Oracle Sample Code Repository Entry# 581

---

Disclaimer:
This sample is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The sample has been tested and appears to work as intended. However, you should always test in YOUR environment before relying on it.

---

 

Name: ODBC ResultSets from a stored procedure Category: ODBC
Port: WIN32
Description: Sample (from the Oracle ODBC Driver 8.0.4.0.3 Help File) modified to work better with MSVC 5.0. This returns 2 refcursors from a stored procedure and fetches upon them with the C ODBC API. Requires Oracle ODBC Driver 8.0.4.0.3 or higher, but is not supported until the 8.0.5 release for the ODBC driver and the RDBMS due to underlying OCI and RDBMS issues.

---

/*   

• Sample Application using Oracle reference cursors via ODBC
  *
  
• Assumptions:
  *
  
• 1) Oracle Sample databases is present with data loaded for the EMP table.
  *
  
• 2) Two fields are referenced from the EMP table ename and mgr.
  *
  
• 3) A data source has been setup to access the sample database.
  *
  *
  
• Program Description:
  *
  
• Abstract:
  *
  
• This program demonstrates how to call result sets using
• Oracle store procedures
  *
  
• Details:
  *
  
• This program Creates and ODBC Connection to the database.
• Creates a Packaged Procedure containing two result sets.
• Executes the procedure it just created, and then retrieves
• the data from both result sets. The data is displayed to
• the user. The package is then delete and the user is
• logged out of the database.
  *
  *
  
• The following is the actual PL/SQL this code generates to
• create the store procedures.
  *
  

DROP PACKAGE ODBCRefCur;
CREATE PACKAGE ODBCRefCur AS
TYPE ename_cur IS REF CURSOR;
TYPE mgr_cur IS REF CURSOR;

PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2);
END;
/

CREATE PACKAGE BODY ODBCRefCur AS
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2)
    AS

      BEGIN 
         IF NOT Ename%ISOPEN 
         THEN 
             OPEN Ename for SELECT ename from emp; 
         END IF; 
         IF NOT Mgr%ISOPEN 
         THEN 
             OPEN Mgr for SELECT mgr from emp where job = pjob; 
         END IF; 
      END; 

END;
/

*

• End PL/SQL for Reference Cursor. */

/* 

• Include Files */

#include <windows.h>

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

/*

• Defines

 */

#define JOB_LEN 9

#define DATA_LEN 100

#define SQL_STMT_LEN 500

/*

• Procedures

 */

void DisplayError( SWORD HandleType, SQLHANDLE hHandle, SQLCHAR *Module );  

/*

• Main Program

 */

int main()

{

    SQLHENV hEnv;

    SQLHDBC hDbc;

    SQLHSTMT hStmt;

    SQLRETURN rc;

    SQLCHAR *DefUserName = (SQLCHAR*) "scott";

    SQLCHAR *DefPassWord = (SQLCHAR*) "tiger";

    SQLCHAR ServerName[DATA_LEN];

    SQLCHAR *pServerName=ServerName;

    SQLCHAR UserName[DATA_LEN];

    SQLCHAR *pUserName=UserName;

    SQLCHAR PassWord[DATA_LEN];

    SQLCHAR *pPassWord=PassWord;

    SQLCHAR Data[DATA_LEN];

    SQLINTEGER DataLen;

    SQLCHAR error[DATA_LEN];

    SQLCHAR *charptr;

    SQLCHAR SqlStmt[SQL_STMT_LEN];

    SQLCHAR *pSqlStmt=SqlStmt;

    SQLCHAR *pSalesMan = (SQLCHAR*) "SALESMAN";

    SQLINTEGER sqlnts=SQL_NTS;

    /*

• Allocate the Environment Handle

     */

    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );

    if (rc != SQL_SUCCESS)

    {

      printf( "Cannot Allocate Environment Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ( (char*) &error);

      exit(1);

    }

    /*

• Set the ODBC Version

     */

    rc = SQLSetEnvAttr( hEnv,

                        SQL_ATTR_ODBC_VERSION, 

                        (void *)SQL_OV_ODBC3, 

                        0); 

    if (rc != SQL_SUCCESS)

    {

      printf( "Cannot Set ODBC Version\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Allocate the Connection handle

     */

    rc = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );

    if (rc != SQL_SUCCESS)

    {

      printf( "Cannot Allocate Connection Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Get User Information

     */

    lstrcpy( (char *) pUserName, (char * )DefUserName );

    lstrcpy( (char *) pPassWord, (char * )DefPassWord );

    /*

• Data Source name

     */

    printf( "\nEnter the ODBC Data Source Name\n" );

      charptr = (SQLCHAR*) gets ((char*) ServerName);

    /*

• User Name

     */

    printf ( "\nEnter User Name Default [%s]\n", pUserName);

      charptr = (SQLCHAR*) gets ((char*) error);

    if (*charptr == '\0')

    {

      lstrcpy( (char*) pUserName, (char *) DefUserName );

    }

    /*

• Password

     */

    printf ( "\nEnter Password Default [%s]\n", pPassWord);

      charptr = (SQLCHAR*) gets ((char*) error);

    if (*charptr == '\0')

    {

      lstrcpy( (char *) pPassWord, (char *) DefPassWord );

    }

    /*

• Connection to the database

     */

    rc = SQLConnect( hDbc,

                     pServerName, 

                     (SQLSMALLINT) lstrlen((char *) pServerName), 

                     pUserName, 

                     (SQLSMALLINT) lstrlen((char *) pUserName), 

                     pPassWord, 

                     (SQLSMALLINT) lstrlen((char * )pPassWord)); 

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_DBC, hDbc, (SQLCHAR *) "SQLConnect");

    }

    /*

• Allocate a Statement

     */

    rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );

    if (rc != SQL_SUCCESS)

    {

      printf( "Cannot Allocate Statement Handle\n");

      printf( "\nHit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

      exit(1);

    }

    /*

• Drop the Package

     */

    lstrcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    /*

• Create the Package Header

     */

    lstrcpy( (char * )pSqlStmt, "CREATE PACKAGE ODBCRefCur AS\n"); 
    lstrcat( (char *) pSqlStmt, "  TYPE ename_cur IS REF CURSOR;\n"); 
    lstrcat( (char *) pSqlStmt, "  TYPE  mgr_cur  IS REF

CURSOR;\n\n");

    lstrcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");

    lstrcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2);\n\n");

    lstrcat( (char *) pSqlStmt, "END;\n");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Create the Package Body

     */

    lstrcpy( (char *) pSqlStmt, "CREATE PACKAGE BODY ODBCRefCur AS\n");

    lstrcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");

    lstrcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2)\n AS\n BEGIN\n");

    lstrcat( (char *) pSqlStmt, " IF NOT Ename%ISOPEN\n THEN\n");

    lstrcat( (char *) pSqlStmt, " OPEN Ename for SELECT ename from emp;\n");

    lstrcat( (char *) pSqlStmt, "      END IF;\n\n"); 
    lstrcat( (char *) pSqlStmt, "      IF NOT Mgr%ISOPEN\n
THEN\n"); 
    lstrcat( (char *) pSqlStmt, "          OPEN Mgr for SELECT mgr
from emp where job = pjob;\n"); 
    lstrcat( (char *) pSqlStmt, "      END IF;\n"); 

    lstrcat( (char *) pSqlStmt, " END;\n");     lstrcat( (char *) pSqlStmt, "END;\n");

        printf((char *) pSqlStmt);

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Bind the Non-Result Set Parameter

     */

    rc = SQLBindParameter(hStmt,

                          3, 

                          SQL_PARAM_INPUT, 

                          SQL_C_CHAR, 

                          SQL_CHAR, 

                          JOB_LEN, 

                          0, 

                          pSalesMan, 

                          0, 

                          &sqlnts); 

    /*

• Call the Store Procedure which executes the Result Sets

     */

    lstrcpy( (char *) pSqlStmt, "{CALL ODBCRefCur.EmpCurs(?,?,?)}");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLExecDirect");

    }

    /*

• Bind the Data

     */

    rc = SQLBindCol( hStmt,

                     1, 

                     SQL_C_CHAR, 

                     Data, 

                     sizeof(Data), 

                     &DataLen); 

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLBindCol");

    }

    /*

• Get the data for Result Set 1

     */

    printf( "\nEmployee Names\n\n");

    while ( rc == SQL_SUCCESS )

    {

      rc = SQLFetch( hStmt );

      if ( rc == SQL_SUCCESS )

      {

        printf("%s\n", Data);

      }

      else

      {

        if (rc != SQL_NO_DATA)

        {

          DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLFetch");

        }

      }

    }

    printf( "\nFirst Result Set - Hit Return to Continue\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    /*

• Get the Next Result Set

     */

    rc = SQLMoreResults( hStmt );

    if (rc != SQL_SUCCESS)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLMoreResults");

    }

    /*

• Get the data for Result Set 2

     */

    printf( "\nManagers\n\n");

    while ( rc == SQL_SUCCESS )

    {

      rc = SQLFetch( hStmt );

      if ( rc == SQL_SUCCESS )

      {

        printf("%s\n", Data);

      }

      else

      {

        if (rc != SQL_NO_DATA)

        {

          DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLFetch");

        }

      }

    }

    printf( "\nSecond Result Set - Hit Return to Continue\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    /*

• Should Be No More Results Sets

     */

    rc = SQLMoreResults( hStmt );

    if (rc != SQL_NO_DATA)

    {

      DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *) "SQLMoreResults");

    }

    /*

• Drop the Package

     */

    lstrcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *) pSqlStmt));

    /*

• Free handles close connections to the database

     */

    SQLFreeHandle( SQL_HANDLE_STMT, hStmt );

    SQLDisconnect( hDbc );

    SQLFreeHandle( SQL_HANDLE_DBC, hDbc );

    SQLFreeHandle( SQL_HANDLE_ENV, hEnv );

    printf( "\nAll Done - Hit Return to Exit\n");

      charptr = (SQLCHAR*) gets ((char*) error);

    return(0);

}

/*

• Display Error Messages

 */

void DisplayError( SWORD HandleType, SQLHANDLE hHandle, SQLCHAR *Module )

{

    SQLCHAR MessageText[255];

    SQLCHAR SQLState[80];

    SQLRETURN rc=SQL_SUCCESS;

    LONG NativeError;

    SWORD RetLen;

    SQLCHAR error[25];

    SQLCHAR *charptr;

    rc = SQLGetDiagRec(HandleType,

                       hHandle,  

                       1, 

                       SQLState, 

                       &NativeError, 

                       MessageText, 

                       255, 

                       &RetLen); 

    printf( "Failure Calling %s\n", Module );     if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)     {

      printf( "\t\t\t         State: %s\n", SQLState); 
      printf( "\t\t\t  Native Error: %d\n", NativeError ); 
      printf( "\t\t\t Error Message: %s\n", MessageText ); 

    }

    printf( "\nHit Return to Exit\n");     charptr = (SQLCHAR*) gets ((char*) error);     exit(1);

} Received on Mon May 24 1999 - 11:37:59 CDT