he most common use of client/server technology is in the database field. Client/server databases are very popular because they provide most of the advantages of mainframe databases, such as security and transaction processing, while maintaining the ease of use and lower life-cycle cost of distributed hardware and software.
This chapter uses Microsoft SQL Server 6.5, which runs under Windows NT Server 4.0, for its examples. SQL Server is, as its name implies, a database server. It was designed to be a powerful and reliable back-end server for use with front-end tools, such as Visual Basic. Those front-end tools provide the user interface.
This chapter explores how to build applications using Visual Basic and SQL Server.
The client/server architecture is fairly easy to understand. A client program sends a request across a network to a centralized database engine (or server), which processes the request and returns the results over the network to the client. Just as the Internet is today's hot topic, client/server has been a key concept in modern computing for some time. Data exists everywhere in the enterprise--in ancient mainframe databases, in file cabinets, on purchase orders, in spreadsheets, and so on. Frequently, the goal of a client/server system is to bring together this information in a SQL database and provide a front end for reporting and analysis. In years past, a small subset of this data was transferred into mainframe databases where it could be widely viewed. However, a PC-based client/server system has several advantages, including usability and lower cost.
During recent years, as desktop computers have proliferated throughout corporations, single-user databases have been created using dBASE, Lotus 1-2-3, Access, and other desktop database programs. These databases have produced a cost savings over paper systems and have made some data available electronically for the first time.
Once data is stored electronically, it begins to attract attention. Other departments perceive that an integration of this data with their own internal data could yield some very interesting information. Those departments begin asking first for reports, then for disks, and finally for online access to this data.
This process is wonderful for the enterprise because it improves the organization's ability to make sound decisions. However, the person on whose desktop the database resides faces new difficulties, including such issues as data integrity, concurrent access, security, and performance. Those issues are the province of computer scientists, not of individual users.
The client/server architecture moves the desktop database to a departmental server where a database administrator can maintain it. Because server databases such as SQL Server are fully functional in the areas of transaction processing, crash recovery, and performance, they have many of the same advantages as mainframe databases. Server databases usually reside on a departmental server and are usually maintained by an employee of the department, so they fit the political reality of companies and governmental organizations.
Performing this conversion of a desktop database to a server database is a booming business. The computer specialists who can master the art of performing this task are in demand both inside traditional companies and in consulting firms that specialize in that type of work.
One of the most popular client/server database platforms is Microsoft SQL Server. SQL Server is a back-end processor that is designed to perform back-end tasks, such as fast updates and retrieval. It stores and retrieves data as quickly as possible while protecting that data from disaster. This separation of the front end from the back end of this server enables the customer to benefit from the advantages of both the mainframe and the PC world. The multiuser support, centralized administration, and security features of a mainframe are combined with the low cost-per-user and rich graphical interactive tools available on a PC platform.
Optional components provide gateways to mainframe and minicomputer database management systems, including IBM DB2, AS/400, and ORACLE.
ODBC drivers are widely available. They connect the SQL server to any front-end development tool that supports ODBC. For more information on ODBC, see Chapter 32, "Using Remote Data Objects (RDO)," and Chapter 33, "Database Access with ActiveX Data Objects (ADO)."
See "Introducing ODBC," Chapter 32
See "Understanding ODBC," Chapter 33
Microsoft SQL Server runs under the Microsoft Windows NT Server network operating system. (There is also a developer's version named "Microsoft SQL Workstation" that runs under NT workstation.) Installation is not difficult, and it usually proceeds without any problems. However, some important choices must be made during installation, so the installation process is briefly reviewed here. To install SQL Server, you need to be at the server console. When setup starts, you are prompted for your name, organization, and product ID. Then the setup Options dialog box appears, as shown in Figure 41.1.
FIG. 41.1
The number of setup options available during installation varies, depending
on whether the SQL server has ever been installed before.
The grayed-out options become available when you run setup again after SQL Server has already been installed. In Figure 41.1, the default option is to install both the database server and the utilities. The utilities are tools that let you administer the server and run queries against it, among other things. After performing the main installation on the server, you need to use the third option, Install Utilities Only, on the machines of the developers and the database administrator.
The next screen that appears, shown in Figure 41.2, enables you to choose the licensing mode. Remember, multiple clients will connect to this server, so make sure that all of them are legally allowed to do so.
FIG. 41.2
Stay legal by setting the licensing options appropriately.
The first option, Per Server, enables you to specify the number of licenses you have purchased for this server. For example, suppose you set your server up for 20 connections. This means you could have any number of installations of your application, but would be allowed only 20 connections at a time. The second option, Per Seat, requires you to purchase licenses for the clients rather than for the server. In this case, everyone who connects to the server is supposed to have a license.
After selecting the licensing method, choose the installation path. The default path is C:\MSSQL. Depending on how your server's disk drives are arranged, you may want to change your installation to another drive to separate SQL Server from the NT operating system.
Next, you are prompted to create the master database device. This dialog is shown in Figure 41.3. SQL Server stores data in its own file system. Devices can be thought of as named logical disk drives within that file system. From NT's point of view, the device is just a file. In Figure 41.3, the NT file name is MASTER.DAT.
FIG. 41.3
The device size does not have to be very large because the master database
stores information about other databases rather than actual data.
As you probably already know, one SQL server can have multiple databases. For example, you might have a payroll database, a human resources database, and a budgeting database on the same SQL server. Each of these databases can be stored on one or more devices. The master database is a special database that is created during the installation of SQL Server. It stores information about the other databases on your SQL server.
When you continue the installation, you are eventually presented with the Installation Options dialog box, shown in Figure 41.4.
FIG. 41.4
Pay special attention to the Installation Options dialog box; some of these
options can only be changed by re-installing SQL Server.
The first two options, Sets and Orders, are important because if you need to change them at a later time, you must rebuild your databases. In addition, database backups made from another SQL server can only be restored to a server with the same sort order and character set.
The character set is exactly what it sounds like--the set of allowable characters that can be stored in the database. I usually choose the recommended default, the ISO Character Set. You must decide whether to make your sort order; the big decision to make is whether or not to be sensitive. Although case-sensitive sorting is supposedly faster, I recommend a case-insensitive sort because it is less complicated for the programmer and for the users.
The third option on the dialog, Network Support, determines the network protocols that clients must use to connect to the SQL Server.
Finally, the two boxes at the bottom of the screen determine whether the SQL services are started automatically. The SQL Server is like any other Windows NT service: It must be "started" before anyone can use it.
These installation options can be changed at any time from the Services icon in the Control Panel. The first option, Start SQL Server at boot time, should always be checked because it represents the database server itself. The second box determines whether the SQL Executive service is started. The SQL Executive is a utility that can be used to automate some administrative functions. During installation, you are asked which NT user account the SQL Executive service should use. This choice is not critical now because whatever you select can be easily changed from the Control Panel.
Upon completion of the installation, a new program group--which is shown in Figure 41.5--is added to the server's Start menu.
FIG. 41.5
The program group on the server's desktop contains all of the utilities you
need to manage a SQL server.
The most important program in this group is SQL Enterprise Manager. With SQL Enterprise Manager, you can run queries and configure, administer, and control most aspects of the multiple SQL servers on your network. The remaining icons, which serve various other purposes, are described briefly here:
By using these programs, the database administrator can perform all of the tasks required to support production systems. The additional support services just discussed make the SQL Server a robust departmental server capable of handling a large number of users.
Before continuing, make sure the SQL Server is running. Start the SQL Service Manager. You should see the screen shown in Figure 41.6.
FIG. 41.6
If you have the "green light," SQL Server is running.
If the traffic light is red, double-click Start/Continue to start the SQL service.
The server is now ready to receive commands from the database administrator and from user applications (once they are written).
Now that the server is running, you need to configure SQL Enterprise Manager so that it can connect to that server. SQL Enterprise Manager is designed to control multiple SQL servers. Each server you want to access has to be registered with SQL Enterprise Manager. If you have not run SQL Enterprise Manager before, the Register Server dialog appears automatically. Otherwise, you can choose Server, Register Server. The dialog box is pictured in Figure 41.7.
FIG. 41.7
To use a SQL server with Enterprise Manager, the server must be registered.
Now you should go through the steps to register a new server. First, type the name of your server in the box labeled Server.
NOTE: The name of the SQL server is the name of the Windows NT server you installed it on. This is by design, because often an entire NT server is dedicated to running the SQL service. Companies have used a wide range of server-naming conventions, from movie star names to planets in the solar system. The examples in this chapter use the name EINTSVR, which stands for Enterprise INTranet SerVeR.
Next, you need to specify how Enterprise Manager should log in to SQL Server. You have two options, using a "trusted connection" or specifying a "standard security" login. A trusted connection means that login information is integrated with NT security. For the purposes of this chapter, however, standard security will be used. (Integrated NT security is more complicated and not available over all of the network protocols.)
SQL Server has its own user logins, which are used by client programs to gain access to the server. One of these logins, the sa login, is meant to be used by the SQL system administrator. By default, the sa account has no password. To register the server, select "standard security" and type sa in the Login ID box. Press Register and then Close to close the dialog box.
TIP: You will want to change the sa account's password. Choose Manage, Logins. After you have changed the password, you must register the server again.
After the server has been registered, it shows up in the Server Manager window of SQL Enterprise Manager. This window shows all of the registered SQL servers, as illustrated in Figure 41.8.
FIG. 41.8
The previous example shows SQL Enterprise Manager with six registered servers.
Note that with the proper scripts installed, SQL Enterprise Manager will work with
older versions of SQL Server.
To work with a particular server, select it in the list. Click the plus symbol next to the server name to expand the tree. When you do so, the SQL Enterprise Manager will connect to the selected server and retrieve additional information about it.
With the server running, you are now ready to create a database. In this section we will create a sample "employee" database, which could be used to store an employee phone directory. Creating this database will be a two-step process. You will need to create both a database device and the actual database.
Prior to creating a database, you must create a device for the database to reside in. Devices are files that can hold one or more databases and transaction logs. Devices can be thought of as named logical disk drives. When SQL Server is first installed, several devices that hold system information and sample databases are created. To see the devices, expand the Database Devices tree in the Server Manager window. No devices have been added to the server EINTSVR in Figure 41.9 except the defaults.
FIG. 41.9
In addition to the master device, the SQL installation creates two other devices,
MSDBData and MSDBLog.
To add a new device, first choose Manage, Database Devices. This brings up the Manage Database Devices window, shown in Figure 41.10.
FIG. 41.10
The Manage Database Devices window shows a color-coded graph of the free and
used disk space for each device.
To add a database device, click the New Device icon (upper-left corner) to display the New Database Device dialog box in Figure 41.11. Name the new device "MyDevice." Set the size to 10M, which will be more than enough for your sample database.
NOTE: As you type the device name, the physical location of the device is filled in automatically. The physical location is a file on the NT server. Remember that even if you are creating a device remotely, the location is on the hard drive of the server, not on your PC.
FIG. 41.11
Creating a new device requires you to specify a file name and size.
Press Create Now. After the device has been created, it shows up in both the Server Manager window and the Manage Database Devices window.
Now that you have a device to store the database in, you can create the employee database (which is created in much the same way as a device). First, choose Manage, Databases to bring up the Manage Databases window (see Figure 41.12).
FIG. 41.12
The Manage Databases window is used to create, edit, and remove databases.
Next, click the New Database button (in the upper-left corner) to display the New Database dialog box, pictured in Figure 41.13. Enter the name "employee" in the Name box so that your sample database will store information about employees.
FIG. 41.13
In the New Database dialog box, specify the name and size of your database.
Databases in SQL Server generally have space for both the actual data and the transaction log. The log space is used for recovery purposes, as well as for rolling back server transactions. Multiple databases can share a device, and devices can be expanded if the need arises. You should read the included documentation carefully regarding transaction log space and database devices.
For the purposes of your sample database, store both the data and log on the "MyDevice" device. Select "MyDevice" from the Data Device box and enter 8 for the size. Make the log size 2M. Press Create Now. The new database appears in both the Manage Databases and Server Manager windows (see Figure 41.14).
FIG. 41.14
The newly created employee database is added to the list of databases.
Armed with a real database, you are now ready to begin adding objects to it.
Once the database is created, the next task is to create a table. The Transact SQL language contains commands to create tables using the SQL syntax. SQL Enterprise Manager includes a way to create tables graphically using the SQL syntax. In this section you see a quick rundown of each method. You learn to create a table named "phonenumbers" that can store employees' names and office phone numbers. The four fields in the table will be employeeid, fname, lname, and phone.
To create this sample table, use the CREATE TABLE command. The syntax is easy to pick up if you look at an example:
CREATE TABLE phonenumbers ( employeeid int NULL, fname char(35) NULL, lname char(35) NULL, phone char(12) NULL )
As you can see, the field names are listed, followed by the data type. The word NULL indicates that that field is allowed to contain a null value. The CREATE TABLE command has many additional options. See the help file for a complete list.
You can run the CREATE TABLE command or any other SQL command from the SQL Query Tool. To start the SQL Query Tool, select Tools, SQL _Query Tool. The SQL Query Tool window is divided into three tabbed sections, as illustrated in Figure 41.15.
FIG. 41.15
To execute a SQL statement, first enter it in the Query tab's edit window.
There are several ways to execute a query: press the Play button, Alt+X, or Ctrl+E.
The Quer_y tab is where you type the SQL statements (or queries). After typing a query, press Alt+X to execute it. The SQL Query Tool automatically switches to the Results tab to display any returned information or results. The third tab, Statistics I/O, (which is not covered in this section) is used to track the execution of your queries. Try to execute the CREATE TABLE command listed previously. (If you need to delete a table, a corresponding DROP TABLE command takes the table name as a parameter.)
NOTE: When working with the SQL Query Tool, it is useful to know that you can enter multiple SQL statements in the Query tab. If you select text with the mouse before executing the query, the server only processes the selected text. If no text is selected, the entire contents of the Quer_y tab are sent to the server, which may cause multiple messages and/or table listings to appear in the Results tab.
SQL Enterprise Manager also has a graphical interface for creating tables. To begin, choose Manage, Tables to display the Manage Tables window. From this window you can view the structure of tables in the current database, as well as create a new table. If you enter the field information for the phonenumbers table, your screen should look like Figure 41.16.
FIG. 41.16
If you do not want to create tables with SQL code, the Manage Tables Window
provides an easy alternative.
After you have set up the fields for the new table, click the Save button (disk icon) and you are prompted for the table name.
There are a few things about tables that every SQL newbie needs to know to survive. On the road to understanding SQL Server, you will find several little conceptual "speed bumps" to get over. Another is the fact that most system objects in SQL Server are defined in the same way as the objects in all of your databases. For example, every user database contains a table called sysobjects that describes the objects in the database. When you created the phonenumbers table, SQL Server updated the sysobjects table with information about the phonenumbers table. The "sys-" prefix indicates that sysobjects is a "system" table. However, it behaves just like a user table. For example, you could run the following query against it in order to list all of the objects in your database:
select * from sysobjects
TIP: If you want to list all of the user tables in a database, use the following SQL statement:select name from sysobjects where type = `U' order by name
Of course, you could also just look at the table list in Enterprise Manager, but using this statement is sometimes quicker for fast typists.
Another good example of a system table is the sysprocesses table, located in the master database. It contains information about the currently connected users and which databases they are using.
Yet another useful item is the sp_help stored procedure. (Stored procedures are discussed later; for now, just think of them as SQL commands that are similar to SELECT.) The sp_help command, when executed without a parameter, lists all of the user tables in the current database. If you pass the sp help command a specific table name, it presents the table definition. It is used in the following manner:
sp_help phonenumbers
Now that you have created a table, you can add data to it. In the first part of this section, you see how to insert a record that includes a SQL statement. However, this method alone is not sufficient because SQL databases are frequently the gathering place for data from a variety of sources. You can use BCP, the SQL utility described in the second part of this section, to import data from an external file.
It is very easy to add records to a table with the SQL INSERT statement. Demonstrate this by adding an entry to the phonenumbers table created earlier.
First, start the SQL Query Tool by choosing Tools, SQL _Query Tool. Next, enter the following SQL code:
INSERT INTO phonenumbers VALUES ( 1234, "Stan ", "McFarley", "901-555-1212")
The syntax is fairly obvious, even if you are new to SQL. INSERT INTO specifies which table to affect. VALUES specifies the data values that you want entered.
After executing the INSERT statement, return to the Query_ tab and enter the following SELECT statement:
SELECT * FROM phonenumbers
The SELECT * statement means that you want to see all of the fields in the phonenumbers table. The results of this query are shown in Figure 41.17.
FIG. 41.17
The output of the SELECT query appears in the Results tab.
A Note About Referencing Tables
You may have noticed that a drop-down box in the Query Tab lists the current database. This box indicates your queries are executed in the context of the current database and that you can reference a table (like phonenumbers) by using only its name:select * from phonenumbersHowever, suppose you are not in the employee database but want to look at the phonenumbers table without changing the current database. If that is the case, the following statement would work:
select * from employee..phonenumbersThe official syntax is the database name, followed by the table owner, and finally the name of the table itself. A period separates each item from the other items. By omitting the table owner name in the previous line, you are assuming that the person currently logged in (sa) is the owner of the table.
The SQL Query Tool displays the data and the field names in the Results tab. If you were developing in VB, you might use the Query tool to test your SQL queries here first. It is important to verify that the data is in the table before attempting to do a remote query. Often, network problems or ODBC setup problems prevent the query from working properly from the client side. Ensuring that this data is in the server's database can speed up the debugging process because one source of the failure has been eliminated.
When you develop a SQL Server application, especially one that relies on data from other systems, you need a way to import large quantities of data in an automated fashion. The Bulk Copy Procedure, or BCP, is a simple way to do this. It can take information from a text file and load it into a database table. It can also copy the data from a table out to a text file. BCP itself is a command line utility, but there are some APIs which can be used to call BCP functions from VB. This feature is especially useful if your SQL database application needs to interact with a mainframe via text files. You can demonstrate the capabilities of the BCP utility by importing several rows from a text file into the phonenumbers table.
First, you can create a sample text file to import. BCP is not very smart, so you must use a consistent format throughout the text file. Your phonenumbers table has four fields. To create an import file, just type values for each field into Notepad, separating those values with commas. Each record should begin on a new line. The completed data file, named IMPORT.TXT, is pictured in Figure 41.18.
FIG. 41.18
Many different types of text files can be imported into SQL Server, including
tab delimited, comma separated, and fixed width.
For SQL Server to understand the structure of IMPORT.TXT, you must also provide a format file. The format file is another text file that describes the input file to SQL Server. The sample format file, IMPORT.FMT, is shown in Figure 41.19.
FIG. 41.19
Format files can be typed in by hand or created by the BCP utility.
The first line of the format file, 6.0, is the BCP version number. The number 4 represents the number of fields in the destination table. Next, there is a line for each individual field. Note that the fifth column indicates the field delimiter. The fourth field's delimiter is a newline character (carriage return + line feed, represented by "\r\n"), while the rest are commas.
After you create the sample format file and data file, you can run the BCP utility. However, one more step is usually performed on the server. That step is to set an option to allow non-logged BCP. Remember, when you created the database you allocated 2M for log space. If you are importing thousands of rows, and SQL Server tries to log each BCP operation, that log space will fill up quickly. For your sample database, this problem is unimportant. But as a general rule, you should open the Edit Database dialog box and choose Select Into/Bulk Copy from the Options tab. This option is pictured in Figure 41.20.
FIG. 41.20
If you plan to use the BCP utility, check the option for non-logged bulk copy.
The final step is to run the BCP utility. BCP.EXE is located in your SQL Enterprise Manager directory, usually in C:\MSSQL. BCP commands can be executed from an MS-DOS prompt window or batch file. The BCP command line is pretty long because you have to specify login and table information as well as specify all of the files involved:
BCP employee..phonenumbers in import.txt /Usa /P /SEINTSVR -fimport.fmt
NOTE: The password ("/P" parameter) is blank in the previous example because you have not yet changed the default (sa user's) password. You can see the exact BCP syntax by typing "BCP" at the DOS prompt (with no additional parameters).
After the BCP command has finished executing, it reports how many rows were copied and the time that elapsed. You can then run a SQL query to verify that the data has indeed been inserted into the phonenumbers table (see Figure 41.21).
FIG. 41.21
The results of a successful BCP operation are confirmed when you run a SELECT
query on the destination table.
Now that your database contains valid data, you can set up the ODBC connection on the client side. This connection is necessary to use the SQL server from VB. To add an ODBC data source name, open the Control Panel. Double-click the 32-bit ODBC icon to display the ODBC Data Source Administrator, as seen in Figure 41.22.
FIG. 41.22
You can install ODBC on client machines by running SETUP.EXE in Visual Basic's
ODBC subdirectory.
As you'll notice, the titles of the first three tabs in the dialog box end in the letters "DSN." DSN is an abbreviation for Data Source Name. Your program uses the DSN to identify an ODBC Data Source. ODBC maps the DSN to the actual driver and to the database server.
To define a data source for a SQL server, click the Add button on the User DSN tab. You are presented with the Create New Data Source dialog box, shown in Figure 41.23. In this first dialog box, choose the ODBC driver you will use to access the data.
FIG. 41.23
To configure a new data source, you must have the appropriate driver available
on your system.
After choosing the driver and clicking the OK button, you are presented with the Setup dialog box for the particular database type associated with the driver. Choose the SQL Server Driver and press the Finish button.
The SQL Server Setup dialog box, pictured in Figure 41.24, contains the following important text input boxes that contain information about the new data source:
FIG. 41.24
To actually set up the data source, you must provide the server name and a
unique data source name.
Having completed the definition, the data source can now be seen in the User Data Sources tab.
Accessing data stored in a SQL Server database from within Visual Basic can be done in a number of ways:
Using any one of these basic data access techniques is basically the same as working with data stored in a local Access (Jet) database. The main difference is that the object(s) that work with the data are not set up and initialized in the same way.
In the remainder of this chapter, you'll learn how to set up the objects in your program so that they can connect to SQL Server data in each of the three ways just discussed. Once the objects are properly set up so that they can "see" the SQL Server data, you can use familiar methods for the rest of your program development, as you would if the data were stored in a local database.
Before you learn how to open the data, you should be aware of a common problem encountered in dealing with client/server database management systems: the security and permissions issue. PC DBMS programmers assume that only one user at a time will access data from their systems. In contrast, client/server programmers assume that the whole enterprise might try to access the data from their systems at the same time. They also assume that some attempted accesses will be unauthorized. Therefore, client/server systems are set up to guard against unauthorized attempts.
The SQL Server (like other client/server systems) requires a login and a password before you can gain access to the data in its databases The SQL Server Login dialog box (see Figure 41.25) appears when the server is attaching tables to the database. If you enter a valid Microsoft SQL Server Login ID and Password, part of the security requirement will be satisfied.
FIG. 41.25
You must supply a valid SQL Server Login ID and Password
to access a SQL Server table.
SQL Server also controls user access at the individual permission level. Even if a user attempting to connect with the SQL Server database uses a valid Login ID and Password, access might still be denied if the Database Administrator (DBA) has not given the user the appropriate permission on the requested table. Recall that in earlier examples you created the phonenumbers table with the sa (system administrator) account. Security was not a problem because you executed all of your queries while using the sa account. However, in the real world, users and client programs should not connect with the sa account. In addition, you might want to restrict permissions so that certain users can read from but not update certain tables.
Create a new login, QueUser. The QueUser account will grant permission to read from, but not modify, the phonenumbers table.
To create a new login, first run SQL Enterprise Manager. Choose Manage, Logins, which opens the Manage Logins dialog box. In the Login Name box, enter QueUser and type elvis for the password.
In order to restrict the QueUser login to only the employee database, unselect the Permit boxes for all other databases. In addition, make sure the Default box for the employee database is selected. Any ODBC connections made under this login should now have employee as their current database. Your screen should look like Figure 41.26 when you are finished.
FIG. 41.26
The Manage Logins dialog box enables you to assign and revoke the users' access
to tables.
Now that the new login has been created, you should ensure that it will give your user the appropriate access to the phonenumbers table. Open the tree in the Server Manager window to the phonenumbers table and select it (single-click) with the mouse. Choose Object, Permissions. You should see the Object Permissions dialog box (see Figure 41.27).
FIG. 41.27
You can grant or revoke specific levels of access for a particular table from
the Object Permissions dialog box.
The Object Permissions dialog box shows group and user permissions related to the phonenumbers table. Notice that QueUser is a member of the Public group. All users in a group will have all of the permissions of their parent group. Therefore, unselect all of the boxes next to public so that full access is not granted by default to all Public members. In the QueUser row, leave only the Select permission selected, which means that the user can view but not modify the data. After you've set the permissions as desired, click the Set button to save them.
Now that you have granted the appropriate permissions, you can test your new login from a VB program, as described in the next section, or you can run the ISQL_w utility and log in as QueUser. If you try to run a delete statement while logged in as QueUser, you will receive an error message.
You can also control permissions with SQL statements. Suppose you want to grant update permission to QueUser. First, open a SQL Query Tool Window in the employee database while logged in as sa. Next, execute the following SQL query:
GRANT UPDATE ON phonenumbers TO QueUser
The SQL GRANT statement enables you to grant permissions for objects to specific users. Its companion statement, REVOKE , enables you to take specific permissions away from users.
If you are already familiar with using data access objects to work with data stored in a local Jet (or other) database, it's pretty simple to use them to work with SQL Server data as well. The only thing that's different is the way the data access objects, specifically Database objects, are created.
Accessing Data Stored in a Local Database When you access data in a local database, you have probably used the OpenDatabase and OpenRecordset methods in a manner similar to this example:
sDBLocation = "C:\Data\employee.mdb" Set MyDB = DBEngine.Workspaces(0).OpenDatabase(sDBLocation, False, False) Set MyRS = MyDB.OpenRecordset("select * from phonenumbers")
In this example, MyRS becomes a Dynaset-type Recordset object that lets you work with the data stored in the underlying phonenumbers table. You could locate specific records by using the FindFirst, FindNext, FindPrevious, and FindLast methods. The following code searches for the first match for a specific last name and first name combination and displays the employee's phone number if found:
sCriteria = "lname = `Doherty' and fname = `Jenny'" MyRS.FindFirst sCriteria If MyRS.NoMatch Then MsgBox "No match.", vbExclamation, "Phone Book" Else MsgBox "That employee's phone number is " & MyRS!phone & "." End If
Additionally, you could edit an existing record with the Edit and Update methods, or you could add a new record with the AddNew and Update methods. All of these techniques are discussed in Chapter 32, "Improving Data Access with Data Access Objects (DAO)."
Accessing Data Stored in SQL Server Accessing data stored in SQL server is just as easy as working with a local database. As was already mentioned, the main difference is in the way the Database object is created. To connect a Database object to SQL Server, utilize the connect argument of the OpenDatabase method. The connect argument, which in essence sets the Database object's Connect property, provides information about the source of the data being opened. To connect to a SQL Server database, use the ODBC version of the connect string. For example, if the data mentioned in the previous example were moved to the SQL Server database you created earlier in this chapter, you could replace the OpenDatabase portion of the previous code as follows:
sConnect = "ODBC;DSN=eintsvr;UID=QueUser;PWD=elvis;DATABASE=employee" Set MyDB = DBEngine.Workspaces(0).OpenDatabase("", False, True, sConnect)
In this example, note the syntax of the connect string (stored in the variable sConnect) that is used to connect to the SQL Server. The components of the connect string are separated by semicolons. The connect string begins with the ODBC string so that the type of connection being sought is immediately identified. DSN=eintsvr identifies the ODBC source; in this case, the SQL Server named eintsvr. (Note that the ODBC source must have been configured in the Windows Control Panel's ODBC applet.). UID=QueUser and PWD=elvis are the Login ID and Password, respectively. The data source will use the Login ID and Password to log in the user. DATABASE=employee identifies the database that contains the data your program needs.
Once the Database object is created properly, the remainder of the code is the same as the code in the phonenumbers table you created earlier. It is still appropriate to use the instruction
Set MyRS = MyDB.OpenRecordset("select * from phonenumbers")
to open the Recordset object because the phonenumbers SQL Server table has the same name as the phonenumbers table in the previous example. In addition, the remaining example code--which searches for a particular record--can be used with no modifications.
Creating a Generic Data Access Procedure Because using DAO with SQL Server is almost the same as using DAO with local databases, it would be wise to set up a procedure in your programs that would create the data access objects and open them appropriately. Use initialization strings of some type--which could be stored in the Registry or in .INI files--to provide an easily modified, generic data-opening routine, as shown in Listing 41.1.
Function OpenData() As Boolean On Error GoTo OpenDataError OpenData = False `Assume that sDBLocation and sConnect are Public variables ` that contain the appropriate data connect information Set MyDB = DBEngine.Workspaces(0).OpenDatabase(sDBLocation, False, True, sConnect) Set MyRS = MyDB.OpenRecordset("select * from phonenumbers") `If we made it this far, we've succeeded. OpenData = True GoTo OpenDataExit OpenDataError: Dim sErrorInfo As String sErrorInfo = "Error number " & Err.Number & ": " sErrorInfo = sErrorInfo & Err.Description If MsgBox(sErrorInfo, vbCritical + vbRetryCancel) = vbRetry Then Resume 0 Else OpenData = False End If OpenDataExit: On Error GoTo 0 End Function
Just like using DAO, connecting bound controls to SQL Server data is merely a matter of getting your program's objects set up correctly. Once bound controls are properly initialized, they work the same connected to SQL Server data as they would if they were attached to a local database.
If you've worked with the Data control and bound controls before, you know the steps needed to connect them to data properly:
Using bound controls with client/server database systems, such as SQL Server, is very similar to using them with local databases. The main difference is that you must set the Data control's Connect property instead of its DatabaseName property. (The connect string used to set the Connect property is the same one described in the previous section.) After you've set the Connect property, you can set the Data control's RecordSource property. Then you can set the DataField property of any bound control.
Use the data model described in the previous section to set up bound controls that can access data from client/server database systems. First, add a Data control to a form. Next, add a text box (or some other control) that will be bound to the Data control. At design time, you must set the DataSource property of the bound control(s) to the name of the appropriate Data control. The rest of this task can be accomplished with code. The following example sets up a TextBox control that is bound to the phone field of the phonenumbers table in your SQL Server's employee database:
sConnect = "ODBC;DSN=EINTSVR;UID=QueUser;PWD=elvis;DATABASE=employee" Data1.Connect = sConnect Data1.RecordSource = "phonenumbers" Text1.DataField = "phone" Data1.Refresh
After is is configured, the Data control and its bound controls work just as they would if they were bound to a local database.
See "Understanding the Data Control," Chapter 29
See "Getting Acquainted with Bound Control Basics," Chapter 29
Attaching an ODBC table to a Jet (Access) table appears to be the fastest way to connect Visual Basic to a non-Access database. Doing so speeds up processing. The Jet Database Engine maintains all of the data needed to process transactions in its internal control blocks, which removes the need for Visual Basic to retrieve that information for every transaction.
The simplest way to attach an ODBC table to a Jet (Access) table is to create a "dummy" database. This database should contain a linked, or attached, table for each ODBC table you need to use. The database can be created using Microsoft Access. However, the following section will demonstrate how they can be created with the Visual Data Manager (VisData) application included with VB.
Creating a Dummy Database First, start the VisData by choosing Add-Ins, Visual Data Manager. Then create a new 32-bit Jet database by choosing File, New, Microsoft Access, Version 7.0 MDB, as shown in Figure 41.28. You are then prompted for the name and location of your database. An empty database is created, such as the one shown in Figure 41.29.
FIG. 41.28
Visual Data Manager provides a convenient way to create a new database.
FIG. 41.29
An empty database is ready for tables to be added to it.
Once the database has been created, you need to attach the desired ODBC/SQL Server table(s) to it. From VisData's Attachments window, which is shown in Figure 41.30, select Utility, Attachments.
FIG. 41.30
VisData's Attachments window helps you manage attached tables.
Click the New button to bring up the New Attached Table window. Supply the appropriate information to attach to the desired table, as shown in Figure 41.31.
FIG. 41.31
You can enter ODBC connection information in the New Attached Table window.
Be sure to select the AttachSavePWD check box so that the user isn't asked for the ODBC password every time the table is used. Once you've entered the connection information, you need to navigate to the appropriate ODBC (or other) data source where the data is located. Once you have reached this data source, the Attachments window is updated to reflect your new attached table (see Figure 41.32).
FIG. 41.32
The Attachments window lists the database's linked tables.
Finally, select Close from the Attachments window to return to VisData's main database window, which is shown in Figure 41.33.
FIG. 41.33
The attached table is now ready to use.
Now the hard part is over. Your database is ready to use. You can use the attached table as a source for DAO and/or bound control processing just as if the table were a local database. In fact, it is a local database. The Jet engine manages the attachment so that your program can work with it locally.
The motivation for going to this much trouble must now be clear. A considerable database engine is required to run a full-blown, 100+ user system over a network. Issues of performance, concurrent access, and security become incredibly significant when large numbers of users are accessing a database. In the past, the solution was to buy a huge mainframe and connect everyone to a terminal. Although this solution did eliminate some problems, such architecture, which requires that all data be stored in a central location, does not work well with the "data everywhere" enterprises of the late 1990s.
When Visual Basic and SQL Server work together, users can manipulate remote data as if it were local to their machines and still maintain the data integrity and security of a mainframe.
In addition to running queries and simple select statements executed from client programs, SQL server is capable of running stored procedures. Stored procedures are lines of SQL code stored as part of the database on the SQL server. Like Visual Basic functions, stored procedures can include parameters, local variables, return values, and calls to other stored procedures. They can be executed directly from a Query Window or client program. SQL server has many built-in stored procedures, such as the sp_help procedure described earlier.
NOTE: The "sp_" prefix is a naming convention for stored procedures. Since SQL Server's built-in system procedures use the "sp_" prefix (and "xp_" for "extended stored procedure"), you might want to come up with your own prefix for user procedures. In this chapter, "gp_" will be used to indicate a general-purpose procedure.
The main uses for stored procedures are the following:
You can create stored procedures from the Query window in Enterprise Manager or from the Stored Procedures command in the Manage menu. Open a Query window in your sample employee database by choosing Tools, SQL _Query Tool. Use the CREATE PROCEDURE statement to create a simple stored procedure.
First, you need to come up with something for your stored procedure to do. For example, suppose you want to retrieve a phone list for a specific area code. From a standard SQL statement, this is pretty easy to do:
SELECT lname, fname, phone FROM phonenumbers WHERE substring(phone,1,3) = "901"
However, suppose you want to make the previous statement more generic, like a function. An input parameter for the area code is required to do so. The user should not have to put quotes around the area code parameter because it is an integer. However, your SQL statement should still require a string in its WHERE clause.
Like most programming concepts, the best way to learn is by doing. Execute the following code in a Query Tool window. A new stored procedure, gp_listarea, is created.
CREATE PROCEDURE gp_listarea @areacode SMALLINT AS DECLARE @strarea char(3) SELECT @strarea=convert(char(3),@areacode) SELECT lname, fname, phone FROM phonenumbers WHERE substring(phone,1,3) = @strarea
The first line in the previous code is the beginning of the CREATE PROCEDURE statement, which is followed by the new procedure name. Next, any input parameters are listed--in this case, the "@areacode" parameter.
The keyword AS separates the procedure declaration from the local variables and the SQL code. Note the way the first SELECT statement is used to assign a value to the @strarea value. The next SELECT statement, unlike the first SELECT statement, returns a result set to the user.
Once the stored procedure has been created, it can be executed from the Query window. First use its name and then use the input parameters in order:
gp_listarea 901
Stored procedures can also be executed from Visual Basic, as discussed in the next section.
It is important to remember that stored procedures and the methods of calling them are DBMS-specific and as such are outside the scope of the ODBC standard. For this reason, stored procedures called from Visual Basic programs must be called as SQL pass-through queries. When you use this option, Visual Basic does not interpret the query; it simply passes the entire query directly through to SQL Server (or another ODBC database source), which will interpret and execute the query.
Visual Basic provides two methods of executing a SQL pass-through query:
In the past, applications consisted of programs and data files. Because the data files were application-specific, you could incorporate all data validation checks in the applications and thus ensure the integrity of the data. However, when using DBMS, such as SQL Server, incorporating the data validation checks is not enough. You can still build applications that can carry out all of the data validation and store the data on the server. But your users are free to connect to the server with MS Access or MS Excel and carry out modifications to the data, thus bypassing your data validation routines.
To overcome this problem, create data validation and triggers in the database. However, this solution falls short when you try to implement business rules. For example, assume that Northwind Traders decides that important customers should receive an extra discount. An important customer is defined as a person to whom you have sold more than $5,000. How would you implement the rule that defines what an important customer is?
You could always code it up in your application, but this would cause a problem. People using Access or Excel to modify data would also have to code it up. In addition, when the rule changed you would have to ensure that all applications that used the rule were changed. To avoid these problems, you can implement this rule as a stored procedure on the server and have each application call it.
To implement the rule for Northwind Traders, you need to total all of the orders for a given customer. and somehow indicate that the customer is an important customer if the total is greater than $5,000. The first element of your procedure is to write the SQL statement to summarize the orders as follows:
SELECT SUM(order_amount) FROM orders WHERE customer_id = @CUST_ID
This statement summarizes the orders for a customer. The customer_id is equal to the variable CUST_ID. (@CUST_ID is the format used to indicate a variable in Transact SQL.)
Next, you need to add the code to see if the total is over $5,000. As you might have suspected, the IF statement is used in the following way:
IF (SELECT SUM(order_amount) FROM orders WHERE customer_id = @CUST_ID) > 5000 /* An important customer */ ELSE /* A normal customer */
The final element of your procedure is to return the value to Visual Basic. Transact SQL provides the following three methods of doing this:
Visual Basic cannot retrieve values that are returned via the RETURN statement, so you can rule that method out. If you use the SELECT statement, your procedure will be the following:
IF (SELECT SUM(order_amount) FROM orders WHERE customer_id = @CUST_ID) > 5000 /* Important Customer */ SELECT 1 ELSE /* Normal Customer */ SELECT 0
If you use the PRINT statement, the procedure will be the following:
IF (SELECT SUM(order_amount) FROM orders WHERE customer_id = @CUST_ID) > 5000 /* Important Customer */ PRINT "Important Customer" ELSE /* Normal Customer */ PRINT "Normal Customer"
The only other task to be carried out that involves the stored procedure is to implement the stored procedure on MS SQL Server. The code required to create the procedure using the PRINT statement is as follows (the code to create the procedure is similar and is not reproduced here):
CREATE PROCEDURE IMPORTANT_CUSTOMER @CUST_ID VARCHAR(5) AS IF (SELECT SUM(order_amount) FROM orders WHERE customer_id = @CUST_ID) > 5000 /* Important Customer */ PRINT "Important Customer" ELSE /* Normal Customer */ PRINT "Normal Customer"
This code tells MS SQL Server to create your stored procedure IMPORTANT_CUSTOMER and also tells the server that the procedure accepts a certain parameter, CUST_ID.
Having implemented the procedure on the server, you should now learn how to call it from Visual Basic. The method you should choose depends on whether or not the procedure returns a value. In this case, both procedures return a value, so call it via an SQL Pass-Through query.
Because the query using the SELECT statement is simpler to call, start with it. The code required to create and execute the query is as follows:
Private Sub cmdProc_Click ` Demo calling stored procedure that returns ` a value via a select statement Dim db As Database Dim qry As QueryDef Dim rsStoredProc As Recordset Set db = DBEngine(0).OpenDatabase(App.Path & "\PROCS.MDB", False, False) Set qry = db.CreateQueryDef("ImportantCustomer") qry.SQL = "EXEC IMPORTANT_CUSTOMER `ALWAO'" qry.Connect = "ODBC;DATABASE=Northwind;DSN=NWIND;UID=Sa; PWD=" qry.ReturnsRecords = True Set rsStoredProc = qryTemp.OpenRecordSet() MsgBox "Returned value was :" & Str$(rsStoredProc(0)) qry.Close rsStoredProc.Close db.Close end Sub
In this segment of code, you create a QueryDef named ImportantCustomer. Set its SQL property to the MS SQL Server-specific command required to call the stored procedure. Set its Connect property to the values required to connect to the server. Indicate that it will return records by setting its ReturnsRecords property. Once you have created the query, create a record set based on it to obtain the return value.
In the case of your stored procedure that uses the PRINT statement, things are little different. Rather than returning a result set, the query returns a message. These messages are trapped by the Jet engine and stored in a table, based on the user's name. First look at the code required to call the procedure and then examine the database to find out how the message is stored.
Private Sub cmdProc_Click ` Demo calling stored procedure that returns ` a value via a select statement Dim db As Database Dim qry As QueryDef Set db = DBEngine(0).OpenDatabase(App.Path & "\PROCS.MDB", False, False) Set qry = db.CreateQueryDef("ImportantCustomer") qry.CreateProperty("LogMessages", dbBoolean, True) qry.SQL = "EXEC IMPORTANT_CUSTOMER `ALWAO'" qry.Connect = "ODBC;DATABASE=Northwind;DSN=NWIND;UID _ =Sa;PWD=" qry.ReturnsRecords = False qry.Execute qry.Close db.Close end Sub
In dealing with the PRINT statement, start out the same way as you did for the SELECT statement. Create a QueryDef and set its properties. In addition to setting the Default properties, create one user-defined property, LogMessages. This property tells Jet to log the messages returned by the PRINT statement to a table in the local database, PROCS.MDB.
You have now learned how to use a commercial database product, Microsoft SQL Server, to implement Visual Basic programs that access data stored on a server. This approach frees Visual Basic programs from the limitations of ordinary PC-based database management products. It enables you to implement systems that scale to hundreds of users, while maintaining Visual Basic's application development advantages.
To increase your understanding of databases and client/server, you should examine the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.