|
In a usual MFC database project we have a lot of
CRecordset
derived classes generated by ClassWizard. The class
presented here, CODBCRecordset
is a very easy to use
replacement of all these CRecordset
classes.
CODBCRecordset
is derived from CRecordset
but it does not
have hardcoded inside the number and the type of the database fields.
I have seen some implementations trying to solve this
problem. But CODBCRecordset
has
at least two advantages:
Because CODBCRecordset
is derived from
CRecordset
and uses its data exchange mechanism it is fully
compatible with MFC ODBC class CDatabase
.
Each field value is stored in a CDBField
object. The CDBField
class is inherited from
CDBVariant
and has some added functionality so it's simple to use.
CODBCRecordset
and CDBField
classes support all database data types. CDBField
makes implicit
type conversion where it is appropriate to supply the data in the requested format.
Here is a list of CODBCRecordset
and
CDBField
methods:
CODBCRecordset class | |||||||||||||||||||||
Constructor | The same as for CRecordset accepting CDatabase* | ||||||||||||||||||||
BOOL Open( LPCTSTR lpszSQL,
UINT nOpenType, DWORD dwOptions );
|
Open the recordset lpszSQL is a SQL statement that returns recordset e.g. SELECT * FROM
tablename
nOpenType is CRecordset open type, see CRecordset::Open() dwOptions is CRecordset
options, see CRecordset::Open() Note that lpszSQL and nOpenType
have exchanged their positions compared to
|
||||||||||||||||||||
short
GetODBCFieldCount() |
Returns number of the fields
(columns) in the recordset. This method is defined in
CRecordset
. |
||||||||||||||||||||
int GetFieldID(
LPCTSTR )
|
Returns a field index by given field
name. It is case insensitive. CRecordset::GetFieldIndexByName()
works, but is case sensitive. |
||||||||||||||||||||
CString GetFieldName( int
) |
Returns a field name by given field index | ||||||||||||||||||||
CDBField& Field( LPCTSTR )
|
Through this method you can get a
reference to the internal CDBField
object corresponding to the specified column (See the CDBField
class table
for details about CDBField ).There are two forms of the method - with argument of type LPCTSTR szName - specifying the name of the column, and int nID - specifying the index of the coulmn in the recordset. These methods can be used as lvalue in expressions. Thus you can write values to the database. |
||||||||||||||||||||
CDBField& operator( LPCTSTR )
|
The function operator is defined for
easy of use of the class and just calls the corresponding Field()
methodThere are two forms of the function operator -
with argument of type These can be used as lvalue in expressions. Thus you can write values to the database. |
||||||||||||||||||||
|
All of these methods do the appropriate type conversions depending on
their return value and the type of the underlying data in the database. These methods
just call Field().AsXXX() There are two forms of these methods - with argument of type LPCTSTR szName - specifying the name of the column, and int nID - specifying the index of the coulmn in the recordset. These cannot be used as lvalue in expressions. |
CDBField class | |||||||||||||||||||||||||
Constructors | No public constructors.
CDBField cannot be instantiated except by
CODBCRecordset to be used in internal structures to
support data exchange. These objects are accessible through
CODBCRecordset
methods. |
||||||||||||||||||||||||
|
All of these methods do the appropriate type conversions depending on
their return value and the type of the underlying data in the database (See the AsXXX methods table for data conversion rules). There is no data
type Int but |
||||||||||||||||||||||||
assignment operators | There are defined assignment operators accepting boo l, unsigned
char , short , int , long , COleDateTime
and CString . So CDBField
objects can be lvalues. There is no assignemt operator accepting
CLongBinary because MFC does not support writing
CLongBinary values into database. These assignment
operators do appropriate conversions to the underlying database column
data type except CLongBinary
(See the assignment
operators table for data conversion rules). |
||||||||||||||||||||||||
const CString& GetName() |
Returns the field name this object corresponds to. | ||||||||||||||||||||||||
|
Each of these return true if the field contains a value of the
corresponding data type. There is no data type Number but There is no data type Int but |
Values in the database |
||||||||||
NULL | BOOL | UCHAR | SHORT | LONG | SINGLE | DOUBLE | DATE | STRING | BINARY | |
AsBool | false | * | * | * | * | * | * | * | ||
AsChar | 0x32 | * | * | * | * | * | * | * | ||
AsShort | 0 | * | * | * | * | * | * | * | ||
AsInt | 0 | * | * | * | * | * | * | * | ||
AsLong | 0 | * | * | * | * | * | * | * | ||
AsFloat | 0.0 | * | * | * | * | * | * | * | ||
AsDouble | 0.0 | * | * | * | * | * | * | * | ||
AsDate | null | invalid | invalid | * | * | * | * | * | * | |
AsString | empty | * | * | * | * | * | * | * | * | * |
AsLongBinary | NULL | * | ||||||||
Empty cells indicate the conversion is not available,
thus code asserts. Cells marked with * indicate conversion is available (See the Conversion algorithms table for data conversion rules). |
Database Field Type | Argument of the assignment operator |
||||||||
bool | unsigned char | short | int | long | float | double | COleDateTime | String | |
NULL | |||||||||
BOOL | * | * | * | * | * | * | * | * | |
UCHAR | * | * | * | * | * | * | * | * | |
SHORT | * | * | * | * | * | * | * | * | |
LONG | * | * | * | * | * | * | * | * | |
SINGLE | * | * | * | * | * | * | * | * | |
DOUBLE | * | * | * | * | * | * | * | * | |
DATE | * | * | |||||||
STRING | * | * | * | * | * | * | * | * | * |
BINARY | |||||||||
Empty cells indicate the conversion is not available,
thus code asserts. Cells marked with * indicate conversion is available (See the Conversion algorithms table for data conversion rules). |
You should include the files ODBCRecordset.h and ODBCRecordset.cpp in your project.
I usually include this line in my StdAfx.h file.
#include "ODBCRecordset.h"
Here is a simple code showing how CODBCRecordset can be used.
///////////////////////////////////////////////////////////////////////////// CDatabase db; // This connect string will pop up the ODBC connect dialog CString cConnect = "ODBC;"; db.Open( NULL, // DSN FALSE, // Exclusive FALSE, // ReadOnly cConnect, // ODBC Connect string TRUE // Use cursor lib ); COleDateTime dOrderDate; CODBCRecordset rs( &db ); rs.Open( "SELECT * FROM Orders \ WHERE ORDER_DATE > 'jan 1 2000' \ ORDER BY ORDER_DATE" ); for( ; ! rs.IsEOF(); rs.MoveNext() ) { // The choice is yours. You may choose whatever way // you want to get the values // // These return COleDateTime value dOrderDate = rs.GetDate( "ORDER_DATE" ); dOrderDate = rs.Field("ORDER_DATE").AsDate(); // These make implicit call to AsDate() dOrderDate = rs("ORDER_DATE"); dOrderDate = rs.Field("ORDER_DATE"); // Now edit the fields in the recordset rs.Edit(); rs("ORDER_DATE") = "jan 1 1999"; // Implicit conversion rs.Field("ORDER_DATE") = "jan 1 1999"; // Implicit conversion rs.Update(); } // for(.... /////////////////////////////////////////////////////////////////////////////
If ORDER_DATE
is
stored in the database as datetime
or compatible data type the value will be get directly.
If ORDER_DATE
is stored in the database as string
or compatible data type (char, varchar) the
value will be converted via COleDateTime::ParseDateTime()
method. If conversion fails,
dOrderDate
will be set to COleDatetime::invalid
.
When opening a resultset generated by join statements it
is possible to get 2 or more columns that have the same name.
CODBCRecordset
leaves the name of the first column intact
but other repeated columns are renamed with adding the number this columns repeats the
name. Not repeated column names are left intact. E.g.
SELECT * FROM Orders, Customers WHERE Orders.CUST_ID = Customers.ID
If the table Orders have a column with name ID and Customers
have a column with name ID, CODBCRecordset
will rename ID from Customers
to ID2
and all other not repeating column names will be intact.
Well, here is a tip: Rename columns manualy to be sure what name they have, e.g.
SELECT Orders.*, Customers.ID as CUSTOMERS_ID FROM Orders, Customers WHERE Orders.CUST_ID = Customers.ID
CODBCRecordset
allocates storage for all fields in the resultset and uses MFC Record
Field eXchange mechanism like it has been inherited from CRecordset
using ClassWizard.
|
Home >>
Database >>
ODBC
Advertise on The Code Project |
Article content copyright Stefan Chekanov, 2000 everything else © CodeProject, 1999-2001. |