Using the Connection Object

Team-Fly

---

As I indicated earlier, it's not always necessary to explicitly declare and open an ADO Connection object; sometimes ADO does this for you. However, the connection string you construct and use with the Recordset Open or the Command Execute method are all built using the same tenants as discussed here (so take notes!).

Connection Object Scope

Unless you knew better, you'd think that it might be cool to construct a Connection object in one process and hand it to another process to use as it saw fit. However, this is not an option—and it never has been. We've never been able to create DB-Library, ODBC, or OLE DB connections and pass the connection handles or objects to another process—even on the same system. While it's possible to marshal Recordset objects (even Command objects) across process boundaries, it's not an option for the Connection object.

There is, however, an alternative. You can remote a connection using the MS REMOTE provider—but with some limitations. The MS REMOTE provider enables the client to create a remote proxy on a remote server via HTTP, HTTPS, or DCOM. This remote proxy server makes the actual connection and performs the queries for you. It's like sending a trusted agent to an auction. The agent tells you what's up for bids and you communicate your instructions through the agent. If it works, you can end up with an antique worth owning; if not, you can end up with a stuffed moose head to hang in your hotel lobby. For security reasons, you should use the MSDFMAP.INI server-side security file whenever possible.

For example, you can code a remote connection like this:

 Dim cn as adodb.connection ' I don't use MSDFMAP.INI here, and this is very insecure so don't do it. Set cn = New Connection cn.open "Provider=MS REMOTE;Remote Server=http://MyServer;" _     "Remote Provider=SQLOLEDB;Data Source=SS_Hoo; " _     & "Initial Catalog=Pubs;User Id=sa;Password=;" cn.execute "create table MyDBTable(id int)" 

Setting the Provider Property

Most of the time I ignore the Connection object's Provider property—at least I don't reference it except when debugging a connection string. The Provider property can be set in a number of ways, and usually automatically. It can be set by:

• Not mentioning a provider anywhere: not referencing the provider in the connection string, not referencing the Connection object's Properties collection in code, and (especially) not referencing the Provider property itself. In this case the Provider property defaults to "MSDASQL"—the OLE DB provider for ODBC.
• Mentioning a provider in the Connection string. In this case, the Provider property is set to the current version of the provider. For example, if the connection string has Provider= SQLOLEDB, the Provider property is set to SQLOLEDB.1.
• Setting the Provider property directly in code.

Switching the Provider property can be troublesome. I have seen situations where once the provider is set (as when referencing the Properties collection), trying to change it by using a different "Provider=" value confuses ADO.

Connecting to OLE DB "Native" and ODBC Data Providers

MDAC 2.5 ships with native providers for at least three "traditional" SQL relational data stores—SQL Server, Oracle, and Jet (.mdb).^[2] The earliest versions of MDAC, required you to go through the OLE DB Provider for ODBC Data, which, in turn, used the appropriate ODBC driver to access these data stores. With MDAC 2.0, the developers think it's best to use native OLE DB providers. They are convinced that these access your data faster and impose a smaller disk and memory footprint. This is due (partially) to the fact that these native providers don't have to translate from ODBC-speak to the native tongue of the backend datasource. For example, the SQL Server provider is written to TDS, the Oracle provider to OCI, and the Jet provider to the Microsoft Jet Engine API interfaces.

I'll divide the discussion into two parts—first, connecting through the default OLE DB provider for ODBC, and second, connecting through OLE DB native providers. There are native providers for SQL Server, Oracle, and both Jet 3.5 and Jet 4.0 (as well as many others).

Connecting with an ODBC Connection String

Let's start with the OLE DB provider for ODBC. It uses a connection string with which you might already be familiar. One challenge you face as an ADO programmer (even an experienced one), regardless of the provider you choose, is creating a connection string to pass to the ConnectionString property. These can be very simple:

 cn.ConnectionString = "DSN=MyServer"        'Set the ConnectionString cn.Open , "Admin", "pw" 

In this case OLE DB takes most of the defaults. It uses the OLE DB provider for ODBC and opens the specified DSN by hitting the Windows Registry. The contents of that DSN Registry entry populate the "Extended Properties" Connection Property. The resulting ConnectionString ends up looking like this (behind the scenes):

 Provider=MSDASQL.1;Password=pw;User ID=Admin;Connect Timeout=15;Extended Properties="DSN=MyServer;Description=Local MSDE Server; SERVER=(local);UID=Admin;PWD=pw;WSID=BETAV8;DATABASE=biblio";Locale Identifier=1033 

After the connection opens, the Connection.Properties("Extended Properties") contains:

 DSN=MyServer;Description=Local MSDE Server;SERVER=(local);UID=Admin;PWD=pw;WSID=BETAV8;DATABASE=biblio 

Notice how ADO has inserted the UserID and password into the connection string. These were provided by the Connection.Open method—not stored with the DSN in the Registry. You can provide these values in the connection string and not have to specify them again in the Open method. And no, there is no way to encode these so that the source code maintains security of the password.

Note 

The password used in the Open is clearly available in these strings. Because of this, you might want to be careful about passing these objects and connections around.

You'll also notice that the Connection string includes a Provider=MSDASQL.1 argument. This tells OLE DB to use the OLE DB provider for ODBC version 1. Removing the ".1" would simply tell OLE DB to use the most current provider by that name. At this point no such provider exists—but that's not to say one won't exist in the future. Note that ADO automatically sets the WSID (Workstation name), although you can override this with your own setting. I use this property in client/server applications to help identify specific clients. However, if your connection is running in the middle tier, changing the WSID argument is pointless—Microsoft Transaction Server sets it for you to keep the connection strings the same so they can be pooled.

Connecting Using OLE DB "Native" Providers

On the other hand, if you aren't planning to connect to SQL Server through an ODBC Data Source Name (DSN), you probably won't want to because of performance and security issues I discuss later. This means, you'll have to create a more complex connection string—at least, one that is a little more complicated, because each provider requires that its own arguments be passed to OLE DB from ADO. Yes, many of the arguments are the same, but there are unique differences.

Remember that if you "touch" the Connection object's Properties collection in code or the IDE, the "Provider Name" Connection property is set under the covers. Although this doesnot show up in the Properties collection, the value is set to ODBC (MSDASQL). So, if your first touch sets the ConnectionString, the Provider Name property is set based on what's specified in the ConnectionString. However, this means that once you touch the Properties collection—even using the Locals Windows in the Visual Basic IDE—you won't be able to change the Provider Name later. Thus, if you try to open a Jet or native Oracle provider, you'll get an ODBC error.

Tip 

You can use the string "(Local)" in the ODBC connect string to refer to the local server, but this won't work for the Data Source argument in an OLE DB Connection string.

File-based Connections

When accessing file-based data sources, the Connection string can often simply point to the path and filename of the database file using the Data Source argument. However, you can also point to a file-based UDL or DSN using the File Name argument, as shown here:

 cn.ConnectionString = "file name=c:\biblio.udl" 

When working with Active Server Pages (ASP), you might want to make sure you fully qualify file path references so that your UDL file is stored outside of your Web site. Keeping with other unprotected files can expose your user ID and password to prying eyes (not good). In this case, place your UDL file outside of the /inetpub/ directory. Using Server.MapPath won't help, as it returns a directory relative to the virtual directory and you want to keep your common files outside of the virtual directory.

Connecting to Jet (Access) Databases

Let's take a brief look at the special issues involved in connecting to the native OLE DB provider for Jet databases. So you try to open a Jet database that has user accounts set up. You know that you have to provide a password and a UserID to the provider, but your application gets the following message: "-2147217843 Cannot start your application. The workgroup information file is missing or opened exclusively by another user." You're confused, so you check MSDN and find a description of the error message: "To ensure referential integrity in databases created by the Microsoft Jet database engine, your application must read the Microsoft Jet database's System.mdw file. Make sure the file is in the location specified in the SystemDB value of the \HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\Office\8.0\Access\Jet\3.5\Engines\Jet key in the Windows Registry."

Oh, swell. Microsoft expects developers to dig into the Registry to make sure that the database can be opened with a password. And then you notice that the Registry entry in the message isn't right. You have Office 2000 installed—they never updated the error message. Sigh. So you dig into the Registry and find the right entry (HKEY_LOCAL_MACHINE\Software\Microsoft\Office\9.0\Access\Jet\4.0\Engines), but it's set to "c:\PROGRA~1\MICROS~1\OFFICE\SYSTEM.MDW". Double sigh—a DOS shortname.

What ADO is complaining about is the Jet workgroup information file (WIF). This is a file the Jet engine reads when the database is first opened. The WIF contains information about the users in a workgroup including users' account names, their passwords, and the groups of which they are members. You have to tell Jet where this file is located by setting an "Extended" property before trying to open the connection.

In Microsoft Access 95 and earlier, preference information for each user (specified in the Options dialog box) is stored in the workgroup information file. In Microsoft Access 97 and later, preference information is stored in the Windows Registry. In Microsoft Access version 2.0, the default name for the workgroup information file is System.mda. It is sometimes referred to just as a "workgroup" or the "system database." In Microsoft Access 95 and later, the default name for this file is System.mdw.

Okay, all of this is very interesting, but how do you open a secure .MDB? Well, thanks to a developer on the list service I subscribe to, I learned that you have to manually set the pathname of the Jet System.MDW file in code. For example, you can address the "Jet OLEDB:System Database" property in the connection string:

 "data source=C:\Databases\Secure40.mdb;" _     & "Jet OLEDB:System Database=C:\Windows\System\System.MDW;" 

You also have to pass the correct UserID and password in the usual User-ID and Password properties to get connected.

Tip 

You have to set the Jet OLEDB:System Database property in the All tab of the Data View Properties dialog box to create a data link to a secure Jet database. When you do, the interface throws up a dialog box asking for the Admin password.

Keep in mind that having a dialog box open up is not what you want to happen if you are connecting from MTS or an ASP page. You will not be able to see the dialog box, and the site will appear to be frozen—or it will crash and go down in flames right away.

Post-Open Connection Strings

After the Connection object opens, ADO copies the provider-constructed connection string back to the Connection.ConnectionString property. This string includes all of the arguments you used in the original ConnectionString or in the Open method or both. It also includes all of the default arguments. This is the string that's cached and compared when using connection pooling in the middle tier.

Note that if ADO can't decide what to do with an argument, it might stick it into the "Extended Properties" section of the constructed ConnectionString. You'll notice that the constructed string contains both OLE DB and ODBC "extended" properties, as well as a host of default settings you never mentioned.

For example, this OLE DB ConnectionString:

 Provider=SQLOLEDB.1;Data Source=betav8 

returned the following post-open ConnectionString:

 Provider=SQLOLEDB.1;Password=pw;User ID=Admin;Data Source=betav8;Locale Identifier=1033;Connect Timeout=20;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BETAV8 

An ODBC Connection string starts out without an "Extended Properties" section:

 Provider=MSDASQL;Driver={SQL Server};Server=Betav8;Database=pubs;uid=sa;pwd=; 

but ends up with a fully populated Extended Properties section containing the OLE DB equivalents of the ODBC arguments:

 Provider=MSDASQL.1;Password=pw;User ID=Admin;Connect Timeout=20; Extended Properties="DRIVER=SQL Server;SERVER=Betav8 ;UID=Admin;PWD=pw; WSID=BETAV8;DATABASE=pubs;Network=DBMSSOCN; Address=betav8,1433";Locale Identifier=1033 

Using Visual Database Tools to Create Connection Strings

Frankly, to connect to sources other than SQL Server using the OLE DB provider for ODBC, I steal (er, leverage) a connection string constructed by one of the Visual Database Tools or the ADO Data Control (ADODC). Because the error messages you get from ADO are, shall we say, sparse or simply confusing, you'll often be frustrated trying to figure out why a connection is not working. Let's walk through the process I show my students for getting the Visual Database Tools to create these connection strings for you.

The trick here is to use the IDE to construct the Connection and steal its connection. You have a couple of options here. I tend to use the ADODC for this exercise, because it is easier to extract the ConnectionString once it is constructed, so let's start there.

1. Start a new Visual Basic project using the Data Access template.
2. Draw an ADODC control from the toolbox on Form1 (or on any form).
3. Right-click the ADODC, check "Use Connection String", and click "Build" on the General tab.
4. Choose the OLE DB provider from the providers list shown in the "Data Link Properties" dialog box (see Figure 4-1).
5. Click Next. Based on the provider you choose, you'll be asked a series of questions that capture properties specific to that provider.
6. After you have filled in the properties (such as filename or server name, UserID, and password), click Test Connection (see Figure 4-2). This verifies that the Connection string constructed for you will actually work (on your development system). Note that if the connection cannot be established, ADO won't be able to populate the Default Database list. If you get connected, the dialog box will populate the "Select the database on the server" dropdown list.

   
   Figure 4-2: Using the Data Link Properties dialog box to build a Connection String

Let's pause here a second, because there are a few points to keep in mind. First, this Data Link Properties dialog box (Figure 4-2) actually connects to the target data provider, and to do that, you often need a valid UserID and password. This means the data source (network and server) must be available to you, and the account must have rights on the server or in the data source file so that the tools can connect. In addition, you might have to set the network library to reach the provider—as when you connect to MSDE (it requires TCP/IP). See the "Using the Client Network Utility" section later in this chapter.

Second, remember that the error handlers built in the Visual Database Tools are pretty crude. They don't seem to understand what to do when connections are exhausted; they can return some pretty silly error messages that won't mean much to you at the time. Because Visual Basic does not always release connections while you're experimenting, you might try shutting down Visual Basic and starting over. This releases any connections being held by Visual Basic itself. It's not enough to simply stop your application. No, it's not enough to simply stop your Visual Basic application and restart.

Third, the UserID and password collected in the Data Link Properties dialog box is not persisted in the Connection string unless you check the "Allow saving password" check box. Otherwise the Connection string is simply used to establish the connection, and capture the list of valid databases, after which it is discarded.

Finally, remember that just because you get connected here on your development system with this set of properties, it isn't a guarantee that your application, component, or Web page will be able to connect when you deploy.

Now, we're ready to proceed to the next step in the process of creating a Connection string using the Visual Database Tools.

1. Click OK if you are satisfied that the connection will open. This returns you to the populated ADODC control. It should look something similar to Figure 4-3.
2. Okay, now we have a working Connection String. Simply select the string in the Use Connection String text control and paste it in your code.
3. Delete the ADODC from your form.

   
   Figure 4-3: The ADODC Property Page after the ConnectionString is built

The connection string we just constructed looks like this:

 Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Admin;Initial Catalog=biblio;Data Source=(local) 

However, this is an easy one. If you use the same technique to create a connection string for a Jet data source, it would look like this when first extracted from the ADODC.

 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False 

Hmmmm, still looks pretty easy. So when does this get ugly? Well, if you did not use the ADODC to capture the connection string, but instead used the Data View window and the Data Environment Designer to do the same thing, you might want to extract the DataEnvironment1.Connection1 object's ConnectionSource property as exposed in the Visual Basic IDE property page. If you capture that string, after having pointed to the same Jet database, you get a dramatically different string:

 Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Mode=Share Deny None;Extended Properties="";Locale Identifier=1033;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";JetOLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False 

Notice the plethora of pairs of double quotes. These are used to indicate empty arguments such as Password="". If you drop this into a Visual Basic string, you would certainly get errors. That's because Visual Basic interprets two double quotes as a single double quote. Clear? Well, anyway, if you want to use this connection string, you'll have to go through and remove all of the pairs of double quotes. But what if there are real quoted parameters passed? Well, that should not be a problem, because the Connection string constructor in the Data Environment Designer does not try to pass empty quoted strings for arguments with parameters. Bug? I suspect so.

You can see why I like the ADODC better. It's easier to steal (er, "leverage") its connection string.

Data Source Names and Microsoft Data Links

In the early days of ODBC, collecting and persisting connection string information was accomplished by using registered Data Source Names (DSNs). You can still reference these in ADO connection strings; I used one in an earlier example. The problem with this approach is that while you are fully capable of creating a DSN on your development system, getting one installed on the target system can be a royal PIA.^[3] You have several alternatives to using registered DSNs:

• Use a file-based ODBC DSN that contains the same DSN information (in .INI file format) but that is stored in a file instead of in the Registry. Benefits: easy to deploy and install—just copy the file. Problems: slow to access the first time, and easy for users to alter or destroy. It exposes internal UserIDs to prying eyes.
• Use a DSN-less ODBC connection. This approach means that you hard-code all of the required parameters in the connection string. You specify the specific server, driver, and all other required ODBC parameters. Benefits: fairly easy to code once you figure it out, and fast—very fast. Problems: you have hard-coded the server name in the application or component; but the server name does not change very often (or does it)?

  Basically, this is the approach we took when we passed an ADODC-generated connection string into our project.

   cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" _     & "User ID=Admin;Initial Catalog=biblio;Data Source=(local)" cn.Open , , "pw" 

  Tip 

  As a "best practice," don't break down strings using the concatenation operator as I have done in the example shown above. It causes Visual Basic to stop and reconstruct astring each time you do so. This approach is used to make the code more human readable for documentation, but it does not necessarily make it faster for Visual Basic to process.

• Use an OLE DB Microsoft Data Link. In this case, you can create a file with a UDL extension that can be stored anywhere just like a file-based DSN. In this case, however, it simply contains a (Unicode) connection string (and nothing else). These are created in Windows 9x or Windows NT 4.0 or earlier through the file explorer (File | New | Microsoft Data Link). On Windows 2000, just create a text file and give it the UDL extension. Double-clicking this new file opens the Data Link Dialog box we just discussed. The connection string constructed is stored in the .UDL file. Referencing its path and name using the File Name argument in a connection string opens the UDL file and uses its contents (the ADO-generated ConnectionString) to open the ADO Connection.

   cn.ConnectionString = "File Name=c:\biblio.udl" cn.Open 

ADO has improved quite a bit in version 2.5 in that it now caches all references to DSNs and UDLs—even if they come from files. This is why it takes longer to open file-based (and some Registry-based) data sources the first time.

Tip 

The File | New | Microsoft Data Link shortcut is not built into Windows 2000. But you can create a .TXT file and give it the UDL extension manually. After this change, Windows and ADO treat it as a UDL file.

Coding "Minimalist" Connection Strings

I tell my daughters (George and Fred) that ofttimes, less is more. With ADO connection strings this is still true. Connection strings can be very short:

 Cn.Open "DSN=Fred" 

This example assumes that your program can accept all of the default values. In this case, you assume the ODBC provider that the DSN is set to assume a "trusted" connection so that you don't even need the UserID and password, and that the SA has assigned the correct default database.

OLE DB connection strings can be short, too, but you have to specify the Provider first to prevent any confusion as the string is parsed:

 Cn.Open "Provider=SQLOLEDB;Data Source=MyServer;" 

Again, this assumes that you can accept the default settings. Here are some short sample connection strings for a wide variety of data sources:

• SQL Server Native OLE DB provider (sqloledb.dll):

   Provider=Sqloledb;Data Source=ServerXX;Initial Catalog=pubs;User Id=sa;Password=; 

• Jet / Access 97 version 3.51 (msjtor35.dll):

   Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\northwind.mdb 

• Jet / Access 97 version 4.0 (msjetoledb40.dll):

   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb 

• Oracle (msdaora.dll):

   Provider=Msdaora;Data Source=Serverxx.world; User ID=scott;Password=tiger" 

• To use an old ODBC, driver use the following syntax:

   "Provider=MSDASQL;Driver={SQL Server}; Server=MyServer;Database=pubs;uid=sa;pwd=;" 

• Or for an ISAM, such as Excel, use this syntax:

   "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)}; DBQ=D:\inetpub\wwwroot\testXL\pubs.xls;" 

• MSDataShape (msadds.dll):

   "Provider=MSDataShape;Data Provider=SQL Oledb;Data Source=ServerXX;Initial Catalog=pubs;User Id=sa;". 

• MS Remote (msdarem.dll)—you can execute three-tier parameterized hierarchies by combining MSDataShape and MS Remote:

   "Provider=MSDataShape;Data Provider=MS Remote;Remote Provider=sqloledb;Remote Server=http://your_IIS_server;Data Source=ServerXX;Initial Catalog=pubs;User Id=sa;" 

Creating a New Jet Database

In some cases, you might want to persist to a Microsoft Jet database. ADO knows how to do this, but you have to reference a different object library. Select Microsoft ADO Ext. 2.1 (or 2.5) for DDL and Security and use the following code:

 Dim cat as ADOX.Catalog Set Cat = New ADOX.Catalog Cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyNewDB.mdb" 

Converting from ODBC to OLE DB Connection Strings

When the OLE DB engineers started to define the arguments to be recognized by the connection string parser, they did not want to make the process of converting too easy, so they redefined a number of the arguments. Table 4-1 lists some of these redefined arguments.

Table 4-1: ODBC to OLE DB Provider Connection String Arguments

ODBC	OLE DB
UID=;PWD=;	User ID=, Password=
Database=	Initial Catalog=
Trusted_Connection=Yes	Integrated Security=SSPI;
Server	Data Source

SQL Server Provider (SQLOLEDB) Connect String Arguments

While the Visual Studio documentation lists the initialization properties for the SQLOLEDB Provider, some of the property descriptions are incorrect. It's important to note that it is now possible to create a SQL Server 7.0 connection string that contains all of the parameters you can specify when creating a registered DSN. In earlier versions of ODBC, this was not an option; many of the arguments were reserved for the Registry-based DSN. Table 4-2 lists the SQL Server provider properties and describes their purpose.

Table 4-2: SQL Server Provider (SQLOLEDB) Connect String Arguments

PROPERTY	DESCRIPTION
"Data Source"	The SQL Server to connect to.
"Initial Catalog"	The "default" SQL Server database.
"Integrated Security"	A string containing the name of the authentication service. This can be set to "SSPI" or to "" for Windows NT Integrated security (Secured Support Provider Interface).
"Locale Identifier"	SQLOLEDB validates the locale ID and returns an error if the locale ID is not supported or is not installed on the client computer.
"Password"	The password assigned to a SQL Server login. This property is used when SQL Server Authentication Mode is selected for authorizing access to a SQL Server database.
"Persist Security Info"	SQLOLEDB persists authentication values, including an image of a password, if requested to do so. No encryption is provided.
"Prompt"	SQLOLEDB supports all prompting modes for data source initialization. SQLOLEDB uses 4 (no prompt) as its default setting for the property. The four possible values are these: (1): Always prompt the user for initialization information. (2): Prompt the user only if more information is needed. (3): Prompt the user only if more information is needed. Do not allow the user to enter optional information. (4): Do not prompt the user. Return a trappable error to the application if the UserID and password pair cannot be validated.
"User ID"	A SQL Server login. This property is used when SQL Server Authentication Mode is selected for authorizing access to a SQL Server database.
"Window Handle"	A window handle from the calling application. A valid window handle is required for the initialization dialog box displayed when prompting for initialization properties is allowed.
"Connect Timeout"	SQLOLEDB returns an error on initialization if a connection to the SQL Server cannot be established within the number of seconds specified. Approximately 10 seconds is the lower limit.

The properties in Table 4-3 are extended properties defined by the native OLE DB SQL Server (SQLOLEDB) provider. These are all exposed in the Connection Properties collection and they are all read/write.

Table 4-3: ODBC "Extended" property arguments for SQL Server 7.0.

EXTENDED PROPERTY	DESCRIPTION
"Application Name"	The client application name.
"Auto Translate"	If True, SQLOLEDB performs OEM/ANSI character conversion when multi-byte character strings are retrieved from, or sent to, the SQL Server. If False, no conversion is done.
"Current Languge" (Note the incorrect spelling.)	A SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on the SQL Server or data source initialization will fail.
"Network Address"	The network address of the SQL Server specified by the property. These are used for IPX and NetBios protocols. See Q175472 on MSDN. It also appears that the Server Name followed by a port number is used for TCP/IP (BetaV9.1433).
"Network Library"	The name of the Net-Library (DLL) used to communicate with the SQL Server. The name should not include the path or the .dll filename extension. The default is provided by the SQL Server client configuration.
"Packet Size"	A network packet size in bytes. The packet size property value must be between 512 and 32767. The default is 4096.
"Use Procedure for Prepare"	Defines the use of SQL Server temporary stored procedures: 0: A temporary stored procedure is not created when a command is prepared. 1: Default. A temporary stored procedure is created when a command is prepared. The temporary stored procedures are dropped when the session is released. 2: A temporary stored procedure is created when a command is prepared. The procedure is dropped when the command is unprepared, or when a new command is specified for the command object, or when all application references to the command are released.
"Workstation ID"	A string identifying the workstation.
"Initial File Name"	This property is for future use with SQL Server 7.0 database files.

Using the Client Network Utility

In cases where your server does not know how to open or create a named pipe, or you need to reference a server somewhere on the Web, you'll have to launch the Client Network Utility (shown in Figure 4.4) to advise ODBC that it should use an alternative network driver to access the specific server. This is a requirement for accessing Microsoft Database Engine (MSDE) versions of SQL Server 7.0 running on Windows 9x. In this case, the OS does not know how to work with named pipes (the default network interface). Frankly, the TCP/IP protocol is faster than named pipes, easier to install, and more reliable. However, you can't use domainmanaged security unless you stick with named pipes.

Figure 4-4: Use the SQL Server 7.0 Client Network Utility to select an alternative protocol.

To use the utility, simply launch it from Start | Programs | Microsoft SQL Server | Client Network Utility. Enter the name of the server in question and choose the appropriate network interface from the option button list on the left.

Actually, there is another option to force the use of TCP/IP (or other protocols) instead of the default (named pipes): add a "network=" argument to your connection string. For example, the following code forces the use of TCP/IP:

 Network=dbmssocn; 

Setting the CursorLocation Property

By default, ADO does not create a cursor to handle any rowsets^[4] resulting from your queries, so the CursorLocation property does not really matter. ADO sets the default CursorLocation to server-side cursors (adUseServer). This means that if you want to use the client-side cursor library's features, you have to switch to client-side cursors (adUseClient). When we get to the Recordset chapters, I discuss the impact of this choice. It turns out that you can switch back and forth between CursorLocation settings whenever you get the urge.

Tip 

If you are having problems with clumsy queries being sent to the server (as evidenced by the SQL Server Profiler or your data provider's trace routines), try switching CursorLocation. The data providers have distinctly different approaches to data access based on where the cursors are being created—even in cases where you are using cursorless result sets. For example, the Prepared property issues I discuss in the Command Object chapter 5 can be mitigated by switching the CursorLocation to adUseClient.

Using Client-side or Server-side Cursors

I have been asked any number of times which are "better"—client-side or server-side cursors. I usually answer, "none-of-the-above." If you must use cursors, you need to consider how (and where) resources are to be used. Server-side cursors construct the rowset in a temporary location on the server, consuming server-side CPU and disk resources. You still have to move the rowset over the wire to the client, so in this regard there isn't much difference compared to client-side implementations. Client-side cursors instruct ADO to construct the keyset or static rowset in the client's RAM and disk space. Of course, in the middle tier, these resources might be the same as those shared by the local server.

Will you find significant differences in performance when using one type of cursor over another? I doubt it. An awful lot of work has gone into client-side cursor technology—the MDAC team is very proud of it. The (relatively) new shape provider is a testament to that. Many Microsoft developers are convinced that, to provide the most features and best performance, client-side cursors are the way to go. I'll let you decide for yourself. We'll look at a number of techniques that can only be implemented with server-side cursors and others only with client-side.

Note 

I've also seen some references to the use of the CREATE CURSOR TSQL syntax in a couple of books. However, this is not what server-side cursors are. That's because it's the ADO provider that generates the TSQL cursors—including server-side cursors when they are needed. While you can code and manage your own server-side cursors using this technique, it seems like a lot of trouble.

Setting Timeout Properties

ADO exposes both ConnectionTimeout and CommandTimeout properties. This is great, but have you ever noticed how some of these timeout settings do not seem to work very well? Perhaps that is because they're not particularly well understood.

• ConnectionTimeout starts when a LAN connection is established to the database server and ends when the data source provider creates a connection. This means that the network component is not part of the timing. Because of this, if your network cable has been cut in two by your spouse's vacuum cleaner, your timeout will be a function of the system NetworkTimeout setting. Too bad there isn't one. Network interface card drivers handle timeouts on their own—there is no way to change how long these cards sit and wait for a network packet that will never come. The MDAC team wants to add an OLE DB-managed thread to timeout the operation based on the timeout settings.
• CommandTimeout starts when the database server has accepted the command and ends when the database server returns the first record. If the server or network is busy (or not answering at all), this setting won't help get control again.

Both property settings are dependent on network timeout—neither ADO timeout property setting accounts for the time ADO and the low-level providers require to connect to the LAN hardware. Because the low-level NIC driver makes a synchronous network API call, and because this call does not return until the network timeout expires, the ADO timeout code is blocked until the network answers—if it ever does.

In any case, if the network is working, most connections can be established in less than five seconds. Setting the ConnectionTimeout property to a value much larger than 15 seconds is silly—and it causes the client (the human) to timeout. That is, if your client application makes the user wait too long for some operation, the user might give up and Ctrl-Alt-Del your application.

Note 

Command and Recordset objects created against the Connection inherit the Connection object's CommandTimeout property. While this value can be reset in a Command object, it cannot be set in a Recordset—you have to take the Connection (or Command) setting.

Prompting for UserID and Password

Humans don't initiate all ADO connections. No, I don't mean to imply that some of your users are rocks, animals, plants, or space aliens—even though some may behave as such. (Don't get me started on the lettuce-head I had to deal with last week.) What I mean is that some connections are launched by components that have no idea what "user" is requesting the operation. For example, a middle tier component simply connects to the data source and asks for data. It does not log on as "Fred" or any other specific UserID tied back to the user. Unless it uses "SA" (which is a very bad thing to do), the component uses a private UserID and password created specifically for this component and those like it.

However, some of you will feel the need to capture a UserID and password and pass these on to the ADO Connection to gain access to the DBMS. In my opinion, this approach is no longer needed and is usually not a particularly good idea as the following discussion explains. The problem you are trying to solve is user validation—should you permit the current human to access the database. There are several approaches to this problem—here are three suggestions:

• Use Windows network security to validate the user. This assumes that the user that logged in is the same user that's now using a program only available to the validated user. This permits you to use the Windows logon name as a UserID when opening a connection, but it assumes that the network protocol exposes this to the server—TCP/IP does not. This approach also assumes that the users are enrolled either in a group that the server recognizes or as individuals. The administration side of this approach can be fairly involved.
• Restrict access to the database so that end-users can neither see nor manipulate the DBMS base tables, views rules, triggers, queries, and so forth. In this case, only certain (perhaps secret) UserID/Passwords can access chosen stored procedures to perform queries and updates. This is my favorite approach, but it still requires a validation mechanism.
• Establish a secondary layer of security. This approach is often needed even in cases where users log on with specific roles. That is, after a user logs on to the application using an application-managed logon ID, either the application itself, or an underlying data access component, logs on with a secret UserID and password. This permits you to manage users with a much higher granularity of control. For example, you could restrict users' database access based on history, time of day, number of accesses, number of current users already logged on, job function, or number of legs.

Prompting Dialogs

Regardless of the type of user validation you choose, you do want to capture valid UserID and password strings yourself—you do not want the data provider to do it for you. That is, you do not want ODBC or OLE DB to throw up a dialog box (shown in Figure 4-5) requesting clarification of the UserID and Password (and database, data source, or server) that was supplied to the Connection Open method. If this dialog appears, the user is presented with an opportunity to guess another UserID/Password combination or to choose another database—and to keep guessing until they get it right. Not good.

Figure 4-5: Be sure to set the prompting level to avoid exposing this dialog box.

ADO controls this behavior using the Connection object's Prompt property. Fortunately, the default behavior is "no prompt," which instructs ADO to raise a trappable error. However, the Visual Database Tools developers felt, for reasons I do not agree with, that the default behavior should be "prompt always." This means that if you use the Data Environment Designer to open connections, you'll have to disarm the Prompt property. This can be done via the Visual Basic property window when you select the DataEnvironment Connection object. Just be prepared to log on to the database about six times when you do so. Note that the Data Environment Designer supports both runtime and design-time Prompt behaviors.

"Trusted" Connections

Both the ODBC and OLE DB providers support domain-managed security. That is, they can capture the Windows login name and password and pass them to the data provider as a surrogate UserID and password. This assumes that the user's Windows logon name can be used because he or she is "trusted" to log on to the database using your application. This approach is not supported in TCP/IP-only configurations, such as MSDE, or where the "user" is really a component in the middle tier—these components aren't logged into the network.

In ODBC you can add "Trusted_Connection=Yes" to the connection string, indicating that this NT (domain-managed or integrated) authentication is to be used. All other UserID and Password entries in the ConnectionString, or that are passed from the Open statement are ignored. The default behavior with the ODBC provider is "Trusted_Connection=No." This enables mixed mode authentication, which means SQL server or NT authentication is used—whichever works.

Using integrated security (NT domain authentication) with ADO is typically more secure than using SQL server authentication. This is because with client-driven security (where humans are involved), developers often end up (unadvisedly) persisting server UserIDs and passwords to disk, either in their code or in a UDL or DSN file. Anyone perusing these files is able to compromise the password. Unfortunately, the way we have engineered our tools and services makes this an easy trap to fall into. For example, it is impossible to use integrated security when running ADO in process with IIS because IIS runs under the local system account and has no authorization to get to the network. If you run your application out of process, you can use integrated security, but the performance with today's IIS implementation will be inadequate for many scenarios. There are ways to deal with this problem by setting up runtime variables containing UserIDs and passwords, but I would bet that many developers do not follow these practices.

OLE DB also supports a similar security scheme, but it uses a different argument in the ConnectionString—"Integrated Security=SSPI" is equivalent to ODBC's "Trusted_Connection=Yes."

I wrote an application to test and log the error messages returned by ADO when performing various operations including management of UserIDs and passwords.^[5] I started with simple connection permission issues and discovered a startling fact. Once the application connected using an ODBC DSN and a valid UserID and password (not necessarily SA), I no longer needed to provide a UserID and password again when connecting to the same DSN. If I provided a wrong UserID or password, ADO failed to connect, but just using the Open method with no UserID and Password would get me back in again. It turned out that my Windows logon name was being used in lieu of the missing UserID and password.

Using SA as Your UserID

Many of the security issues you'll face are masked when you choose to use SA as your UserID when accessing SQL Server. Because the SA account has rights to all resources on the server, it is permitted to do virtually anything—including dropping the entire database in a single stroke. It is a gross security violation to divulge the SA password. Without it, "ordinary" users are easily prevented from doing significant harm. Whether you're writing, speaking, training, or coding, make it a point to avoid using SA as your UserID.

Choosing the Default Database

When you connect to a DBMS such as SQL server that supports multiple, your UserID is associated with one of the databases on the system. If your SA is on the ball, the default database is changed to the working database you are most likely to use and that you have permission to access. If the default database (initial catalog) isn't preset, your default database could easily be "Master"—the system database where you (should) have no rights. Because you did not log on as "SA," you should not have to set the default database to be used by this connection. Remember that unless you specify otherwise in your query, your SELECT statements and action queries are executed against this default database.

Tip 

If you get the dreaded ConnectionWrite("GetOverLappedResult") error, you might try changing network protocols. Anything except named pipes seems to work, but "multiprotocol" or TCP/IP seem to work best in this situation—especially if both client and server are configured to use TCP sockets. In most scenarios, TCP/IP is the best choice in terms of speed, stability, and ease of management.

To select a new default database before the connection is opened, simply add an argument to the connection string. For example, to change your default database to "Fred", add "Database=Fred;" to an ODBC connection string or "Initial Catalog=Fred" to an OLE DB native connection string.

After the connection is open, it is possible to change the default database, but you have to be careful doing so. You cannot permit ADO to create another connection object for you in the process. Actually, ADO 2.5 seems to behave itself better than ADO 2.1 in this respect. If you were to try to change the default SQL Server database using the "Use <db>" TSQL command, and you used the Command object's Execute method to run the query, ADO might create another connection just to execute this query—especially if the current connection was busy working on another operation.

It's really safer to tell ADO that you're making the change—just set the "Current Catalog" property (ODBC or OLE DB connections) to the new database. ADO sends a "USE <db>" query for you on the current connection, but only if there are no results pending on the connection. After that, the queries executed will use the new default database. Of course, you could simply close and open the connection again, unless you were depending on other DB state settings. Because it's likely that the connection is pooled, this should not take a big performance hit.

The following code illustrates a typical Open method, and subsequently dumps the default database (current catalog) to show how the server changed it during the process of opening the connection. We then change it to another value using the Connection object Properties collection.

 cn.Open "dsn=LocalServer;", "admin", "pw"    ' Logs on with SA-defined default DB Debug.Print cn.Properties("Current Catalog")    ' See what the default DB is cn.Properties("Current Catalog") = "adoclass"    ' Change it to whatever… rs.Open "select ID from justinadoclass", cn ' Now you can reference objects in the new DB 

Tip 

If you get a "Create File" error when connecting with the OLE DB for ODBC provider (the default), it might simply mean that ADO could not locate the specified server. You did specify one, didn't you? And it's running, isn't it?

Managing Connection State

The default database is one of the database connection state properties maintained on the server and (to a limited extent) in the Connection object's Properties collection. These property settings are persisted for as long as the net connection to the server lives. If ADO creates another connection for you, it won't inherit any properties that you change after the connection is open. That's because it's created from the already constructed ConnectString property—even if you've changed one of the Command object properties, such as Current Catalog. However, if Microsoft Transaction Server pools the connection you create and close, other users will inherit the state maintained at the server (because the physical database connection is never closed).

This behavior could also occur on your ordinary client/server system because connection pooling is not restricted to Microsoft Transaction Server—it's implemented virtually everywhere unless you disable it. It's important that you realize the impact of changing state on the server in situations where it's possible that the connection could be reused by some pooling mechanism— even your own. The default database is not the only property that can wreak havoc with your system. Any time you use a SET statement in TSQL, the database state is changed. However ADO knows nothing about this change of state so it can't back out the change—even if it were designed to (it's not). For example, if you use SET NOCOUNT ON outside of a stored procedure, the change is persisted in the connection until it is closed. If you use a SET statement in a stored procedure, the state is reset when the stored procedure ends. When you create a temporary table or cursor, these are also maintained as part of the database state. Any application or component inheriting the connection gets all of these state changes, and it has no way of knowing what changes to the "virgin" state were made.

So, what happens when your development team starts tinkering with state? Well, this problem can be somewhat controlled because the connection won't be shared by other components or applications that don't share an identical connection string. However, if you plan to share connections across components (a desirable goal), you have to be far more careful. The symptom of corrupted state is components that work most of the time but fail at seemingly random times—perhaps after some other operation has just taken place—but not always. Ugly.

Using the Data View Window to Build Connections

The Data View window in the Visual Basic 6.0 IDE is designed to capture ADO provider connection strings and expose database schema to view and manipulate. Once you create a Data Link to one of the "enlightened" ADO providers, you'll see a list of tables (at least), and (perhaps) database diagrams, views, and stored procedures, as shown in Figure 4-6:

Figure 4-6: The Visual Basic 6.0 Data View window

Not all providers are smart enough to work with the Data View window—at least, not completely. If the provider is capable, you'll be able to use all of the Data View window features. Individual tables can be opened to examine and change data or schema—all using interactive graphical interfaces. When you make changes to the table, the Data View window submits the appropriate SQL query to the backend to make the changes. This means you can make changes to "live" databases, whether that makes sense or not. You'll also be given an option to simply save the SQL command script, which can then be submitted to your System Administrator (if you are still speaking, after having previously changed the database without her permission).

Want more information on the Data View window? My book The Hitchhiker's Guide to Visual Basic and SQL Server has a lengthy section on these tools.

IMHO Visual Basic tries to make getting at ADO data sources as easy as possible. Too bad the developers for this code didn't speak to each other any more than they did. As I see it, there are far too many "disconnects" between what was implemented and what could/should have been.

Okay, now you have a data link set up. The next time you start Visual Basic 6.0, this data link will be exposed in the Data View window (shown in Figure 4-6) again for you to use. Did you expect the Microsoft Data Links to appear here? I did. They don't. The data links created here are persisted in the Registry—the Microsoft Data Links are not.

Using the data links to manage schema is cool, but you can also extract the Connection string from the data link to use in your own code. Unfortunately, it's not as easy as using the ADODC. You might try to go to the Properties page of a selected data link, but the dialog box there only shows the connection string; you can't copy it to the clipboard. I guess you could write out the salient parts in longhand, but that seems like a big oversight to me.

So how do you get at this pretested connect string? Well, you have to use the Data Environment Designer. The Data Links are not visible programmatically until you drag one of the elements (table, view, stored procedure, database diagram) to the Data Environment Designer's window. (Figure 4-7 shows a stored procedure dragged from the Data View Window.)

Figure 4-7: The Data Environment Designer exposing a stored procedure dragged from the Data View window.

Try this:

1. Start a new Visual Basic Datatemplate project. This adds the ADO 2.0 reference, a Data Environment Designer, and a Data Report Designer to your project. You can remove the Data Report Designer if you don't need it.
2. Establish a data link to your favorite data source (see Figure 4-8). In this case I'll connect to the Biblio test database on my local (MSDE)

   
   Figure 4-8: Using the Data View window to create a new Data Link

   SQL Server. Simply click on Add a New Data Link and fill in the dialog boxes as we discussed when working with the ADC to create a ConnectionString. Yes, the Data View window uses the same OLE DB dialogs. (See Figure 4-8.)

3. When you are finished filling in the OLE DB provider properties, click OK and name your new data link. Use a name that reminds you of what it connects to; DataLink1 won't do.
4. Click the Tables icon under the new data link to expand the visible tables. If you can't see any tables, it could be that your provider is not smart enough to work with the Data View window, or that you don't have permission to view low-level tables. However, it's more likely that the tables will be visible despite the fact you don't have permission to access them. You won't find out about the permission violations until runtime. If you aren't the owner of the tables, you won't be able to change them using the Visual Database Tools.
5. Double-click the Data Environment Designer in the Project window to open a new Data Environment Designer window.
6. Select one of the tables and drag it to the Data Environment Designer window. At this point the Data Environment Designer and ADO construct a new Connection object (Connection2) based on your data link connection string and create an ADO Command object wrapped as a method of a DataEnvironment object.
7. To extract the data link connection string for Connection2, simply click the Connection2 icon in the Data Environment Designer window, press F4 to open the Visual Basic property page, and copy the ConnectionSource property to the clipboard—and into your application's code.
8. Remember to remove any extra double double-quote strings before trying to use the string in Visual Basic.

Using the Data Environment Designer

So, now that you have a Data Environment Designer set up in your Visual Basic project, how can you best use this in a Visual Basic program? Unfortunately there are a number of disconnects here that make it tough to use the Data Environment Designer efficiently. Sure, you can create applications using it, but I have had too many developers report mysterious problems and disappointed managers. Because of these poor reports, the Data Environment Designer no longer has a place of distinction on my "Best Practices" list. Don't get me wrong—there are some redeeming features of the Data Environment Designer, but there are also a number of issues that make it a challenge to use if you step over the yellow tape. See those ski-tips sticking out of the snow over there? That's Jim. He tried to use the Data Environment Designer on the other side of the DataEnvironment double-diamond area.

Understanding how the Data Environment Designer works can bring many of your expectations back to reality. First, remember that Data Environment Designer, like so many other tools, is simply a way of exposing ADO to the developer, but the Data Environment Designer does this by using another runtime engine. That is, a Visual Basic designer (unlike a Wizard) does not create any source code to tinker with. The DSR file contains binary instructions describing how the DataEnvironment runtime engine should construct the Connection, Command, and hierarchical Shape objects. This means that you won't be able to change the DataEnvironment object properties in code—at least not to any great extent. I've seen many people try to change the connection string or Command properties only to discover that their system is getting really confused.

Using Data Source Controls

Based on feedback I've received over the years, many developers migrate away from the use of Data Source controls, such as the ADO (ADODC), DAO (Data), and RDO (RDC) data controls. It's not that they don't have a place; it's just that when we start evolving our applications to handle more sophisticated situations, we find that these controls get in the way. Because the controls try to perform so many operations "for us" behind the scenes, we have too little control over what needs to be done. In the long run, we end up writing almost as much code anyway—and writing "around" the shortcomings and features of these "smart" controls.

Connection Query Execution Methods

Okay, now we're ready to try to run some queries against the Connection object. Nope, you don't need to construct a Command object, but if your query returns a rowset we'll have to build a Recordset—I discuss that in detail in the chapter "Working with Recordsets".

Suppose you opened the connection to run a query or run an action query. There are a few ways to do this after the connection is open.

• Use the Connection object's Execute method to execute a query string. This returns rows to a Recordset that you constructed ahead of time.
• Use the Connection object's Execute method to execute an action query that does not return rows.
• Execute a named ADO Command object against the Connection object. In this case, you pass in the Command arguments and a Recordset that was set up earlier.
• Execute any stored procedure by simply using it as a method of the Connection object. ADO accepts the stored procedure arguments as method arguments, accepting a named Recordset object as the last argument. (Cool!) We discuss the use (and implications) of this technique in the chapter "Working with Recordsets".

Because we haven't talked about the Command object yet, we'll postpone discussion of executing Command objects until we do, but let's take a look at the Connection object's ability to execute an SQL statement, run a maintenance query, or simply dump a table's rows back to a Recordset.

• The basic syntax for the Connection object's Execute method calls for:
• Command text—this argument can be a string containing an SQL statement, a table name, the name of a stored procedure, a URL, or text that makes sense to your data provider.
• Optionally, you can provide a Long variable to receive the number of rows affected by the query—assuming the provider sends back a result set.
• And optionally you can pass in a few parameters that help ADO decide how to execute the query. Basically, these describe the command text argument and tell ADO how to process the query. These are discussed in the Chapter 5.

For example, a typical Execute method invocation looks similar to this:

 Set rs = cn.Execute("select City, State from publishers",,adCmdText) 

And for "action" queries, it looks something like this:

 cn.Execute"truncate table framis",, adExecuteNoRecords 

Because ADO manufactures the Recordset object for you, it's always a read-only, forward-only, "cursorless" resultset.

Tip 

If you need a Recordset object with more functionality, create a Recordset object with the desired property settings and use the Recordset object's Open method to execute the query and return the desired cursor type.You can also use your own Recordset objects with the Command method. I discuss this in Chapter 5.

Connection Pooling

ODBC implemented connection pooling quite some time ago, and OLE DB followed suit in the ADO 2.1 timeframe. This feature was developed to help increase throughput on Microsoft Transaction Server and IIS-managed servers. That is, instead of actually closing connections established by middle-tier components and Web pages, the connection pooling subsystem simply marks the connection as "reusable". If another application tries to open a connection with a connect string that matches an open connection (matches exactly—byte for byte), the connection handle is simply passed to the new "user". It seems that by default, connection pooling is enabled. If you don't want it, you can change the CPTimeout value in the Registry (HKEY_LOCAL_MACHINE\ Software\ODBC\ODBCINST.INI\SQL Server) to 0—or simply remove the entry.

By default, ADO uses OLE DB session pooling to maintain a pool of connections to the database. In some cases, you might want to use ODBC connection pooling instead of OLE DB session pooling.

To enable OLE DB session pooling in a desktop application, make sure that one reference to a connection object is retained at global scope in your Visual Basic application. In other words, Microsoft expects you to keep a connection open at all times for each application that wants a pool. Keeping a connection open maintains a reference to the IDataInitialize interface—the OLE DB Service Components where session pooling occurs. In MTS and ASP, however, you don't need to do this. These environments go hand-in-hand with ODBC Connection Pooling and OLE DB Session Pooling without making you jump through any hoops (at least most of the time). Microsoft recommends using session pooling unless there is some bug or other issue that prevents you from doing so. For more information on "free-session" pooling, consult the MSDN documentation and Web site—there are several articles on free-session pooling there.

Switching Connection Pooling in ODBC

To enable ODBC connection pooling from a Visual Basic/ADO application, Microsoft wants you to jump through a few hoops—with bricks tied to your ankles. There are two necessary steps:

1. If you have ODBC Administrator 3.5 or later (and you should by now), open the ODBC Data Source Administrator from the control panel shown in Figure 4-9. Select the Connection Pooling tab. Find the driver that you are using in the list and double-click it. Check the option "Pool connections to this driver" and enter a timeout value in the dialog box.

   
   Figure 4-9: Using the ODBC Data Source Administrator to select connection pooling options.

2. Add an ODBC API function call to SQLSetEnvAttr in your application with the appropriate options to enable ODBC connection pooling for the process. Yes, I said an ODBC API function call. Sigh. This function should only be called once per process and must be called prior to executing any ADO code. If you aren't comfortable with the ODBC API, you can read all about them in the Hitchhiker's Guide and/or check out the following code example.

Note 

This code example was leveraged from a Microsoft Knowledge base article (Q237844). I dropped this into a class and put it out on the CD if you care to look there. Also note that this does not need to be called if you are opening your connections inside of MTS or ASP—they take care of this.

This code creates the SQLSetEnvAttr value using the ODBC API. Good luck!

 Dim rc As Integer Const SQL_ATTR_CONNECTION_POOLING = 201 Const SQL_CP_ONE_PER_DRIVER = 1 Const SQL_IS_INTEGER = −6 Const SQL_CP_OFF = 0 Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" ( _                       ByVal EnvironmentHandle As Long, _                       ByVal EnvAttribute As Long, _                       ByVal ValuePtr As Long, _                       ByVal StringLength As Long) As Integer Public Function TurnOffPooling() As Integer        TurnOffPooling = SQLSetEnvAttr(0&, _                   SQL_ATTR_CONNECTION_POOLING, _                   SQL_CP_OFF, _                   SQL_IS_INTEGER) End Function Public Function TurnOnPooling() As Integer     TurnOnPooling = SQLSetEnvAttr(0&, _                   SQL_ATTR_CONNECTION_POOLING, _                   SQL_CP_ONE_PER_DRIVER, _                   SQL_IS_INTEGER)     If rc <> 0 Then         Debug.Print "SQLSetEnvAttr Error " & rc     End If End Function 

If you're serious about using SQLSetEnvAttr, be sure to RTFM: page 943 of ODBC3.0 Programmer's Reference, Volume 2 (Microsoft Press) discusses the details of the API call and the ramifications of the call on your system and on everyone else's system. You've been officially warned.

Switching Connection Pooling Options in OLE DB

For OLE DB, you can "permanently" deactivate connection pooling—at least until some other program re-enables it. (I don't recommend dinking with the Registry because, like self-directed brain surgery in the mirror, it is fraught with danger. About the time you think you have it right, your daughter comes in and bumps your elbow while looking for her makeup.) However, in this case you have to locate the provider's GUID (search by name in the Registry). For example, the SQLOLEDB provider entry is located at: HKEY_LOCAL_MACHINE\Software\CLASSES\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}. Be sure to memorize this GUID—just so you can impress your geeky friends at the bar. After you find the GUID, you can (safely?) change its OLEDB_SERVICES key using the values in Table 4-4:

Table 4-4: OLEDB_Services Options Registry Settings

SERVICE	VALUE
All services (the default)	0xffffffff
All except Pooling and AutoEnlistment	0xfffffffc
All except Client Cursor	0xfffffffb
All except pooling, enlistment, and cursor	0xfffffff0
No services	0×00000000

I don't know how uncomfortable you are about changing the Registry, but I certainly don't like to. It not only affects your code, but everyone else's, too. That's why "best practices" dictate that you simply amend the connect string to set the "OLE DB Services" argument to one of the same options, as shown in Table 4-5:

Table 4-5: OLEDB_Services ConnectionString Arguments

SERVICE	VALUE
All services (the default)	"OLE DB Services = −1;"
All except Pooling and AutoEnlistment	"OLE DB Services = −4;"
All except Client Cursor	"OLE DB Services = −5;"
All except pooling, enlistment, and cursor	"OLE DB Services = −7;"
No services	"OLE DB Services = 0;"

For additional information on connection and session pooling, consult MSDN. The following Knowledge Base articles might also be of interest: Q189410, Q237844, and Q169470, and one of the best: Q176056 "INFO: ADO/ASP Scalability FAQ". See http://www.msdn.microsoft.com. No, not all of them are on the MSDN CD(s).

Warning 

When ADO feels it's necessary, it might spawn a new Connection to carry out some request for you. When this happens, ADO creates a new transaction scope and database connection quietly behind the scenes and closes it when it's done.

Tracing Orphaned Connections When Pooling Connections

There has been a lot of advice passed around about connection pooling and how to figure out whether the connections are being reused, abused, or unused and left to fend for themselves. I think this snippet pulled from my mail is probably one of the best lists so far:

1. Use the SQL Server 7.0 Profiler (shown in Figure 4-10) to determine if your connections are constantly getting created and never reused. You should be able to use the Connection ID and SPID columns to verify this:

   
   Figure 4-10: Using the SQL Server Profiler to trace TSQL querie

2. Check to see whether any of these connections time out after 60 seconds or whether they remain active but never appear to be reused. If connections are never reused and time out after 60 seconds, then session pooling is not correctly reusing connections.
3. Next, try to verify whether all connections are only used once, or whether only a few are left unused but they are accumulating. If some accumulate and never time-out, then an interface probably is not being released correctly, so the connections don't get put back in the pool.
4. If only a few are left unused but they are accumulating, see if they can be tied to any particular activity. Use "dbcc inputbuffer(spid)" in Query Analyzer to help see the last activity done on that particular connection.
5. Check whether any transactional work is being done. Try watching for some pages having an "@transaction=required" tag, while others do not.
6. Check whether the authentication of the ASP pages is Anonymous, Basic, or NTLM. Basic and NTLM can cause connections to be made on a per-user basis, thereby negating any benefit of session pooling. On a multiprocedure system, this can cause connections to accumulate very rapidly.
7. Add "Use Procedure for Prepare=0" to your connection strings and see if it helps. This prevents the provider from attempting to create temporary stored procedures in the process of executing the query.
8. And of course, there's always the last resort; try disabling session pooling.

Disabling session pooling can be accomplished in a number of ways. But this list of suggestions seems to make sense. Remember that with ADO 2.1, or later service packs of ADO 2.1, all ODBC drivers by default are enrolled in session pooling when using the OLE DB Provider for ODBC (MSDASQL). (Note that if you are using an ODBC driver from ADO you are using MSDASQL.) Within a user application, this means that if you have at least one connection open, subsequent connections opened are automatically pooled, even if the ODBC connection pooling for the driver is turned off in the ODBC Administrator. If connections are pooled, they remain open in the pool, even after you close them, for a hard-coded 60 seconds.

To attempt to disable pooling, here are a few things to try:

• Ensure that ODBC Connection pooling is turned off for the ODBC Driver. You can check this via the ODBC Administrator "Connection Pooling" tab (see Figure 4-10)—you should see <not pooled> next to the driver name. If not, double-click the appropriate driver (the one you're using) in the list box and turn it off.
• Add "OLE DB Services = −2;" to all connection strings to turn off session pooling for ADO. See "Pooling in the Microsoft Data Access Components" white paper in MSDN for more information on the OLE DB Services setting in ADO. A typical connection string would look something like this:

   conn.open "DSN=MyDB2DataSource;UID=Me;PWD=My;OLE DB Services=−2;" 

The rule requiring one persistent connection per set of unique user credentials applies only if your application is not running under either IIS (4.0) or MTS (2.0). IIS maintains the pool as well as MTS (at least until the connection times out or is no longer reused). That is, outside of these two environments, the (OLE DB Resource) pool goes away when the last connection in your application that has the unique set of user credentials used to create the pool is closed. MTS and IIS make the pool persistent.

Persistent connections can be a PIA—especially if you design your app not knowing that this is a requirement. However, remember that ODBC Connection Pooling jumbles connections together, regardless of authentication and underlying source driver/datastore. When your application requests a connection, it is blocked while this potentially huge pool of connection strings is searched for a match. In contrast, session pooling is optimized so that this doesn't happen. The only pool searched for an open connection is the pool (if it exists) that matches your user authentication. Session pooling has less locking contention on any given pool, which means that you should get your connection back much faster. OLE DB also manages "sets" of pools, depending on the number of processors in the computer, to further optimize finding a free connection as fast as possible.

Opening a Connection Asynchronously

In a traditional client/server application, I often (usually) open the Connection object asynchronously. Yes, this qualifies as another "best practice" because it makes better use of your user's time. In my applications, it gets the form painted faster, and no, I don't enable the controls on the form that require access to the connection to work—not until the connection is open.

The Visual Basic code for connecting asynchronously is simple—just add the adAsyncConnect option to the Open method:

 cn.Open "dsn=localserver", "admin", "pw", adAsyncConnect 

Note 

Not all of the providers support asynchronous operations, and there are no additional properties in the Connection object to inspect to determine whether you can connect or run queries asynchronously.

Connecting asynchronously starts ADO working on the process of getting the connection established while my client thread finishes other work, such as completing Form_Load. While the connection operation is in progress, the Connection object's State property is set to adStateConnecting.

After ADO completes connecting, successfully or not, ADO fires the ConnectComplete event and turns off the adStateConnecting bit in the Connection object's State property. If the event handler returns an Error object set to nothing, the connection operation succeeded. If something went wrong, the Connection object's Errors collection has the details. Along with a pointer to the Error object, you get (at no extra charge) a Status object that also reports whether or not the operation worked. Checking Status for adStatusOK does the job.

So do you have to create an event handler to check whether the connection operation has completed? Nope, you can simply check the State property, waiting for the adStateOpen bit to turn on (and the adStateConnecting bit to turn off).

Testing for a Live Connection

Want to see if the connection is still open? Well, there aren't any properties on the Connection object that change when the connection is lost. And, you can't just check the State property, because ADO does not poll or check on its own. You might feel the need to poll yourself. Try sending a low-impact query, such as SELECT 1. This query takes about three packets and is fairly efficient. If it works, the connection is still open. Not that it'll be open when you need it, but it won't time out for another n seconds.

^[2]The list of OLE DB providers included in my version of Visual Basic and ADO 2.5 has twelve providers in it, including the OLE DB Provider for ODBC. There are two versions of the Jet provider supplied—one for Jet 3.51 and one for Jet 4.0.

^[3]PIA (a technical term).

^[4]A rowset is simply a set of rows containing the columns specified in the SELECT statement and meeting the membership requirements of the WHERE clause of a query.

^[5]This application is available on the CD supplied with the book. Search for ConnErrTrap.exe.

---

Team-Fly