Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 25 -
Accessing SQL Server Databases Through Front-End Products

In this chapter, you will see how to use SQLWindows, Delphi 2, and Visual Basic 4.
In the section "Configuring ODBC for SQL Server 6.5," you'll learn how to install the connectivity required for accessing SQL Server through front-end tools.
SQL Server's client interface from three popular languages is explored with sample applications that show how to develop smart applications.

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.

Configuring ODBC for SQL Server 6.5

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:

1. Install the ODBC drivers that ship on the SQL Server 6.5 CD-ROM.

2. Choose Settings, Control Panel from the Start menu. See Figure 25.1.

FIG. 25.1
Windows NT Work-station's Control Panel is really just a window with several icons in it.

3. Double-click the ODBC icon to display the ODBC configuration dialog box shown in Figure 25.2.

FIG. 25.2
ODBC's Data Sources dialog box lists available ODBC data sources that have had drivers loaded.

4. The Add Data Source dialog box, shown in Figure 25.3, is where you add new sources of data to be accessed via ODBC. In SQL Server's case, you add a new data source for each SQL Server that you have on the network. In this case, you can configure a default installation to access the PUBS database.

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.

Click the Add button in the Data Sources dialog box.

5. Select SQL Server from the list and click OK. The ODBC SQL Server Setup dialog box appears. See Figure 25.4.

FIG. 25.4
The
Options button displays the bottom half of the ODBC SQL Server Setup dialog box.

6. Enter a name for the data source, such as LocalServer. The name can be anything meaningful to you.

7. Enter a description of the ODBC data type--that is, MS SQL Server 6.5--so that you can determine what source of data this ODBC service is providing without having to rely on its name.

8. Enter the name of the actual SQL Server where the data resides. If SQL Server is running locally on Windows NT, it is possible to enter (local), and the ODBC driver will find the server using the Named Pipes protocol.

9. Enter a network address or network library if your network/database administrator indicates that one is necessary. Generally, these can be left on "(Default)" and the ODBC driver will find the server when first connecting.

10. Enter a database name that the ODBC service should connect to, such as PUBS. Some ODBC client programs lack the capability to change databases via ODBC commands, so it may be necessary to specify a data source for each database you want to connect to on the same server.

11. Unless there is a good reason to override the defaults of language and code page translation, they should be left as defaults. The completed SQL Server Setup dialog box is shown in Figure 25.5.

FIG. 25.5
The ODBC SQL Server Setup dialog box is complete and ready to add a new data source for ODBC.

12. Click OK to add the data source. After clicking OK, the new server will be added to the list of available data sources. See Figure 25.6.

FIG. 25.6
The new entry, LocalServer, is in the User Data Sources (Driver) selections.

Using Gupta's SQLWindows

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.

Establishing a Connection

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:

1. Install SQLWindows from the CD-ROM.

2. From the program group that SQLWindows installed in, double-click the Quest icon. See Figure 25.7.

FIG. 25.7
The Database Explorer lets you work with the different attributes of the ODBC configuration.

3. From the Utilities menu, select Database and then click Add. This displays the Add Database dialog box shown in Figure 25.8.

FIG. 25.8
The New Database Alias dialog box in SQL Explorer has MSSQL selected.

4. Select the LocalServer database, or the name that was entered previously for an ODBC data source.

5. Enter the user that is going to be used to connect to the database--for example, SA. Note that user names and passwords are case-sensitive.

6. Enter the password that is required for the user entered in step 5.


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.


7. Click OK to test the connection and to confirm that the user and password were entered correctly. If it's successful, the LocalServer database will be added to the list in the main Quest window shown in Figure 25.9.

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.

Listing 25.1 SWINDEMO.APP--Connecting to SQL Server Using SQLWindows' SqlConnect( ) Function

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.

Preparing and Executing SQL Statements

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.

Listing 25.2 SWINDEMO.APP--Executing a SQL Statement on SQL Server Using SQLWindows' SqlPrepareAndExecute( ) Function

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.

Using Stored Procedures and Command Batches

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.

Listing 25.3 SWINDEMO.APP--Simple Stored Procedure Used to Test Stored Procedure Execution in SQLWindows

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.

Listing 25.4 SWINDEMO.APP--Executing Stored Procedure on SQL Server Using SQLWindows' OdrExecuteProc( ) Function

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 SQLWindows

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.

More Information and Examples

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.

Using Borland's Delphi 2

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.

Establishing a Connection

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.

Understanding the Delphi 2 Database Model

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.

About the Sample Application DPHIDEMO

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.

Listing 25.5 DPHI20.PRJ--Dynamic Form Resizing with Objects that Paint Inside the Form's Boundaries

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.

Preparing and Executing SQL Statements

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.

Listing 25.6 DPHI20.PRJ--Setting a SQL Statement in a Delphi 2 Control and then Activating (Executing) It

procedure TForm1.Button2Click(Sender: TObject);
begin
     MyQuery.Active := False;
     MyQuery.SQL.Clear;
     MyQuery.SQL.Add( `Select * from publishers');
     MyQuery.Active := True;
end;

Using Stored Procedures and Command Batches

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.

More Information and Examples

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.

Using Microsoft Visual Basic 4 and 5

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.

Understanding the Visual Basic Database Model

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.

Connecting to 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.

Preparing and Executing SQL Statements

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

About the Sample Application VB4DEMO

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

From Here...

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.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.