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 ;
}
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";
csConRemote+=";UID=anju";
csConRemote+=";PWD=anju";
int nfalg=1;
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);
retcode = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,10,0,&nEmpNo,0,NULL);
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 (?,?,?)}");
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);
}
catch(...)
{
m_OnLinedb.Close();
nfalg=1;
}
}
m_OnLinedb.Close();
AfxMessageBox("***Successfully***");
return;
}</code>
further u need any information search "ODBC CALL-LEVEL INTERFACE " in google
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.