Chapter 22 - Accessing SQL Server Databases Through Front-End Products

• How to use some of the most popular client/server tools with SQL Server - In this chapter you will see SQLWindows, Delphi 2 and Visual Basic 4 in action.

• How to configure ODBC for use with SQL Server - In the section entitled Configuring ODBC for SQL Server 6.5 you'll learn how to install the connectivity required for accessing SQL Server through front-end tools.

• Tips and tricks for client/server development - 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 (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 configured through a Control Panel Applet: ODBC or ODBC32. In this chapter we will show you how to configure ODBC under Windows NT Workstation. In Windows 95, the only difference is the shading and etching of the dialog boxes; functionally, the methods detailed later in the chapter are identical.

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 fig. 22.1).

   Fig. 22.1 - Windows NT Workstation's Control Panel is really just a window with several icons in it. Each icon is an applet—a small application that manages a particular part of the operating systems behavior.

3. Double-click the ODBC icon to display the ODBC configuration dialog box (see fig. 22.2).

   Fig. 22.2 - ODBC's Data Sources dialog box lists available ODBC data sources that have had drivers loaded.

4. The Add Data Source dialog box (see fig. 22.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, configure a default installation to access the pubs database.

   Click the Add button in the Data Sources dialog box.

   Fig. 22.3 - Notice that SQL Server is listed at the bottom of the Installed ODBC Drivers list box in the Add Data Source dialog box.

5. Select SQL Server from the list and click OK. The ODBC SQL Server Setup dialog box appears (see fig. 22.4).

   Fig. 22.4 - We have clicked the Options button, which displays the bottom half of the ODBC SQL Server Setup dialog box.

6. Enter a name for the data source (for example, LocalServer). The name can be anything meaningful to you.

7. Enter a description of the ODBC data type (for example, 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 a 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 and/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 (for example, pubs). Note: 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 22.5.

    Fig. 22.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 fig. 22.6).

    Fig. 22.6 - The new entry, LocalServer, is in the User Data Sources (Driver) selections.

Using Gupta's SQLWindows

All the source code that follows for the SQLWindows application can be found in SWINDEMO.APP on the enclosed CD-ROM.

Establishing a Connection

Preparing SQLWindows for SQL Server 6

1. Install SQLWindows from the CD-ROM.

2. From the program group that SQLWindows installed in (usually Gupta), double-click the Quest icon (see fig. 22.7).

   Fig. 22.7 - Quest has just been started.

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

   Fig. 22.8 - The Add Database dialog box in Quest shows that the ODBC data source has a small ODBC icon to its immediate left.

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.

   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 have a password configured for the user that you want to access the database via ODBC with.

   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 (see fig 22.9).

   Fig. 22.9 - The LocalServer database is added to the list of available databases in Quest. Quest has connected to it, which is indicated by the lack of an asterisk next to the database name.

Connecting to SQL Server 6.5

Listing 22.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 )

Preparing and Executing SQL Statements

Listing 22.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 )

Using Stored Procedures and Command Batches

A simple stored procedure is used to test stored procedure execution. Listing 22.3 shows the code for the stored procedure.

Listing 22.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 22.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 )

Sneak Preview of Centura

As a new product, Centura was 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 metaphor (see fig. 22.10).

Fig. 22.10 - The Explorer view of Centura allows you to view the source code in style! However, for old-timers, the Outline is always available—check out the Outline tab on the lower-left corner of the right pane.

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 fig. 22.11).

Fig. 22.11 - The Three-Tier Wizard supports the most popular architectures available. Centura even comes in a special edition for Tuxedo that includes all of the Tuxedo development environment.

Finally, Centura adds a new tool for the developer—the Database Explorer. 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. Fig. 22.12 shows the Database Explorer editing the authors table.

Fig. 22.12 - The Database Explorer allows you to browse the data in the databases of all the different types to which Centura supports native connections: SQLBase, Oracle, Microsoft SQL Server, DB2, Sybase, Informix and ODBC.

More Information and Examples

Using Borland's Delphi 2

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

To use the Database Explorer to prepare the ODBC interface, perform the following steps:

1. Install Delphi 2 from the CD-ROM.

2. From the program group that Delphi 2 is installed in (usually Borland Delphi 2), select the Borland Delphi folder and double-click the Database Explorer icon (see fig. 22.13).

   Fig. 22.13 - Delphi 2's SQL Explorer has just been started.

3. From the Object menu, choose New. Select MSSQL (for Microsoft SQL Server) from the New Database Alias dialog box and click OK (see fig. 22.14).

   Fig. 22.14 - The New Database Alias dialog box in SQL Explorer has MSSQL selected.

4. Click the editable portion of the DATABASE NAME property in the right pane and enter the name for the database to be referenced: pubs.

5. Enter the name of the server in the SERVER NAME property in the right pane.

6. Enter the name of the user that will by default connect to this database in the USER NAME property in the right pane: sa.

7. Enter any additional configurations that you want to select for this database. The completed dialog box is shown in Figure 22.15.

   Fig. 22.15 - A completed database definition is ready to be applied 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.

8. From the Object menu, select Apply to activate this database (you will notice that the arrow goes away). Figure 22.16 shows the new database is available and ready for use.

   
   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.
   

   Fig. 22.16 - The SQL Explorer has the new database highlighted and the name of it has been changed to LocalServer.

9. 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 a database logon dialog box, as shown in figure 22.17.

   Fig. 22.17 - This is the Database Login dialog box from the SQL Explorer.

10. Enter the sa password and 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

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 DML 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 visual objects. These physical data interface classes are typically non-visual.

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 non-visual class, TDataSource, which transfers data from the physical data class to the visual objects that you place on an edit form.

Manipulating the data in TQuery or TTable to fetch records, update them, etc., 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 (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 application demonstrates the use of basic TTable.Table_Authors to read from the database table directly. 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 "beef up" 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 22.5 is so simple that it's amazing to anyone who has ever had to write this in a language such as C.

Listing 22.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.

Preparing and Executing SQL Statements

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 22.6 shows what is required to do this SQL changing at runtime in Delphi 2.

Listing 22.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

More Information and Examples

Also, check out the Delphi forum on CompuServe where you will get a lot of help from other Delphi users.

Using Microsoft Visual Basic 4

This was the first time we had ever tried to use Visual Basic for database connectivity. We were consistently frustrated by a non-intuitive environment that clearly lacked the integration of a mature database development environment.

All the source code that follows for the Visual Basic 4 application can be found in VB4DEMO.FRM and VB4.VBP on the CD-ROM.

Understanding the Visual Basic 4 Database Model

VB4's Data control is more of a combination of the TDataSource and TDBNavigator classes that are found in Delphi 2, and the visual nature of the arrow buttons explains the need to have it visible most of the time.

Once a Data control is correctly configured and bound to a particular table, it is possible to hook data fields to it as a declared data source on the form.

Connecting to SQL Server

To set up a Data control we tried to specify ODBC as the Connect property of the Data control that we was adding to the form; however, VB4 refused to correctly work with this. In the end, we gave up and used the Data Form Designer Wizard to create a data form and then copied the Connect string from there. This was truly an inelegant interface and is very surprising from Microsoft.

For future reference, the following is a Connect string for ODBC connectivity to SQL Server:

ODBC;DSN=LocalServer;UID=sa;PWD=dell;
APP=Data Form Designer;WSID=DELL_NT_SERVER;DATABASE=pubs

Preparing and Executing SQL Statements

Private Sub Command3_Click()
    MyDataSource2.RecordSource = "select * from titles"
    MyDataSource2.Refresh
End Sub

About the Sample Application VB4DEMO

Private Sub Command1_Click()
    MyDataSource.UpdateRecord
End Sub

Private Sub Command2_Click()
    MyDataSource.Recordset.Delete
End Sub

Private Sub Form_Resize()
    MyDataSource.Top = Form1.ScaleHeight - MyDataSource.Height
    MyDataSource.Width = Form1.ScaleWidth
End Sub

From Here...

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.

Table of Contents

21 - Communicating with SQL Server

Appendix A - Installation and Setup of the Server and Client Software