Ad Hoc Queries in ODBC
Dennis Bowen -- dbowen@pamd.cig.mot.com
Friday, April 12, 1996
VC++ 4.1 / Win95
Stupid question: Is it possible to do ad hoc queries using ODBC?
For example, say I have an employee table and in one situation I want to
retrieve the employee's name and in another, unrelated, situation I want
to select the employee's address (city, state, zip, etc.).
(a) Do I need to create separate CRecordset's for each situation?
(b) Do I need to create a CRecordset with all the columns and only
use what I need in the given situation?
(c) Can I create an ad hoc query of the database and get at the data
selected?
(d) Can I just modify the member variables of the CRecordset prior
to opening it and achieve the desired results?
(d) Is there a preferred (i.e. standard) way to do this?
I apologize if this really is a stupid question. I have looked through
documentation and samples and have not found an answer.
--
Dennis Bowen
dbowen@pamd.cig.mot.com
David Phillip Oster -- oster@netcom.com
Tuesday, April 16, 1996
[Mini-digest: 3 responses]
>VC++ 4.1 / Win95
>Stupid question: Is it possible to do ad hoc queries using ODBC?
Not a stupid question.
>For example, say I have an employee table and in one situation I want to
>retrieve the employee's name and in another, unrelated, situation I want
>to select the employee's address (city, state, zip, etc.).
> (a) Do I need to create separate CRecordset's for each situation?
No, although this _will_ work.
> (b) Do I need to create a CRecordset with all the columns and only
> use what I need in the given situation?
You could add a bit-vector to the CRecordset, and in your DoFieldExchange()
you use code like:
if( (1 << kFieldIdNum) & m_enabledFields){
RFX_Long(pFX, "[ID]", m_id);
}
you just need to fix up the m_nFields to correspond to the number of ON bits
in m_enabledFields before you call DoFieldExchange().
> (c) Can I create an ad hoc query of the database and get at the data
>selected?
Yes, you can return it from your CRecordset's GetDefaultSQL() or you can
pass it as a parameter to your CRecordset's Open().
> (d) Can I just modify the member variables of the CRecordset prior
> to opening it and achieve the desired results?
Modifying the classWizard generated variables will have no effect, except for
m_nParams and m_nFields.
> (d) Is there a preferred (i.e. standard) way to do this?
What I did was throw away the whole concept of a pre-set list of
RFX_??? calls. Instead, I associated an enum with each kind of RFX, called
RFXtype. then I just built a CArray of records that have a field for the
RFXtype, and a field for the value. The CArray interpreter looks like:
void CAdHocSet::DoFieldExchange(CFieldExchange* pFX){
int i;
m_nFields = m_queryArray.GetSize();
for(i = 0; i < m_nFields ; i++){
switch(m_queryArray[i].rfxType){
case kRFXLong: RFX_Long(pFX,
m_queryArray[i].FieldName,
m_queryArray[i].longValue);
break;
// similar cases for each RFX I care about.
}
}
}
This lets me build a CAdHocSet that can handle the results from
any query.
-----From: Dan Kirby
Look at article Q141802 in the Microsoft Developer Knowledgebase. It
describes a sample which demonstrates creating dynamic recordsets (that is,
CRecordset objects which can be used for multiple queries). The basic idea
is that you have to place conditional code in the DoFieldExchange function
of the CRecordset-derived class which maps the proper RFX calls based on
the SQL types for each of the fields in the resulting recordset.
--dan
-----From: wallym@callan.win.net (Wally Meerschaert)
See the MSDN article "Recordset: Dynamically Binding Data Columns
(ODBC)". which refers to the samples DYNABIND and CATALOG which
do dynamic data type binding with ODBC queries. It is not a
stretch to go from these to a general ad hoc query mechanism
p.s., the only stupid questions are the ones you don't ask! Don't
work in the dark!
-------------------------------------------------------------------
Wally Meerschaert wallym@callan.win.net
Callan Associates Inc.
Play nice together now everyone...
-------------------------------------------------------------------
Frank McGeough -- frankhm@synchrologic.com
Monday, April 22, 1996
Dennis Bowen wrote:
>
> VC++ 4.1 / Win95
>
> Stupid question: Is it possible to do ad hoc queries using ODBC?
That's definitely possible. ODBC is just a standard call level interface
to databases (as opposed to a vendor specific one like Oracle's CLI, or
Sybase's DBLIB). You can do everything with ODBC that you can do in an
embedded SQL program (only portably). We do dynamic stuff all the time.
> For example, say I have an employee table and in one situation I want to
> retrieve the employee's name and in another, unrelated, situation I want
> to select the employee's address (city, state, zip, etc.).
>
> (a) Do I need to create separate CRecordset's for each situation?
> (b) Do I need to create a CRecordset with all the columns and only
> use what I need in the given situation?
> (c) Can I create an ad hoc query of the database and get at the data
> selected?
> (d) Can I just modify the member variables of the CRecordset prior
> to opening it and achieve the desired results?
> (d) Is there a preferred (i.e. standard) way to do this?
>
> I apologize if this really is a stupid question. I have looked through
> documentation and samples and have not found an answer.
>
This is a different question from the original. CRecordSet, CDatabase are
Microsoft wrappers around ODBC for quick development and the things that
they've done are setup for static queries. You can do dynamic stuff with
them but there are limitations (like most wrappers). The number of fields
in the result set must be less than 255 for example. I haven't seen any
examples of dynamic CRecordSet on the MSDN or elsewhere which says a lot
about how they think they should be used.
You should examine dbcore.cpp in the mfc src directory to see what's going
on. The CRecordSet::Open method allows you to open an arbitrary result set
(since you can pass any SQL select statement into it). The Open method
eventually calls MoveFirst which calls the Move method. This will
perform the binding thru the BindFieldsToColumns method.
> Dennis Bowen
> dbowen@pamd.cig.mot.com
--
__________________________________________________
Frank McGeough frankhm@synchrologic.com
Synchrologic, Inc. (http://www.synchrologic.com/)
Voice: 404.876.3209 Fax: 404.876.3809
| Вернуться в корень Архива
|