ODBC 3.0 is a simple API which meets the requirements for a faster ISAPI Server Extension. It supports:
- Multiple Threads to SQL Server
- Handles Connection Caching
- Is a low level API
, In this issue we show how to use ODBC 3.0 connection caching to speed up ISAPI server extensions that connect to databases. , ODBC 3.0 is a simple API which meets the requirements for a faster ISAPI Server Extension. It supports:
- Multiple Threads to SQL Server
- Handles Connection Caching
- Is a low level API
, In this issue we show how to use ODBC 3.0 connection caching to speed up ISAPI server extensions that connect to databases. This IssueODBC 3.0 is a simple API which meets the requirements for a faster ISAPI Server Extension. It supports:
- Multiple Threads to SQL Server
- Handles Connection Caching
- Is a low level API
, In this issue we show how to use ODBC 3.0 connection caching to speed up ISAPI server extensions that connect to databases. , ODBC 3.0 is a simple API which meets the requirements for a faster ISAPI Server Extension. It supports:
- Multiple Threads to SQL Server
- Handles Connection Caching
- Is a low level API
, In this issue we show how to use ODBC 3.0 connection caching to speed up ISAPI server extensions that connect to databases. Connection Pooling When connecting to a database from within an ISAPI server extension you must be fast. One way to do that is to pool connections to the database. Making a connection is expensive, usually making the connection takes longer then querying the data. For this reason if you can pool the connections you can save an enormous amount of time. Currently ODBC 3.0 is the only database API that caches connections. Besides connection pooling ISAPI server extension have some other requirements that need to be considered when choosing a database API. Why ODBC 3.0? When connecting to database you have many APIs to choose from including: ODBC, OLEDB, DAO, and ADO. We choose ODBC 3.0 because it handles connection caching and is a low level API. Example Code We have provided some example code of an ISAPI Server Extension that is thread safe and handles connection caching with ODBC 3.0 Thread Safe All ISAPI server extensions must be thread safe. This includes the code that is specific to an the ISAPI server extension and all the APIs the ISAPI server extensions that the thread calls. It is important when choosing a database API that the API is thread safe. Currently ODBC, OLEDB, and ADO are thread safe. DAO is not thread safe, and should not be used in ISAPI server extensions. Why use ODBC 3.0? ODBC 3.0 supports Connection Caching, is thread safe, and is a low level API. Why not use OLEDB? OLEDB has more functionality then ODBC, is mufti-threaded, but does not support connection caching. Why not use ADO? ADO is easier to write then either OLEDB or ODBC, but ADO adds an extra layer that slows down the connection. Why not use DAO? DAO is the worst of the bunch, not only is it not thread safe, it does not support connection caching and is several layers above ODBC. Why Not Use OLEDB? There are three major reasons that we are not using OLEDB for this example. - OLEDB does not support connection caching.
- OLEDB is slower then ODBC.
- OLEDB requires more code.
Each one of these is gone into detail below. Connection Caching One of the major reasons to avoid OLEDB when writing ISAPI server extensions is that it doesn't use connection caching. Since this Issue is all about connection caching in ISAPI server extensions we have chosen not to demonstrate OLEDB. OLEDB is slower then ODBC The current OLEDB provider from Mircosoft is a OLEDB layer to ODBC 3.0. Since it is a layer the OLEDB provider is slower then ODBC. So any call to Mircosoft OLEDB goes through ODBC 3.0. This means that if you can make an ODBC call directly then your call has the potenial to be faster. This is only true however when for the Mircosoft OLEDB provider. Other OLEDB providers could bypass ODBC. Currently however, there is only one OLEDB provider. More Code OLEDB was designed to accessing relational databases and non-relational databases. OLEDB proveds extra APIs that allow delvelopers to access non-relational databases. However this has an adverse effect on developers that want to access relational databases. It means that the an OLEDB developer writing to a realtional database will have to write more code then if he/she wrote staight to ODBC. More code might means more bugs. Requirements The code needs to be compiled with Microsoft Visual Developers Studio version 4.2 or better. Also, ODBC 3.0 needs to be installed on the machine that the code is compiled on and the machine that the code is to be run on. The Code Here is a code sample of an ISAPI Server Extension written using ODBC 3.0. This is fully commented skeleton code that you can take and use in your own ISAPI Server Extensions. You will need WinZip to extract the zip file since most of the file names are longer then 8.3. http://15seconds.com/files/121096.zip 8K. Code Notes A few notes about how the code operates and why. The ODBC Environment The ODBC Environment is allocated and set in the constructor of the ISAPI Server Extension. This guarantees two things. First of all it is only run once. Secondly, the constructor is thread safe, because it is only called once.The ODBC Environment is a member variable and is used by all threads entering the web space by the way of CallFunction. Since a single environment can handle multiple connections, this is not a problem. In fact having one constantly allocated environment aids the connection caching process. Notice that a flag (m_bBadConnection) is set if the code fails to make a connection successfully. This flag is checked in the method CallFunction(). A connection should not be allocated if the flag is TRUE. Parse Maps Notice that parse maps have been removed. By overriding CallFunction() we can handle all request in CallFunction(). This eliminates the need for Parse Maps. TODO The only code that needs to be added is the code that converts the skeleton code to code that performs a specific task. Look for the //TODO comment in the CallFunction() method. If you are not versed in the new ODBC 3.0 APIs you can still use the old ODBC 2.0 APIs with the hstmt variable. Connection Caching will not be effected if you use the skeleton code provided. ODBC Configuration You will need to set the DSN, User name and Password at the top of the ODBCExample.h file so that they match the ODBC Datasource that the ISAPI Server Extension queries. ODBC 3.0 Location ODBC 3.0 can be gotten free of charge from Microsoft on their web site.
http://www.microsoft.com/ODBC/download/default.htm ODBC 3.0 Installation Tricks When Installing ODBC 3.0 keep these things in mind:- Since SQL Server uses ODBC you will need to stop SQL Server, SQL Server Executive and the Internet Information Server before installing ODBC 3.0
- If you are using Microsoft Developer Studio then you will need to copy the ODBC libraries from
c:\odbcsdk\lib32
to
c:\msdev\lib
You will also need to copy the header files from: c:\odbcsdk\include
to
c:\msdev\include
This will allow you to compile ODBC 3.0 with Microsoft Developers Studio.
ODBC 3.0 and ODBC 2.0 Installing ODBC 3.0 will not effect any ODBC 2.0 code. You will NOT need to recompile. Nor will you have to re-implement any of your data sources.
| 
