With SQL Server 6, Microsoft introduced a new client-side interface through open database connectivity, or ODBC. Rather than using the old DB-Library approach that was created by Sybase, Microsoft decided to create a new access path through ODBC.
As custodian of the ODBC specification, it was easy for Microsoft to tailor the ODBC interface, making it no longer truly "generic" and, in fact, optimizing it for SQL Server access. With this new interface, there is no longer the stigma of slow performance associated with ODBC access. Microsoft still supports the DB-Library interface for backwards compatibility, but it seems that their primary focus is on making ODBC the standard for database access.
ODBC is a way of connecting various data services to different front-end applications in a consistent manner. ODBC has undergone several revisions since its inception in 1991.
ODBC is configured through a Control Panel applet: ODBC or ODBC32. Configuring under Windows NT Workstation or Server version 4.x or later and under Windows 95 will look identical. Under Windows for Workgroups, or Windows NT Workstation or Server versions 3.51 or earlier, the dialog boxes contain the same information; however, there may be some difference in the shading of values and the layout of the dialog box close/minimize/restore options.
To configure ODBC for use with Microsoft SQL Server 6.5, follow these steps:
FIG. 25.1
Windows NT Work-station's Control Panel is really just a window with several icons
in it.
FIG. 25.2
ODBC's Data Sources dialog box lists available ODBC data sources that have had drivers
loaded.
FIG. 25.3
Notice that SQL Server is listed at the bottom of the Installed ODBC Drivers
list box in the Add Data Source dialog box.
FIG. 25.4
The Options button displays the bottom half of the ODBC SQL Server
Setup dialog box.
FIG. 25.5
The ODBC SQL Server Setup dialog box is complete and ready to add a new data source
for ODBC.
FIG. 25.6
The new entry, LocalServer, is in the User Data Sources (Driver) selections.
Gupta's SQLWindows is a classic front-end tool that has been around since the late 1980s. Since that time, it has acquired various drivers written natively to provide communications to different DBMSs. Most recently, ODBC was added with SQLWindows 5.0. However, because Microsoft revised ODBC with the release of SQL Server 6, Gupta was required to address the new functionality provided in the ODBC driver and put out a special release. Consequently, in order to connect to SQL Server 6.5 from SQLWindows, you must use version 5.0.2.
All the source code that follows for the SQLWindows application can be found in SWINDEMO.APP on the enclosed CD-ROM.
Connecting or preparing SQLWindows for use with SQL Server 6 involves using Gupta's query and report writing tool, Quest, to set up statements in a private INI file with connection information about the ODBC data source. The GUPTA.INI file can be found in the directory that is indicated in the WIN.INI file by the SqlWinDir option.
Preparing SQLWindows for SQL Server 6.x To use Quest to prepare the ODBC interface, perform the following steps:
FIG. 25.7
The Database Explorer lets you work with the different attributes of the ODBC
configuration.
FIG. 25.8
The New Database Alias dialog box in SQL Explorer has MSSQL selected.
CAUTION: SQLWindows and Gupta's other products, such as Quest, require that you enter a password for the database that you are connecting to. If you do not enter a password, Gupta will pass in the default password of sysadm. You must configure a password for the users that will access the database via ODBC.Remember that the default installation of SQL Server is to install SA with no password. This must be changed.
FIG. 25.9
A completed database definition is ready to be applied by using the SQL Explorer
from Delphi 2. Note that there is a highlighted arrow pointing to the database being
worked on, indicating that it has not been activated yet.
Connecting to SQL Server 6.5 Connecting to SQL Server using SQLWindows is relatively straightforward. SQLWindows has three reserved words that are used to specify the user ID, password, and database that is going to be connected to. Listing 25.1 shows the setting of the variables with hard-coded values. In your application, you would probably have some kind of dialog box that you use to achieve the same result.
Pushbutton: pbConnect . . . Message Actions On SAM_Click Set SqlUser = `sa' Set SqlPassword = `dell' Set SqlDatabase = `LocalServer' If NOT SqlConnect( hSql ) Call SalMessageBox(`Failed to connect to SQLServer!',
`Demo - Warning', MB_IconAsterisk)
hSql is a local variable of type Sql Handle that is defined on the form.
SQLWindows provides a simple interface for executing. The same interface is used to prepare and execute queries on all database server types. Listing 25.2 shows the execution of a simple SELECT to count the number of objects and place the results in the data field on the screen dfCount.
On SAM_Click Set sSQL = ` Select count(*) Into :dfCount From sysobjects' If NOT SqlPrepareAndExecute( hSql, sSQL ) Call SalMessageBox( `Failed to execute a select from the SQLServer!', `Demo - Warning', MB_IconAsterisk ) If NOT SqlFetchNext( hSql, nReturn ) Call SalMessageBox( `Failed to fetch on the select from the SQLServer!', `Demo - Warning', MB_IconAsterisk )
Notice the use of the SQL Handle, hSql, is in all activity that involves the database. The SQL Handle is the logical entity through which all database interaction is performed.
In SQLWindows, there are some extension functions used for executing stored procedures on the server. These functions are prefixed with the letters Odr. To add this functionality to an application, include the Gupta-supplied include file, ODBSAL.APL, which should be located in the root Gupta directory.
A simple stored procedure is used to test stored procedure execution. Listing 25.3 shows the code for the stored procedure.
create proc ConnectivityTest as Select Count(*) From Sysobjects
Where Type = `U'
Listing 25.4 shows results are fetched and returned to the same dfCount data field.
On SAM_Click Set sSQL = `ConnectivityTest' If NOT OdrExecuteProc( hSql, sSQL, `:dfCount' ) Call SalMessageBox( `Failed to execute a select from the SQLServer!', `Demo - Warning', MB_IconAsterisk ) If NOT SqlFetchNext( hSql, nReturn ) Call SalMessageBox( `Failed to fetch on the select from the
SQLServer!', `Demo - Warning', MB_IconAsterisk )
Centura Corporation, formerly Gupta Corporation, changed its name to Centura Software Corporation in March 1996. Along with the name change comes a whole new development tool: Centura.
As a new product, Centura is remarkably stable and feature rich. It is fully code compatible with SQLWindows and yet adds full 32-bit engineering to its arsenal. The new user interface embraces Windows 95's Explorer model. See Figure 25.10.
FIG. 25.10
The SQL Explorer has the new database highlighted and its name has been changed to
LocalServer.
In addition to beefing up its development tool, Centura sports new class wizards that support three-tier programming through three of the most popular architectures: Novell's QuickTuxedo, Open Environment Corporation's DCE-based QuickRPC, and QuickCICS. See Figure 25.11.
FIG. 25.11
When you open the connection to SQL Server, you'll be prompted to log on.
Finally, Centura adds a new tool for the developer. The Database Explorer is similar to Delphi 2's and allows easy browsing of all the key attributes. It even includes editable tables that allow you to directly manipulate the data in the database.
Gupta has provided two instructive sample applications, ODBSAL1.APP and ODBSAL2.APP, that can be found in the \ SAMPLES directory below the Gupta directory. These examples show in more detail how to connect to the database and perform various operations on them.
Borland's Delphi 2 is a strong client/server application tool that helps ease the way to working with ODBC data sources. Based on Pascal, Delphi 2 is the newest version of Delphi that was originally released in 1996. Delphi 2 is a 32-bit development environment that has received much praise for its ease of use and integration.
All the source code that follows for the Delphi 2 application can be found in DPHI20.PRJ and DPHIDEMO.* on the CD-ROM.
Delphi 2 has native drivers for SQL Server 6.5 that are shells over Microsoft's ODBC. The simplest way to prepare Delphi 2 for use with a database is to use the Database Explorer applet that ships with Delphi 2.
To use the Database Explorer to prepare the ODBC interface, start Delphi and select Database, Explore. See Figure 25.7.
From the Object menu, choose New. Select the configuration you have set up for your SQL Server. In the example, MSSQL is used for SQL Server. Click OK when finished. See Figure 25.8.
Click the editable portion of the DATABASE NAME property in the right pane and enter the name for the database to be referenced: PUBS.
Enter the name of the user that will connect by default to this database in the USER NAME property in the right pane: sa. In addition, update the ODBC driver settings as needed for the properties listed in the dialog box. See Figure 25.9.
NOTE: Though used in these examples for ease of presentation, you should always avoid using the SQL Server SA account for database development. Create a new user and set it up for appropriate access to the system.
From the Object menu, select Apply to activate this database. You will notice that the arrow goes away. Figure 25.10 shows the new database is available and ready for use.
NOTE: If you want to change the alias name for this database from the default of MSSQLX, where X corresponds to the number of default databases installed so far, highlight the database in the left pane of the Explorer. From the Object menu, select Rename. Enter the new name for the database alias and press Enter.
To test that everything is configured correctly, click the plus sign to the left of the database alias name to expand the Explorer view. This will display the Database Login dialog box, as shown in Figure 25.11.
Enter the sa password. The Explorer tree will expand, indicating the various components of the SQL Server that are available for manipulation via SQL Explorer.
In its most common use, Delphi 2 has a layered approach to interfacing with databases. This layering provides several levels of abstraction from the database itself, allowing a generic application programming interface through common objects.
Delphi 2 has a number of classes that can be used to actually manipulate the data. These classes are responsible for executing the appropriate queries to perform any manipulation required. The classes that can be used vary from TTable, which is used for representing a table, to TQuery, which enables a custom query to be presented to the controls that make up the user interface. These data interface classes are typically not seen on the form.
These physical data sources are then mapped to a class that is responsible for interfacing with user interface objects/controls, such as data fields and lists. This interface is performed through a nonvisual class, TDataSource, which transfers data from the physical data class to the visual objects and data-bound controls that you place on an edit form.
Manipulating the data in TQuery or TTable to fetch records, update them, and so forth, can be performed by invoking the methods that they have or by adding a Navigation control to the edit form. TDBNavigator is a class that interacts with TDataSource and provides the standard Next, Previous, Insert, and Update buttons to manipulate the data on the form. By placing one of these controls on an edit form and then hooking--either at design time by setting its property or at runtime by adjusting its property to the required data source on the form--you will have all the necessary components to build an edit window to a data structure. This structure can be either a query or database table.
Finally, in Delphi 2 it is necessary to add controls to view/edit the actual data. Delphi 2 provides all the standard edit controls, including list boxes and combo boxes, that have properties that enable them to be hooked to a particular TDataSource. Placing the control on the form and setting its DataSource and DataField properties is all that is required.
The sample application provided here demonstrates a simple edit form to the authors' table in the PUBS database. This application took less than 10 minutes to write and shows how easy it is to use Delphi 2.
The application demonstrates the use of basic TTable.Table_Authors to read directly from the database table. On top of this class is TDataSource.MyDataSource that performs the data source manipulation. There are data fields on the form that enable editing of the basic name and address information in the table. They are all of class TDBEdit.
Finally, to control the interaction with the database, TDBNavigator.MyNavigator is hooked to TDataSource.MyDataSource. To upgrade the application and to learn a tiny part of Delphi 2, some code executes whenever the form is resized so that the Navigator control stays "docked" to the bottom of the window. The code in Listing 25.5 shows the simple code behind the form.
procedure TForm1.FormResize(Sender: TObject); begin MyNavigator.Top := Form1.ClientHeight - MyNavigator.Height; MyNavigator.Width := Form1.ClientWidth; end; end.
Perhaps the nicest feature of Delphi 2 is that the data access can be tested during design. The TTable class has a property of Active, which, if enabled, will connect to the database and present data to the controls if they are hooked via a TDataSource. This enables you to at least see something of what the application is going to look like at runtime.
Delphi 2 represents ad hoc queries through the class TQuery. TQuery is a nonvisual class that has properties that enable it to be attached to a database. The SQL property is provided to enable the setting of the required SQL statement. TQuery then interacts with a TDataSource just like TTable.
In the sample application, the Active property is set to True when the Titles button is clicked. This causes the SQL to be executed in TQuery.MyQuery and the Grid control to be populated with the results of the SELECT statement. The Grid control, TDBGrid.MyDBGrid, is hooked to the data source for presentation of the query results.
Clicking the Titles button executes the default SQL that was set in TQuery.MyQuery at design time. The Publishers button dynamically changes the SQL and executes it. Listing 25.6 shows what is required to do this SQL changing at runtime in Delphi 2.
procedure TForm1.Button2Click(Sender: TObject); begin MyQuery.Active := False; MyQuery.SQL.Clear; MyQuery.SQL.Add( `Select * from publishers'); MyQuery.Active := True; end;
Delphi 2's implementation of stored procedures and command batches is identical to that of the general query execution principle. A StoredProcName property is provided to enable you to hook the object with a stored procedure in the server. The TStoredProc is then attached/hooked to a TDataSource and accessed normally through other data controls.
A nice feature of Delphi 2 is that class-specific help is invoked whenever F1 is pressed during design if an object is highlighted. This will make it easy for you to find out about the properties and methods of the Delphi 2 classes provided by Borland.
Also, check out the Delphi forum on CompuServe where you will get a lot of help from other Delphi users, along with their Web site, which hosts several discussion forums, online service bulletins, and additional downloadable samples. They can be found on the Internet at http://www.borland.com.
Visual Basic 5 (VB5) is Microsoft's latest incarnation of the BASIC standard. VB5 begins the major overhaul of the different application-based environments like PowerPoint, Excel, and Word to include VB. In addition, it provides the links from developing stand-alone applications and tools for the Internet.
As a general tool, it has great features and has a huge install base. VB also includes some form-wizard-like features, allowing VB to generate some generic VB forms that you can use as a starting point for your development efforts.
As mentioned in Chapter 22, "Developing Applications to Work with SQL Servers," VB also supports the use of the object libraries for access to SQL Server. These are important advancements and provide a solid footing for VB in client/server application development.
All the source code that follows for the Visual Basic application can be found in VB4DEMO .FRM and VB4.VBP on the CD-ROM.
Visual Basic and Delphi 2 have a similar approach to data access. Data access is controlled by a data object that resides on a form. Unlike Delphi 2, VB does not use truly nonvisual object classes or container classes. Instead, it is possible to alter the visible property of the data object to hide it if you want.
You can work with databases a number of different ways in VB. First, if you need to have a series of fields bound to the database, and want to have the connection provide the process of updating the field automatically, you can use the data-bound control. The data control lets you indicate the connection properties and then link controls on your form to the control and its associated columns.
The other alternatives to working with databases in VB include the use of objects and their associated properties, collections, and more. Accessing SQL Server using these approaches is outlined in Chapter 22, "Developing Applications to Work with SQL Server" on developing applications for use with SQL Server.
Because the data-bound controls in VB are dependent upon the ODBC configuration, you must first set up an ODBC driver and its associated properties. Once you've completed this, you can bind the control to the ODBC installation and configuration. When the application is run, it uses this information to connect to SQL Server and work out the connection details.
You can use the Data Form Designer Wizard to create a data form. This will also create a connect string for you that you can use in connecting to the database in other portions of your application.
The connection string that is used by the ODBC connection includes all of the information necessary to log onto and use the SQL Server in question. The syntax for the connection string is
ODBC;DSN=LocalServer;UID=sa;PWD=dell; APP=Data Form Designer;WSID=DELL_NT_SERVER;DATABASE=pubs
The information in this string can also be set at runtime by altering the Connect property of the Data control.
VB's Data control can be used to either represent tables or process queries directly. The RecordSource property can be set to either a table name or to a query. The following code snippet demonstrates a pushbutton changing the query used to populate the Data control.
In turn, the Data control acts as a DataSource for a DBGrid control that is also on the form. Performing the Refresh method of the Data control causes the query to execute and the grid to be populated:
Private Sub Command3_Click() MyDataSource2.RecordSource = "select * from titles" MyDataSource2.Refresh End Sub
The sample application that is included on the CD-ROM demonstrates a basic form that enables browsing and editing of the authors table in the PUBS database. The application has an Update button that interfaces with Data.MyDataSource and invokes its UpdateRecord method, as follows:
Private Sub Command1_Click() MyDataSource.UpdateRecord End Sub
In a similar fashion, the Delete button removes a record from the table. By default, the typical toolbar provided with the data control does not provide for deletion of records.
Instead, you must resolve the reference manually by referring to the Data control's Recordset. You need to reference the Delete method:
Private Sub Command2_Click() MyDataSource.Recordset.Delete End Sub
In this chapter, you learned about the basics of ODBC and how to configure it on a work- station. In addition, you were introduced to three different approaches to client/server application programming and connectivity: Gupta's SQLWindows 5.0.2, Borland's Delphi 2, and Microsoft's Visual Basic.
Each of the products covered in this chapter is strong in certain areas and weak in others. Choosing one of these development tools should be done after carefully evaluating a project's needs.
© Copyright, Macmillan Computer Publishing. All rights reserved.