Visual Basic Expert Solutions

book coverQUE

Chapter 9

Client/Server Databases

By Steve Potts


The most common use of client/server technology is in the database field. Client/server databases are very popular in nonmainframe-based companies because they provide most of the advantages of mainframe databases, like security and transaction processing, while maintaining the ease of use and lower life-cycle cost of distributed hardware and software.

This chapter will use Microsoft SQL Server 4.21 running under Windows NT/Advanced Server 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 and it relies on front-end tools like Visual Basic to provide the user interface for the applications.

In this chapter, we will learn how to build applications using Visual Basic and SQL Server. The following topics will be covered:

Understanding the client/server database architecture

You will be create a simple, but real application using Visual Basic and SQL Server.

Client/Server Databases

The client/server architecture is an important step in the evolution of computing. Data exists everywhere in the enterprise: in desks, in file cabinets, on purchase orders, in spreadsheets, etc. In years past, a small subset of this data was transferred into mainframe databases where it could be widely viewed. The expense of this approach, and its regimented nature limited the amount of data that could be managed in this manner. The other data remained in the functional units of companies.

As desktop computers proliferated throughout the organization, single user databases were created using dBase, Lotus 1-2-3, Access, and other desktop databases. These databases produced a cost savings over paper systems, and 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. They start by asking first for reports, then for diskettes, and finally for online access to this data.

This is wonderful for the enterprise because it improves the ability of the organization to make sound decisions, but it is difficult for the person with the desktop database. They now must deal with issues such as data integrity, concurrent access, security, and performance. These issues are the province of computer scientists, not of functional experts.

Ten years ago, the universal answer would have been to port the database to the mainframe. Now, an alternative has appeared: the client server database.

This architecture moves the desktop database to a departmental server where a database administrator can maintain it. Because server databases like SQL Server are fully functional in the areas of transaction processing, crash recovery, and performance, they have the advantages of a mainframe database. They reside on a departmental server and are normally maintained by and employee of that department, so they better fit the political reality of companies and governmental organizations.

Performing this conversion of a desktop database to a server database is a booming business. Those computer specialists who can master the art of performing these tasks will be in demand both inside traditional companies, and in consulting firms who specialize in this type of conversion.

Microsoft SQL Server

One of the most popular client/server database platforms is Microsoft SQL Server. It provides a symmetric server architecture that mulitprocesses at the thread level. This allows for the efficient use of multiprocessing servers that are becoming common. It uses the additional CPUs without any intervention by either the user or the database administrator.

Optional components provide gateways to mainframe and minicomputer database management systems, including IBM DB2, AS/400, and ORACLE. In addition, it can interoperate with SYBASE SQL Server running on UNIX or VMS.

ODBC drives are widely available to connect SQL server to any front-end development tool that supports ODBC. For more information on ODBC, see chapter 6, "Working with ODBC".

SQL Server is a back-end processor, and is architected to perform back-end tasks such as fast updates and retrieval. It is an engine whose sole purpose is to store and retrieve data as quickly as possible while protecting the data from disaster.

Separating the front end from the back end in this way allows the customer to receive 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 graphical interactive tools from the PC.

Installing SQL Server

Microsoft SQL Server runs under the Microsoft Windows NT/Server network operating system. It requires 16 MB of memory and can be loaded in about 35 MB of hard disk space.

Installation is not difficult, and normally proceeds without any problem. Upon completion of the installation the workgroup will be added to your Windows NT desktop.

Through this user interface, the database administrator can perform all of the tasks required to support production systems. It is the existence of these additional support services that makes SQL Server a robust departmental server that is capable of handling a large number of users.

Using the SQL Service Manager, we can now start the database server by following these steps:

1. Double-click the SQL Service Manager icon in the SQL Server program group. This will bring up the dialog box to perform server start, pause, and stop.

2. Click the Start/Continue to start up the server.

3. Look at the bottom of the SQL Service Manager window at the status line. It should say, The service is starting for a few seconds, then say The service is running.

The server is now ready to receive commands from the database administrator and later from user applications (once they are written).

Creating a Database

With the server running, you are ready to create a database. To perform this task, SQL Server contains a utility called the Microsoft SQL Administrator. This utility provides a user interface to facilitate device and database creation.

Prerequisite 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 are created which hold system information and a few sample databases. To create a device to hold the database for this chapter's examples, follow these steps:

1. Start the SQL Administrator by double-clicking its icon.

2. Click the Toolbar button labeled Devices then choose Create from the Manage Devices menu. The Create Device dialog box appears, as shown in figure 9.1.

Fig. 9.1 Devices are required to hold databases and transaction logs.

1. In the Logical Name box, type QUEDevice.

2. Type the path for the device file's location. One will be suggested by the system, but you may choose a different name and path.

3. In the Type area, select Database as the type of device that you want to create.

4. in the Size (MB) box, type 2. This is the minimum database size, but it will be more than adequate for this chapter's examples.

5. Click the OK button. The device will be created and an entry representing it will be added to the Device Management list as shown in figure 9.2.

Fig. 9.2 The Device Management list contains a listing of all the devices defined for this server.

You are now the proud owner of a device suitable to contain a test database. You create databases using the same SQL Administrator utility that you used to create the device to contain it. You create this database by following these steps:

1. Start the SQL Administrator by double-clicking its icon.

2. Click the Toolbar button labeled DB. The Database Management window appears and lists all of the current databases on this server, as shown in figure 9.3.

Fig. 9.3 The Database Management window lists all of the databases on this server.

1. From the Manage menu, choose Database, Create Database. The Create Database dialog box appears, as shown in figure 9.4.

Fig. 9.4 The Create Database dialog box allows you to specify the characteristics of the database.

1. In the Database Name box, type QUEDatabase.

2. In the Data Device box, choose QUEDevice. This is the device that we created earlier.

3. In the Data Size (MB) box, type 2.

4. In the Log Device box, choose (none). The purpose of the log device is to allow for rollback and recovery in case of a system crash. For our test database, this can be ignored because we are not storing any real data.

5. Click the OK button. This will create the database and a listing in the Database Management window.

Armed with a real database, you are now ready to begin building objects.

Creating a Table

Once the database is created, the next task will be to create a table. The Transact SQL language contains commands to create tables using the SQL syntax. The SQL Object Manager, however, is more convenient because it provides a graphical way of accomplishing the same task. SQL Object Manager manipulates the objects of a database such as tables, indexes, triggers, views, keys, rules, defaults, datatypes, and stored procedures. SQL Object Manager allows you to create, alter, and delete database objects, as well as grant and revoke object permissions.

To create a new table in the database, follow these steps:

1. Double-click the SQL Object Manager icon in the SQL Server program group. The Connect Server dialog box appears, as shown in figure 9.5.

Fig. 9.5 The SQL Server Object Manager will connect to the server called MANAHI.

1. The sa in the Login Id text box indicates that you are the system administrator. No password is required if you are using the default-integrated security features of Windows NT.

2. Click the Connect button to make the connection. The name of the server that you are connected to is shown in a combo box on the SQL Object Manager window. It is possible to be connected to several databases at the same time, but for the sake of clarity, we will limit this discussion to one connection.

3. In the Current Database combo box, select QUEDatabase (the database that you created).

4. From the Manage menu, choose Tables and the Manage Tables window appears (see fig. 9.6).

Fig. 9.6 The Manage Tables window allows you to specify the characteristics of the table that you are creating.

1. In the Tables box, choose <New Table>.

2. Enter the following fields and data types in the table:

Column Name Datatype Length Nulls Default Rule

SSN text no

LastName varchar no

FirstName varchar no

Age int yes

Balance Money no

1. Click the Create button and a dialog box appears, asking for a table name (see fig. 9.7).

Fig. 9.7 The Specify New Table Name dialog box appears when you click Create.

1. In the New Table Name box, type CustomerTable and click OK. SQL Server will add this table to the database.

With a table in the database, we are ready to store and retrieve data.

Adding Data to the Table

Now you need to add data to the table in order to make it useful. The easiest way to do this is with a utility called ISQL/W. ISQL/W.This is an interactive SQL front-end program that allows you to enter SQL commands in a window. It shows you the results of that query immediately. It is useful as a quick way to query and update a database, and to test SQL statements before including them in your code.

You invoke ISQL/W by double-clicking the ISQL/W icon in the SQL Server program group. When you first invoke ISQL/W, the window is set to go against the default database, normally master. You change that by choosing Change Database from the Query menu. This will direct all queries to the database containing the sample data. In your case, set the database to QUEDatabase. With the database set, you are now ready to enter data into the table. The following code will add one row of data into the table:

INSERT INTO Customer_Table
VALUES ( '234-56-7890',
'Banks',
'Dusty',
49,
123.45 )

The syntax is fairly obvious, even if you are new to SQL. INSERT INTO specifies what table to affect. VALUES specifies the data values that you want entered. Because all of the columns in the table will receive a value from this INSERT statement, you may omit any reference to the row values by name.

Now, also enter all of these rows into the table at this point:

650-09-9008 Jones Johnny 15 8.75

650-09-9008 Jones Johnny 15 8.75

123-45-4321 Hearn Will 9 45.66

The repeated entry of Johnny Jones was intentional. Next, use ISQL/W to look at the data in the table in the table. We do this using a SELECT statement:

SELECT * FROM Customer Table

The SELECT * means that you want to see all the fields from Customer Table. The results of this query are shown in figure 9.8.

Fig. 9.8 The output of the query appears in a child window.

ISQL/W displays the data along with the field names in the Results window. The practice of verifying that the data is in the table before attempting to do a remote query is important. Often, network problems or ODBC setup problems will prevent the query from working properly from the client side. Being sure that this data is indeed resident in the database on the server can speed up the debugging process by reducing the number of possible causes of the failure.

Making the ODBC Connection

Now that we have a database that contains valid data, we can move to the client side and set up the connection. You do this by choosing the Control Panel icon. Double-clicking this icon will bring up the program group that contains the 32bit ODBC icon.

When you first bring up the ODBC Data Sources window, you will see no data sources defined. To define one, click the Add button on the Data Sources dialog box (see fig. 9.9).

Fig. 9.9 You define data sources through the Add button on the Data Sources dialog box.

The Add button will invoke the Add Data Source dialog box. This dialog box lists all of the ODBC drivers that are currently installed on the client system. The ODBC drivers come from a number of sources. Some of them, like SQL Server, ship with Visual Basic. Another set of them can be obtained from Microsoft directly or through their distributors.

Note: A small industry of ODBC driver writers has emerged who sell drivers which are claimed to be superior to the common drivers. These companies advertise in programming magazines. Finally, the vendor of your DBMS has probably written one also. Contact him for information.

In our case, the driver ships with Visual Basic, so it shows up right away (see fig. 9.10).

Fig. 9.10 The ODBC drivers installed on your system show up in the Add Data Source dialog box.

Clicking OK brings up the ODBC SQL Server Setup dialog box. It contains the following important text input boxes that gather critical information:

Having completed the definition, the data source can now be seen in the Data Sources dialog box. The only data source defined is QUEDatabase. The SQL Server shown in parentheses beside the Data Source Name indicates the driver that will be used to implement the accesses to this database will be the Microsoft SQL Server Database. This client has only one ODBC data source that can be accessed by Visual Basic, C++, or other application development tools.

Accessing the SQL Server

Accessing the SQL Server from within Visual Basic can be done in a number of ways. In this text, we will use the recommended way, which is to attach the SQL Server table that we wish to access to a table in a Microsoft Access Database. This sounds convoluted, but it is the way that Microsoft says will yield the best performance. To do this, we need to create a dummy Access database. This could be done in Microsoft Access, but it can be done just as easily from the Data Manager product in Visual Basic itself.

You start the Data Manager by selecting Data Manager from the Add-ins menu. This brings up the main Data Manager window. Next, choose New Database from the File Menu in Data Manager. This will bring up the New Database dialog box (see fig. 9.11).

Fig. 9.11 The Data Manager can be used to create a new database in the Microsoft Access database format.

Name the new database SQLStub to signify that it exists to attach SQL Server tables to. Click OK to bring up the dialog box that manages the creation and attachment of tables. This dialog box contains the fully qualified name of the database in its title bar, as shown in the upper half of figure 9.12. Click the Attach button to invoke the Attach Tables dialog box, as shown in the lower half of figure 9.12.

Fig. 9.12 The Data Manager Add-in can be used to create and attach tables from ODBC data sources.

Choose ODBC as the data source type in the Attach Tables dialog box and click OK. This brings up the SQL Data Sources dialog box, which contains a listing of all of the data sources that are available to your Visual Basic program as a result of the work done with the ODBC tools. In this case, only one choice appears, QUEDatabase. This is because you only defined one data source when interacting with ODBC directly earlier in the chapter.

A common problem encountered in dealing with a client/server database management system concerns security and permissions. In contrast to ordinary PC DBMS systems (which assume that only one user will be accessing data at any one point in time) client/server database management systems assume that the whole enterprise will be trying to access the data. It also assumes that some unauthorized accesses will be attempted, and must be guarded against. As a result, SQL Server requires a login and password before granting access to the data in one of its databases. To address this requirement, the SQL Server Login dialog box (see fig. 9.13) appears during the process of attaching tables to the database. Putting in a login ID and password that is valid to Microsoft SQL Server satisfies part of the requirement.

Fig. 9.13 You must supply a valid SQL Server Login ID and Password to attach a SQL Server table to an Access database.

Granting and Revoking Permissions

In addition to the required Login ID and password, SQL Server controls users at the individual permission level. This means that even if a user attempts to make a connection with the SQL Server database using a valid Login ID and Password, access may still be denied if the Database Administrator(DBA) has not given this user the appropriate permission on the requested table. The procedure used to grant and revoke permissions is as follows:

1. Open the SQL Server for Windows NT program. Double-click the SQL Object Manager icon.

2. Double-click the SQL Object Manager icons to bring up the main window of that application. Select the database that you want to modify by pulling down the list box and selecting QUEDatabase. QUEDatabase becomes the current database, which means that all Toolbar and menu requests will be applied to the tables and objects in this database.

3. Click the Objects button on the Toolbar. This brings up a window containing one line for each object in the current database (see fig. 9.14). These objects can be rules, stored procedures, events, or tables. The object of greatest interest to this discussion is the Customer Table, which contains the data that was entered earlier using ISQL/W.

Fig. 9.14 The objects in the QUEDatabase are displayed in a list when the Object button on the Toolbar is clicked.

1. Highlight the Customer Table row in the Database Objects window and choose Object Permissions from the Object menu.

2. Select the permissions that you want the user to have. The granting of permissions can be as simple or as difficult as you need it to be. You can grant all permissions to all users to read, modify, augment, or remove the table from the database by selecting All in the Permissions area (see fig. 9.15). You also can give each user selected permissions.

Fig. 9.15 The granularity of the permissions can be controlled at a low level using the Object Permissions window.

1. In the Existing Users/Groups box, highlight the users or groups that you want to alter.

2. Click the Add button, which adds the name to the Users With Selected Permissions.

3. Select the permissions that you wish to give to the users and groups in the lower right. For the purpose of this exercise, give your user account all privileges for this table. In the real world, you would only want to grant permission to users on a "need-to-know" basis. This gives your data maximum protection against the inadvertent corruption of data by legal users, and the malicious destruction of important data by hackers. In a world that is dominated by remote database accesses, security is a very big issue.

4. Click the Grant button to make your selections known to the system. This registers the permissions in the database and allows the clients to access data up to the limit of their permissions.

Attaching a Table to a Jet Engine Database

According to Microsoft, attaching ODBC tables to Jet engine table is the recommended way to connect Visual Basic to a non-Access database. The reason for doing this is one of processing speed. The Jet Database Engine maintains all of the data needed to process transactions in its internal control blocks. This removes the need for Visual Basic to retrieve that information for every transaction.

Having granted the proper permissions to your user account in SQL Server, you are ready to complete the task of attaching an SQL Server table to a Microsoft Jet Engine database. The following steps will accomplish this:

1. Return to Data Manager and click the Attach button again.

2. Next, click ODBC in the Attach Table dialog box as before.

3. Select QUEDatabase as the ODBC data source. The Login Dialog box to appear again.

4. Enter the Login ID and Password for the accounts that have been granted permissions.

5. This brings up a list of all of the ODBC tables that your client can view. Select the one labeled dbo_CustomerTable. The dbo prefix indicates which user owns the table.

6. Select this table and click the Attach button. This completes the attachment of the table to the Access database called SQLStub. Figure 9.16 shows that the name of the table, dbo_CustomerTable, now appears in the Data Manager's Tables dialog box.

Fig. 9.16 The tables in, or attached to, a database are listed in the Tables dialog box.

While you are still in the data manager, clean up the data in the database a little. Earlier in the chapter, we entered the following data in the Customer Table.

234-56-7890 Banks Dusty 49 123.45

650-09-9008 Jones Johnny 15 8.75

650-09-9008 Jones Johnny 15 8.75

123-45-4321 Hearn Will 9 45.66

Notice that rows two and three contain identical data. You need to delete them and add one of them back. This can be done easily in the Object Manager using the following procedure:

1. Access the Microsoft SQL Object Manager exactly as you did earlier.

2. Select QUEDatabase as the Current Database.

3. Click the Query button on the Toolbar.

4. Enter the following code in the upper half of the Query window.

Delete from CustomerTable Where FirstName = 'Johnny'

The result of this action will be displayed as (2 row(s) affected).

Querying the Table

Now that the data in the database is correct, you can use Visual Basic to access it. The easiest way to do this is through the Data Manager.

1. Start Visual Basic and invoke the Data Manager by picking it from the Add-Ins menu. This brings up the Data Manager.

2. Next, choose Open Database from the File menu to open the SQLStub database.

3. Click the Open button on the Tables dialog box with dbo_CustomerTable highlighted. This brings up a form that is automatically configured to allow the display and editing of the fields in the dbo_CustomerTable (see fig. 9.17).

Fig. 9.17 The Open button on the Tables Dialog box generates a data input screen.

The generation of the fields on this form was done automatically by the Data Manager software.

Using the Data Control to View SQL Database Tables

The Data Manager is good for a quick look at the data, but the real power of Visual Basic is in the Data control. You can use this control on an SQL Server table that is attached to an Access database just like you can on native Access database tables. The following procedure illustrates how this is done:

1. Open a Visual Basic form.

2. Double-click the Data control in the toolbox.

3. Set the following properties on the Data control:

Property               Value

Name                     Data1

DatabaseName      C:\VB\SQLSTUB.MDB

Connect                 Access

Caption                  QueCustomer

RecordSource        dbo_CustomerTable

1. The DatabaseName property applies to the Access database and not the database on SQL Server. All interaction between the server and this program is being handled by a combination of the Microsoft Access database engine and the SQL Server ODBC driver in ways that are not visible to the Visual Basic programmer.

2. As always, the Data control needs a bound control that is capable of displaying the data from the database. In this case, use the text box control. To bind the text box to the Data control, set the following TextBox properties:

Property         Value

Name               Text1

DataSource      Data1

DataField         LastName

1. Run the program and experiment with the data. Verify that the data being retrieved is the same data that you input into the SQL Server Database. Figure 9.18 shows the program with a field from one of the rows displayed.

Fig. 9.18 The rows of the SQL Server are accessed by the Data control and displayed using a bound text box control.

1. Expand the number of data items to include all of the fields in the database table as shown in figure 9.19. The following table shows how their properties should be set.

Property               Value

Name                    Text2, Text3, Text4, etc.

DataSource           Data1

DataField              FirstName, Age, SSN, etc.

Now, run the program and observe that the form that you have created looks a lot like the one created by Data Manager to handle these same fields.

Fig. 9.19 The entire table can be displayed in a professional fashion.

The motivation for going to this much trouble must now be clear. In order to implement a full-blown 100+ user system running over a network, a considerable database engine is required. Issues of performance, concurrent access, and security become huge when a large number of users are accessing a database. The solution in years gone by is to buy a huge mainframe and connect everyone to a terminal. This solved some of the problems, but this architecture does not fit in well with the "data everywhere" enterprises of the 1990s because it requires that all data be stored in a central location.

Visual Basic and SQL Server working together allows users to manipulate remote data as if it were local to their machines, while maintaining data integrity and security as if the data was on a mainframe.

Stored Procedures

Most Database Management Systems include some type of programming language that can be used to manipulate data, produce reports and so on. This language is normally an extension of the SQL language, to include variables, control statements, printing and so on. When you write a program using these languages they are normally compiled and stored in the database as stored procedures. They are executed on the server with the result sets, with messages and errors being returned to the workstation. The main uses for stored procedures are:

Triggers—A trigger is a procedure that is executed by the DBMS, whenever you modify the data in a specific table. In a customer order system, when an order is placed, a trigger could be executed to see if the customer has an available line of credit.

Rules—A rule controls what you can or can not enter in the column of a table. This allows the database administrator to control the data in the database. For example, a rule could be written that rejects social security numbers if they contain alphabetic characters.

Stored Procedures—Stored procedures are full blown programs using SQL statements, control statements, variables and so on, to manipulate data on the server. We'll examine these in more detail below.

Since we are working with MS SQL Server, we will be using the Transact SQL Language in our examples.

Calling Procedures from Visual Basic

It is important to remember that stored procedures and calling stored procedures are DBMS specific and as such are outside the scope of the ODBC standard. For this reason, the calling of stored procedures must be done using the SQL Pass-Through option. When you use this option the ODBC does not interpret the query, it simply passes the whole thing directly to the DBMS and lets it deal with it.

Visual Basic provides two methods of executing an SQL Pass-Through query:

In the past applications consisted of programs and data files. Because the data files were application specific, you were able to incorporate all data validation checks in the applications and thus ensure the integrity of the data. However, when using DBMS like SQL Server this is not enough. While we can still build applications that can carry out all the data validation as before and store the data on the server, our users are free to connect to the server with MS Access or MS Excel and carry out modifications to the data, thus by-passing our data validation routines.

The way to overcome this is to create data validation and triggers in the database. However this falls short when it comes to implementing business rules. For example, assume that Northwind Traders decide that important customers should get an extra discount. An important customer is one to whom we have sold more than $5,000. How would we go about implementing the rule that defines what an important customer is?

We could always code it up in our application, but this would cause a problem, since people using Access or Excel to modify data, would also have to code it up. When the rule changes, you would also have to ensure that all applications using the rule were changed. To avoid all this problem, implement this rule as a stored procedure on the server and have each application call it.

The basic requirement is that we need to total all the orders for a given customer and if the total is greater than $5,000 then indicate that the customer is an important customer. The first element of our 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, where the customer_id is equal to the variable CUST_ID (@CUST_ID is the format used to indicate a variable in Transact SQL).

Next we need to add the code to test if the total is over $5,000. As you might have suspected, we use an IF statement as follows:

IF (SELECT SUM(order_amount) FROM orders
WHERE customer_id = @CUST_ID) > 5000
/* An important customer */
ELSE
/* A normal customer */

Final element of our procedure is to return the value to Visual Basic. Transact SQL provides three methods of doing this.

1. A SELECT statement

2. A RETURN statement

3. A PRINT statement

Since Visual Basic can not retrieve values that are returned via the RETURN statement, we can rule that method out. So using the SELECT statement, our procedure becomes the following:

IF (SELECT SUM(order_amount) FROM orders
WHERE customer_id = @CUST_ID) > 5000
/* Important Customer */
SELECT 1
ELSE
/* Normal Customer */
SELECT 0

While using the PRINT statement the procedure becomes 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 from the Stored Procedure's point of view is to implement it on MS SQL Server. The final code required to create the procedure using the PRINT statement that 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 our stored procedure IMPORTANT_CUSTOMER and also tells it that the procedure accepts a parameter CUSD_ID.

Having implemented the procedure on the server it's now time to take a look at how we can go about calling it. The method we choose depends on whether or not the procedure returns a value. In our case, both procedure returns a value, so we'll call it via an SQL Pass-Through query.

Since the query using the SELECT statement is the simpler to call, we'll deal with that first. 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, we create a QueryDef called ImportantCustomer, set it's SQL property to the MS SQL Server specific command required to call the stored procedure, as well as setting it's Connect property to the values required to connect to the server and indicate the it will return records by setting it's ReturnsRecords property. Once we have created the query we create record set based on it, to obtain the return value.

In the case of our 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 Jet and stored in a table, based on the user's name. Let's 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, we start out the same way, creating a QueryDef and setting it's properties. However, in addition to setting the Default properties, we create one user define property, LogMessages. This tells Jet to log the messages returned by the PRINT statement to a table in the local database, PROCS.MDB.

From Here...

In this chapter you were introduced to the details of implementing a Visual Basic program that accesses data stored on a server using a commercial database product, Microsoft SQL Server. This approach adds power to Visual Basic programs by freeing them from the limitations of ordinary PC-based database management products. It will allow you to implement systems that scale to hundreds of users while maintaining the advantages of application development using Visual Basic.

In order to increase your understanding of databases and client/server, you should examine the following chapters:


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.