Table of Contents
| Pt. I | Understanding Data Access Interfaces | |
| Ch. 1 | The Road Ahead | |
| Ch. 2 | Data Interfaces | |
| Ch. 3 | Data Access: A Jump Start | |
| Ch. 4 | Choosing an Architecture | |
| Pt. II | Designing SQL Server Applications | |
| Ch. 5 | Planning Your Design | |
| Ch. 6 | Making Connections to SQL Server | |
| Ch. 7 | Designing and Building Queries | |
| Ch. 8 | Retrieving Data and Working with Result Sets | |
| Pt. III | Using DAO with the Jet Database Engine | |
| Ch. 9 | The Jet Database Engine and the DAO Model | |
| Ch. 10 | Data Access Objects Up Close | |
| Ch. 11 | Configuring and Tuning the Jet Engine | |
| Ch. 12 | Using DAO/Jet to Get Connected | |
| Ch. 13 | Using DAO/Jet to Access Data | |
| Ch. 14 | Executing Transact-SQL Queries | |
| Ch. 15 | Using the Data Control | |
| Ch. 16 | Understanding the Jet Query Processor | |
| Ch. 17 | Using ODBCDirect | |
| Ch. 18 | Jet: A Traveler's Diary | |
| Pt. IV | Using Remote Data Objects | |
| Ch. 19 | Understanding Remote Data Objects | |
| Ch. 20 | Remote Data Objects Up Close | |
| Ch. 21 | Getting Connected with RDO | |
| Ch. 22 | Using the RDO UserConnection Designer | |
| Ch. 23 | Building Result Sets with RDO | |
| Ch. 24 | Optimistic Batch Updates | |
| Ch. 25 | Taking RDO Off Road | |
| Ch. 26 | Maintaining Data with the RDO Interface | |
| Ch. 27 | Using the RemoteData Control | |
| Pt. V | Using ADO and OLE DB | |
| Ch. 28 | Understanding ADO and OLE DB | |
| Ch. 29 | ActiveX Data Objects Up Close | |
| Ch. 30 | Getting Connected with ADO | |
| Ch. 31 | Building ADO Recordsets | |
| Ch. 32 | Working with ADO Events and Asynchronous Operations | |
| Ch. 33 | Taking ADO Off Road | |
| Ch. 34 | Migrating from RDO to ADO | |
| Pt. VI | Using the New Tools | |
| Ch. 35 | Using the Transact-SQL Debugger | |
| Ch. 36 | Using the Data View Window and Query Builder | |
| Ch. 37 | Taking the Data Environment Designer Out for a Spin | |
| Ch. 38 | Test Driving the Data Object Wizard | |
| App. A | Setting Up the Test Databases | |
| App. B | SQL Server Error Codes Decoded | |
| App. C | SQL Server Tips and Tricks | |
| Dusty Archives (Chapters on the Companion CD) | |
Read an Excerpt
Chapter 21: Getting Connected with RDO
You don't have a connection manager to worry about when you use the Remote Data Objects (RDO) interface. You make and break all connections yourself, whenever you need to. But you're still faced with the same challenges when it comes to managing connections. On the one hand, you don't want to overload the server and lock out other users by making too many connections. On the other hand, unless you use server-side cursors, Microsoft SQL Server doesn't support multiple operations on a single connection, so you often have to open additional connections to update rows or perform other operations. You also have to be concerned about leaving idle connections open while the user decides what to do, but you don't want to slow him down when he finally figures out what he wants-or comes back from his two- hour lunch. Properly managing connections can mean that an application is scalable to (or beyond) several hundred users or limited to a few dozen (or less).
Locating and Naming the SQL Server
Like other ODBC-based interfaces, RDO connects to your SQL server using the information kept in the ODBC data source name (DSN) or a file-based DSN or using the information you provide in the connect string. Personally, I'm not always in favor of establishing a permanent DSN, since it can be a source of trouble when your application is installed and run in the field. To work around this problem, you can take one of several approaches:
- Use the ODBC registry functions to determine whether a suitable DSN already exists This technique is really a lot of trouble, as far asI'm concerned, so I rarely use it. You can use the ODBC API SQLDataSources method to list registered DSN entries.
- Use the rdoRegisterDataSource function to create a new DSN entry This technique is also troublesome, but it's not as bad as using the ODBC registry functions.
- Use an already established DSN. Most people who use this approach install the DSN themselves or have the user install it when the application is installed. This approach can be risky, though, and it can lead users to phone you in the middle of the night.
- Launch the ODBC Control Panel applet directly from your application, and tell the user how to set up a DSN Yeah, right. Unless your users are a bit more comfortable than mine are with this sort of thing, this isn't a viable option. Letting some users take this route is like turning a four-year-old loose with a 17- blade Swiss Army knife.
- Create a file-based DSN. This relatively new approach is virtually identical to the DSN-less connection technique explained below, except that it requires a file to store the connection information instead of requiring that you provide the information in the connect string itself. When you point to this DSN at run time, ODBC fills in the blanks just as it would with a DSN-less connection. To use the DSN, you can use the ODBC connection management dialog boxes (launched by the ODBCControl Panel applet). The file- based DSN should work fine until the connection file is lost, or not installed with the application. Filebased DSNs can also lead to security problems because it is fairly easy to view or tinker with the file's ASCII contents. This is the technique of choice when you work with middle-tier components in which registered DSNs are not always available. Note that only "system" DSNs are suitable for use with some Microsoft Active Server Pages-based connections.
- Don't use a DSN entry at all, but include all the driver information in the Connect argument of the OpenConnection method This approach is far less risky. All it assumes is that the name of the SQL server doesn't change, since you've hard-coded it into the application. The drawback to this technique is that it requires you to take all of the default DSN settings (we'll look at these a little later), but it's the fastest way because it doesn't require a Registry hit. It also assumes that the name of the server is cast in stone. Since I'm still connecting to servers that were set up eight years ago, this might not be a problem.
- Put the name of the SQL server in a Registry entry (my preference) or in an INI file, and fetch the name when the application is run the first time or when the SQL server can't be located When an OpenConnection method fails, the message returned often says that the SQL server couldn't be found- which can mean any of a dozen things. Generally, the name of the server doesn't change after installation, but if the same application is used to access more than one SQL server, permitting the user to select from a list of valid servers is an alternative.
- Create a UserConnection object to manage the connection Actually, since the UserConnection designer was introduced in Microsoft Visual Basic 5.0, getting connected to SQL Server couldn't be made much easier. We devote a chapter to the UserConnection designer a little later, but in essence, this designer captures all of the connection parameters at design-time and automatically builds the correct Connect string for you at runtime. It supports all types of connections discussed so far including DSN-Iess connections.
Establishing a Connection
Once you know which SQL server to connect to, you have a couple of options for establishing the actual connection. In RDO 1.0, you can use the OpenConnection. method to create an rdoConnection object, which establishes a physical link to the data source. While you can still use this technique in RDO 2.0, you can also declare a stand-alone rdoConnection object and use the EstablishConnection method to hook it to a selected SQL server. This is what the UserConnection designer does for you-except without you having to build the Connect string in code. Let's look at these two techniques a little more closely. To establish a connection, you must somehow identify the network location of the data source, as well as the driver type. Your code also provides a number of parameters for logging the user on to the database. By choosing an appropriate Prompt argument, you can program the ODBC Driver Manager to prompt the user for missing arguments and thereby prevent the use of alternate arguments. However, as I've said repeatedly in my lectures, I feel extremely uncomfortable exposing these dialog boxes. On occasion, the previous login ID is shown, and the next user only has to supply the password. For some unusual applications, this might be just fine. For others, even login names are secure. For these reasons, I always set the rdDriverNoPrompt option to prevent any dialog boxes from appearing. I then must add code to trap the situations in which the user doesn't get logged on. No, this option isn't available in DAO, but it is supported in ADO-well, sort of. ADO's support of ODBC's prompt behavior is generally lacking-especially when you use the Data Environment designer-but we'll talk about that in Chapter 37. The OpenConnection method accepts the following arguments. They can be supplied by name, supplied with Visual Basic named argument syntax, or supplied positionally.
- The dsName argument. This argument indicates the name of the registered data source. It points to a valid, registered DSN entry (which can include the new file-based DSN). If you supply all the DSN-related parameters in the connect string, you must pass an empty string as the dsName argument. If you supply the dsName argument value, you can use it to identify the connection. Since you might connect to the same DSN more than once, however, or simply pass an empty string, you can't really depend on the name to index the rdoConnections collection. You can always use the rdoConnection object's ordinal position in the rdoConnections collection to choose an open rdoConnection object.
- The Prompt argument. This argument indicates whether the user is permitted to supply arguments to ODBC connection dialog boxes. If you don't want the user to supply a different DSN, user name, password, or default database, use the rdDriverNoPrompt constant as the Prompt argument. But if the Connect and dsName arguments don't lead to a connection (which you have to deal with in code), a trappable error results and the user doesn't see any ODBC-generated dialog boxes to assist with the connection. This feature is unique to RDO, ADO, and ODBCDirect because the DAO/Jet interface is stuck with Prompt if required, which displays the dialog boxes whenever there's a problem with the login information or with establishing the connection.
- The ReadOnly argument. This is set to False if the user expects to update data through the connection. Setting it to True can improve performance because the ODBC drivers can skip code needed to support updatability.
- The Connect argument. This argument gives the ODBC Driver Manager either the entire set of ODBC connection parameters or just the parameters that are not already supplied by the DSN entry. These parameters can include username, password, default database, DSN (which overrides the value provided in the dsName argument), and others. The list of arguments has been expanded for ODBC 3.x. These arguments are described in Table 21-1.
The ODBC Driver Manager uses these Connect string arguments to establish the connection. If you don't provide enough of these arguments, ODBC can't figure out how to connect and it might expose dialog boxes to capture these missing parameters. The ODBC prompt behavior determines whether or not these dialog boxes are shown. In some instances, you might get away with just providing the DSN itself because it can encapsulate enough information to get connected. If you leave out the DSN, you must provide a lot more of the arguments...
loading...