Chapter 21 - Communicating with SQL Server
- Different approaches for developing with SQL Server - You have several options for the specifics of how you'll communicate with SQL Server, making the versatility of the platform a good tool at your disposal.
- Cross-platform development capabilities - All of the major development platforms from Microsoft and Borland are "SQL-ready."
- Exposed object model for SQL administration - You can even create administrative modules for your SQL Server using new object-oriented techniques.
Developing applications for SQL Server can take many different forms. As you've seen throughout this book, queries executed by the server are at the heart of the system. The queries you've worked with thus far are all based in the SQL language and are generally entered or executed from the ISQL or ISQL/W utilities.
This approach won't prove very useful, however, when you're developing an application to work with SQL Server information. The client applicationthat portion of the program responsible for information formatting, presentation, and other user interactionis where the power of SQL Server is presented to the average user. The way your application interfaces with the SQL Server can be just as important as all of the hard work you've done behind the scenes in defining the tables, creating the stored procedures, and optimizing the system to work at its peak.
In this chapter, we'll review a few of the top methods of working with SQL Server from an application development perspective. Each of these technologies is a comprehensive environment for working with SQL Server, and each warrants far more coverage than is afforded here. This information is provided so you'll have a starting point of knowledge for selecting the method that is right for you.
The most common methods of working with SQL Server are:
The coming sections will review the basics of each of these options and explain how they may be useful in your work with SQL Server.
DB-Library, or "DB-LIB," as it's often referred to, is an API for both C and VB that allows you to work directly with SQL Server. The API provides you with the different tools you need for sending queries to, and receiving information from, SQL Server. It also provides the means for working with that information by allowing you to extract information from the results sets returned from your queries.
To use DB-LIB, you'll need to include several different supplemental files with your project. Table 21.1 shows the different files you'll need in the VB and C environments.
Some of the files listed in the following tables might not have been included with your version of SQL Server. If this is the case and the files are necessary, they can be obtained through the SQL Server SDK from Microsoft.
Table 21.1Required Components for DB-LIB
Table 21.2 shows the different components that you'll be working with as you develop applications with Borland's developer tools. Be sure to use the memory model that matches your application development environment.
Table 21.2Borland DB-LIB Components
|BLDBLIB.LIB||Large memory model static library|
|BMDBLIB.LIB||Medium memory model static library|
You'll use slightly different components when developing with Microsoft environments. Table 21.3 shows the required elements in this environment.
Table 21.3Microsoft-Oriented Components
|MSDBLIB3.LIB||Windows: Import library|
|NTWDBLIB.LIB||Win32: Import library|
|RLDBLIB.LIB||DOS: Large memory model library|
|RMDBLIB.LIB||DOS: Medium memory model library|
Working with DB-LIB typically follows a standard cycle of calls. Figure 21.1 shows the typical chain of events, starting with a logon to the SQL Server that you need to access.
Fig. 21.1 - You typically log on, perform one or more calls to SQL Server, and then log off when you're using DB-LIB in your applications.
Two structures are used to establish your connection to the server. Both dbproc and login are used to establish and continue communications between your application and the SQL Server.
You use the DBOpen API call to initiate the connection to the server. DBOpen will initialize the DBProcess structure, giving you the information you need to continue working over the same connection to the server. Table 21.4 shows the different ways you accomplish these steps in the VB or C languages.
Table 21.4Basic Components for SQL Server Communications with DB-LIB
|Initialize new loginrec structure||SqlLogin%||dblogin|
|Set user name for login||SqlSetLUser||DBSTLUSER|
|Set user password for login||SqlSetLPwd||DBSTLPWD|
|Set client application
|Open the connection to SQL Server||SqlOpen%||dbopen|
|Close the connection to SQL Server||SqlClose||dbclose|
|Close all connections ||SqlExit||dbexit|
Using these statements, you create a new login structure and populate the required fields. There are other properties of the login structure, but the user name and password are the only required items.
If you are using integrated security, these fields are required, but they are ignored by the server when the connection is made. The server will use the user's credentials from the currently logged-on user. In this situation, if you know ahead of time that integrated security is used, you may want to pass in arbitrary information in these fields (for example, a user name and password of "blah" will suffice). Since the users will be authenticated by their network sign on, the user ID and password are not needed. The sign-on presents the user with an additional dialog box.
The descriptive name is not required, but it is strongly recommended. The reason for supplying this information is simple. If you have a system administrator working with the SQL Server and reviewing the open connections, the name you provide here will be shown in the connection listing. By providing meaningful information, the administrator will know who is on the server at any given point. Because of that, you should avoid the temptation to just sign in all users on an application as the application name. If you can provide the application name and the station ID or user ID, you'll be adding some key identifying elements that the administrator can use.
Using the login structure and the server name, the call to open the connect is made, and the server connection is opened. When you issue the Open command, a structure is established and returned by the call. In future calls to the server, you'll be using this structure pointer whenever you issue a command to the server.
SQL Server login and logout operations are among the most costly transactions in terms of performance. If you log in each time you need to make a call or series of calls and then log out, you'll find that your application can be slower than you'd expect.
To remedy this, consider using a separate connection for each major classification of work that is to be done. For example, if you have a point-of-sale system, at the front counter you may want to develop a system to maintain the connection open to the Inventory table and the Accounts Receivable and Cash Drawer tables to help performance.
By doing so, you can take the time up front, during the loading of the application, to create your connections to SQL Server. Later, since the connections will already exist, the amount of time to access the separate tables will be minimized, and the user will have a much easier time using your application.
One consideration in this scenario can be the number of licensed connections you have to SQL Server. You need to make sure you end up running the number of licenses you have purchased for your SQL Server. If you use more, you may have to re-think your application, purchase additional licenses, or do both in order to have an optimal price-performance installation.
When you send statements to SQL Server, you first build them in the SQL command buffer. Putting the commands into the buffer is done by calling SqlCmd with the parameters you need to place in the buffer.
The examples provided here are largely VB-related. Though the actual statement varies in C, the calling conventions are similar and require many of the same approaches as apply to VB.
The syntax for SqlCmd() in VB is:
Status% = SqlCmd(MyConnection%, "<statement>")
Each statement is appended to the previous statement, if any, that is currently in the SqlCmd structure. Be careful of building your statement if it requires more than one line. Remember that the string you specify is simply concatenated with any prior information in the buffer. Consider the following code sample:
Status% = SqlCmd(MyConnection%,"Select * from pubs")
Status% = SqlCmd(MyConnection%,"where author like 'A%')
Why will this statement provide a syntax error and fail? There would not be any resulting spaces between "pubs" and "where" in the example. Be sure to provide spaces in these situations, or you'll generate syntax errors as shown.
Once you've created the buffered statement, you need to send it to SQL Server to be executed. You use the SqlExec statement to accomplish this.
Status% = SqlExec(MyConnection%)
Since you've queued up the commands and associated them with the particular connection, SqlExec will know exactly what you're executing. It sends the buffered statement to SQL Server and allows SQL to translate and run your request. In this example, the entire command that would be sent to SQL Server would be "Select * from pubs where author like 'A%'" (adding in the required spaces as indicated earlier).
If you want to call a stored procedure, you can create your command and preface it with Execute. For example:
Status% = SqlCmd(MyConnection%,"Execute GetAuthors 'A%'")
In this case, you'd be executing a stored procedure called GetAuthors and passing a parameter, 'A%' to the stored procedure, presumably to be used as a search value. Executing the call and processing the results occur the same as if you had issued a select statement.
Once you've sent your request to SQL Server, you'll need to be able to work with the information returned from the query. To do so, you'll need to use two constants to monitor your work with the data sets. These constants are defined in the .BAS files, which are required to develop with the DB-LIB libraries.
What you're doing when you process returned results sets from SQL Server is to walk down through the rows returned until you receive NOMOREROWS, indicating that all rows which were returned have been accessed by your application. You can retrieve the current status of the record set by using the SqlResults% function. This function will return either SUCCEED or NOMOREROWS, and your application can determine what to do next based on this information.
Status% = SqlResults%(MyConnection%)
You should call SqlResults before launching into any processing loops. This will ensure that you're not working with an empty data set. If you have successfully returned information from your query, you can loop through the results by using SqlNextRow%. SqlNextRow, as the name suggests, will work down through the rows in your results, one at a time. The results are placed into the working buffer so you can work with them. When SqlNextRow hits the end of the data set, it will return NOMOREROWS, allowing your application to stop processing the data set.
Results returned from DB-LIB's functions are enumerated, rather than named, properties. As you work with columns returned, you'll be indicating the column by number, not name. You'll need to keep in mind the order in which you specify the columns in your select statement or stored procedure. Otherwise, the information column you requested may not return what you expect, as it would be returning a different column's information.
The final step in working with the information is to retrieve it from the buffer. SqlData and SqlDatLen are the functions that are regularly used to work with this information. The code sample below shows how a sample processing loop would be implemented, allowing you to print the author name.
After you've finished working with a given connection, you should make sure to close the connection, freeing up the memory associated with it and releasing the connection to the server. The SqlClose function will close an associated connection for just this purpose.
Status% = SqlCmd(MyConnection%,"Select au_lname from authors")
Status% = SqlExec(MyConnection%)
While SqlNextRow%(MyConnection%) <> NOMOREROWS
Print "No more information to present."
Status% = SqlClose%(MyConnection%)
You will need to close each connection you open for access to the server. Alternatively, you may wish to call SqlExit, which will close all currently open connections to the server. If your application has completed and is exiting, it may be easier to use the SqlExit statement to ensure that all connections are closed properly.
Aside from distributing the OCX with your client application, no other modules are required with the client application. The functionality of the DB-LIB add-in is provided in the OCX and .BAS files.
In the C environment, you'll need to include the appropriate DLLs with your application and network environment. The DLLs will vary depending on the LIBs you employ, as mentioned previously. Please refer to Table 21.2 and Table 21.3 for more information.
DB-LIB is a SQL Server-specific interface layer. This means that, of the three different options presented here, this is the least "portable" between back-end database servers but also one of the faster ways to access information. This is due not only to the fact that it's an optimized interface, but also that you're developing directly in the host language. The other options, ODBC and SQL OLE, offer similar services but also impose an abstraction layer between your application and the calls to SQL Server.
One thing you may notice is that DB-LIB is very "manual" in how it is implemented. This is because you create and issue the Select statements, you create and issue the Update statements, etc. There is no concept of "bound" or automatically updating values. This can be good in that you can control the interface to the server, optimizing the connections and making sure that all data meets your criteria. In addition, you have complete control over the error trapping associated with the transactions.
DB-LIB is an excellent API-level interface to SQL Server. Keep in mind that this is not the method you'll be using to work with SQL Server through more user-oriented tools like Access and Excel. These types of tools use an abstraction layer, Open Database Connectivity (ODBC), to make working with the database less developer-intensive.
If you've been working in the personal computer industry for any length of time, you know that there are a significant number of database applications which different people have installed to address different needs. Gone are the days when you could count on a specific database type at the installation site. This is especially true if you're developing a utility program, such as one that is expected to query a database, regardless of where that database came from, who designed it, etc.
ODBC attempts to rectify this, although, as you'll see, there are some costs involved with this approach. Your best-case solution will depend on a number of factors, including how diverse the database types are in the location or locations where you plan to deploy any given solution.
To address the concern of connectivity to, and between, database systems, Microsoft developed the ODBC approach. ODBC is a layer of abstraction between the application and the underlying database system. This layer allows you to issue one Select statement and have that statement run against any supported database, including some cases where the databases do not directly support the SQL language.
Figure 21.2 shows the relationship of ODBC to the application. ODBC serves as the access layer to the operating system and database files. ODBC is responsible for taking your request for information and changing it into the language the database engine will understand and use for retrieving the information in the database.
Fig. 21.2 - ODBC insulates the application developer from the specifics of the database structure and access methods.
ODBC presents a common interface to your application. This allows you to develop to a common set of calls and methodologies without having to worry about the subtleties of the underlying database. You can see an excellent example of this in Microsoft Access. In Access, you can choose to link or attach a table to a database. When you do, Access will prompt you for the type of database table you want to work with. You have the option of selecting from several formats that Access works with directly, or you can simply select ODBC. When you do, you are presented with the different ODBC configurations you've established. Thus, you are able to select any one of them, without regard to database engine.
Access will be able to attach the table to the database because it won't know about, or care about, the database, only that it can use the database table with standardized SQL statementsthe key to ODBC.
Since the main purpose of ODBC is to abstract the conversation with the underlying database engine, the use of ODBC is somewhat transparent once you're connected. This is different when compared with DB-LIB reviewed earlier. DB-LIB required special syntax to buffer statements and work directly with the server. ODBC, on the other hand, requires only that you create the standardized SQL statement and then pass that statement to ODBC.
When you work with ODBC in your application, you are working with a data source and the database engine it references. As you'll see under the Client Configuration section, when you install ODBC, you should install not only the overall ODBC subsystem but also driver-to-database combinations. These combinations are given a name and then used in your connection request when you want to access the database they refer to. These database and driver combinations are called Data Source Names, or DSNs. When you open an ODBC connection and don't otherwise specify this information, ODBC will step in and prompt you for it.
In most languages, when you specify the connect string for ODBC, you'll have a couple of options. First, you can specify only that it's an ODBC connection you want to open, in which case ODBC will step in and prompt you for the DSN to use.
Set db = OpenDatabase("",,"odbc;")
In this case, the information provided by the user will determine the database that is opened. You can also specify the connection to use by indicating the DSN, UserID and Password for the connection, as applicable.
If you allow your user to specify the ODBC connection to use, you may end up working against a database that you have not planned to interact with. In nearly all cases, your application should provide the DSN information that will allow ODBC to connect to the database, ensuring that you know the database schema for the information sources you're accessing.
Set db = OpenDatabase("",,"odbc;<DSN Info>")
Your second option is to indicate the details for the connection in the connection string itself. In this type of connection, <DSN Info> represents any of the different items you can specify as part of the DSN. Some of the more commonly used items are shown in Table 21.5.
Table 21.5Common DSN Elements
|DSN||The DSN name you have configured in the ODBC settings|
|UID||The userID to use to log in to the database|
|PWD||The password to use for the login|
For example, consider the following sample VBA statement:
Set db = OpenDatabase("",,"odbc;DSN=MyDSN;UID=MyUserName;PWD=MyPassword")
This will connect to the ODBC data source using the MyDSN configuration. It will also use the user and password indicated in the parameters. Using this command, the user will not be prompted for ODBC DSN information but will be connected automatically.
In this example, the db variable represents a VB variable declared as a Database object type. In this example, the db variable will be the reference point for future actions against the database.
In this example, we're using some of the Data Access Objects, or DAO, to access the ODBC data source. Using this access method, you can work through the tables, fields and information stored in the database system by using common objects and object browsing methodologies. For a simple example, consider the following procedure:
Listing 21.1Example of Connecting to ODBC with DAO
'set up the variables
Dim db As DATABASE
Dim i As Integer
'connect to the database
Set db = OpenDatabase("", , "odbc;DSN=BILLING;UID=SA;PWD=;")
'Determine how many tables there are and then print
i = db.TableDefs.Count
Debug.Print "There are " & Str$(i) & " table(s) in this database."
'Close the connection
The output from this routine will be a statement indicating the number of tables in the database. By using the object-oriented nature of DAO, it's easy to work quickly with the database connection once it's been established.
The final step to working with SQL is to close the connection. The specifics of how you'll close it may vary between host languages, but, in VB or VBA, you can simply use the .Close method. This will close the connection to the database and free up the memory structures associated with the connection.
ODBC drivers are installed when you install SQL Server client utilities. They are also installed or updated when you install several Microsoft products such as Office '95 and Access '95. The drivers are installed on your system, but you still need to create the specific DSNs that you'll be referencing when you open a connection to the database.
The ODBC Administrator is located on the Control Panel (see fig. 21.3).
Fig. 21.3 - The ODBC Administrator is used to manage new and existing ODBC DSNs.
The Administrator will allow you to select from the known ODBC connections so you can make any necessary changes. You can also add new DSNs to your system. Figure 21.4 shows the initial ODBC DSN listing, and the different options you can access to manage the DSNs.
Fig. 21.4 - The DSN names listed are the names you specify in the ODBC connection string.
In the next section, you'll see how you can work with new and existing DSNs as well as set up your system so you can take advantage of ODBC in your applications.
From the ODBC Data Sources dialog box, you have two options that relate to managing ODBC connections. The Setup and New options will let you specify the different characteristics of the DSN's you establish. Figure 21.5 shows a sample dialog of options for setting up a SQL Server connection. Note that the dialog box is the same for both setting up a new connection and making changes to an existing connection.
Fig. 21.5 - When you set up ODBC connections, you should indicate as much information as possible to make connecting easier for the user of your applications.
The figure shows the Options portion of the dialog extended, allowing access to the default database and other less-often used options. The key options that you should always set up are:
- Data Source Name
You should always try to establish the Database Name, as well. Doing so will help ensure that, when the connection is made, it will be to the correct database and will not rely on the default database assigned to the user that is logging in.
Since ODBC provides the abstract access to just about any popular database format available, it brings a fair amount of leverage to your development effort. A key element in the ODBC framework is the capability for your DSN name to refer to any database. This allows you to develop against an Access database and implement your system in production against a SQL Server just by changing the drivers used by the DSN you've defined for the application.
The abstraction of the calls to the database engine is not without cost. The biggest downside to ODBC is that it must be able to support the capability to translate the calls. This means that additional processing overhead can slow the data access a bit. With ODBC, you can gain a significant speed advantage with a true client/server implementation. By taking the processing away from the client and into the server within SQL Server, you can eliminate much of the scrolling of information that is one of the primary slowing points for ODBC.
Consider using stored procedures as the basis for your ODBC calls when database engine processing is required. This will save processing time on both ends of the spectrum.
As you may already be aware, the vast majority of development in emerging technologies is going on in object-oriented development. Microsoft, and other software tool builders, has a major push to bring out into the public object methods and classes that can be reused by third-party developers. This can be seen in the Office '95 suite of applications in their consistent use of Objects and Classes to work with their different components. Everything from sheets in Excel to TableDefs in Access is now accessible with objects and collection.
This accessibility is no different with SQL Server. In one of the newest developments for developers of SQL-based applications, the SQL OLE interface allows you to work with SQL Server by using objects, methods, and collections that relate to your database.
By including Type Library (TLB) references in your application environment, you can begin using the OLE automation objects to work with SQL Server. The TLB that you'll need for Visual Basic applications is SQLOLE32.TLB. This library will expose the methods you'll be using.
In short, you'll use the .Connect method for attaching to SQL Server to begin working with the database. You'll then be able to use the different objects and collections to perform the administrative tasks associated with SQL Server. It may be easiest to review the SQL Enterprise Manager for examples of how these containers and objects relate to one another. Figure 21.6 shows the top-level hierarchy for the SQL OLE objects.
Fig. 21.6 - The SQL OLE objects allow full access to the administrative objects in SQL Server. You can write the equivalent of an Enterprise Manager using these objects.
You work with the SQL OLE objects by first setting up the object variables that refer to the SQL OLE objects. The first step is to set up the reference to the TLB for SQL OLE.
Once you've established the references, you can create the routines you need to work with the objects. Listing 21.2 shows setting up a reference to the Database object, followed by printing the tables in the collection to the debug window.
Listing 21.2Using SQL OLE in Visual Basic Code
Sub SQL OLEDemo()
'declare the variables
Dim objSQLServer As New SQL OLE.SQLServer
Dim objDatabase As New SQL OLE.DATABASE
Dim objTable As New SQL OLE.TABLE
'connect to the server
objSQLServer.Connect "pluto", "sa", ""
'get the PUBS database
Set objDatabase = objSQLServer("pubs")
'iterate through the tables
For Each objTable In objDatabase
'disconnect from the server
Set objSQLServer = Nothing
As you can see, by using the objects and collections in the SQL OLE suite, you can easily navigate the SQL Server you are administering. This gives you the added benefit of providing a concise set of capabilities to your users, possibly limiting access to certain features, while granting ready access to others.
SQL OLE requires a good working knowledge of the architecture of SQL Server. If you haven't spent very much time in the Enterprise Manager, it may be somewhat difficult to picture the object model and work within it. This can be a hindrance for people developing their first application to manage SQL Server. It's probably not a good idea to use SQL OLE and building administrative applications as your first work with SQL Server. SQL OLE will be more meaningful and more useful to you after you've had a chance to become used to the object-oriented nature of managing SQL Server using the native tools available.
On the plus side, the SQL OLE objects provide you with easy, comprehensive, and ready access to the objects that make up the SQL Server's core functionality. By knowing these objects inside and out, you'll be able to provide more comprehensive administration of the system, simply because you'll know and understand the relationships between objects. Also, as mentioned previously, if you have people that are responsible for administering certain aspects of the system, but you need to limit their overall access to high-level tasks, a custom administrative application can be just the ticket. Also, there is no easier way to work with the SQL objects than using the SQL OLE suite of capabilities.
You've had a whirlwind tour of the different techniques and technologies that are available to you for working with, and administering, your SQL Server system and its databases. By combining these technologies with the comprehensive coverage throughout this book on the SQL language and the capabilities of SQL Server, you'll be able to develop comprehensive applications for working with SQL Server.
From here, you may want to consider reviewing the following related materials:
QUE Home PageFor technical support For our books And software contact email@example.comCopyright © 1996, Que Corporation
Table of Contents
20 - Setting up & Managing Replication
22 - Accessing SQL Server Databases Through Front-End Products