ORA-01008: not all variables bound error when using MFC CRecordset::Requery

From:

Horst Nabulke <nabulke@arcor.de>

Newsgroups:

microsoft.public.vc.mfc

Date:

Wed, 13 Sep 2006 18:14:40 +0200

Message-ID:

<1mipsjoa9kodc.7pcxght122hf$.dlg@40tude.net>

Hello,

I've got a problem using the MFC CDatabase and CRecordset C++ classes with
the Oracle ODBC driver.
After successfully opening a database connection and retrieving data using
a recordset, I want to rebuild the recordset using a new filter parameter
value.

Setting a new filter value and calling the CRecordset::Requery() function
should do the trick but it always results in an "ORA-01008: not all
variables bound" error. I attached some sources and an ODBC trace captured
when the error occurs.

Got any ideas what's wrong?

Any help in resolving that issue would be greatly appreciated.

Regards

Horst

Environment:
============
Oracle 10g ODBC Driver 10.02.00.01 (SQORA32.DLL), WIN XP, Visual C++ 6.0
MFC
Oracle 10g database

Main program:
=============
CDatabase oraDB;

oraDB.OpenEx("DSN=xxxxx;", CDatabase::noOdbcDialog)

CDBTest paraTestSet;

paraTestSet.m_pDatabase = &oraDB;

// Set SQL-WHERE clause
paraTestSet.m_strFilter = "PRODUKTIONSNR = ?";

// Set the filter for the first query
CString strFilter = "12345";
paraTestSet.m_PRODUKTIONSNRParam = strFilter;

// Run the first query and fetch some results
paraTestSet.Open();

.....//SUCCESS

// Set new filter
strFilter = "45678";

paraTestSet.m_PRODUKTIONSNRParam = strFilter;

// If I uncomment this line it works!
// paraTestSet.m_strSort = "PRODUKTIONSNR";

// Run the query again with changed filter settings
paraTestSet.Requery();

Error: Requery attempt failed.
ORA-01008: not all variables bound
State:07001,Native:1008,Origin:[Oracle][ODBC][Ora]

// Parts of the source code
CDBTest::CDBTest(CDatabase* pdb)
: CRecordset(pdb)
{
//{{AFX_FIELD_INIT(CDBTest)
m_PRODUKTIONSNR = _T("");
m_PRUEFMODUL = _T("");
m_nFields = 2;
//}}AFX_FIELD_INIT
m_nDefaultType = snapshot;
m_nParams = 1;
m_PRODUKTIONSNRParam = "";
}

void CDBTest::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CDBTest)
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX, _T("[PRODUKTIONSNR]"), m_PRODUKTIONSNR);
RFX_Text(pFX, _T("[PRUEFMODUL]"), m_PRUEFMODUL);
//}}AFX_FIELD_MAP
pFX->SetFieldType( CFieldExchange::param );
RFX_Text(pFX, "PRODUKTIONSNRParam", m_PRODUKTIONSNRParam);
}

// Header file of Recordset
class CDBTest : public CRecordset
{
public:
CDBTest(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(CDBTest)

// Feld-/Parameterdaten
//{{AFX_FIELD(CDBTest, CRecordset)
CString m_PRODUKTIONSNR;
CString m_PRUEFMODUL;
//}}AFX_FIELD
CString m_PRODUKTIONSNRParam;
.....
}

ODBC Trace:
===========

[....]

ODBCTest d0c-974 ENTER SQLExtendedFetch
HSTMT 00931960
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 1
SQLULEN * 0x0012FE28
UWORD * 0x00334F08

ODBCTest d0c-974 EXIT SQLExtendedFetch with return code 100
(SQL_NO_DATA_FOUND)
HSTMT 00931960
UWORD 1 <SQL_FETCH_NEXT>
SQLLEN 1
SQLULEN * 0x0012FE28
UWORD * 0x00334F08

// End of first successfull query

ODBCTest d0c-974 ENTER SQLFreeStmt
HSTMT 00931960
UWORD 0 <SQL_CLOSE>

ODBCTest d0c-974 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 00931960
UWORD 0 <SQL_CLOSE>

// Requery attempt that fails....
ODBCTest d0c-974 ENTER SQLExecute
HSTMT 00931960

ODBCTest d0c-974 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 00931960

DIAG [07001] [Oracle][ODBC][Ora]ORA-01008: not all variables bound