| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Calling Oracle Stored Procedure in VC++
Anyone knows how to call a Stored Procedure to return a resultset in VC++ with "Oralce provider for OLEDB driver8.1.6", I tried with "Merant OLEDB driver for Oracle" and "MicroSoft OLEDB driver for Oracle", it works well, I also can retrieve a resultset by passing a common Sql statement, but I failed by calling a Oracle Stored Procedure.
I'm a new VC++ programmer, Please help me. A sample should be much better. Thanks.
I attached VC++ and Oracle Source codes, please give me your hint.
/////////////Stored procedure////////////////////
CREATE OR REPLACE PACKAGE Employees AS
TYPE empcur IS REF CURSOR;
PROCEDURE GetEmpRecords(p_cursor OUT empcur); END Employees;
/
CREATE OR REPLACE PACKAGE BODY Employees AS
PROCEDURE GetEmpRecords(p_cursor OUT empcur) IS
BEGIN
OPEN p_cursor FOR
SELECT *
FROM emp;
END GetEmpRecords;
END Employees;
/
//////////////VC++////////////////////////////////////////////////////////////////////
#import "C:Program FilesCommon FilesSystemADOmsado15.dll" no_namespace rename("EOF", "EndOfFile")
#define TESTHR(x) if FAILED(x) _com_issue_error(hr)
#include <stdio.h> #include <string.h> #include <ole2.h> #include "conio.h"
//Function declaration
void ActiveConnectionX(VOID);
void PrintProviderError(_ConnectionPtr pConnection);
void PrintComError(_com_error &e);
void main (void)
{
if (FAILED (::CoInitialize (NULL))) return;
HRESULT hr = S_OK;
_ConnectionPtr pConnection = NULL;
_RecordsetPtr rs = NULL;
FieldPtr fld = NULL;
_variant_t val;
_CommandPtr pCmd = NULL;
static char line[1024];
// Microsoft
// static const char *str_con = "Provider=MSDAORA;Data Source=LORAX.WORLD;"
// "User Id=scott;Password=tiger;";
// Oracle OLEDB
// Doesn't work with this driver
static const char *str_con = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;"
"Data Source=listener;Password=tiger";
// Merant ADO OLEDB Provider
// Works with this driver
// static const char *str_con = "Provider=DataDirect.Oracle8ADOProvider.2;Persist Security Info=False;User ID=scott;"
"Data Source=listener_m;Password=tiger";
static const char *query = "Employees.GetEmpRecords()";
// Employees.GetEmpRecords return a Ref Cursor
// static const char *query = "select * from emp";
// MircroSoft
// static const char *str_con = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=cuiTEST;Data Source=JERVIS; Password=";
// static const char *query = "dbo.Get_dlsepr";
// Merant (Intersolv)
// static const char *str_con = "Provider=DataDirect.Oracle8ADOProvider.2;User ID=scott;Password=tiger;"
// "Data Source=mera_ora;Persist Security Info=False";
// static const char *query = "select * from emp order by ename";
// static const char *query = "{call test1 (1,{resultset 1, p2})}";
// static const char *query = "insert into brighttab1 (name,age) values ('asd',1)";
// static const char *query = "select * from brighttab1";
int t;
try
{
TESTHR (pConnection.CreateInstance(__uuidof(Connection)));
TESTHR (pConnection->Open (str_con, "", "",NULL));
TESTHR (rs.CreateInstance (__uuidof (Recordset)));
TESTHR(pCmd.CreateInstance(__uuidof (Command)));
t = GetTickCount();
// TESTHR (rs->Open(query, _variant_t ((IDispatch*) pConnection, true),
// adOpenForwardOnly, adLockReadOnly, adCmdText));
// TESTHR (rs->Open(query, _variant_t ((IDispatch*) pConnection, true),
// adOpenForwardOnly, adLockReadOnly, adCmdStoredProc));
// pCmd->Parameters->Append (pCmd->CreateParameter("LastName", adChar, adParamInput, 20, strName));
pCmd->CommandText = query;
pCmd->PutActiveConnection (_variant_t((IDispatch*) pConnection));
//rs = pCmd->Execute (NULL,NULL, adCmdText);
rs = pCmd->Execute (NULL,NULL, adCmdStoredProc);
printf ("Time of execution: %ld
", GetTickCount() - t);
// process all records 1 by 1
printf ("Query: %s
Processing answer...
", query);
rs->MoveFirst();
long rec_nr = 0;
while (!(rs->EndOfFile))
{
printf ("Record %ld
", rec_nr);
line[0] = 0;
// process all fields in 1 record
printf ("Fields # : %ld
", rs->Fields->Count);
for (long field_nr = 0; field_nr < rs->Fields->Count; field_nr++)
{
fld = rs->Fields->GetItem (field_nr);
val = fld->GetValue();
if ((val.vt != VT_EMPTY) && (val.vt != VT_NULL))
strcat (line, (LPCSTR)(_bstr_t) val);
else
strcat (line, "NULL"); // ?? check what dblib does in that case !!!!
strcat (line, " ");
} // end of fields
printf ("%s
", line);
rec_nr++;
rs->MoveNext();
} // end of records
//rs->Close();
//pConnection->Close();
}
catch (_com_error &e)
{
// Notify the user of errors if any.
_bstr_t bstrSource (e.Source());
_bstr_t bstrDescription (e.Description());
PrintProviderError (pConnection);
printf ("Error: Source : %s
Description : %s
",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);
}
printf("
Press any key to continue..");
getch();
::CoUninitialize();
}
///////////////////////////////////////////////////////////
// //
// PrintProviderError Function //
// //
///////////////////////////////////////////////////////////
VOID PrintProviderError(_ConnectionPtr pConnection)
{
ErrorPtr pErr;
long nCount;
long i;
if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
// Collection ranges from 0 to nCount -1.
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("Error number: %x %s
", pErr->Number,(LPCSTR)pErr->Description);
}
}
 |
 |