CRecordset, Oracle7.3 and parameterized queries 
Author Message
 CRecordset, Oracle7.3 and parameterized queries

I am using a CRecordset derived class  - CMyRecordset
in an application that talks to Oracle 7.3 using a
parameterized query.
I have done everything by the book.
Have my parameter declared correctly, added the
necessary code for field exchange, m_nParams is
set correctly too.
My SQL statement looks like:
"SELECT VENDORNAME FROM VENDORS WHERE VENDORID=?"
The parameter is being set before the call to
CMyRecordset::Open().
If I replace ? in the CMyRecordset::GetDefaultSQL()
with the data I want to pass to the parameter everything
works fine (hard coded, that is).
Everything is fine also if I build my query string at
runtime by concatenating strings and pass the result
to the CMyRecordset::Open().
Tried also using parameterized filter - same result.
The "funny" thing is that all of the above works fine
with Access database.
(Used exactly the same code with both databases).
I do not get any error messages, no exceptions are being thrown.
Seems to me that the Oracle ODBC driver is failing.
In the string passed to the CMyRecordset::Open() the '?'
never gets replaced with anything.
I suspect the SQLBindParameter API call, but I am not
sure. Tried to trace the API calls but all of them return
success.

My app is build with VC6 on Win95.
Oracle 7.3 is running on WinNT4
Tried Oracle ODBC drivers v2.0.3.1.1 and v2.5.3.1.0b.
Oracle claims the second one is Level 2 compliant.

Has anyone out there struggled with the same problem?
Would appreciate any help.

Thanks,
Val



Sat, 27 Oct 2001 03:00:00 GMT  
 CRecordset, Oracle7.3 and parameterized queries

I have found that parameters are a bit difficult to work with and that it is usually a lot easier and less time consuming to just work with the m_strFilter and build it myself before opening the recordset.  But if it is for some reason necessary for you to use parameters, please post a snippet of your code where you define your recordset with the parameter...maybe the problem is there.

Quote:

>I am using a CRecordset derived class  - CMyRecordset
>in an application that talks to Oracle 7.3 using a
>parameterized query.
>I have done everything by the book.
>Have my parameter declared correctly, added the
>necessary code for field exchange, m_nParams is
>set correctly too.
>My SQL statement looks like:
>"SELECT VENDORNAME FROM VENDORS WHERE VENDORID=?"
>The parameter is being set before the call to
>CMyRecordset::Open().
>If I replace ? in the CMyRecordset::GetDefaultSQL()
>with the data I want to pass to the parameter everything
>works fine (hard coded, that is).
>Everything is fine also if I build my query string at
>runtime by concatenating strings and pass the result
>to the CMyRecordset::Open().
>Tried also using parameterized filter - same result.
>The "funny" thing is that all of the above works fine
>with Access database.
>(Used exactly the same code with both databases).
>I do not get any error messages, no exceptions are being thrown.
>Seems to me that the Oracle ODBC driver is failing.
>In the string passed to the CMyRecordset::Open() the '?'
>never gets replaced with anything.
>I suspect the SQLBindParameter API call, but I am not
>sure. Tried to trace the API calls but all of them return
>success.

>My app is build with VC6 on Win95.
>Oracle 7.3 is running on WinNT4
>Tried Oracle ODBC drivers v2.0.3.1.1 and v2.5.3.1.0b.
>Oracle claims the second one is Level 2 compliant.

>Has anyone out there struggled with the same problem?
>Would appreciate any help.

>Thanks,
>Val

-----------------** -- Posted from CodeGuru -- **-----------------
http://www.codeguru.com/    The website for Visual C++ programmers.


Sun, 28 Oct 2001 03:00:00 GMT  
 CRecordset, Oracle7.3 and parameterized queries
Thanks for looking into my problem!
Here is a portion of the header file:

class CMyRecordset : public CRecordset
{
public:
CMyRecordset(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(CMyRecordset)

// Field/Param Data
//{{AFX_FIELD(CMyRecordset, CRecordset)
CString m_COLUMN1;
CString m_COLUMN2;
CString m_COLUMN3;
//}}AFX_FIELD
CString m_strMyParam;

// Overrides
// ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(CMyRecordset)
public:
virtual CString GetDefaultConnect();    // Default connection string
virtual CString GetDefaultSQL();    // Default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
//}}AFX_VIRTUAL

// Implementation
#ifdef _DEBUG
virtual void AssertValid() const;
virtual void Dump(CDumpContext& dc) const;
#endif

Quote:
};

This is a portion of my implementation file:

IMPLEMENT_DYNAMIC(CMyRecordset, CRecordset)

CMyRecordset::CMyRecordset(CDatabase* pdb)
: CRecordset(pdb)
{
//{{AFX_FIELD_INIT(CMyRecordset)
m_COLUMN1 = _T("");
m_COLUMN2 = _T("");
m_COLUMN3 = _T("");
m_nFields = 3;
//}}AFX_FIELD_INIT
m_nDefaultType = snapshot;
m_nParams = 1;
m_strMyParam = _T("");

Quote:
}

CString CMyRecordset::GetDefaultSQL()
{
return _T("SELECT VAL.MYTABLE.COLUMN1, VAL.MYTABLE.COLUMN2,
VAL.MYTABLE.COLUMN3 FROM VAL.MYTABLE WHERE MYTABLE.COLUMN1=?");

Quote:
}

void CMyRecordset::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CMyRecordset)
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN1]"), m_COLUMN1);
RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN2]"), m_COLUMN2);
RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN3]"), m_COLUMN3);
//}}AFX_FIELD_MAP
pFX->SetFieldType(CFieldExchange::param);
RFX_Text(pFX, _T("[MyParam]"), m_strMyParam);

Quote:
}

Thanks again and take care,
Val

Quote:

>I have found that parameters are a bit difficult to work with and that it

is usually a lot easier and less time consuming to just work with the
m_strFilter and build it myself before opening the recordset.  But if it is
for some reason necessary for you to use parameters, please post a snippet
of your code where you define your recordset with the parameter...maybe the
problem is there.
Quote:


>>I am using a CRecordset derived class  - CMyRecordset
>>in an application that talks to Oracle 7.3 using a
>>parameterized query.
>>I have done everything by the book.
>>Have my parameter declared correctly, added the
>>necessary code for field exchange, m_nParams is
>>set correctly too.
>>My SQL statement looks like:
>>"SELECT VENDORNAME FROM VENDORS WHERE VENDORID=?"
>>The parameter is being set before the call to
>>CMyRecordset::Open().
>>If I replace ? in the CMyRecordset::GetDefaultSQL()
>>with the data I want to pass to the parameter everything
>>works fine (hard coded, that is).
>>Everything is fine also if I build my query string at
>>runtime by concatenating strings and pass the result
>>to the CMyRecordset::Open().
>>Tried also using parameterized filter - same result.
>>The "funny" thing is that all of the above works fine
>>with Access database.
>>(Used exactly the same code with both databases).
>>I do not get any error messages, no exceptions are being thrown.
>>Seems to me that the Oracle ODBC driver is failing.
>>In the string passed to the CMyRecordset::Open() the '?'
>>never gets replaced with anything.
>>I suspect the SQLBindParameter API call, but I am not
>>sure. Tried to trace the API calls but all of them return
>>success.

>>My app is build with VC6 on Win95.
>>Oracle 7.3 is running on WinNT4
>>Tried Oracle ODBC drivers v2.0.3.1.1 and v2.5.3.1.0b.
>>Oracle claims the second one is Level 2 compliant.

>>Has anyone out there struggled with the same problem?
>>Would appreciate any help.

>>Thanks,
>>Val

>-----------------** -- Posted from CodeGuru -- **-----------------
>http://www.codeguru.com/    The website for Visual C++ programmers.



Sun, 28 Oct 2001 03:00:00 GMT  
 CRecordset, Oracle7.3 and parameterized queries

Sorry, although I haven't ever used parameters in the Default sql I don't see any obvious problems in your code...looks like it should work okay.

Quote:

>Thanks for looking into my problem!
>Here is a portion of the header file:

>class CMyRecordset : public CRecordset
>{
>public:
>CMyRecordset(CDatabase* pDatabase = NULL);
>DECLARE_DYNAMIC(CMyRecordset)

>// Field/Param Data
>//{{AFX_FIELD(CMyRecordset, CRecordset)
>CString m_COLUMN1;
>CString m_COLUMN2;
>CString m_COLUMN3;
>//}}AFX_FIELD
>CString m_strMyParam;

>// Overrides
>// ClassWizard generated virtual function overrides
>//{{AFX_VIRTUAL(CMyRecordset)
>public:
>virtual CString GetDefaultConnect();    // Default connection string
>virtual CString GetDefaultSQL();    // Default SQL for Recordset
>virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
>//}}AFX_VIRTUAL

>// Implementation
>#ifdef _DEBUG
>virtual void AssertValid() const;
>virtual void Dump(CDumpContext& dc) const;
>#endif
>};

>This is a portion of my implementation file:

>IMPLEMENT_DYNAMIC(CMyRecordset, CRecordset)

>CMyRecordset::CMyRecordset(CDatabase* pdb)
>: CRecordset(pdb)
>{
>//{{AFX_FIELD_INIT(CMyRecordset)
>m_COLUMN1 = _T("");
>m_COLUMN2 = _T("");
>m_COLUMN3 = _T("");
>m_nFields = 3;
>//}}AFX_FIELD_INIT
>m_nDefaultType = snapshot;
>m_nParams = 1;
>m_strMyParam = _T("");
>}

>CString CMyRecordset::GetDefaultSQL()
>{
>return _T("SELECT VAL.MYTABLE.COLUMN1, VAL.MYTABLE.COLUMN2,
>VAL.MYTABLE.COLUMN3 FROM VAL.MYTABLE WHERE MYTABLE.COLUMN1=?");
>}

>void CMyRecordset::DoFieldExchange(CFieldExchange* pFX)
>{
>//{{AFX_FIELD_MAP(CMyRecordset)
>pFX->SetFieldType(CFieldExchange::outputColumn);
>RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN1]"), m_COLUMN1);
>RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN2]"), m_COLUMN2);
>RFX_Text(pFX, _T("[VAL.MYTABLE.COLUMN3]"), m_COLUMN3);
>//}}AFX_FIELD_MAP
>pFX->SetFieldType(CFieldExchange::param);
>RFX_Text(pFX, _T("[MyParam]"), m_strMyParam);
>}

>Thanks again and take care,
>Val


>>I have found that parameters are a bit difficult to work with and that it
>is usually a lot easier and less time consuming to just work with the
>m_strFilter and build it myself before opening the recordset.  But if it is
>for some reason necessary for you to use parameters, please post a snippet
>of your code where you define your recordset with the parameter...maybe the
>problem is there.


>>>I am using a CRecordset derived class  - CMyRecordset
>>>in an application that talks to Oracle 7.3 using a
>>>parameterized query.
>>>I have done everything by the book.
>>>Have my parameter declared correctly, added the
>>>necessary code for field exchange, m_nParams is
>>>set correctly too.
>>>My SQL statement looks like:
>>>"SELECT VENDORNAME FROM VENDORS WHERE VENDORID=?"
>>>The parameter is being set before the call to
>>>CMyRecordset::Open().
>>>If I replace ? in the CMyRecordset::GetDefaultSQL()
>>>with the data I want to pass to the parameter everything
>>>works fine (hard coded, that is).
>>>Everything is fine also if I build my query string at
>>>runtime by concatenating strings and pass the result
>>>to the CMyRecordset::Open().
>>>Tried also using parameterized filter - same result.
>>>The "funny" thing is that all of the above works fine
>>>with Access database.
>>>(Used exactly the same code with both databases).
>>>I do not get any error messages, no exceptions are being thrown.
>>>Seems to me that the Oracle ODBC driver is failing.
>>>In the string passed to the CMyRecordset::Open() the '?'
>>>never gets replaced with anything.
>>>I suspect the SQLBindParameter API call, but I am not
>>>sure. Tried to trace the API calls but all of them return
>>>success.

>>>My app is build with VC6 on Win95.
>>>Oracle 7.3 is running on WinNT4
>>>Tried Oracle ODBC drivers v2.0.3.1.1 and v2.5.3.1.0b.
>>>Oracle claims the second one is Level 2 compliant.

>>>Has anyone out there struggled with the same problem?
>>>Would appreciate any help.

>>>Thanks,
>>>Val

>>-----------------** -- Posted from CodeGuru -- **-----------------
>>http://www.codeguru.com/    The website for Visual C++ programmers.

-----------------** -- Posted from CodeGuru -- **-----------------
http://www.codeguru.com/    The website for Visual C++ programmers.


Mon, 29 Oct 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Parameterized query using CRecordset

2. CRecordset - parameterized predefined queries/stored procedures

3. Crecordset: parameterized access to pre-defined queries

4. Help: CRecordset using Oracle7 and VC1.52

5. parameterized Management Query String

6. Dynamically Creating parameterized queries using VC++

7. ADO Parameterized Queries

8. Parameterized SQL query failing

9. Parameterized queries - Please help

10. Calling MSQuery from Visual C++ to run a parameterized query

11. Parameterized query

12. COleVariant and DAO parameterized queries

 

 
Powered by phpBB® Forum Software