ODBC Data Sources
 |  |  |  |
 |
| |||||||||
 |
| ||
ODBC (Open Database Connectivity) is a well-known methodology for connecting to a database that was introduced in 1992. Structured Query Language (SQL) is used as ODBC’s data access language, and while there are many variations of this language, the core commands are somewhat universal.
At a very high level, ODBC can be described as middleware that sits between a data source and an application. In our case, it can be used as the go-between for a data source and Crystal Reports. As depicted in Figure 10.3, there are four layers in the ODBC model.
From this diagram, you should get the sense that a single application like Crystal Reports can access a variety of different data sources as long as they are registered and managed by the ODBC driver manager. The driver manager’s job is to respond to Crystal’s request for data from a configured data source, locate the correct DLL to use to fulfill the request, and then pass the request down to the DLL layer. At that point, the DLL layer interacts with the data source layer, retrieves the requested data, and returns it to the Crystal Reports application.
Figure 10.3. ODBC layers
If you were to ask a roomful of people who have worked with ODBC to give you a one-word description of it, you would hear words like easy, slow, cumbersome, reliable, mandatory, functional, free, and probably many others. The gist of the message is that, yes, ODBC access is slower than being in the database directly, but it sure does simplify the job of getting to the data. This is no surprise. Take a second look at Figure 10.3 and you’ll realize that it is slow(er) because of the two layers between the application (Crystal Reports) and the data source. The trade-off for this slower access (by the way, we’re talking milliseconds slower, not hours slower) is consistent, reliable access that doesn’t have to be coded anew each time a different application wants to get to the data. Tried, true, tested code. Sounds like a good trade against fast any day.
ODBC also offers the advantage of providing an upgrade path for a report. Many reports start out life retrieving data from a smaller database like Microsoft Access and then get promoted to official duty in a larger database like Microsoft SQL Server. If the table structures between the two databases are identical, the process of converting a Crystal Report from one data source to another is easy. The goal is to not have to start from the beginning again on a new report.
| Warning | If table structures are not identical, changing an ODBC data source may render a Crystal report unusable. |
Putting ODBC to work for you in Crystal Reports is a two-step process:
-
Install and configure a data source using the appropriate ODBC driver at the operating system level.
-
Use the Database Expert to connect to the data source from within Crystal Reports.
Configuring an ODBC Driver
Database vendors have done a very good job of supplying ODBC drivers to access their product from other products. It is rare that a database doesn’t come with an ODBC mechanism to use it. In order to use an ODBC data source in any application, including Crystal Reports, the data source has to be installed and configured at the operating system level. In Microsoft Windows systems, this is done using the ODBC Data Source Administrator, shown in Figure 10.4. In Windows 2000, you’ll find this from Control Panel > Administrative Tools > Data Sources (ODBC).
Figure 10.4. ODBC Data Source Administrator
| Note | If the ODBC driver you want to use is not installed on your computer, you would need to do that first using the installation program provided with the driver. Many drivers are available for downloading directly from the Internet. |
From here, you configure an individual data source name (DSN) to represent the database you want to access. What you are doing is telling the operating system (Microsoft Windows) how to physically find the database on the computer. You can think of a DSN as an alias or nickname that an
application uses to request a connection to an ODBC data source rather than using the full filename and path of the database. Table 10.2 shows what each tab of the ODBC Data Source Administrator is used for.
| Data Source Tab | Description |
|---|---|
| User DSN | Contains a DSN created for a specific user and usable only by this logged-in user. The DSN connection parameters are stored in the Registry. |
| System DSN | Contains a DSN created for all users of a system and usable by any user who logs into the system. The DSN connection parameters are stored in the Registry. |
| File DSN | All parameters needed to connect to a data source are stored in a text file with a .dsn extension. The DSN name is configured to point to the file, e.g., C:\ program files\ common files\ODBC\Data Sources\vistanations.dsn. |
| Drivers | Lists all ODBC drivers installed on the computer. |
| Tracing | Traces system calls to ODBC for debugging purposes. |
| Connection Pooling | Optimizes performance by setting several options. |
| About | Determines what versioning of ODBC is in use. |
If multiple users will be logging into the same physical computer, a system DSN is your best choice and is probably the one you will use the most frequently. Figure 10.4 depicts the ODBC Data Source Administrator. When it opens, it defaults to a user DSN, so you need to change tabs to create a system DSN.
 |
File DSNs can be used to create connections across systems. Since all of the connection information is stored in a file, you can store it in a drive that is network-accessible by users in an organization. When a file DSN is created, the file extension must be .dsn and is generally stored in the \program files\common files\ODBC\Data Sources directory.
Here’s an example of what a file DSN to the VistaNations.MDB database might look like using standard connection parameters.
[ODBC] DRIVER=Microsoft Access Driver (*.mdb) UID= PWD= ReadOnly=0 UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=8 MaxBufferSize=512 ImplicitCommitSync=Yes FIL=MS Access DriverId=25 DefaultDir=c:\ program files\common files\ODBC\Data Sources DBQ=c:\vistanations.mdb
|
Each database is its own data source; to create a new one, click the Add button shown in Figure 10.4 to display a setup screen similar to the one shown in Figure 10.5; the setup screen is specific to the type of database. Here, an ODBC data source is being configured for the Vista Nations database, vistanations.mdb, which is visible as the database just above the Select button. Click the Select button to browse the current computer or any network resources to locate a database. Notice that the Data Source Name area allows you type in a meaningful alias or nickname to identify the database.
Figure 10.5. Setting up a DSN
Using an ODBC Driver
Once an ODBC driver is installed, you can use it in Crystal Reports via the Database Expert by selecting the ODBC (RDO) folder beneath the Create New Connection folder, as shown in Figure 10.6. RDO is an abbreviation for the Microsoft data access methodology, Remote Data Objects.
Figure 10.6. Database Expert ODBC connections
The list displays all the built-in ODBC connections that were installed either with your computer or with subsequent software installs as well as any DSNs that you created using the ODBC Data Source Administrator. When you double-click the plus sign (+) to the left of the ODBC (RDO) folder, the Data Source Selection window shown in Figure 10.7 appears. Notice that the VistaNationsDSN data source that was created previously is in this list. Notice also that just above it is a data source called VistaNations.dsn. This data source was created using the File DSN tab in the ODBC Data Source Administrator, as denoted by the file extension .dsn, and it is indeed a different data source than the VistaNationsDSN, although the names are perhaps a little too similar for comfort.
Figure 10.7. Data Source Selection
Earlier we mentioned that Crystal Reports provides no security to the data, but rather the database itself provides the security. When you connect to an ODBC data source in Crystal Reports, you’ll be prompted to supply a password, as shown in Figure 10.8. That doesn’t mean you have to provide one! For instance, the VistaNations database has no security applied to it in Microsoft Access, so there is no password. On the other hand, if the database requires a password, you are required to provide it at this point.
Figure 10.8. Connection Information
When you’ve completed the connection, the new data source and all its tables will show up in the ODBC (RDO) folder of the Available Data Sources, as shown in Figure 10.9. In Chapter 1, “Building Your First Report,” we used a different method to connect to VistaNations. That method is called DAO and is described a bit later. This makes three ways that you have now learned to connect to the vistanations.mdb database: file DSNs, ODBC using RDO, and DAO. DAO is an abbreviation for the Microsoft data access methodology, Data Access Objects. The point here is that you can have multiple connection types to the same database. Each connection creates a distinct data source.
Figure 10.9. VistaNations ODBC connection
As a mature technology, ODBC is a powerful data access tool, especially because software products such as Crystal Reports ship with it built-in and ready to go. Virtually any database can be treated as an ODBC data source even if it is not a relational data source. This greatly expands the types of databases that Crystal can service.
| Note | ODBC and OLE DB are specification guidelines detailing how applications (such as Crystal Reports) can communicate with data sources (such as Microsoft Access). ODBC and OLE DB are not software products. |
| ||
 | |
| Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement | |
| Conello © 2000-2003 Feedback | |
EAN: 2147483647
Pages: 217