jmmcg::NTUtils::Database::ODBC Namespace Reference

Namespaces
namespace	Exceptions

Classes
class	Connection
class	DBTransaction
class	Environment
class	RecordsetBase
class	ROBulkRecordset
class	RORecordset
class	RORecordsetBase
class	WOBulkConnection
class	WOConnection
class	WOConnectionBase

Detailed Description

For details on the ODBC SDK see the following link in the MSDN (assuming the Jan 2000 MSDN is installed):

"mk:@MSITStore:D:\devtools\Microsoft%20Visual%20Studio\MSDN\2000JAN\1033"

To see a comparison of ADO/OLE DB/ODBC see (M$ keeps moving these around...):

Or: ADO: Excerpt from page: "...ADO is the API most recommended for general-purpose data access to SQL Server for these reasons: - ADO is easy to learn and program. - ADO has the feature set required by most general-purpose applications. - ADO enables programmers to quickly produce robust applications..." ODBC: Excerpt from page: "...ODBC can be used in tools, utilities, or system level development needing either top performance or access to SQL Server features. These features include: - A set of bulk copy functions based on the earlier DB-Library bulk copy functions. - Extensions to the ODBC diagnostic functions and records to get SQL Server-specific information from messages and errors. - A set of functions that exposes catalog information from the linked servers used in SQL Server distributed queries. - Various driver-specific attributes and connection string keywords to control SQL Server-specific behaviors." Useage details: This ODBC wrapper requires a minimum of ODBC version 3 header files/libs/dlls. They must support the following, to quote the MSDN: "ODBC aligns with the following specifications and standards that deal with the Call-Level Interface (CLI). (The ODBC features are a superset of each of these standards.) - The X/Open CAE Specification "Data Management: SQL Call-Level Interface (CLI)" - ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI) As a result of this alignment, the following are true: - An application written to the X/Open and ISO CLI specifications will work with an ODBC 3.x driver or a standards-compliant driver when it is compiled with the ODBC 3.x header files and linked with ODBC 3.x libraries, and when it gains access to the driver through the ODBC 3.x Driver Manager. - A driver written to the X/Open and ISO CLI specifications will work with an ODBC 3.x application or a standards-compliant application when it is compiled with the ODBC 3.x header files and linked with ODBC 3.x libraries, and when the application gains access to the driver through the ODBC 3.x Driver Manager. (For more information, see "Standards-Compliant Applications and Drivers <odbcstandards_compliant_applications_and_drivers.htm>" in Chapter 17, "Programming Considerations.")" Further specific details of this layer follow: h1You must have the "Platform SDK" installed. (Specifically the ODBC SDK in the "Data Access Services\MDAC SDK" books.) You can download the MDAC SDK from the M$ web site at: "http://www.microsoft.com/Data/download.htm". The MDAC SDK contains just the ADO/OLE DB/ODBC SDKs, a sub-set of the Platform SDK. .To run executables created with this code you must install the required ODBC drivers. These must conform to the above specification. To get the correct ODBC to SQL server drivers, which are included in MDAC there are two ways: a) Install them using "mdac_typ.exe" available from: The minimum version to install is "2.1.2.4202.3 (GA)". Note that this installs: installs:ADO v 2.10.4202.12.10.4202.1ODBC v 3.510.4202.0 Also note that as of MDAC v2.6, ODBC support (amongst others) is no longer included. b) MDAC v2.5 is shipped with Windows 2000 & Windows XP. This page: "http://support.microsoft.com/support/kb/articles/Q271/9/08.ASP" Search for article id "Q271908". I quote from that page: "The ODBC Desktop Drivers and Visual FoxPro ODBC driver, which no longer ship with MDAC 2.6, are shipped with the Microsoft Windows 2000 operating system (which contains MDAC 2.5). Microsoft Windows Millennium Edition (Me) also ships with MDAC 2.5. Microsoft Windows XP also contains the ODBC Desktop Drivers and Visual FoxPro Driver. For Microsoft Windows NT and Microsoft Windows 9x, MDAC 2.5 or an earlier version will need to be installed to obtain these drivers." Amongst other things. (See the release manifest at: .) )The exceptions all inherit from the "ODBCExceptionErr" class, which can be used as a filter to catch all exceptions thrown by this layer. .The "Environment" class is pretty useless, apart from the fact that it hides a lot of the messy allocation and deallocation details of accessing the ODBC drivers. This class is derived from to be actually useful, despite the fact that it is concrete. Note that this class checks to ensure the ODBC layer is the correct version. Which must be ODBC version 3. .The "Connection" class wraps a few more messy ODBC details regarding setting various connection based attributes for read only or write-only connections. Again this class is only really useful if derived from. By default this class enables connection pooling. Note that DBMS-specific translation on SQL strings is turned off by this class for speed. Hence only ANSI SQL is allowed. .The "RORecordset" class. This is a fairly trivial class that gets the data from the data base one element at a time. It was created mainly to be able to implement the loathed "count()" more memory efficiently than using the "ROBulkRecordset". Tests reveal that this class is over 10% slower than the "ROBulkRecordset" class for a given SQL statement. So basically only use it if you want a single row with a single column as the result of the SQL. For any other case don't use it, use it's big brother. .The "ROBulkRecordset" class. Now we're getting to the meat of this layer. This class is for read only (i.e. no SQL "update", "insert" or "delete" statements) data base manipulations. It attempts to mimick a real recordset, at which it is only possibly partially successful. It does not require an ODBC connection to be in scope as it creates and manages its own. The reference to "bulk" in the name means that the class has the ability to pre-fetch a pre-defined quantity of rows from the data base at a time. (Calls to "moveNext()" only get more data from the data base when the internal buffer is exhausted.) In general it has been found that optimium values for the internal buffer size are either "1" (one) or >100. Some testing may be required to determine the exact value, which will be dependant upon the process, the data base server, the network, and the dynamic profiles of these. .The "WOConnection" class. This is used to write data to the data base. It is implemented as write-only, but this is not a limitation of ODBC, only the wrapper. The "count()" function returns the number of records affected by the update. (Note well the comments about "count()" in "RecordsetBase".) Also it is perfectly acceptable to have multiple "WOConnection"s in scope with the same data base connection string. Further it is acceptable to have multiple transactions (they are wrapped within the "WOConnection" class) active at any one time, even on the same table. But obviously not the same row... The "WOConnection" class checks to ensure that multiple transaction support is supported by the data base driver. (This is part of the ODBC v2.0 standard.) )The "WOBulkConnection" class. This is also used to write data to the data base. It prepares the SQL statement before execution. (Hence the "execute()" implementation does not take an argument.) Also you can optionally bind the arguments the SQL needs, if it is parameterised. Note that if you bind a "tstring" this MUST be sized correctly before binding. i.e. the variable must be declared as "tstring param(50, '\0');" where "50" is at least the size of the string in the data base, and '\0' is the value the string is initialised to. ('\0', or NULL is the fastest value to use.) Otherwise you'll get exceptions thrown at run-time when committing the SQL. Note that this class does similar multiple transaction support checks, etc, as per the "WOConnection" class. Performance vs. ADO: In all cases the core loop has been designed to be such that the rate determining step is the data base activity. Also all values are quoted to within 10% error (average deviation). In all read cases an entire row of the calls table was returned. ."RORecordset": ~50% faster than ADO.."ROBulkRecordset"::1 row: 3x faster..10 rows: 20% slower..100 rows: same speed as ADO..1000 rows: 3x faster..>1000 rows: varies depending upon returned data. If the data is the full row of a calls table, as 1000. If the data is an arbitrary number of non-nullable "long"s, up to 100x faster. (i.e. so fast the data base is no longer the limiting factor, but the memory copies to get the data out of the recordset.) The constructor for the "ROBulkRecordset" can take a argument of the number of rows it is to fetch at a time. By default this is set to "1" (one), as reasonable & fast default that can only be beaten by careful tweaking.."WOConnection": ~50% faster than ADO.."WOBulkConnection": About the same speed as "WOConnection" for simple SQL. Untested for more complex SQL..Indexing the elements in a row. Testing has revealed that indexing an element of a row by the name is around 40% slower than using the correct column number. If your SQL returns a single column, plainly this can be indexed by number without causing a dependancy on the data base table design. (And is recommended.) (Specifically the order in which rows are returned.) If more than one column is returned and indexing by column number is used, a dependancy on the data base table design is introduced, which may not be acceptable. As a matter of interest the ODBC layer (thus ADO) cannot transfer a single character into an SQL table with a column of type "char" size 1. It has to make a string out of this. Thus it would be better to use a "tinyint" of size 1, precision 3 and cast it. (This is equivalent to the C data type "char". (Also the ODBC layer has to do a lot more messing around with passing the data too, which makes it even worse.) Future Improvements: h1Write a prepared "ROBulkRecordset" layer & test its performance..Improve the error reporting to give even more information..Implement "item(... , tstring &...)"..Implement "item(...)" for nullable columns. Currently if a nullable element that is "NULL" is returned, an ODBC exception of "SQLState 22002: Indicator variable required but not supplied" is thrown. This is because the "StrLen_or_IndPtr" parameter is set to "NULL", as it is unused..Resolve the use of maps across a dll boundary, as really slows the stuff down. (Compiler bugs again prevent me from doing this...))More...?