Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Article

Store Procedure call in vc++ (ORACLE)

Rate me:
Please Sign up or sign in to vote.
2.12/5 (6 votes)
16 Aug 2006 55.4K   535   17   5
How to Call or Executive Store Procedure (Oracle ) in vc++

Sample Image - SpCall.jpg

Introduction

This Application connect to remote oracle server till connection was established and call strore procedure in oracle (remote) output to display in Dialog Box.This application takes empno and get complete record from table.

TABLE STRUCTURE:-

create table emp (empid number(10),name varchar2(20),sal number(10));

SAMPLE Data:-
insert into emp values (1,'Ramanji.M',10000)
insert into emp values (2,'Yogi',20000)
insert into emp values (3,'sathu',30000)
insert into emp values (4,'BOSS',130000)
insert into emp values (5,'Palani',40000)
insert into emp values (6,'Taj',50000)

select * from emp;

EMPID NAME                SAL
    1    Ramanji.M          10000
    2    Yogi                  20000
    3    sathu                30000
    4    BOSS                130000
    5    Palani                40000
    6    Taj                   50000

PROCEDURE (ORACLE):-

CREATE OR REPLACE PROCEDURE GETSAL (IEMPID IN EMP.EMPID%TYPE,ONAME OUT EMP.NAME%TYPE,OSAL OUT EMP.SAL%TYPE)IS

TEMPNAME VARCHAR2(20);
TEMPSAL  NUMBER(10);

 BEGIN
    SELECT NAME,SAL INTO TEMPNAME,TEMPSAL FROM EMP WHERE EMPID=IEMPID;
 ONAME:=TEMPNAME;
 OSAL:=TEMPSAL;
 END;

Information about ODBC ,ODBC data type and error

Kind of Value ,Informix ODBC Driver C Data Type (fCType) ,Informix ODBC Driver Typedef ,Standard C Data Type

SQL_C_SHORT        SWORD              signed short int
SQL_C_SLONG             SDWORD             signed long int
SQL_C_SSHORT           SWORD             signed short int
SQL_C_STINYINT           SCHAR              signed char
SQL_C_TINYINT         SCHAR               signed char
SQL_C_ULONG         UDWORD             unsigned long int
SQL_C_USHORT         UWORD             unsigned short int
SQL_C_UTINYINT         UCHAR             unsigned char

SQLSTATE Error

01000           General warning;
01001           Cursor operation conflict;
01002           Disconnect error;
01003         NULL value eliminated in set function;
01004           String data, right-truncated;
01006           Privilege not revoked;
01007        Privilege not granted;
01S00        Invalid connection string attribute;
01S01           Error in row;
01S02             Option value changed;
01S06        Attempt to fetch before the result set returned the first rowset;
01S07              Fractional truncation;
01S08              Error saving File DSN;
01S09        Invalid keyword;
07001        Wrong number of parameters;
07002        COUNT field incorrect;
07005        Prepared statement not a cursor-specification;
07006            Restricted data type attribute violation;
07009        Invalid descriptor index;
07S01        Invalid use of default parameter;
08001        Client unable to establish connection;
08002        Connection name in use;
08003        Connection does not exist;
08004        Server rejected the connection;
08007        Connection failure during transaction;
08S01        Communication link failure;
21S01        Insert value list does not match column list;
21S02         Degree of derived table does not match column list;
22001        String data, right-truncated;
22002        Indicator variable required but not supplied;
22003        Numeric value out of range;
22007        Invalid datetime format;
22008        Datetime field overflow;
22012        Division by zero;
22015        Interval field overflow;
22018        Invalid character value for cast specification;
22019        Invalid escape character;
22025        Invalid escape sequence;
22026        String data, length mismatch;
23000        Integrity constraint violation;
24000        Invalid cursor state;
25000        Invalid transaction state;
25S01        Transaction state;
25S02        Transaction is still active;
25S03        Transaction is rolled back;
28000        Invalid authorization specification;
34000        Invalid cursor name;
3C000        Duplicate cursor name;
3D000        Invalid catalog name;
3F000        Invalid schema name;
40001        Serialization failure;
40002        Integrity constraint violation;
40003        Statement completion unknown;
42000        Syntax error or access violation;
42S01        Base table or view already exists;
42S02        Base table or view not found;
42S11        Index already exists;
42S12        Index not found;
42S21        Column already exists;
42S22        Column not found;
44000        WITH CHECK OPTION violation;
HY000         General error;
HY001         Memory allocation error;
HY003         Invalid application buffer type;
HY004        Invalid SQL data type;
HY007        Associated statement is not prepared;
HY008        Operation canceled;
HY009        Invalid use of null pointer;
HY010        Function sequence error;
HY011        Attribute cannot be set now;
HY012         Invalid transaction operation code;
HY013         Memory management error;
HY014         Limit on the number of handles exceeded;
HY015        No cursor name available;
HY016        Cannot modify an implementation row descriptor;
HY017        Invalid use of an automatically allocated descriptor handle;
HY018        Server declined cancel request;
HY019         Non-character and non-binary data sent in pieces;
HY020         Attempt to concatenate a null value;
HY021        Inconsistent descriptor information;
HY024        Invalid attribute value;
HY090         Invalid string or buffer length;
HY091         Invalid descriptor field identifier;
HY092        Invalid attribute/option identifier;
HY095         Function type out of range;
HY096         Invalid information type;
HY097         Column type out of range;
HY098         Scope type out of range;
HY099         Nullable type out of range;
HY100         Uniqueness option type out of range;
HY101         Accuracy option type out of range;
HY103         Invalid retrieval code;
HY104         Invalid precision or scale value;
HY105        Invalid parameter type;
HY106         Fetch type out of range;
HY107         Row value out of range;
HY109        Invalid cursor position;
HY110        Invalid driver completion;
HY111        Invalid bookmark value;
HYC00            Optional feature not implemented;
HYT00           Timeout expired;
HYT01           Connection timeout expired;
IM001        Driver does not support this function;
IM002        Data source name not found and no default driver specified;
IM003        Specified driver could not be loaded;
IM004        Driver's SQLAllocHandle on SQL_HANDLE_ENV failed;
IM005        Driver's SQLAllocHandle on SQL_HANDLE_DBC failed;
IM006        Driver's SQLSetConnectAttr failed;
IM007        No data source or driver specified dialog prohibited;
IM008         Dialog failed;
IM009         Unable to load translation DLL;
IM010         Data source name too long;
IM011         Driver name too long;
IM012         DRIVER keyword syntax error;
IM013        Trace file error;
IM014        Invalid name of File DSN;
IM015         Corrupt file data source;
Return codes
0- indicates Success.
1- indicates Success with information.
100-indicates No data found. 
99- indicates Data needed.dynamic parameter value needed.
-1-indicates Error.
-2-indicates Invalid handle.

Add Variable to Dailog class

CDatabase m_OnLinedb;

<code>
void CSPCallDlg::OnRun() 
{
   UpdateData();
   if(m_strEmpNO.IsEmpty())
    {
       AfxMessageBox("Pl. enter Employee NO");
       return ;
     }

    // imp for error find
    UCHAR szSqlState[1024], szErrorMsg[1024];
    
    SDWORD pfNativeError;
    SWORD pcbErrorMsg;
    HSTMT hstmt;
    RETCODE retcode;
    SDWORD nEmpNo=0;
    
    CString  csConRemote;             

   TCHAR szName[10] = "\0", szsal[10] = "\0", szQuery[50] = "\0";          

    csConRemote="DSN=AAA";        //AAA=Microsoft ODBC for Oracle (32 bit)
    csConRemote+=";UID=anju";    
    csConRemote+=";PWD=anju";    
    int nfalg=1;

    // it will connect till connection was established

    while(nfalg)
    {
        try
        {
           m_OnLinedb.OpenEx(csConRemote,CDatabase::noOdbcDialog);        
           retcode = SQLAllocStmt(m_OnLinedb.m_hdbc,&hstmt);

        if(retcode == SQL_ERROR)  m_OnLinedb.Close();                            
                nEmpNo=atoi(m_strEmpNO);
        // number input to store procedure 
    retcode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,10,0,&nEmpNo,0,NULL);
            // out put char 
            retcode = SQLBindParameter(hstmt,2,SQL_PARAM_OUTPUT,SQL_C_CHAR, SQL_CHAR,10,0,szName,sizeof szName,NULL);                    

            retcode =SQLBindParameter(hstmt,3,SQL_PARAM_OUTPUT,SQL_C_CHAR, SQL_CHAR,10,0,szsal,sizeof szsal,NULL);
            
 sprintf(szQuery,"{call GETSAL (?,?,?)}");                                            
     // to exective proceture  
    retcode = SQLExecDirect(hstmt,(UCHAR *)szQuery,SQL_NTS);                    
    
        if(retcode == SQL_ERROR)
    {
         SQLError(NULL,m_OnLinedb.m_hdbc,hstmt,szSqlState,&pfNativeError,                                                                    szErrorMsg,1024,&pcbErrorMsg);                
    AfxMessageBox((char *)szErrorMsg);
    AfxMessageBox((char *)szSqlState);
    AfxMessageBox("Data is Out Of Ranga see Table");
    
        m_OnLinedb.Close();
    return;
    }
    SQLFreeStmt(hstmt,SQL_DROP);
    nfalg=0;
    m_strEmpName.Format("%s",szName);
    m_strEmpName.TrimLeft();
    m_strEmpName.TrimRight();

    m_strEmpSal.Format("%s",szsal);
    m_strEmpSal.TrimLeft();
    m_strEmpSal.TrimRight();

    UpdateData(false);
        //AfxMessageBox();
    }
    catch(...)//CDBException *e
    {
        //AfxMessageBox(e->m_strError);
        m_OnLinedb.Close();
        nfalg=1;
    }    
    }// end while
    m_OnLinedb.Close();
    AfxMessageBox("***Successfully***");
    return;
}</code>

further u need any information search "ODBC CALL-LEVEL INTERFACE " in google

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
India India
I am working at Hyderabad as vc++ software developer,I passed B.Com and B-level from DOEACC (www.doeacc.org.in)through O.U.Campus.

Comments and Discussions

 
GeneralLacks proper explanation...may be a good approach Pin
Bhushan198024-Feb-10 7:19
Bhushan198024-Feb-10 7:19 
Hi. It has been a long time since I have worked with ODBC API and I that too, I have to say that I have very little knowledge of. So, when I looked at that code, I knew that it does the trick, but I found it difficult to google out for everything I do not understand in the code. Just wanted to say that, for a novice and not so novice, it would be really difficult to understand with out any explanation behind your approach. All the more, I have never worked with stored procs in Oracle. By the way of this article, I got to see how one is return in Oracle. So I have mixed comments about this article...Also, I am a little late to comment as well...So Good luck.
Bhushan

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.