We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Executing Oracle Stored Procedures with MFC and ODBC

BalajiS
BalajiS asked
on
Medium Priority
1,171 Views
Last Modified: 2008-01-09
Hello,

I have a simple stored procedure in Oracle 9i like this:

create or replace procedure Add_Numbers(var1 IN number,var2 IN number,var3 OUT number) as
begin
var3 := var1 + var2;
end;

I am trying to execute this procedure using MFC's CRecordset derived class like this (relevant portions only...)
--------------------------------------------------------
IMPLEMENT_DYNAMIC(AddNumbers, CRecordset)

AddNumbers::AddNumbers(CDatabase* pdb)
       : CRecordset(pdb)
{
      // Param Init
      m_Return_Value = -1;
      m_lNumber1 = 0;
      m_lNumber2 = 0;
      m_nParams = 3;

      m_nDefaultType = snapshot;
}

CString AddNumbers::GetDefaultSQL()
{
      return _T("{? = CALL Add_Numbers (?, ?)}");
}

void AddNumbers::DoFieldExchange(CFieldExchange* pFX)
{
      // RFX output param mapping
      pFX->SetFieldType(CFieldExchange::outputParam);
      RFX_Long(pFX, _T("[@var3]"), m_Return_Value);

      // RFX input param mapping
      pFX->SetFieldType(CFieldExchange::inputParam);
      RFX_Long(pFX, _T("[@var1]"), m_lNumber1);
      RFX_Long(pFX, _T("[@var2]"), m_lNumber2);
}

void AddNumbers::Move(long lRows, WORD wFetchType)
{
      m_bBOF = m_bEOF = TRUE; // By pass CRecordset
}

BOOL AddNumbers::ExecDirect()
{
      return Open(CRecordset::forwardOnly, NULL, CRecordset::executeDirect);
}

I call this class like this:

CDatabase db;
CString strConn = _T("DSN=ORADB90;UID=system;PWD=manager");
db.OpenEx(strConn,CDatabase::noOdbcDialog);
AddNumbers rs(&db);
rs.m_lNumber1 = 10;
rs.m_lNumber2 = 20;
BOOL bRet = rs.ExecDirect();
long ret = rs.m_Return_Value;

When I run this code, I am getting this error at the "ExecDirect" line:
----------------------------------------------------
ORA-24334: no descriptor for this position.
----------------------------------------------------

Please tell me what is that I am doing wrong?
(The same code works fine for a similar procedure in SQL Server!)

Thanks,

Balaji
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
Using this, does it just use the MS ODBC Driver? or can you get it to use the Oracle ODBC Driver? If you can, try using the Oracle driver and see what happens

Author

Commented:
The DSN ORADB90 that I have used in my code actually uses an Oracle ODBC Driver.
Hi,
  The actual function call should be
         return _T("{? = CALL Add_Numbers (?, ?, ?)}");

Since the Add_Numbers procedure takes 3 parameters, you should pass 3 parameters when calling the function as well.
The return value is the result of executing the stored procedure and not the out parameter.

Hence the functions should be as follows.
void AddNumbers::DoFieldExchange(CFieldExchange* pFX)
{
     // RFX result param mapping
     pFX->SetFieldType(CFieldExchange::outputParam);
     RFX_Long(pFX, _T("RETURN_VALUE"), m_Result);

     // RFX input param mapping
     pFX->SetFieldType(CFieldExchange::inputParam);
     RFX_Long(pFX, _T("[@var1]"), m_lNumber1);
     RFX_Long(pFX, _T("[@var2]"), m_lNumber2);

     // RFX output param mapping
     pFX->SetFieldType(CFieldExchange::outputParam);
     RFX_Long(pFX, _T("[@var3]"), m_Return_Value);
}

CString AddNumbers::GetDefaultSQL()
{
     return _T("{? = CALL Add_Numbers (?, ?, ?)}");
}

I hope this should solve your problem.

regards
Sathis

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Great answer! It works fine. With Microsoft ODBC for Oracle your solution works perfectly.

With the Oracle driver I had to make some small changes:

1. Change GetDefaultSQL like this:
return _T("{CALL Add_Numbers (?,?,?)}");

2. Change DoFieldExchange accordingly:

Thanks,

Balaji
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.