Writing External Applications to Query and Manipulate Database Data

Creating a Data Source Name (DSN) for an SQL Open Database Connectivity (ODBC) Connection

A data source name (DSN), as its name implies, is a name by which a data source is known to the programs running on your computer. After you attach a DSN to a DBMS (like MS-SQL Server, Oracle, or DB2, for example), your applications can use the Open Database Connectivity (ODBC) driver to connect with the DBMS by name and send SQL statements to it. The ODBC driver not only passes your SQL commands to the DBMS for execution, but also returns query results back to your program.

After you install and start an SQL server on an accessible network workstation, server, or standalone personal computer, you can create a DSN for the DBMS by performing the following steps:

1. Open the Control Panel by selecting the Windows Start menu Settings option and clicking your mouse pointer on Control Panel.
2. Double-click your mouse pointer on the ODBC Data Sources icon. Windows will start the ODBC administrator, which will display an ODBC Data Source Administrator dialog box similar to that shown in Figure 368.1.

   Figure 368.1: The User DSN tab of the ODBC Data Source Administrator dialog box

3. Click your mouse pointer on the Add button. The ODBC administrator will start the Create a New Data Source Wizard, which will prompt you to select the driver to use in communicating with the database.
4. To work with the ODBC driver that communicates with an SQL server, click your mouse pointer on SQL Server in the scroll box on the wizard's Create New Data Source dialog box. Then click your mouse pointer on the Finish button. The wizard will prompt you to describe the SQL server with a dialog box similar to that shown in Figure 368.2.

   Figure 368.2: The Create a New Data Source Wizard's database description (second) screen

5. Enter the name (the DSN) that you want your applications to use when they need to communicate with the SQL server in the Name field. The DSN you enter does not have to be any particular name. For example, you could use the name of the SQL server or the name of the DSN's default database base on the server. For the current project, enter MSSQLServer.

   Note

   If you plan to set up DSNs for multiple SQL servers, you may want to include the name of the server in each DSN. If you do so, each name will tell you which DSN to use when you want a program to communicate with a specific server. For example, if you have an MS-SQL Server running on an NT server named NVBIZNET2, enter a descriptive DSN similar to MSSQLServer_NVBizNet2 in the Name field.

6. Enter a description of the data source data source in the Description field. For the current example, enter Microsoft SQL Server Database.
7. Enter the name of the server on which the DBMS is running in the Server field. If the DBMS is running on the computer on which you are defining the DSN, you can click your mouse pointer on the drop-down list button to the right of the Server field and select "(local)".
8. Click your mouse pointer on the Next button. ODBC will prompt you for username and password information as shown in Figure 368.3.

   Figure 368.3: The Create a New Data Source Wizard's username/password (third) screen

9. If your MS-SQL Server uses Windows NT Integrated Security (which you learned about in Tip 136, "Understanding MS-SQL Server Standard and Windows NT Integrated Security") you can accept the defaults shown in Figure 368.3. SQL Server will assume that Windows NT authenticated the username/password pair at login. To have the SQL Server authenticate the username/password pair instead, click your mouse pointer on the radio button to the left of With SQL Server Authentication Using a Login ID and Password Entered by the User, and then enter your username and password for the MS-SQL Server in the Login ID and Password fields, respectively.
10. Click your mouse pointer on the Next button. The wizard will try to make a connection to the SQL server you specified in Step 7. If you do not have an SQL server running or if the DBMS does not recognize the username/password pair you entered in Step 9, ODBC will hang momentarily (less than a minute) and then display an error message that explains why the connection attempt failed. To create a DSN, your session must be able to connect to the DBMS you are trying to name. Therefore, take the necessary corrective actions and then repeat Step 10.
11. After using the username/password pair specified in Step 9 to connect to the DBMS selected in Step 7, the wizard will prompt you for the SQL database defaults with a dialog box similar to that shown in Figure 368.4.

    Figure 368.4: The Create a New Data Source Wizard's database defaults (fourth) screen

12. To select the default database for applications using the DSN you are creating to connect to the DBMS, click your mouse pointer on the check box to the left of the Change the Default Database To label until a check mark appears.
13. Click your mouse on the drop-down list button to the right of the default database name field and select the default database from the drop-down list. For the current example, click your mouse pointer on Northwind. Then click your mouse pointer on the Next button.
14. Depending on the version of ODBC running on your computer, the wizard will display one or two more setup screens. Simply click your mouse pointer on the Next button to accept the defaults on each screen until you reach the wizard's final screen, where you will click your mouse pointer on the Finish button. The wizard, in turn, will display an ODBC Microsoft SQL Server Setup dialog box similar to that shown in Figure 368.5.

    Figure 368.5: The Create a New Data Source Wizard's test ODBC settings (final) screen

15. Click your mouse pointer on the Test Data Source button. The wizard will attempt to connect to the DBMS using the settings you entered in Steps 5-13. If the test is unsuccessful, go back through the DSN setup procedure and make any changes called for by the wizard's error dialog boxes, and then repeat Step 15. After the wizard indicates it successfully completed its data source tests, click your mouse pointer on the OK button on the Test Results dialog box.
16. Click your mouse pointer on the OK button near the bottom-right corner of the ODBC Microsoft SQL Server Setup dialog box.

After you complete Step 16, the Create a New Data Source Wizard will register the new DSN on your computer and display its name and description in the User Data Sources list box on the ODBC Data Source Administrator dialog box. To exit the ODBC Data Source administrator application, click your mouse pointer on the OK button (first button on the left) in the row of buttons at the bottom of the ODBC Data Source Administrator dialog box.

Adding the Data Control Component to a Visual Basic (VB) Form to Retrieve SQL Table Data

The Visual Basic (VB) Data Control component supplies subroutines (methods) a VB application can use to work with data stored in an SQL database. In fact, after adding the Data Control component to a VB form, your application will be able to perform most data access operations without you having to write any code at all. Moreover, if you bind an MSFlexGrid Control to the Data Control (which you will learn to do in Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data"), your VB program will have all of the tools it needs to retrieve, manipulate, and display data from one or more tables on SQL servers to which you have access rights.

To add a Data Control component to a VB form, perform the following steps:

1. If you have not already done so, start MS-Visual Basic (VB) and open a Standard.EXE project.
2. Double-click your mouse pointer on the Data icon on the VB toolbox along the left-hand side of the VB application window shown in Figure 369.1. VB will add the Data Control component to the currently active form (Forml, in the current example).

   Figure 369.1: The Microsoft Visual Basic 5.0 application window

3. Use your mouse pointer to drag the Data Control toward the lower-left corner of your form. (By the time you finish the current project, which spans Tips 369 and Tip 370, you will have positioned and sized the Data Control below an MSFlexGrid Control as shown in Figure 370.2.)
4. To work with the Data Control properties, select the View menu Properties Window option (or press F4).
5. Click your mouse pointer on the Caption property label. (The Data Control's property labels [and properties] are listed alphabetically on the Alphabetic tab of the Properties window.)
6. Click your mouse pointer in the Caption property field (immediately to the right of the Caption property label), and enter the label you want VB to display on the Data Control. For the current project enter: Product/Supplier in the Caption property field.
7. Click your mouse pointer on the DatabaseName property label. Then click your mouse pointer in the DatabaseName property field and enter the data source name (DSN) for the SQL server with the database you want to access. If the database you want is located on the SQL Server whose DSN you created (registered) in Tip 368, "Creating a Data Source Name (DSN) for an SQL Open Database Connectivity (ODBC) Connection," enter MSSQLServer in the DatabaseName property field.
8. Click your mouse pointer on the Connect property label. Then click your mouse pointer in the Connect property field and enter the information that the ODBC Driver Manager needs to connect with your SQL server in the form:

   ODBC;UID=;PWD=;DATABASE=

   For example, to connect to an SQL server as user KONRAD with password KING and use the NORTHWIND database, you would type

   ODBC;UID=KONRAD;PWD=KING;DATABASE=NORTHWIND

   in the Connect properties field. (Be sure to enter you own username and password in place of username KONRAD and password KING shown in the current example.)

   Note

   You can omit the DATABASE= portion of the connect string if the DSN defaults to the database you want to use, or if the DBA has assigned it as the default database for your username.

When your VB application uses the Data Control component's methods to access the database, Windows will combine the Data Control's connection properties and the DSN connection settings, and attempt to establish a connection with the SQL server. If you omit the username or password (or if the username/password pair you entered in the Connect property field is invalid), the ODBC driver will prompt you to enter the proper username/password pair to log in to the SQL server with the DSN you entered in Step 7.

(Tip 370 will show you how to bind an MSFlexGrid Control to the Data Control so you can display data you retrieve, and Tip 371, "Adding Text and Button Controls to a Visual Basic (VB) Form to Create an Application That Sends a Query to an SQL Server," will show you how to add a text field and Search button for interactive queries to the VB form.)

Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data

The MSFlexGrid Control gives a VB application the ability to display and work with tabular data. Using the MSFlexGrid Control methods (subroutines), the VB program can sort, merge, and format tables that contain text or graphics images. If you bind the MSFlexGrid Control to the Data Control (you added to a VB form in Tip 369, "Adding the Data Control Component to a Visual Basic (VB) Form to Retrieve SQL Table Data"), you can use it to display read-only data that the Data Control retrieves from SQL server tables.

To add an MSFlexGrid Control to a Visual Basic form, perform the following steps:

1. If you have not already done so, start MS-Visual Basic (VB), open a Standard.EXE project and add a Data Control to the form (by following the procedure in Tip 369).
2. To add the MSFlexGrid Control component to your project, right-click your mouse pointer on the project toolbox (along the left-hand side of the VB application window) and select Components from the pop-up menu. VB, in turn, will display a Components dialog box similar to that shown in Figure 370.1.

   Figure 370.1: The Microsoft Visual Basic 5.0 Components dialog box

3. Use the scroll bar to the right of the Components list box on the Controls tab to scroll the Control Components list until you see the Microsoft FlexGrid Control. Then click your mouse pointer on the check box to the left of Microsoft FlexGrid Control 5.0 until the check mark appears. (Select the latest version of the MSFlexGrid Control available on your system.)
4. Click your mouse pointer on the OK button near the bottom center of the Components dialog box. VB will return to the VB application window and add the MSFlexGrid Control as the last control component in the VB toolbox.
5. Double-click your mouse pointer on the MSFlexGrid icon to add an MSFlexGrid Control to your VB form.
6. To work with the MSFlexGrid Control properties, select the View menu Properties Window option (or press F4).
7. Click your mouse pointer on the DataSource property label. (The MSFlexGrid Control's property labels [and properties] are listed alphabetically on the Alphabetic tab of the Properties window.)
8. Click your mouse pointer on the drop-down list button to the right of the DataSource property field (immediately to the right of the DataSource property label) and select the name of the Data Control that will supply data to the MSFlexGrid. For the current project, you added a single Data Control named Data1 to the VB form in Tip 369. As such, select Data1 from the drop-down list.
9. Right-click your mouse pointer on the MSFlexGrid Control (on your VB form) and select Properties from the pop-up menu. VB will display the Properties Pages dialog box.
10. Set the number of rows and columns of data you want the MSFlexGrid Control to display. Make the number of columns equal to the number of columns you expect the SQL query (which you will learn how to enter and execute in Tip 371) to return. For the current project, enter 6 in the Rows field and 4 in the Columns field on the General tab of the Properties Pages dialog box.
11. Enter the number of (fixed) headings columns and rows the MSFlexGrid is to display in the Fixed Cols field. For the current project, enter 1 in the Fixed Rows field and 0 in the Fixed Cols field. (You don't have to enter the column headings; VB will fill them in at run time.)
12. To let the user resize the columns while the VB application is running, click your mouse pointer on the drop-down list box to the right of the AllowUserResizing field and select 1-Columns from the drop-down list.
13. Click your mouse pointer on the OK button at the bottom left of the Properties Pages dialog box. VB will return to the VB design application window.

After you complete Step 13, your VB form will contain an MSFlexGrid control bound to a Data Control (named Data1, which you added to the VB form in Tip 369).

Now, use your mouse pointer to arrange the controls and size them (by using the mouse pointer on the each control's sizing handles), such that your VB form appears similar to that shown in Figure 370.2.

Figure 370.2: A VB form with an MSFlexGrid Control (at the top) and Data Control (near the bottom)

You will learn how to add a text field and a Search button for interactive queries to the VB form in Tip 371.

Adding Text and Button Controls to a Visual Basic (VB) Form to Create an Application That Sends a Query to an SQL Server

In Tip 369, "Adding the Data Control Component to a Visual Basic (VB) Form to Retrieve SQL Table Data," you learned how to place a Data Control on a VB form to give your application the methods (subroutine calls) it needs to retrieve data from an SQL Server. Then Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data," showed you how to add an MSFlexGrid Control to display the data retrieved. Now you need only add a Text Control that lets a user enter an SQL query and Button Controls that tell VB to send the query to the SQL Server, and you will have a VB application that can query an SQL DBMS and display its data.

To add Text and Button Controls to a VB form, perform the following steps:

1. Double-click your mouse pointer on the TextBox icon in the VB toolbox along the left side of the VB application window. (If you do not recognize the TextBox icon, move your mouse pointer over each icon in the VB toolbox and let VB display the name of the control the icon represents.)
2. To work with the TextBox Control properties, select the View menu Properties Window option (or press F4).
3. Click your mouse pointer on the "(Name)" property label. (The TextBox Control's property labels [and properties] are listed alphabetically on the Alphabetic tab of the Properties window.)
4. Click your mouse pointer in the "(Name)" property field (immediately to the right of the "(Name)" property label) and enter the name you want to use when referring to the contents of the form's text box. For the current project, enter SupplierName in the "(Name)" property field.
5. Click your mouse pointer on the Text property label. Then click your mouse pointer in the Text property field, and delete its contents.
6. To add a CommandButton Control to the VB form, double-click your mouse pointer on the CommandButton icon in the VB toolbox along the left side of the VB application window.
7. To work with the CommandButton Control properties, select the View menu Properties Window option (or press F4).
8. Click your mouse pointer on the "(Name)" property label. Then click your mouse pointer in the "(Name)" property field and enter the name you want to use when referring to a mouse pointer click on the button. For the current project, enter SearchButton in the "(Name)" property field.
9. Click your mouse pointer on the Caption property label. Then click your mouse pointer in the Caption property field, and enter the label you want VB to put on the button. For the current project, enter Search in the Caption property field.
10. Repeat Steps 6-9, only this time, enter CloseButton in the "(Name)" property field in Step 8, and enter Close in the Caption property field in Step 9.

After you complete Step 10 (for the second time), position and size the two CommandButton Controls and the TextBox control on the VB form as shown in Figure 371.1.

Figure 371.1: A VB SQL query form with a Data Control, an MSFlexGrid Control, a TextBox Control, and two CommandButton Controls

Now that you have all of the controls on the VB form, all that is left to do is to write the code you want the computer to execute when a user clicks the mouse pointer on each of the command buttons. Suppose, for example, that you want the Search button to send the SELECT statement

SELECT productid 'Product ID', productname 'Description', companyname 'Supplier', s.supplierid 'ID' FROM products p, suppliers s WHERE p.supplierid = s.supplierid AND companyname LIKE '%:SupplierName%' ORDER BY supplier, description

which lists all products (in the PRODUCTS table) supplied by the supplier whose name (or partial name) the user entered in the form's SupplierName field (its TextBox Control) to the SQL server.

To add code to the SEARCH button, double-click your mouse pointer on the CommandButton Control with the "Search" label to open the VB code panel to the SearchButton method (subroutine), and enter the VB code as shown in Figure 371.2. To add code to the Search button, double-click your mouse pointer on the CommandButton Control with the Search label to open the VB code panel to the SearchButton method (subroutine), and enter the VB code as shown in Figure 371.2.

Figure 371.2: The code pane of the VB application window with the code for the CommandButton Control named SearchButton

As a quick explanation of the Search button's code, consider the following:

• Data1 is the name you gave the Data Control in Tip 369.
• The SQL statement you want the SQL server to execute must be placed in the Data Control's RecordSource property.
• Placing the enumerated constant dbSQLPassThrough in the Data Control's Options property tells the Jet database engine not to parse the query or check its syntax, and simply to pass it on to the SQL server for processing.
• The Data Control's Refresh method call sends the query in the RecordSource property to the SQL server and then accepts and displays the query results in the rows and columns that make up the MSFlexGrid Control.

Select the View menu Object option to redisplay the VB form in the VB application window. Then double-click your mouse pointer on the CommandButton Control with the Close label to edit the code VB will execute when the user clicks the mouse pointer on the Close button. For the current project, enter

Private Sub CloseButton_Click() Unload Me End Sub

in the VB Code pane of the VB application window. Then select the View menu Object option to redisplay the VB form you created.

Finally, take your new VB application for a test drive by selecting the Run menu Start option! After the VB interpreter displays your form on the screen, enter a supplier's name into the form's text box and then click your mouse pointer on the Search button. For example, to display all products supplied by companies with "new" in their names, enter "new" into the text box and click your mouse pointer on the Search button. The VB application (you wrote) will respond by displaying products and suppliers as shown in Figure 371.3.

Figure 371.3: VB application window with the results of an SQL query of the Northwind database for suppliers with "new" in the company name

To end the VB application gracefully, click your mouse pointer on its Close button.

Creating a Simple C++ Shell for Use in Communicating with an SQL Server

As of this writing, C is still the language of choice when writing Windows applications, especially those that communicate with MS-SQL Server and other DBMS products. However, as you saw in Tips 369-371, Visual Basic's (almost) seamless interface with data sources (such as SQL servers) and ease of use are bound to give C some serious competition in the near future. This tip shows you how to write a C main program shell that you will use to connect to and communicate with an SQL server in Tips 373-383.

If you are at all like me, the first thing that you want to do when learning a new programming language is to print something to the screen. After you get over the hurdle of writing that "first program," you know that any future development is just a matter of looking up the proper function calls that you need and adding what you need to a "working" program shell that you wrote previously. Therefore, let us start with the "Hello World!" example that you have most likely seen and perhaps written several times before.

When written for a Windows environment, the MS-DOS "Hello World!" program

#include void main() { printf ("Hello world! "); return; }

becomes:

#include int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int iCmdShow) { MessageBox (0,"Hello World!","1001 SQL Tips - Tip 372", MB_OK) return 0; }

The first statement in the program (#include ) tells the compiler to include the WINDOWS.H header file that comes with every C programming environment for Windows. WINDOWS.H, in turn, has #INCLUDE statements (in the form #include .h) that tell the compiler to include additional header files that contain declarations of Windows functions, structures, data types, and numeric constants.

Every C program has a "main" entry point at which the program begins execution. When Windows starts the compiled program (the .EXE file), the system first executes a few lines of "startup" code inserted by the compiler. The compiler's startup code, in turn, calls the main function written by the programmer. For MS-DOS programs, the main entry point is the function main(); for Windows programs, the main entry point is always called WinMain.

In the current example, the "Hello World!" program uses the WINAPI calling sequence and returns a value of data type integer (int) to the Windows operating system when the program ends. (The last statement in the program [return 0;] tells the main function [WinMain] to return the value 0 to Windows after the program completes its execution.)

The first parameter that Windows passes to the "Hello World!" program, hInstance, is the instance handle, a number that uniquely identifies the program to the Windows operating system. Think of hInstance as the program's process ID (or PID), a unique number that a multitasking operating system such as Windows NT assigns to each of the programs (or tasks) that it is running concurrently. If you start multiple copies (or instances) of the same program, Windows assigns each instance a unique instance handle and then uses the hInstance parameter to pass the value of the handle to the application's WinMain routine.

The second parameter, hPrevInstance, became obsolete with Windows 95 and is retained for backward compatibility. If Microsoft were to eliminate the second parameter, all C programs running on a Windows/NT platform would have to be recompiled and redistributed. (Can you imagine the logistics of such an undertaking?). hPrevInstance was the instance handle of the most recent copy of the program started prior to the current instance that was still active. If no other copies of the program were running, Windows set hPrevInstance to either 0 or NULL. Windows versions starting with Windows 95 and NT currently set hPrevInstance NULL, regardless of the number of copies of the program currently running in sessions under the operating system.

lpCmdLine is a long (32-bit) pointer to a NULL-terminated character string that contains any command-line arguments that Windows wants passed to the program. For example, if you start the program TIP372.EXE by typing

TIP372 Parameters for startup

at the MS-DOS command line or in the Start menu Run option's dialog box, Windows will pass a pointer to the memory location in which it has stored the character string "Parameters for startup" to the program.

The final parameter, iCmdShow, tells the application how the user wants Windows to display the window for the session in which the program is running. The different values for the iCmdShow parameter are listed in the ShowWindow() Commands section of the WIN-RESRC.H header file (which is included in the "Hello World!" program by an #INCLUDE statement in the WINDOWS.H header file). Typically, iCmdShow will have a value of 1 (SW_SHOWNORMAL), to run the program in an active, normal window displayed on the Windows desktop, or 7 (SW_SHOWMINNOACTIVE), to run the application minimized as an icon on the taskbar.

Do not worry too much about the only statement in the example program that does any work:

MessageBox (0,"Hello World!","1001 SQL Tips - Tip 372", MB_OK)

The Windows MessageBox function simply displays the message "Hello World!" on the screen in a (you guessed it!) Windows message box. As you write more C programs, you cannot help but memorize the required parameters for many functions that you often use. In (almost) all cases, when you call a function, you will not concern yourself with knowing how the function does what it does. Instead, you will need to know only what the function does (instead of how it does it) and what parameters you need to pass when you call the function.

If you work the project in Tips 373-383, you will build on the basic C program shell used as an example in this tip so that you end up with a working application that you can use to access any SQL DBMS with an ODBC driver and a defined Data Source Name (DSN).

Using SQLAllocEnv and SQLFreeEnv to Allocate and Release ODBC Environment Resources

Open Database Connectivity (ODBC) is a set of functions (applications program interface [API] calls) that your programs can use to work with data in any database for which there is an ODBC driver. Because you have to know only which function calls perform what tasks, the ODBC call-level interface frees you from having the know the specifics of how each DBMS that you want to use retrieves, updates, and stores its data. You need specify only the function (or work) that you want the DBMS to perform by calling the appropriate ODBC function. The function then lets the specific ODBC driver for the DBMS that you want to access take care of the "details" involved in executing your requests.

Prior to calling any other ODBC function, an application must call SQLAllocEnv to allocate memory for an ODBC environment handle and initialize the ODBC call-level interface. Note that the parameter passed to SQLAllocEnv in the following example program is the memory address in which the function is to store the actual physical address (the handle) of the program's ODBC environment.

#INCLUDE #include #INCLUDE int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int iCmdShow) { HENV henv; //pointer to a memory location RETCODE retcode; //signed short (16-Bit) integer LPSTR retcode_text = " "; //character string retcode = SQLAllocEnv(&henv);//allocate environment handle if (retcode == SQL_SUCCESS) { /* CONNECT TO SQL SERVER & PERFORM WORK HERE */ SQLFreeEnv(henv);//free environment handle & its memory } else sprintf(retcode_text,"Error on SQLAllocEnv = %d", retcode); /* display any error messages */ if (strcmp{retcode_text," ") != 0) MessageBox (0, retcode_text, "Connect to MSSQLServer", MB_OK|MB_ICONERROR); return 0; }

In the current example, if SQLAllocEnv is successful in allocating and initializing a memory area for the ODBC environment, the pointer henv will contain for use by the application's functions the memory address (or handle) of the the application's ODBC environment. (For example, in Tip 374, "Using SQLAllocConnect and SQLFreeConnect to Allocate and Release Connection Handles and Memory Resources," you will pass the ODBC environment handle to the SQLAllocConnect function when you call it to initialize the connection parameter portion of the ODBC environment.)

Notice that the C program in the example tells the compiler to include the header file, SQLEXT.H (in addition to STDIO.H and WINDOWS.H). The SQLEXT.H header file, in turn, contains type definitions and #INCLUDE statements that tell the compiler to include the header files SQL.H, SQLTYPES.H, and SQLUCODE.H. Taken together, the four header files provide your C applications with the data types, function calls, structures, and constants they need to access data on SQL servers.

Using SQLAllocConnect and SQLFreeConnect to Allocate and Release Connection Handles and Memory Resources

SQLAllocConnect and SQLFreeConnect are similar in function to SQLAllocEnv and SQLFreeEnv, respectively. When your program establishes a connection to an SQL server, the ODBC driver must keep track of several details about your session. For example, because each command sent to the SQL server must be validated by the DBMS security mechanism, the ODBC driver keeps each connection's username and password on file. That way, the driver can pass the username/password pair along to the DBMS when its sends your statement to the server for execution. Similarly, the ODBC driver must keep track of any statements the DBMS is currently executing for a session and whether the session has any open transactions.

The ODBC driver stores all of the information it needs about each connection within a portion of the ODBC environment space (the area in memory) that you allocated by calling the SQLAllocEnv function (in Tip 373, "Using SQLAllocEnv and SQLFreeEnv to Allocate and Release ODBC Environment Resources"). While the SQLAllocEnv function maps out a portion of the computer's total memory for use by the ODBC driver interface between your application and the DBMS, the SQLAllocConnect function maps out a portion of the memory allocated by SQLAllocEnv to use as storage for details about each of the ODBC connections that the program makes to a DBMS. When you call SQLAllocConnect, the function not only maps out a portion of the ODBC environment (memory) space, but the function also returns a pointer (called the connection handle) that gives your program the starting memory address at which it can find the connection settings area within the ODBC environment area in memory.

The syntax of the SQLAllocConnect function call is

RETCODE SQLAllocConnect(henv, phdbc)

where:

• henv is the environment handle-that is, the pointer to the memory location where the program can find the memory allocated for use as the ODBC environment by the SQLAllocEnv function (which you learned about in Tip 373).
• phdbc is the database connection handle-that is, the pointer to the memory location where the program can find the settings and information on a particular connection with the DBMS.

In the example program from Tip 373, you would call SQLAllocConnect to establish a connection handle with a statement similar to this:

retcode = SQLAllocConnect(henv, &hdbc);

Notice that (as was the case with SQLAllocEnv) the second parameter is the memory address of the hdbc (pointer) variable (defined in the program with the statement: HDBC hdbc;). The SQLAllocConnect function will place the actual memory location of the connection area (the connection handle) in the hdbc variable.

Just as your application should call the SQLFreeEnv function to release memory allocated by the SQLAllocEnv function when it no longer needs it, your program should call the SQLFreeConnect function to release memory allocated by SQLAllocConnect when you no longer need the memory to hold connection information.

The syntax of the SQLFreeConnect function call is

RETCODE SQLFreeConnect(hdbc)

where:

• hdbc is the connection handle (the pointer to the starting memory location) of the connection area allocated by calling the SQLAllocConnect function.

If you add the SQLAllocConnect and SQLFreeConnect functions, the C program started in Tip 373 will look like:

#INCLUDE #include #INCLUDE int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int iCmdShow) { HENV henv; //HENV is data type: void* HDBC hdbc; //HDBC is data type: void* RETCODE retcode; //signed short (16-Bit) integer LPSTR retcode_text = " "; //character string retcode = SQLAllocEnv(&henv); if (retcode == SQL_SUCCESS) { //allocate connection handle retcode = SQLAllocConnect(henv,&hdbc); If (retcode == SQL_SUCCESS) { /* CONNECT TO SQL SERVER & PERFORM WORK HERE */ SQLFreeConnect(hdbc); } else sprintf(retcode_text,"Error on SQLAllocConnect = %d", retcode); SQLFreeEnv(henv); } else sprintf(retcode_text,"Error on SQLAllocEnv = %d", retcode); /* display any error messages */ if (strcmp(retcode_text," ") != 0) MessageBox (0, retcode_text, "Connect to MSSQLServer", MB_OK|MB_ICONERROR); return 0; }

Using SQLSetConnectOption to Set a Session Option for an ODBC Connection with an SQL Server

After you call the SQLAllocConnect function to allocate a connection handle and a memory area for a DBMS connection, you can use the SQLSetConnectOption function to set the connection's options summarized in Table 375. The syntax of the SQLSetConnectOption function call is:

RETCODE SQLSetConnectOption(hdbc, wOption, dwOpVal)

Where:

• hdbc is the connection handle (the pointer for the starting memory location) of the connection area of the ODBC environment (memory) space returned by SQLAllocConnect.
• wOption is one of the connection options shown in Table 375.1.

  Table 375.1: SQLSetConnectionOption Function Options and Option Values

  wOption (Connection Options)	dwOpVal (Connection Option Settings)
  SQL_ACCESS_MODE	SQL_MODE_READ_ONLY, SQL_MODE_READ_WRITE.
  SQL_AUTOCOMMIT	SQL_AUTO_COMMIT_OFF, SQL_AUTO_COMMIT_ON.
  SQL_CURRENT_QUALIFIER	A null-terminated character string that contains the data source qualifier. On MS-SQL Server, data source qualifiers are the database names. Therefore, the driver will send a USE statement to the DBMS, where is the string supplied in dwOpVal.
  SQL_LOGIN_TIMEOUT	The number of seconds to wait for the DBMS to complete a login request. A value of 0 disables the timeout, and the driver will wait indefinitely for the DBMS to complete the login request.
  SQL_ODBC_CURSORS	SQL_CUR_USE_IF_NEEDED, SQL_CUR_ USE_ODBC, SQL_CUR_USE_DRIVER.
  SQL_OPT_TRACE	SQL_OPT_TRACE_OFF, SQL_OPT_TRACE_ON.
  SQL_OPT_TRACEFILE	The name of the trace file in a null-terminated character string.
  SQL_PACKET_SIZE	The network packet size, in bytes.
  SQL_QUIET_MODE	The handle of the window in which the ODBC driver is to display dialog boxes. If set equal to a NULL pointer, the ODBC driver will not display any dialog boxes.
  SQL_TRANSLATE_DLL	A null-terminated character string with the name of the DLL that contains the functions SQLDriverToDataSource and SQLDataSourceToDriver that the ODBC driver is to load and use to perform tasks such as character set translation.
  SQL_TRANSLATE_OPTION	A 32-bit integer value that the ODBC driver is to pass to the translation DLL.
  SQL_TXN_ISOLATION	SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, SQL_TXN_SERIALIZABLE, SQL_TXN__VERSIONING.

• dwOpVal is the value setting for wOption. Depending on which option you are calling SQLSetConnectOption to set, dwOpVal will be either a 32-bit integer or a null-terminated character string.

Your program must call the SQLConnectOption function once for each of the session's connection options that you want to set.

When you call the SQLSetConnectOption by executing the statement

retcode = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);

the ODBC driver sets the option for all active statements and for all subsequent statements that you send to the DBMS for processing using ODBC function calls. (The code sample in Tip 376, "Using SQLConnect and SQLDisconnect to Establish and Terminate a DBMS Session," uses SQLSetConnectionOption to set the connection timeout value to 15 seconds.)

Using SQLConnect and SQLDisconnect to Establish and Terminate a DBMS Session

To access database data, you must first log onto the DBMS with a valid username/password pair that has the correct privileges to carry out the work you want to perform. For example, each time you start another instance of the MS-SQL Server Query Analyzer, the program asks you for your username and password. Query Analyzer then passes this information to the DBMS for authentication. If you entered a valid username/password pair, Query Analyzer then establishes a connection to the DBMS and passes your SQL statements to the system for execution.

The SQLConnect function lets your C program establish a connection with the DBMS. After establishing a connection with an SQL server, your application can send SQL statements to the DBMS for execution.

The syntax of the SQLConnect function call is

RETCODE SQLConnect(hdbc, szDSN, cbDSN, szUID, cbUID, szAUthStr, cbAuthStr)

where:

• hdbc is the connection handle (pointer value) returned by the SQLAllocConnect function.
• szDSN is a null-terminated string with the Data Source Name (DSN) that the ODBC driver is to use when connecting to the DBMS.
• cbDSN is the length of the DSN in the szDSN parameter.
• szUID is a null-terminated string with the username to use when logging onto the DBMS.
• cbUID is the length of the username in the cbDSN parameter.
• szAuthStr is the user's authentication string (password).
• cbAuthStr is the length of the password in the szAuthStr parameter.

Therefore, to log into the default database associated with the DSN MSSQLServer as user-name konrad with password king, you would first place the parameter values (other than hdbc) into appropriately typed variables such as

unsigned char data_source_name[]="MSSQLServer"; unsigned char user_ID[]="konrad"; unsigned char password[]="king";

and then call the SQLConnect function as:

retcode = SQLConnect(hdbc, data_source_name, SQL_NTS, user_ID, SQL_NTS, password, SQL_NTS);

To terminate a session on the DBMS, call the SQLDisconnect function and pass it the connection handle of the session that you want to end. For example, in the current example, you would have the program execute the statement

when the application completes the work that you want it to perform.

After adding function calls SQLConnect and SQLDisconnect to create and break a connection to a DBMS to the example program started in Tip 373, "Using SQLAllocEnv and SQLFreeEnv to Allocate and Release ODBC Environment Resources," the application will be able to take all of the steps necessary to connect to a DBMS and then to disconnect and free all handles and memory allocated to the ODBC driver:

#INCLUDE #include #INCLUDE int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR IpCmdLine, int iCmdShow) { HENV henv; //HENV is data type: void* HDBC hdbc; //HDBC is data type: void* RETCODE retcode; //signed short (16-Bit) integer LPSTR retcode_text = " "; //character string unsigned char data_source_name[]="MSSQLServer"; unsigned char user_ID[]="konrad"; unsigned char password[]="king"; retcode = SQLAllocEnv(&henv); if (retcode == SQL_SUCCESS) { retcode = SQLAllocConnect(henv, &hdbc); If (retcode == SQL_SUCCESS) { //connect to a DBMS retcode = SQLConnect(hdbc,data_source_name,SQL_NTS, user_ID,SQL_NTS,password,SQL_NTS); if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { /* SEND STATEMENTS FOR EXECUTION BY DBMS HERE(Tip 377) */ MessageBox(0,"Connection to MSSQLServer, OK!", "Connect to MSSQLServer", MB_OK); SQLDisconnect(hdbc); //disconnect from DBMS } else sprintf(retcode_text,"Error on SQLConnect = %d", retcode) SQLFreeConnect(hdbc); } else sprintf(retcode_text,"Error on SQLAllocConnect = %d", retcode); SQLFreeEnv(henv); } else sprintf(retcode_text,"Error on SQLAllocEnv = %d", retcode); /* display any error messages */ if (strcmp(retcode_text," ") != 0) MessageBox (0, retcode_text, "Connect to MSSQLServer", MB_OKIMB_ICONERROR); return 0; }

In this tip, you completed the C program that lets you connect to any DBMS that has a defined Data Source Name (DSN) and an ODBC driver. In Tip 377, "Using SQLAllocStmt and SQLFreeStmt to Allocate and Release SQL Statement Handles and Memory Resources," you will learn how to send statements to the DBMS for execution.

Using SQLAllocStmt and SQLFreeStmt to Allocate and Release SQL Statement Handles and Memory Resources

After you make the necessary function calls in your C program to establish a connection with an SQL server (as you learned to do in Tips 373–376), you can use the connection to send SQL statements to the DBMS for execution. However, before you can call SQLExecDirect to send a command to the SQL server, you must call the SQLAllocStmt function to reserve a memory area for SQL statement information and a statement handle that points to the area.

The syntax of the SQLAllocStmt function header is

RETCODE SQLAllocStmt (hdbc, phstmt)

where:

• hdbc is the handle of the connection through which you plan to send SQL statements to the DBMS. (You learned how to call the SQLAllocConnect function to allocate a connection handle in Tip 374, "Using SQLAllocConnect and SQLFreeConnect to Allocate and Release Connection Handles and Memory Resources.")
• phstmt is a pointer to the memory address in which the system is to store the statement handle. (The statement handle is a pointer to the memory location where the program can find information about the statement such as its current processing status, error codes and messages, the name of the statement's cursor, and the number of columns it contains.)

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUC-CESS_WITH_INFO, SQL_INVALID_HANDLE, or SQL_ERROR.

As such, when your application calls the SQLAllocStmt function with a statement such as

retcode = SQLAllocStmt (hdbc, &hstmt);

the ODBC driver allocates memory for statement information and stores the value of the statement handle (the pointer to the statement memory area) in the variable hstmt.

When your program is finished sending SQL statements to the DBMS, call SQLFreeStmt to release the statement handle and its memory resources. The syntax of the SQLFreeStmt function header is

SQLFreeStmt(hstmt,uioption)

where:

• hstmt is the statement handle (which you called with the SQLAllocStmt function call).
• uiOption is one of the following options:
  • SQL_CLOSE— Closes the cursor associated with the statement handle hstmt and discards all pending results.
  • SQL_DROP— Releases the hstmt statement handle, closes the cursor (discards all of its pending results), and frees all memory resources associated with the handle.
  • SQL_UNBIND— Releases all column buffers bound to the hstmt statement handle by an SQLBindCol function call.
  • SQL_RESET_PARAMS— Releases all parameter buffers bound to the hstmt statement handle by an SQLBindParameter function call.

Review the C source code in Tip 378, "Using SQLExecDirect to Send an SQL Statement to a DBMS for Execution," for an example of how to use both the SQLAllocStmt and the SQLFreeStmt functions. The important thing to understand now is that your program must make an SQLAllocStmt call to allocate a statement handle and an area in memory through which the ODBC driver can pass SQL statements to the DBMS. Moreover, the program can allocate the statement handle (and memory resources) only after the application successfully allocates a connection handle (see Tip 373, "Using SQLAllocEnv and SQLFreeEnv to Allocate and Release ODBC Environment Resources") and uses it to establish a connection with the DBMS (see Tip 376, "Using SQLConnect and SQLDisconnect to Establish and Terminate a DBMS Session").

Using SQLExecDirect to Send an SQL Statement to a DBMS for Execution

After you allocate a statement handle by calling SQLAllocStmt, you can call the SQLExecDirect function to send an SQL statement to the data source (that is, the DBMS to which you already have a connection). The advantage of using the ODBC interface is that you only have to make sure that your SQL statement is syntactically correct. The ODBC driver will modify the statement to conform to the particular form of SQL used by the data source before it sends it to the target DBMS for execution.

The syntax of the SQLExecDirect function is

RETCODE SQLExecDirect(hstmt, szSQLStmt, cbSQLStmt)

where:

• hstmt is the statement handle that you allocated by calling the SQLAllocStmt function (in Tip 377, "Using SQLAllocStmt and SQLFreeStmt to Allocate and Release SQL Statement Handles and Memory Resources").
• szSQLStmt is the character string that contains the SQL statement you want the DBMS to execute.
• cbSQLStmt is the length of the character string (the SQL statement) in szSQLStmt.

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUC-CESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Suppose, for example, that you want the DBMS to execute the UPDATE statement

UPDATE products SET unitprice = unitprice * 1.20

on the PRODUCTS table in the NORTHWIND (sample) database. Assuming that other parts of the C program called the function to connect to the DBMS using a Data Source Name (DSN) that specifies NORTHWIND as the default database, you can execute the UPDATE command by calling a function similar to:

if (retcode !=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) sprintf(retcode_text,"Error on SQLExecDirect = %d", retcode); else MessageBox (0,"Sucessful action", "Send statements to MSSQLServer in Raise_Prices", MB_OK); SQLFreeStmt (hStatement_handle, SQL_DROP);} else sprintf(retcode_text,"Error on SQLAllocStmt = %d", retcode); if (strcmp(retcode_text, " ") != 0) MessageBox (0,retcode_text, "In Raise_Prices", MB_OK|MB_ICONERROR);}

Using the SQLFetch Function to Retrieve a Row of Data from an SQL Database

In Tips 372–377, you learned how to write a C program to connect with an SQL database. Then, in Tip 378, "Using SQLExecDirect to Send an SQL Statement to a DBMS for Execution," you learned how to use the SQLExecDirect function to send SQL statements to an SQL server for execution. Although the example only sent an UPDATE statement to the DBMS, any valid SQL statement is fair game—including SELECT statements that let you extract data from the database for use in your C program.

Extracting data from an SQL database into your C application involves four steps. First, you must establish a connection with the SQL server (as you learned to do in Tips 372–377). Second, you must call the SQLExecDirect statement (which you learned about in Tip 378) to send the SELECT statement to the DBMS. Third, you must call the SQLBindCol function to tell the ODBC driver which variables in your program it is to use when transferring data from table columns. Finally, you must call the SQLFetch or the SQLExtendedFetch functions to retrieve the SQL database data from the ODBC statement buffer into the C variables that you created to receive it.

The syntax of the SQLFetch function header is

RETCODE SQLFetch(hstmt)

where:

• hstmt is the statement handle.

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

The important thing to understand is that you must call the function once for each column of data that you want to retrieve into a C variable. For example, to extract COMPANY-NAME, CONTACTNAME, and PHONE from columns in the CUSTOMERS table in the NORTHWIND database into your C program, you would the three SQLBindCol statements and SQLFetch in the Get_Cust_Info function:

void Get_Cust_Info(HDBC hDb_connection_handle) {#define COMPANYNAME_LEN 40 #define CONTACTNAME_LEN 30 #define PHONE_LEN 24 UCHAR szCompanyName[COMPANYNAME_LEN], szContactName[CONTACTNAME_LEN], szPhoneNumber[PHONE_LEN]; SDWORD cbCompanyName, cbContactName, cbPhoneNumber; HSTMT hstatement_handle; //data type void* RETCODE retcode; //signed short (16-Bit Integer) LPSTR retcode_text = " "; //character string LPSTR szCustomerString = " "; retcode = SQLAllocStmt(hDb_connection_handle, &hStatement_handle); if (retcode == SQL_SUCCESS) {retcode = SQLExecDirect(hStatement_handle,(UCHAR *) "SELECT companyname, contactname, phone FROM customers" " WHERE customerid LIKE 'B%'" " ORDER BY companyname",SQL_NTS); if (retcode !=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) sprintf(retcode_text,"Error on SQLExecDirect = %d", retcode); else {/* Bind columns CompanyName (SELECT statement column 1), ContactName(SELECT statement column 2), and Phone(SELECT statement column 3) */ SQLBindCol(hStatement_handle, 1, SQL_C_CHAR, szCompanyName, COMPANYNAME_LEN, &cbCompanyName); SQLBindCol(hStatement_handle, 2, SQL_C_CHAR, szContactName, CONTACTNAME_LEN, &cbContactName); SQLBindCol(hStatement_handle, 3, SQL_C_CHAR, szPhoneNumber, PHONE_LEN, &cbPhoneNumber); /* Fetch and display each row of data. On an error, display a message and exit. */ while (TRUE) {retcode = SQLFetch(hStatement_handle); if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) sprintf(retcode_text,"Error on SQLExecDirect = %d", retcode); if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) (strcpy(szCustomerString,"Company: "); strcat(szCustomerString,(const char *)szCompanyName); strcat (szCustomerString," Contact:*"); strcat(szCustomerString,(const char *)szContactName); strcat(szCustomerString," Phone: *); strcat(szCustomerString,(const char *)szPhoneNumber); MessageBox (0,(const char *)szCustomerString, "In Get_Cust_Info",MB_OK);} else break;}} SQLFreeStmt (hStatement_handle, SQL_DROP);} else sprintf(retcode_text,"Error on SQLAllocStmt = %d", retcode); if (strcmp(retcode_text, " ") != 0) MessageBox (0,retcode_text,"In Raise_Prices", MB_OK|MB_ICONERROR);}

Using the SQLExtendedFetch Function to Create an Updateable Cursor

Tip 379, "Using the SQLFetch Function to Retrieve a Row of Data from an SQL Database," used the SQLExecDirect function to send a SELECT statement to the DBMS that would most likely return more than one row of data. The ODBC driver handles multiple rows returned from a query by creating a cursor, a buffer area, on your local hard drive in which it stores the data values retrieved. When the application that sent the query to the DBMS calls the SQLFetch function, the ODBC driver advances the cursor's pointer to the next row of data retrieved from the database and sends data values to bound variables in the program. A second SQLFetch function call retrieves data from the second row in the cursor; the third call retrieves data from the third row, and so on.

If you only need to retrieve column data values and you are not planning to update the database by deleting rows in the cursor or updating their contents, use the SQLFetch function. On the other hand, if your program needs to update cursor values and have those changes reflected in the database, use the SQLExtendedFetch function instead.

The syntax of the SQLExtendedFetch function header is

RETCODE SQLExtendedFetch(hstmt, fFetchType, irow, pcrow, rgfRowStatus

where:

• hstmt is the statement handle.
• fFetchType is the type of fetch, either SQL_FETCH_NEXT, SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_PRIOR, SQL_FETCH_ABSOLUTE, SQL_FETCH_ RELATIVE, or SQL_FETCH_BOOKMARK.
• irow is the number of rows to retrieve from the DBMS.
• pcrow is the number of rows actually retrieved from the DBMS.
• rgfRowStatus is an array of status values regarding changes in status of the row since it was last retrieved from the data source (the DBMS). Possible values are SQL_ROW_SUCCESS (meaning that the row is unchanged), SQL_ROW_UPDATED, SQL_ROW_DELETED, SQL_ROW_ADDED, SQL_ROW_ERROR, or SQL_ROW_NOWROW (for each row where the irow exceeds the actual number of rows retrieved [pcrow]).

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUC-CESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Unlike the SQLFetch function call, which can only move forward one row at a time through the cursor, the SQLExtendedFetch function lets you specify both the direction (forward or backward) in which you want to move through the cursor and the number of rows at a time you want to move.

For example, given the type definitions

RETCODE retcode //signed short (defined in SQLTypes.h) HSTMT hstmt //void* (defined in SQLTypes.h) UDWORD pcrow UWORD rgfRowStatus

the SQLExtendedFetch function call

retcode = SQLExtendedFetch(hstmt, SQL_FETCH_LAST, 1, &pcrow,rgfRowStatus)

will fetch column values from the last rowset in the cursor. Alternatively, this call

retcode = SQLExtendedFetch(hstmt, SQL_FETCH_FIRST, 1, &pcrow,rgfRowStatus)

will fetch columns values from the first rowset. Meanwhile, the function call

retcode = SQLExtendedFetch(hstmt, SQL_FETCH_ABSOLUTE, 10, &pcrow,rgfRowStatus)

will fetch the rowset that contains the tenth row in the cursor. The function call

retcode = SQLExtendedFetch(hstmt, SQL_FETCH_RELATIVE, -4, &pcrow,rgfRowStatus)

will retrieve column values from the rowset that contains the row that is four rows before the first row in the current rowset.

Review the C++ source code in Tip380.cpp on the book's companion Web site at www.pre-mierpressbooks.com/downloads.asp for a fully functional application using the SQLExtendedFetch function. In Tips 383–385, you will learn how to use the SQLSetPos function call subsequent to an SQLExtendedFetch function call to update, delete, or add rows of data through the cursors created by the SQLExtendedFetch function.

Understanding the Difference Between Row wise and Column wise Binding

When retrieving data values from an SQL database for use in an application written in a programming language such as C, the ODBC driver first takes the data generated by a query and stores it temporarily on your hard disk. As you learned in Tip 380, "Using the SQLExtendedFetch Function to Create an Updateable Cursor," this temporary storage area (or buffer) is called a cursor. To use the database data in a program, you must fetch the data from the cursor into the application's declared variables. The processes of telling the ODBC driver which variables in the program are to receive data from which columns in the cursor is called binding.

In column-wise binding, you call the SQLBindCol function to establish the link between a variable declared in the program and a column in the cursor. In row-wise binding, you call the same SQLBindCol function, but you use the function to establish a link between one of the members (or fields) in a structure and a column in a cursor.

If you plan to use column-wise binding, there is no need to call the SQLStmtOption function—unless you previously called the SQLStmtOption function to request row-wise binding. As such, because you have already established a database connection and have allocated a statement handle (hStatementHandle, in the current example), your application could execute the following code to set up column-wise binding:

#define TEN_ROWS 10 #define CMPNY_LEN 40 #define CONTACTNAME_LEN 30 #define PHONE_LEN 24 UCHAR szCmpnyName[CMPNY_LEN], szContactName[CONTACTNAME_LEN], szPhoneNumber[PHONE_LEN]; SDWORD cbCmpyName, cbContactName,cbPhoneNumber; SQLBindCol(hStatementHandle, 1, SQL_C_CHAR, szCmpnyName, CMPNY_LEN, &cbCmpnyName); SQLBindCol(hStatementHandle, 2, SQL_C_CHAR, szContactName, CONTACTNAME_LEN, &cbContactName); SQLBindCol(hStatementHandle, 3, SQL_C_CHAR, szPhoneNumber, PHONE_LEN, &cbPhoneNumber);

After executing the three SQLBindCol function calls, any subsequent calls to either SQLFetch or SQLExtendedFetch would result in the ODBC driver copy the data values in the first column in the cursor to the variable CmpnyName and noting the number of bytes of data used to hold the data in the variable cbCmpnyName. The driver would copy data in the second cursor column to ContactName and would note the number of bytes copied in cbContactName. Finally, the ODBC driver would copy data from the cursor's third column to the PhoneNumber variable and would note the number of bytes copied in the cbPhoneNumber variable.

Because column-wise binding is the default, you must call the SQLStmtOption function and request row-wise binding if you want to use either SQLFetch or SQLExtendedFetch function calls to transfer cursor data into the members of structure instead of into individual variables. Again, assuming that you have already established a connection to the database and have allocated a statement handle (hStatementHandle, as in the last example), your application could execute the following code to set up row-wise binding:

#define CMPNYNAME_LEN 40 #define CONTACTNAME_LEN 30 #define PHONE_LEN 24 typedef struct(UCHAR szCmpnyName[CMPNYNAME_LEN], SDWORD cbCmpnyName; UCHAR szContactName[CONTACTNAME_LEN], SDWORD cbContactName; UCHAR szPhoneNumber[PHONE_LEN]; SDWORD cbPhoneNumber;} CustInfoTable; CustInfoTable citCustInfo; SQLSetStmtOption(hStatementHandle, SQL_BIND_TYPE, sizeof(CustInfoTable)); SQLBindCol(hStatement_handle, 1, SQL_C_CHAR, citCustInfo.szCmpnyName, CMPNYNAME_LEN, &citCustInfo,cbCmpnyName); SQLBindCol(hStatement_handle, 2, SQL_C_CHAR, citCustInfo.szContactName, CONTACTNAME_LEN, citCustInfo.&cbContactName); SQLBindCol(hStatement_handle, 3, SQL_C_CHAR, citCustInfo.szPhoneNumber, PHONE_LEN, citCustInfo.&cbPhoneNumber);

After executing the three SQLBindCol function calls, any subsequent calls to either SQLFetch or SQLExtendedFetch would result in the ODBC driver copying the data values stored in the first column in the cursor to the structure memory CmpnyName and then noting the number of bytes of data used to hold the data in the structure member cbCmpnyName. The driver would copy data in the second cursor column to the structure memory ContactName and would note the number of bytes copied in the structure member cbContactName. Finally, the ODBC driver would copy data from the third cursor column to the structure member PhoneNumber variable and would note the number of bytes copied in the structure member cbPhoneNumber variable.

To switch from row-wise binding back to column-wise binding, call the SQLSetStmtOption function with the defined constant SQL_BIND_BY_COLUMN as the third parameter

SQLSetStmtOption(hStatementHandle, SQL_BIND_TYPE, SQL_BIND_BY_COLUMN));

instead of passing the size of the structure that will hold the data as the third parameter:

SQLSetStmtOption(hStatementHandle, SQL_BIND_TYPE, sizeof(name_of_structure));

Using the SQLSetConnectOption Function to Select the Database to Use When Executing SQL Statements

In Tip 378, "Using SQLExecDirect to Send an SQL Statement to a DBMS for Execution," you learned how to call the SQLExecDirect function to send an SQL statement to a data source (a DBMS) for execution. Then, in Tips 379–381 you learned how to use the SQLFetch and SQLExtendedFetch functions to retrieve data from an SQL database into a C application's variables. The examples in Tips 378–381 assumed that the programs needed data from the default database selected when the Data Source Name (DSN) was created. (You learned how to create a DSN for an SQL server in Tip 368, "Creating a Data Source Name (DSN) for an SQL Open Database Connectivity (ODBC) Connection.") To find out the name of the database for a particular connection handle, call the SQLGetConnectOption function.

The syntax of the SQLGetConnectOption function header is

RETCODE SQLGetConnectOption(hdbc, fOption, vParam)

where:

• HDBC hdbc. Is the connection handle used to connect the C program to an SQL server.
• UWORD fOption. Is the connection option whose value you want to retrieve. (For a list of option values, see Table 375 in Tip 375, "Using SQLSetConnectOption to Set a Session Option for an ODBC Connection with an SQL Server.")
• UDWORD vParam. Is either a 32-bit integer value or a pointer to a null terminated character string, depending on the type of connection option (as determined by the "fOption" value) the function is to retrieve.

Therefore, given that the program previously established a connection with an SQL server and made the variable declarations

HDBC hdbc; LPSTR szDatabaseToUse = " "; RETCODE retcode;

the function call

retcode = SQLGetGonnectOption(hDb_connection_handle, SQL_CURRENT_QUALIFIER, szDatabaseToUse);

will place the name of the database that statements sent to the DBMS through the connection handle hDb_connection_handle will use into the variable szDatabaseToUse.

To change the database used by a connection handle, call the SQLSetConnectOption function. The SQLSetConnectOption function header has the same syntax as the SQLGetConnectOption function. However, in an SQLSetConnectOption function call, the vParam parameter is an input parameter. Therefore, to have the DBMS use the SQLTips database when executing statements sent through the connection handle hDb_connection_handle, execute the SQLSetConnectOption function call:

retcode = SQLSetConnectOption(hDb_connection_handle, SQL_CURRENT_QUALIFIER, (UDWORD) "SQLTips");

Tip 382.cpp on the companion CD contains the source code for an application that uses the SQLGetConnectOption function to retrieve the name of the database used by a connection handle and then selects a new database for the handle to use by calling the SQLSetConnectionHandle function.

Using the SQLSetPos Function to Set the Cursor Position in a Rowset

SQLSetPos is a multipurpose function in that it not only lets you set the pointer in a cursor to a particular row in a rowset of query results, but also lets you refresh and/or change the cursor's contents. Bear in mind that when you call the SQLSetPos function to modify a cursor's contents (by specifying SQL_UPDATE, SQL DELETE, or SQL_ADD in the function call), the ODBC driver will send SQL statements to the DBMS to make the same changes on the cursor's underlying data on the SQL server.

The syntax of the SQLSetPos function header is

RETCODE SQLSetPos(hstmt, irow, fOption, fLock)

where:

• HSTMT hstmt. Is the statement handle.
• UWORD irow. Is the number of the row in the rowset on which to perform the operation specified in the fOption parameter.
• UWORD fOption. Is the operation to perform in the cursor and in the database on the data source (the database) to which the statement handle is connected through its connection handle. The possible fOption operations are SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE, and SQL_ADD.
• fLock. Is used to control database concurrency by simulating transactions on data sources that do not support them. Although fLock can be set to SQL_LOCK_NO_CHANGE, SQL_LOCK_EXCLUSIVE, or SQL_LOCK_UNLOCK, only the SQL_LOCK_ NO_CHANGE option is supported for SQL server cursors because the DBMS has its own isolation level (concurrency) and transaction support.

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUC-CESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

For example, to position the cursor's pointer to the fifth row in a rowset, execute the statement:

retcode = SQLSetPos(hstmt, 5, SQL_POSITION, SQL_LOCK_NO_CHANGE)

After you position the cursor pointer (often referred to as "positioning the cursor"), the SQL_POSITION option tells the ODBC driver to take no further action. As you will learn in Tips 384–385, you can have the ODBC driver update or delete a row in a database table by substituting SQL_UPDATE or SQL_DELETE for SQL_POSITION in the SQLSetPos function call.

Using the SQLSetPos Function SQL_UPDATE Option to Perform a Positioned UPDATE

If the ODBC driver for the data source allows it, a program can use the SQLSetPos function's SQL_UPDATE option to modify data values in a cursor and have those changes reflected in the database row from which the cursor row's data was retrieved. When an application calls the SQLSetPos function with the SQL_UPDATE option, the ODBC driver positions the cursor's pointer to the row in the rowset passed to the function as the value of irow parameter. Next, the driver and updates the underlying row of data in the database by sending data values from the cursor row's buffers (its columns) to the columns in the table row from which the row in the cursor retrieved its data.

Suppose, for example, that a program calls the SQLSetStmtOption function to set its rowset size to 10 and then calls the SQLExecDirect function (see Tip 378, "Using SQLExecDirect to Send an SQL Statement to a DBMS for Execution") to send the query

SELECT cust_ID, f_name, l_name, phone_number FROM customers384

to the DBMS. After calling the SQLBindCol function to bind the buffers in the cursor to variables in the program (as you learned to do in Tips 379–381), the application can update the table columns bound to szFirstName and szLastName from the 12th row of data retrieved by executing the following code:

retcode = SQLExtendedFetch(hStatementHandle, SQL_FETCH_ABSOLUTE, 12, &pcrow, rgfRowStatus); if (rgfRowStatus[0] != SQL_ROW_DELETED && rgfRowStatus[0] != SQL_ROW_ERROR) { strcpy(citCustInfoRecord[0].szFirstName,"Konrad"); strcpy(citCustInfoRecord[0].szLastName,"King"); SQLSetPos(hStatementHandle, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE) }

In the current example, the SQLExtendedFetch function call retrieves rows from the second rowset in the cursor starting with row 12. Because the array of citCustInfoRecord structures was bound to the cursor buffers by SQLBindCol function calls (not shown), the first element in the array (index 0) contains the values from the 12th row in the cursor after the SQLExtendedFetch function call. Next, the strcpy function calls update the cursor buffers in the first row of the current rowset (the 12th row in the cursor) by changing the values of the structure elements szFirstName and szLastName in the first row of the structure array. Finally, the SQLSetPos function call tells the ODBC driver to copy the values in the buffers of the first row in the current rowset (the 12th row in the cursor) into the underlying table columns in the row from which they were originally retrieved by the SQLExtendedFetch function call.

Please review the C source code in Tip384.cpp on the companion CD to see a program that creates a database connection, retrieves database table values into the cursor, and then uses SQLSetPos to perform a positioned update to the underlying data in the database.

Using the SQLSetPos Function SQL_DELETE Option to Perform a Positioned DELETE

As you learned in Tip 384, "Using the SQLSetPos Function SQL_UPDATE Option to Perform a Positioned UPDATE," when you use the SQLSetPos function to update the values in a cursor's buffers, the ODBC driver sends commands to the DBMS to make the same changes to the data values in the columns of the underlying row from which the updated cursor row was derived. Similarly, when you use the SQLSetPos function's SQL_DELETE option, the ODBC driver positions the cursor to the row specified by the irow parameter, tells the DBMS to delete the underlying row in the database table, and changes the cursor row's status flag (in the rgfRowStatus array) to SQL_ROW_DELETED.

As you learned in Tip 383, "Using the SQLSetPos Function to Set the Cursor Position in a Rowset," the syntax of the SQLSetPos function header is:

RETCODE SQLSetPos(hstmt, irow, fOption, fLock)

Therefore, to remove the row in the database underlying the ninth row in the cursor, your application would execute code similar to:

retcode = SQLExtendedFetch(hStatementHandle, SQL_FETCH_ABSOLUTE, 9, &pcrow, rgfRowStatus); if (rgfRowStatus[0] != SQL_ROW_DELETED && rgfRowStatus[0] != SQL_ROW_ERROR) SQLSetPos(hStatementHandle, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE)

Notice that the value of the irow parameter in the SQLSetPos function call is set to 1, not 9. As you learned in Tip 380, "Using the SQLExtendedFetch Function to Create an Updateable Cursor," the SQLExtendedFetch function retrieves data from the cursor one rowset of data at a time. The first row in the set of rows retrieved by the SQLExtendedFetch function call is the row passed as its irow parameter—9, in the current example. Therefore, to work with the ninth row in the cursor, the application must tell the driver to work with first row in the rowset.

When using the SQLSetPos function's SQL_DELETE option, be sure to set the value of the irow parameter to something other than zero (0) unless you want the ODBC driver to delete the underlying rows for all of the rows in the rowset. For example, while the function call

SQLSetPos(hstmt, 2, SQL_DELETE, SQL_LOCK_NO_CHANGE)

will delete the underlying row for the second row in the cursor's rowset, the function call

SQLSetPos(hstmt, 0, SQL_DELETE, SQL_LOCK_NO_CHANGE)

will delete all rows in the rowset. Therefore, if the rowset contains 10 rows, the first SQLSetPos function call will delete a single row, while the second SQLSetPos function call will delete 10 rows.

Please review the contents of Tip384.cpp on the companion CD for the source code of a C++ application that connects with a DBMS, executes a query to create a cursor and fill it with data, and then uses the SQLSetPos function to delete a row of data in the cursor's underlying table.

Using the SQLExecDirect Function to DELETE Rows in the Database When an ODBC Driver Does Not Support Positioned Deletes

A SQL DELETE statement such as

DELETE FROM customers WHERE cust_id = 9

is called a searched DELETE because the DBMS searches the target table for rows that satisfy the search criteria in the WHERE clause and then deletes them. The term positioned DELETE, on the other hand, applies only to a programmatic SQL statement that deletes the single underlying row in the database table referenced by the "current" row in a cursor. Similarly, an SQL UPDATE statement such as

UPDATE customers SET f_name = 'Konrad', l_name = 'king' WHERE cust_id = 9

is a searched UPDATE because the DBMS uses the search criteria in the WHERE clause to determine which row(s) to update as it scans the rows in the CUSTOMERS table. Like a positioned DELETE statement, a positioned UPDATE statement changes the data values in columns of the single underlying row referenced by the "current" row in a cursor.

If your ODBC driver does not support them using the SQLSetPos function, you can simulate both positioned updates and positioned deletes by calling the SQLExecDirect function with an UPDATE or DELETE statement string in which the WHERE clause links the "current" cursor row with its underlying row in the database table. For example, if the underlying table includes a TIMESTAMP column named TSTAMP, your program can execute a "positioned" delete of the database table row underlying the fifth row in the cursor by executing code such as:

strcpy(szSQLStatement, "DELETE FROM customers WHERE tstamp = 0x"); strcat(szStatementstring, citCustinfoRecord(0].szTimeStamp retcode = SQLExecDirect(hDeleteStmtHandle, (UCHAR *)szSQLStatement,SQL_NTS)

Notice that although the DELETE statement sent to the DBMS by the SQLExecDirect function in the current example "acts like" a positioned DELETE in that it tells the DBMS to remove the one row in the table from which the cursor row was retrieved, it is really a searched DELETE. Since the value in each row of a column of type TIMESTAMP is guaranteed to be unique across the entire database, the searched DELETE in the current example will always delete at most one row from the underlying table. (It is possible to delete zero rows if another user has already deleted the cursor's underlying row.)

If the underlying table does not have a column of type TIMESTAMP, you can still simulate a positioned DELETE. Simply change the search condition in the WHERE clause to look for a value in one of the underlying table's PRIMARY KEY column(s) in any column constrained as UNIQUE, or values in some other combination of columns whose composite value is unique for each row in the underlying table.

Please review the source code for Tip386.ccp on the companion CD for a complete listing of the source code necessary to use SQLExecDirect to simulate a positioned DELETE.

Using the SQLExecDirect Function to UPDATE Column Values in a Database When an ODBC Driver Does Not Support Positioned Updates

In Tip 384, "Using the SQLSetPos Function SQL_UPDATE Option to Perform a Positioned UPDATE," you learned that you can use the SQLSetPos function's SQL_UPDATE option to perform a positioned UPDATE on a cursor row's underlying row in the database. When performing a positioned UPDATE, the ODBC driver tells the DBMS to UPDATE the values in the table row from that the current row in the cursor was derived to match the values in the cursor row's buffers. In short, a positioned UPDATE is nothing more than a searched UPDATE in which the search condition in the WHERE clause uniquely identifies the underlying row for the current row in the cursor.

Unfortunately, many ODBC drivers do not support positioned updates (or positioned deletes). As a result, you may have to use the SQLExecDirect function to simulate a positioned UPDATE just as you used the function to simulate a positioned DELETE in Tip 385, "Using the SQLSetPos Function SQL_DELETE Option to Perform a Positioned DELETE." For example, suppose you have a cursor with rows produced by calling the SQLExecDirect function with the query:

retcode = SQLExecDirect(hQueryStmtHandle, (UCHAR *) "SELECT tstamp, cust_ID, f_name, l_name, phone_number" " FROM customers", SQL_NTS);

After you make the SQLBindCol function calls that bind the buffers in the cursor to variables your C application, you can execute the statements

retcode = SQLExtendedFetch(hQueryStmtHandle, SQL_FETCH_ABSOLUTE, 5, &pcrow, rgfRowStatus); strcpy(citCustInfoRecord[O].szFirstName,"Sally"); strcpy(citCustInfoRecord[0].szLastName,"Wells");

to update the values in the fifth row of the cursor. Then you would execute the statements

strcpy(szStmtString,"UPDATE customers SET f_name = ' "'); strcat(szStmtstring,citCustInfoRecord[0].szFirstName); strcat(szStmtString,"', l_name = '" strcat(szStmtString,citCustInfoRecord[0].szLastName); strcat(szStmtString,"' WHERE tstamp = 0x"); strcat(szStmtString, citCustInfoRecord(0].szTimeStamp); retcode = SQLExecDirect(hUpdateStmtHandle, (UCHAR *)szStmtString,SQLNTS);

to have the DBMS make the same changes to the cursor's underlying row in the CUSTOMERS table.

Although the WHERE clause search criteria in the examples in both Tip 385 and the current tip used a value in a TIMESTAMP column (TSTAMP) to uniquely identify the cursor row's underlying row in the database, the value in any column constrained as UNIQUE will do just as well. For example, if the CUST_ID column in the current example is unique for each row cursor's underlying table, then the executing the statements

strcpy(szStmtString,"UPDATE customers SET f_name = '"); strcat(szStmtString,citCustInfoRecord[0].szFirstName); strcat(szStmtString,"', l_name = '" strcat(szStmtstring,citCustInfoRecord[0].szLastName); strcat(szStmtString,"' WHERE cust_ID = "); strcat(szStmtString, citCustInfoRecord[0].szCust_ID); retcode = SQLExecDirect(hUpdateStmtHandle, (UCHAR *)szStmtString,SQLNTS);

will have the same effect as calling the SQLExecDirect function using the value of the TSTAMP column as the search criteria in the preceding example.

Review the contents of Tip387.ccp on the book's companion Web site at www.premier-pressbooks.com/downloads.asp for a complete listing of the source code necessary to use SQLExecDirect to simulate a positioned UPDATE.

Using the SQLError Function to Retrieve and Display ODBC Error Codes and Error Messages

When you call an ODBC function, the ODBC driver communicates with a data source using one or more communication handles. The SQLConnect function, for example, creates and uses a connection handle (data type HDBC). Meanwhile, the SQLAllocEnvironment function uses values in the database connection handle and creates the environment handle (data type HENV). Finally, the SQLAllocStmt function uses values in the connection handle and creates the statement handle (data type HSTMT). Any ODBC function call can post zero or more error, warning, or information messages in the handles it creates or uses. Moreover, whenever a function returns a return code (RETCODE) of SQL_ERROR or SQL_SUCCESS_ WITH_INFO, you will definitely find at least one message in the communications handle used by the function. The SQLError function lets you retrieve a message from the data structure of its rightmost non-null handle parameter, and it removes the message from the stack of up to 64 messages that each handle can hold.

The syntax of the SQLError function header is

RETCODE SQLError(henv, hdbc, hstmt, szSQLState, pfNativeError, szErrorMessage, cbErrorMsgMax, pcbErrorMsg);

where:

• HENV henv is an environment handle or SQL_NULL_HENV.
• HDBC hdbc is a database handle or SQL_NULL_HDBC.
• HSTMT hstmt is a statement handle or SQL_NULL_HSTMT.
• UCHAR SQLState is the numeric ODBC driver-mapped error code cast as a null-terminated string.
• SDWORD FAR* pfNativeError is a pointer to 32-bit numeric variable (memory location) that will hold the data native (data source-specific) error code that is mapped into an ODBC driver error code, as specified in the ANSI SQL specification.
• UCHAR FAR* szErrorMessage is a pointer to the memory location for the null-terminated character string that will hold the error message.
• SWORD cbErrorMsgMax is the maximum length of the error message storage area szErrorMessage. The value must be less than or equal to SQL_MAX_MESSAGE_ LENGTH - 1 (or 511, as of this writing).
• SWORD FAR* pcbErrorMsg is a pointer to a 16-bit numeric variable (memory location) that will hold the number of non-null bytes placed in the error message area szErrorMessage.

The value of the RETCODE returned by the function can be SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_ERROR, or SQL_INVALID_HAN-DLE.

Therefore, to retrieve an error message stored in an environment handle, call the SQLError function with a valid environment handle (henv) and the hdbc and hstmt parameters set to null, as in:

retcode = SQLError(henv, SQL_NULL_HDBC, SQL_NULL_HSTMT, szSQLState, &pfNativeError, szErrorMessage, MSG_BUFF_SIZE, &cbErrorMsg);

Similarly, to retrieve an error associated with a connection handle, call the SQLError function with a valid connection handle (hdbc) and null parameters for henv and hstmt, as in:

retcode = SQLError(SQL_NULL_HENV, henv, SQL_NULL_HSTMT, szSQLState, &pfNativeError, szErrorMessage, MSG_BUFF_SIZE, &cbErrorMsg);

Finally, to retrieve an error message posted in a statement handle, call the SQLError function with a valid statement handle (hdbc) and null parameters for henv and hdbc, as in:

retcode = SQLError(SQL_NULL_HENV, SQL_NULL_ENV, hstmt, szSQLState, &pfNativeError, szErrorMessage, MSG_BUFF_SIZE, &cbErrorMsg);

Review the source code in Tip388.cpp to see how an application can make a call to the SQLError function to retrieve error information and then call the MessageBox routine to display it as shown in Figure 388.1.

Figure 388.1: MessageBox displaying the results of an SQLError function call

As mentioned previously, each SQLError function call retrieves one error code (and message) from the rightmost non-null handle. To display multiple errors posted in any of the three handle structures, call the SQLError function multiple times. If you call the SQLError function and there are no (additional) error messages in the handle structure, SQLError will return a retcode value of SQL_NO_DATA_FOUND, szSQLState will equal 00000, pfNativeError will be undefined, and szErrorMsg will contain only a single null termination byte.

Handling NULL Values in Host Program Variable

Most programming languages do not support SQL NULL values. In C, for example, a variable declared as

SWORD sSalespersonID

can hold a 16-bit integer value. However, while the value of sSalespersonID can be negative, 0, or positive, it cannot have an unknown or missing value. In short, sSalespersonID cannot be NULL. As such, when you retrieve data from an SQL table column that may contain one or more NULL values, you must check the number of bytes retrieved into the variable passed as the pcbValue parameter of the SQLBindCol function.

For example, given the syntax of the SQLBindCol

SQLBindCol(hstmt, icol, fCType, rgbvalue, cbValueMax, pcbValue)

the program statement

SQLBindCol(hStatementHandle, 4, SQL_C_SSHORT, &sSalespersonID, 0, &cbSalespersonlD);

will bind the fourth buffer column in the ODBC cursor to the variable sSalespersonID. Each time the application calls the SQLFetch (see Tip 379, "Using the SQLFetch Function to Retrieve a Row of Data from an SQL Database") or the SQLExtendedFetch (see Tip 380, "Using the SQLExtendedFetch Function to Create an Updateable Cursor") function, the ODBC driver will place the value from the fourth column in the cursor into the variable sSalespersonID and store the number of bytes transferred in the variable cbSalespersonID-unless the value in the fourth column is NULL. If the value in a cursor column is NULL and the program calls the SQLFetch or SQLExtendedFetch functions, the ODBC driver does not change the value in the program variable to which the cursor buffer is bound. The driver does, however, set the value of the pcbvalue parameter-cbSalespersonID, in the current example-to SQL_NULL_DATA, which is -1 as of this writing.

Therefore, before using the value from an SQL column that may contain NULL data values, have your program check the value of the number of bytes retrieved from the cursor (the pcbValue value). For example, the IF statement

if (cbSalespersonID != SQL_NULL_DATA) sprintf((char *)szsalerspersonID, " salesperson ID: %d",sSalespersonID); else strcpy((char *)szSalespersonID, " Salesperson ID: **UNASSIGNED **");

will place the salesperson ID from the fourth column in the cursor into the szSalespersonID character string if the column has a non-NULL value. Otherwise, the string will show the salesperson ID as * * UNASSIGNED * *.

Review the code in Tip389.cpp on the book's companion Web site at www.premierpressbooks.com/downloads.asp for a complete listing of the variable definitions and function calls necessary to retrieve data from an SQL table and handle NULL values appropriately.

Understanding the Role of APIs in a Client Server Environment

Before the development of the Open Database Connectivity (ODBC) standard, the only way for an application to work with the data in a database was through embedded SQL. In embedded SQL, SQL statements are intermixed with other non-SQL language statements.

The embedded SQL statements serve as the program's interface with the DBMS by retrieving data from it, updating the data in the database, and manipulating its objects.

When creating an executable program using embedded SQL with a programming language such as C, COBOL, PL/I, FORTRAN, Pascal, and so on, the source code is first submitted to an SQL precompiler that is unique for each programming language and SQL server. The SQL precompiler parses, validates, optimizes, and converts the SQL statements into their binary forms. The code is then sent through the native language compiler, which converts the non-SQL statements into their executable (binary) form and links them with the binary SQL statements to produce an executable program.

While the embedded SQL approach combines SQL and non-SQL statements in a program's source code, the application program interface (API) approach keeps the two separate. The API approach (which you learned about as you worked with the ODBC API in Tips 373-389) is to provide the procedural language (C, COBOL, Visual Basic, Pascal, and so on) a set of function calls that it can use to work with the data and objects in a database. Because most programmers already have some experience in using function libraries for such things as string manipulation, mathematical functions, and file and screen I/O, the SQL APIs are a straightforward and easy way to use SQL.

As shown in Figure 390.1, in a client/server environment, an application makes one or more API calls that connect the program with a DBMS. The application then allocates a memory (and perhaps hard-disk) buffer area at the workstation for use in passing commands and data back and forth between the DBMS running on the server and the application running on the client (workstation). After creating SQL statements as text strings and placing them in the buffer along with any necessary data values, the program calls API functions that send the buffer's contents to the DBMS for processing. The program then makes API calls that check the status of the commands/statements sent to the DBMS and handles any errors.

Figure 390.1: The API approach to DBMS access

The DBMS, for its part, analyzes the buffer contents it receives from the client (the program running at the workstation) and executes the SQL statement batch in it. If the statements include a query, the DBMS generates the query's results table and sends the requested data to buffers on the client workstation for further processing. (You learned about the process of retrieving (and sending) rows of data through cursors in Tips 379-387.)

When a program is finished working with the DBMS, it ends its database access by making API calls that disconnect it from the DBMS and free up the resources allocated both at the server and at the client for the session's communication's processes.

Adding DB Library (DBLIB) Functionality to Visual Basic (VB)

Tips 372–389 showed you how to use an Open Database Connectivity (ODBC) driver to work with SQL server data. Using the data source name (DSN) you created in Tip 368, "Creating a Data Source Name (DSN) for an SQL Open Database Connectivity (ODBC) Connection," the ODBC applications program interface (API) lets you work with a variety of different data sources, one of which happens to be MS-SQL Server. The DB-Library (DBLIB), on the other hand, is a native MS-SQL Server data access technology provided by Microsoft and can be used only to access data on an MS-SQL Server.

DBLIB for Visual Basic (VB) is a subset of the DBLIB for C. Both are included with MS-SQL Server. However, neither the C nor the VB DBLIB is installed as part of the default MS-SQL Server client installation process. To install DBLIB, run MS-SQL Server's Custom installation and select the Development Tools option. After you install the VB DBLIB on your hard drive, you can use its functions in your VB projects to allow them to make API calls that work with MS-SQL Server data, much like those you learned about when using the ODBC API.

Because a DBLIB-enabled VB application makes calls to various DBLIB functions defined in the VBSQL.OCX dynamic link library (DLL), you will need to add the VBSQL.OCX component to your VB toolbox by performing the following steps:

1. If you have not already done so, start MS-Visual Basic (VB) and open a Standard.EXE project.
2. Right-click your mouse pointer on the project toolbox (along the left side of the VB application window) and select Components from the pop-up menu. VB will display a Components dialog box (similar to the one you saw in Figure 370.1 in Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data").
3. Use the scroll bar to the right of the Components list box on the Controls tab to scroll through the Control Components list until you see the Vbsql Control. Then click your mouse pointer on the check box to the left of Vbsql OLE Custom Control Module until the check mark appears. (The controls are listed in alphabetical order, so the control that you want will be near the end of the list because its name starts with a V.)

   Note

   VB will try to find the Vbsql Control in the folder in which it was installed when installed you the MS-SQL Server development tools. For MS-SQL Server 7.0, the default folder is C:MSSQL7DevToolsLib. If you moved to VBSQL.OCX file to another folder, you will need to click your mouse pointer on the Browse button and search for or enter the full pathname of the folder in which you placed VBSQL.OCX.

4. Click your mouse pointer on the OK button near the bottom center of the Components dialog box. VB will return to the VB application window and add the Vbsql Control as the last control component in the VB toolbox.

Before you can use any of the functions in the Vbsql Control that you added to the VB toolbox, you must declare the function headers (their names and parameters) in a .BAS or .CLS module. Fortunately, Microsoft provides the VBSQL.BAS module for the Vbsql Control, and you can add it to your VB environment by performing the following additional steps:

1. Right-click your mouse pointer in the Project window near the upper-right side of your VB application window. Then move your mouse pointer over the Add option on the popup menu and select the Module option. VB will display an Add Module dialog box similar to that shown in Figure 391.1.

   Figure 391.1: The Visual Basic (VB) Add Module dialog box

2. Click your mouse pointer on the Existing tab and enter the full pathname for the VBSQL.BAS module into the File_name field. If you installed the MS-SQL Server development tools to the C:MSSQL7 folder, for example, enter C:MSSQL7DEV-TOOLSVBSQL.BAS into the File_name field.
3. Click your mouse pointer on the Open button. VB will add the VBSQL.BAS module to your current project and return to the VB application window.

Now that you have both the Vbsql Control and the VBSQL.BAS modules available in your project, you can use the DBLIB function to work with MS-SQL Server data, as you will learn to do in Tips 392–417.

Starting a Visual Basic (VB) Program Without Displaying a Form

As you learned in Tips 369–371 (when you wrote a Visual Basic [VB] program to display SQL DBMS data), VB is forms-oriented. In fact, when you started VB and chose to work on a Standard.EXE project in Tip 391, "Adding DB-Library (DBLIB) Functionality to Visual Basic (VB)," VB automatically added the form named Form1 to your project. Sometimes, however, you may want to perform some work in a VB program before displaying a form on the screen. Fortunately, VB lets you do just that—as long as you define a function named MAIN() in one of the modules in your application.

If you have not done so already, execute the procedure in Tip 391 to start a VB project that includes the VBSQL.BAS module and has the Vbsql.ocx control in the VB Toolbox. Next, add a module that contains the MAIN() function that your VB application will execute on startup by performing the following steps:

1. Right-click your mouse pointer in the project window, select Add from the pop-up menu, and then select Module from the Add submenu. VB will display the Add Module dialog box.
2. Click your mouse pointer on the New tab and then on the Open button. VB will add a module named Modulel to your project.
3. Double-click your mouse pointer on the name (Module1) in the "(Name)" field of the Module1 properties window to select the module's name. Enter a more descriptive name for the module. For the current project, replace Module1 with MainRoutine.

   Note

   Renaming Module1 is optional. However, changing default object names such as Module1, Form1, and so on will make your VB source code easier to read. Moreover, you do not have to name the module in which you place the MAIN() routine MainRoutine—or any specific name, for that matter. You cannot, however, name the module itself MAIN. If you do so, VB will generate an error indicating that you cannot have a module named MAIN when you attempt to compile your program.

4. Select the Project menu Properties option. VB will display a Project Properties dialog box similar to that shown in Figure 392.1. (If you have not yet changed the name of the project, the Properties option will be Project1 Properties on the Project menu.)

   Figure 392.1: The Visual Basic (VB) Project Properties dialog box

5. Click your mouse pointer on the General tab.
6. Click your mouse pointer on the drop-down list button to the right of the Startup Object field, and select Sub Main from the drop-down list of startup objects.
7. Click your mouse pointer on the OK button.

After you complete Step 7, you are ready to enter the code for the MAIN() routine that your application will execute at startup into the code window of the MainRoutine module you added to your project. Tip 393, "Using the SglInit() Function to Initialize the DB-Library and the SqlWinExit Routine to Release Memory Allocated by SqlInit()," will have you write the code for a MAIN() routine that initializes the DB-Library for use in a VB application.

Be sure to save your work to disk periodically so you do not lose it due to a computer lockup or power failure. To save your work to disk now, select the File menu Save Project option. VB will prompt you for the file names and folder in which you want to store your project's modules and forms. For the current project, store your VB application's files in a folder such as C:SQL ProjectsTip392.

Using the SqlInit() Function to Initialize the DB Library and the SqlWinExit Routine to Release Memory Allocated by SqlInit()

The DB-Library (DBLIB) contains functions and subroutines that let your VB application work with data in an MS-SQL Server database. However, before you can call any other routine in the DBLIB, you must call SqlInit. When running in a Windows environment, the DBLIB maintains information about each of the applications that reference it. Calling the SqlInit function allocates the memory and initializes the variables DBLIB uses to keep track of its use by a particular application.

The following code (which you should enter into the VB module [MainRoutine] you created in Tip 392, "Starting a Visual Basic (VB) Program Without Displaying a Form") calls SqlInit to initialize DBLIB and then calls SqlWinExit to release the memory resources allocated for the application by DBLIB:

Sub main() DIM sDBLIBVersion AS String sDBLIBVersion = SqlInit() If sDBLIBVersion = vbNULLString Then MsgBox "Error! Failed to initialize the DB-Library!" Exit Sub Else 'Make subroutine calls and start the main program 'loop here MsgBox sDBLIBVersion 'Exit the DB-Library and free its memory resources SqlWinExit End If End Sub

After reviewing the example VB code, you are probably thinking, "Hey! This program doesn't do anything other than let me know whether or not it was able to initialize the DBLIB successfully!"—and you are right! As you work the projects in Tips 394–419 you will add code to the "Else" part of the "If-Then-Else" structure in the MAIN() routine.

For now, the important thing to understand is that a VB application must call the Sqllnit function before calling any other routine in the DBLIB. The SqlInit function has no parameters and returns a NULL character string if it is unable to initialize the DBLIB for your VB application. If successful, SqlInit returns a character string with the DBLIB's version identification. Be sure that you do not call other DBLIB routines if SqlInit returns an empty string because doing so will cause unpredictable results.

As is the case with any well-behaved program, the application in the example releases the memory resources allocated to it prior to exiting. In this case, the SqlWinExit function call tells the DBLIB to release the memory allocated by SqlInit. SqlWinExit has no parameters and does not return any values.

After calling the SqlWinExit routine, an application must call SqlInit again before any subsequent calls to other routines or functions in the DBLIB.

Using the SqlOpenConnection() Function to Log On to an MS SQL Server

After your application initializes the DB-Library (DBLIB) by making a successful call to the SqlInit function, the program must log on to an MS-SQL Server before working with its data. The logon process is fairly straightforward in that it involves a single function call, SqlOpenConnection, whose syntax is

hConnHandle = SqlOpenConnection (sServerName, sLoginID, sPassword, sWsName, sAppName)

where:

• Long hConnHandle. Is the connection handle returned by the SqlOpenConnection function. The application will use the value of hConnHandle in subsequent DBLIB function calls that send commands to the MS-SQL Server over the connection opened by the SqlOpenConnection function.
• String sServerName. Is the name of the MS-SQL Server to which the program wishes to connect.
• String sLoginID. Is the program's login ID, its username, on the MS-SQL Server. The sLoginID can be up to 30 characters in length, and the application must provide a valid sLoginID/sPassword (username/password) pair to open a connection (logon) to an MS-SQL Server.
• String sPassword. Is the password associated with the username (login ID) in sLoginID. The sPassword can be up to 30 characters in length, and the application must provide a valid sLoginID/sPassword (username/password) pair to open a connection (logon) to an MS-SQL Server.
• String sWsName. The name of the workstation on which the application is running. Although sWsName can be up to 30 characters in length, MS-SQL Server stores only the first 10 characters. (MS-SQL Server displays the sWsName as the HOSTNAME when you execute the stored procedure SP_WHO.)
• String sAppName. The name the MS-SQL Server is to place in its SYSPROCESSES table to help identify the connection. Although sAppName can be up to 30 characters, MS-SQL Server stores only the first 16. (sAppName is an option and can be left blank.)

The value of the connection handle (hConnHandle) will be nonzero if SqlOpenConnection was successful in its effort to open a connection with the DBMS sServerName. If unsuccessful, the SqlOpenConnection function will return a connection handle of zero (0), so be sure to test the value of hConnHandle before using it access DBMS data in subsequent DBLIB function calls.

For example, to open a connection with an MS-SQL Server named NVBizNet2 by logging on as username konrad with password king, a program would make an SqlOpenConnection function call similar to:

hConnHandle = SqlOpenConnection("NVBizNet2", _ "konrad", "king","myworkstation", _ App.EXEName)

Tip394.bas on the companion CD contains Visual Basic (VB) source code that includes the preceding function call and displays the connection handle value in a message box. Although the example program (in Tip394.bas) displays the value of the 32-bit (Long) integer connection handle, the handle's value is of little use to the person using your program. However, the application will need to pass the connection handle to other DBLIB functions that need to communicate with the MS-SQL Server using the open connection.

Using the SqlClose() Routine to Close a Single MS SQL Server Connection, or Calling SqlExit to Close All Open Connections

As you learned in Tip 394, "Using the SqlOpenConnection() Function to Log On to an MS-SQL Server," you can call the SqlOpenConnection function to log on to an MS-SQL Server. The SqlClose function has the opposite effect-it ends (closes) a single connection with an MS-SQL Server. Because a program can open multiple connections with one or more MS-SQL Servers, the syntax of the SqlClose routine

SqlClose (hConnHandle)

includes one parameter-the connection handle (hConnHandle) of the connection the routine is to close.

For example, if the application executed the function calls

hConnHandlel = SqlOpenConnection("NVBizNet2", _ "konrad", "king","my_ws-1", App.EXEName) hConnHandle2 = SqlOpenConnection("NVBizNet2", _ "konrad", "king","my_ws-2", App.EXEName) hConnHandle3 = SqlOpenConnection("NVBizNet2", _ "konrad", "king","my_ws-3", App.EXEName)

you would make the subroutine call

SqlClose(hConnHandle2)

to close the second connection while leaving the first and third connections open for use in subsequent DBLIB function calls.

As an alternative to closing each connection, your application can call the SqlExit routine as

SqlExit

to close all open connections. Notice that SqlExit has no parameters. Thus, in the current example, calling SqlExit once will close both of the remaining open connections (hConnHandlel and hConnHandle2) if executed after calling SqlClose(hConnHandle2). (Of course, if the program had not called SqlClose at all, then the calling SqlExit would have closed all three of the open connections.)

Using the SqlSendcmd Function to Send an SQL Statement to an MS SQL Server for Execution

The DB-Library (DBLIB) provides two methods for sending SQL statements to an MS-SQL Server for execution. SqlSendCmd lets you send individual commands. On the other hand, calls to the SqlCmd function let you put multiple SQL statements into a batch that you can then tell the DBMS to execute by calling the SqlExec function. (You will learn how to use SqlExec in conjunction with SqlCmd in Tip 398, "Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query.")

To send an SQL statement to the MS-SQL Server using the SqlSendCmd function, use the syntax

nRetCode = SqlSendCmd(hConnHandle, sSQLStatement)

where:

• Long nRetCode. Is the integer result returned by the SqlSendCmd function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long hConnHandle. Is the connection handle returned by the SqlOpenConnection function.
• sSQLStatement. Is the string (variable) containing the SQL statement the MS-SQL Server is to execute.

For example, to execute a USE statement to switch the default database to NORTHWIND and then execute an UPDATE statement to increase the values UNITPRICE column of the PRODUCTS table, execute the following (2) SqlSendCmd function calls:

nRetCode = SqlSendCmd{hConnHandle, "USE Northwind") nRetCode = SqlSendCmd(hConnHandle, _ "UPDATE PRODUCTS SET unitprice = unitprice * 1.20")

Before calling the SqlSendCmd function, the application must, of course, call SqlOpenConnection to open a connection with the target MS-SQL Server (as you learned to do in Tip 394, "Using the SqlOpenConnection() Function to Log On to an MS-SQL Server"). Please review the contents of Tip396.BAS on the companion CD for a complete listing of the Visual Basic program that updates the UNITPRICE column and displays the value of nRetCode in a message box after executing each of two function calls in the current example.

Using the SqlNumCols() Function to Determine the Number of Columns in the Results Set Generated by a Query

As you learned in Tip 396, "Using the SqlSendCmd Function to Send an SQL Statement to an MS-SQL Server for Execution," you can use the SqlSendCmd() function to send SQL statements (in the form of character strings) to the DBMS for execution. Any valid SQL statement that the program used to open a connection with the server, provided that the username/login ID has enough privilege to execute is fair game. Therefore, in addition to statements that modify data values or objects in the database, an application can send queries that return zero, one, or several rows of data for the program to display or modify.

When an application sends a SELECT statement to an MS-SQL Server, the DBMS automatically creates a cursor and fills it with the query's results table. The application can then retrieve data values from the cursor into program variables or forms fields for display or modification. By using the SqlNumCols() function in conjunction with the SqlColName() function (see Tip 398, "Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query"), you can build a general-purpose subroutine that will fill an MSFlexGrid (which you learned about in Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data") with the names of the columns returned by the query. Then (as you will learn in Tip 399, "Using the SqlData() Function to Retrieve Query Results from a Cursor into an Application") you can use the SqlData() function to retrieve the cursor's data into the MSFlexGrid's columns as you move forward through the rows in the cursor by calling SqlNextRow().

The syntax of the SalNumCols() function call is

SqlNumCols (nConnHandle)

where Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd() (or SqlSend()) to send an SQL statement (or set of set statements) to the MS-SQL Server for execution.

The function returns the number of columns in the nConnHandle cursor's current set of results.

For example, the SqlNumCols() function near the end of the Visual Basic program

Sub main() Dim sDBLIBVersion As String Dim nConnHandle As Long Dim nRetCode As Long sDBLIBVersion = SqlInit() nConnHandle = SqlOpenConnection("NVBizNet2", "konrad", _ "king", "my_ws-1", App.EXEName) nRetCode = SqlSendCmd(nConnHandle, "USE Northwind") nRetCode = SqlSendCmd(nConnHandle,"SELECT * FROM products") MsgBox "Cursor column count = " & SqlNumCols(nConnHandle) SqlExit SqlWinExit End Sub

will display the number of columns in the cursor created by the DBMS when it executed the query:

SELECT * FROM products

The usefulness of being able to retrieve the number of columns in a cursor will become apparent when you learn how to write a general-purpose subroutine that displays cursor column names in an MSFlexGrid in Tip 398.

Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query

In Tip 397, "Using the SqlNumCols() Function to Determine the Number of Columns in the Results Set Generated by a Query," you learned how to use the SqlNumCols() function to determine the number of columns of data the DBMS put into the results set for a query you sent to the MS-SQL Server by calling the SqlSendCmd() function. Being able to determine the number of columns in a cursor while the program is running makes it possible to write a single subroutine that retrieves and displays the names of the columns returned by various queries—even if each query's SELECT clause contains a different number of columns.

The syntax of the SqlColName() function call is

SqlColName(nConnHandle, iColNo)

where:

• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd() (or SqlSend()) to send an SQL statement (or set of set statements) to the MS-SQL Server for execution.
• Integer iColNo is the number of the column whose name the function is to retrieve. The first column in the cursor is number 1 (vs. 0).

The function returns a character string that contains the name of the results table column referenced by the iColNo value. (If the column has no name or if the value of iColNo is greater than the number of columns in the cursor, the function will return an empty string.)

Therefore, the subroutine

Private Sub GetColumnNames _ (nConnHandle As Long, FlexGrid As MSFlexGrid) Dim i As Integer FlexGrid.Cols = SqlNumCols(nConnHandle) FlexGrid.Rows = 1 FlexGrid.Row = 0 FlexGrid.Col = 0 For i = 1 To FlexGrid.Cols FlexGrid.Text = SqlColName(nConnHandle, i) FlexGrid.ColWidth(FlexGrid.Col) = _ Forml.TextWidth(FlexGrid.Text) + 120 If FlexGrid.Col < FlexGrid.Cols - 1 Then FlexGrid..Col = FlexGrid.Col + 1 End If Next i FlexGrid.Redraw = True End Sub

will fill the first row in an MSFlexGrid object (which you learned about in Tip 370, "Adding the MSFlexGrid Control to a Visual Basic [VB] Form to Display SQL Table Data") with the names of the columns in the query results set in cursor for the connection handle passed to the subroutine as the nConnHandle parameter.

Please review Tip398.bas on the companion CD to see an example application that opens a connection with an MS-SQL Server, uses the connection to send a query to the DBMS, and then displays the column names in the query's results table.

Using the SqlData() Function to Retrieve Query Results from a Cursor into an Application

The MSFlexGrid is a "flexible" data grid that a Visual Basic (VB) application can use to display DBMS data without knowing the number of rows and columns of data values to be displayed until run time. As you learned in Tip 398, "Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query," a program can change the number of columns in an MSFlexGrid on the fly. For example, an application might use an MSFlexGrid to display a query results set with 5 columns and 300 rows at one point during the program's execution, and later resize the same grid to display 250 rows of data with 15 columns per row.

After adding an MSFlexGrid to a VB form (see Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data") and filling in its column headings (see Tip 398), you can use the SqlData() function to retrieve query results from the connection handle's cursor into the cells that make up the MSFlexGrid. The syntax of the SqlData() function call is

SqlData(nConnHandle, iColNo)

where:

• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd() (or SqlSend()) to send an SQL statement (or set of set statements) to the MS-SQL Server for execution.
• Integer iColNo is the number of the column whose value the function is to retrieve. The first column in the cursor is number 1 (vs. 0).

The function returns a character string containing the data value in the results column given by the value of iColNo. If the cursor column is of data type binary, varbinary, or image, the SqlData() function will return a string of binary data with one character per byte of data in the cursor column. For all other data types (both character string and numeric), the SqlData() function will return a character string of readable characters. (If the column number to be retrieved [iColNo] is greater than the highest-numbered cursor column, or if the cursor's column value is blank or NULL, the SqlData() function will return an empty string.)

Because each SqlData() function call returns the character string representation of the data value in only a single column of a row in the cursor, your program must call the function multiple times to retrieve the data values from a row of query results that has several columns. For example, to retrieve the data value in the sixth column in the nConnHandle cursor into the sUnitPrice character string, use the VB expression:

sUnitPrice = SqlData(nConnHandle,6)

To retrieve all of the column values from the "nConnHandle" cursor into the cells of the MSFlexGrid "FlexGrid", use a VB For-Next loop such as:

FlexGrid.col = 0 For i = 1 to SqlNumCols(nConnHandle) FlexGrid.text = SqlData(nConnHandle,i) If FlexGrid.col < FlexGrid.cols - 1 Then FlexGrid.col = FlexGrid.col + 1 End If Next i

Using the SqlNextRow() Function to Move Forward Through the Rows in a Cursor

As you learned in Tip 399 the SqlData() function retrieves the value from one of the columns in the current row of the cursor. The SqlNextRow() function lets you change the cursor's current row by moving the cursor pointer forward one row at a time. Because the DBLIB positions the cursor pointer prior to the first row in the cursor after the DBMS fills the cursor with query results, be sure to call the SqlNextRow() before the first SqlData() function call to retrieve data from a column in the first row of query results. By calling the SqlNextRow() function repeatedly until it returns the value NOMOREROWS, an application can move forward (one row at a time) through all of the rows of query results that the DBMS placed into the cursor.

The syntax of the SqlNextRow() function call is

SqlNextRow (nConnHandle)

where Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd() (or SqlSendf)) to send an SQL statement (or set of set statements) to the MS-SQL Server for execution.

The function returns one of five integer values:

• REGROW (-1). If the current row in the cursor contains SELECT statement results.
• . If the current row in the cursor contains the results from a COMPUTE statement.
• FAIL (0). If the SqlNextRow() function was unsuccessful.
• NOMOREROWS (-2). If there are no more rows in the cursor-whether the SELECT statement returned no rows or the DBMS was unable to execute the query due to a deadlock, for example.
• BUFFULL (-3). If row buffering is turned on and the buffer is full.

For example, the Visual Basic subroutine

Private Sub GetResultsSet _ (nConnHandle As Long, FlexGrid As MSFlexGrid) Dim i As Integer FlexGrid.Redraw = False FlexGrid.AllowUserResizing = FlexResizeColumns FlexGrid.FixedCols = 0 FlexGrid.Row = 0 Do Until NOMOREROWS = SqlNextRow(nConnHandle) FlexGrid.Col = 0 FlexGrid.Rows = FlexGrid.Rows + 1 FlexGrid.Row = FlexGrid.Row - 1 For i = 1 To FlexGrid.Cols FlexGrid.Text = SqlData(nConnHandle,i) If FlexGrid.Col < (FlexGrid.Cols - 1) Then FlexGrid.Col = FlexGrid.Col + 1 End If Next i Loop FlexGrid.Redraw = True End Sub

will fill the rows of cells in an MSFlexGrid object (which you learned about in Tip 370, "Adding the MSFlexGrid Control to a Visual Basic (VB) Form to Display SQL Table Data") with the data values from each of the columns in the rows of query results of the nConnHandle cursor.

Please review Tip400.bas on the companion CD for an example program that opens a connection to an MS-SQL Server, uses the connection to send a query to the DBMS, and then uses an MSFlexGrid to display the column names and data values in the query's results table.

Using the SqlCmd() Function to Build an SQL Statement Batch

While the SqlSendCmd() function (which you learned about in Tip 396, "Using the SqlSendCmd Function to Send an SQL Statement to an MS-SQL Server for Execution") lets you send individual SQL statements to an MS-SQL Server for execution, the SqlCmd() function lets you build a statement batch. After you finish adding statements to the batch, the SqlExec() and SqlSend() functions let you send the entire set of statements to the MS-SQL Server for execution as a single transaction.

The syntax of the SqlCmd() function call is

nRetCode = SqlCmd(nConnHandle, sSQLStatement)

where:

• Long nRetCode is the integer result returned by the SqlCmd() function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long nConnHandle is a connection handle returned by the SqlOpenConnection() (or SqlOpen()) function.
• String sSQLStatement is a character string the function is to append onto the existing contents of the nConnHandle command buffer. The sSQLStatement parameter in an SqlCmd() function call can contain a complete SQL statement or a portion of one or more statements.

For example the Visual Basic code

nRetCode = SqlCmd(nConnHandle, "USE Northwind") nRetCode = SqlCmd(nConnHandle, " SELECT * FROM products") nRetCode = SqlCmd(nConnHandle, " USE Pubs") nRetCode = SqlCmd{nConnHandle, " SELECT * FROM authors")

builds an SQL statement batch that consists of two USE statements and two queries. Each SqlCmd() function call appends the contents of its sSQLStatement parameter onto the existing contents of the nConnHandle statement buffer (vs. deleting or overwriting any existing text). (A connection handle's statement buffer is not cleared until the program makes an SqlExec() or SqlSend() function call that sends the buffer's contents [the SQL statement batch] to the DBMS for execution.)

Using the SqlExec() Function to Submit an SQL Statement Batch to an MS SQL Server for Execution

As you learned in Tip 401, "Using the SqlCmd() Function to Build an SQL Statement Batch," the SqlCmd() function calls let you create an SQL statement batch by appending character strings onto the existing text in a connection handle's command buffer. Calling the SqlExec() function sends the contents of the command buffer (the SQL statement batch) as a single (and perhaps lengthy) character string to the DBMS for execution.

The syntax of the SqlExec() function call is

nRetCode = SqlExec(nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlExec() function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long nConnHandle is the connection handle returned by an SqlOpenConnection() (or SqlOpen()) function.

For example, if the Visual Basic expression

nRetCode = SqlExec(nConnHandle)

follows the SqlCmd() function calls in Tip 401, the SqlExec() function will send the character string

USE Northwind SELECT * FROM products USE Pubs SELECT * FROM authors

to an MS-SQL Server, clear the command buffer, and wait for the DBMS to finish executing all of the statements in the batch. If the command buffer is empty, or if any of its statements contain syntax errors or attempt actions that exceed the connection's privileges on the server, the SqlExec() function will return a value of FAIL (0). Conversely, if the DBMS successfully executes all of the statements in the SQL statement batch, the SqlExec() function will return a value of SUCCEED (1), and each statement's results will be available for retrieval into the connection handle's cursor by calling the SqlResults() function (which you will learn about in Tip 403, "Using the SqlResults() Function to Retrieve an SqlExec() Submitted Query's Results Set").

Using the SqlResults() Function to Retrieve an SqlExec() Submitted Query s Results Set

After calling the SqlExec() function to send an SQL statement batch to an MS-SQL Server for execution, your application must call the SqlResults() function once for each statement in the batch to tell the DBLIB to prepare each statement's results set for retrieval by function calls in the application program. Moreover, if you neglect to call the SqlResults() function until there are no more unprepared results sets, any subsequent SqlExec() function call will fail because previous results were not completely processed. Although you do not have to call SqlData() to retrieve all of the data values returned by SELECT statements (if any) in an SqlExec() (or SqlSend()) transmitted statement batch, you do have to flag each results set as prepared and processed by calling the SqlResults() function until there are no more results sets for it to process.

The syntax of the SqlResults() function call is

nRetCode = SqlResults(nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlResults() function call. After the function call, the value of nRetCode will be SUCCEED (1), FAIL (0), NOMORERESULTS (2), or NOMORERPCRESULTS (3).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSend() to send an SQL statement batch to the MS-SQL Server for execution.

To avoid having to keep track of the number of statements in the command buffer so you know how may times to call the SqlResults() function, simply use a Do-Until loop, such as

Do Until NOMORERESULTS = SqlResults(nConnHandle) If SqlNumCols(nConnHandle) > 0 Then 'Routines that retrieve and work with query results GetColumnNames nConnHandle, Forml.QueryResults GetResultsSet nConnHandle, Form1.QueryResults MsgBox "Click your mouse pointer on the OK button " _ & "to process the next set of results." End If Loop

which will continue making SqlResults() function calls until there are no more results sets to process (at which point the SqlResults() function will return the value NOMORERESULTS).

Because the SqlResults() function only prepares a results set for processing, your application must still call the SqlNextRow() function to work its way through the rows in a query results set's cursor and the SqlData() function to retrieve the value in a column of the cursor's current row. For example, the Do-Until loop in the preceding example checks to see if the results set from an executed statement in the SQL statement batch returned any columns of data (in which case SqlNumCols(nConnHandle) > 0). If so, then the program calls the subroutines (which you developed in Tip 398, "Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query," and Tip 400, "Using the SqlNextRow() Function to Move Forward Through the Rows in a Cursor") to retrieve the cursor's column names and data values.

Please review the contents of Tip402.bas on the companion CD to see an example Visual Basic (VB) program that connects with an MS-SQL Server, uses the SqlExec() function to send two USE statements and two queries to the DBMS for execution, and then displays the results of each query in an MSFlexGrid on a VB form.

Using SqlSend() to Submit a Statement Batch Without Waiting for the DBMS to Finish Executing All of Its Statements

As mentioned in Tip 402, "Using the SqlExec() Function to Submit an SQL Statement Batch to an MS-SQL Server for Execution," an SqlExec() function call halts further program execution while the SqlExec() function waits for the DBMS to finish processing the SQL statement batch. Consequently, if one (or more) of the statements in the batch retrieves or sorts a large amount of data, or has to wait for another DBMS user to release required resources, the application may appear to "hang" for an unacceptable length of time. If you want the program to perform other tasks while the DBMS executes the SQL statements in a batch, submit the batch by calling the SqlSend() function instead of calling the SqlExec() function.

The syntax of the SqlSend() function call is

nRetCode = SqlSend(nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlSend() function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long nConnHandle is a connection handle returned by the SqlOpenConnection() (or SqlOpen()) function.

After executing an expression such as

nRetCode = SqlSend(nConnHandle)

(which tells the DBLIB to send the SQL statements in the nConnHandle command buffer to the MS-SQL Server for processing), the system will execute the next statement in the program—without waiting for the DBMS to finish processing the statements in the batch. However, before making another SqlSend() or SqlExec() function call, the program must call the SqlOk() function to find out whether or not the DBMS finished processing the statements in the batch. In fact, the function call

nRetCode = SqlExec(nConnHandle)

is equivalent to

nRetCode = SqlSend(nConnHandle) nRetCode = SqlOk(nConnHandle)

because SqlOk() (like the SqlExec()) will wait for the DBMS to finish processing the statements in the nConnHandle command buffer before returning control to the application program.

Fortunately, the DBLIB lets you use the SqlDataReady() function (which you will learn about in Tip 405, "Using the SqlDataReady() Function to Determine If an MS-SQL Server Has Finished Processing an SQL Statement Batch") to check whether or not the DBMS has finished processing the SQL statement batch (submitted by SqlSend()) without waiting for the system to actually finish executing all of the statements in the batch.

Therefore, a Visual Basic (VB) command sequence that would allow an application to perform other tasks while waiting for the DBMS to process an SQL statement batch would be similar to:

'SqlCmd() function calls to build the SQL batch SqlSend(nConnHandle) Do Until SUCCEED = SqlDataReady(nConnHandle) 'VB statements that perform work you want the application 'to do while waiting for the DBMS to finish processing 'the SQL statements in the "nConnHandle" command buffer Loop If SqlOk(nConnHandle) = SUCCESS Then 'VB statements that process the results sets (one for 'each statement in the batch) for the SQL statement batch 'submitted to the MS-SQL Server by the SqlSend() function 'call End If

After you learn how to use the SqlDataReady() function in Tip 405, review the contents of Tip405.bas on the companion CD for an example of a program that submits a statement batch to the DBMS for execution and performs other work while waiting for the DBMS to finish processing the batch.

Using the SqlDataReady() Function to Determine If an MS SQL Server Has Finished Processing an SQL Statement Batch

In Tip 404, "Using SqlSend() to Submit a Statement Batch Without Waiting for the DBMS to Finish Executing All of Its Statements," you learned how to use the SqlSend() function to submit the contents of a connection handle's command buffer to an MS-SQL Server for processing. The main advantage gained by calling SqlSend() instead of SqlExec() is that the SqlSend() function submits the contents of the command buffer to an MS-SQL Server for processing and lets your application continue on with other work while the DBMS executes the statements in the batch. (As you learned in Tip 402, "Using the SqlExec() Function to Submit an SQL Statement Batch to an MS-SQL Server for Execution," after an application calls it, the SqlExec() function sends the contents of the connection's command buffer to the MS-SQL Server for processing and halts further program execution while it waits for the DBMS execute all of the statements in the statements batch.)

The SqlDataReady() function lets you check whether or not the DBMS is finished processing a batch of statements submitted to an MS-SQL Server by calling the SqlSend() function. After an application calls SqlSend() (and the DBLIB sends the contents of the command buffer to the DBMS), the program must call SqlOk() to find out whether or not the DBMS successfully executed all of the statements in the batch. Unfortunately, SqlOk(), like SqlExec(), will not return control to an application until the DBMS finishes processing the entire batch.

SqlDataReady(), on the other hand, does not wait for the DBMS to finish processing the contents of the command buffer. Instead, the function simply just checks whether or not the DBMS is finished with the batch. Therefore, if SqlDataReady() returns SUCCEED (1), calling SqlOk() will not cause the program to "hang" because the DBMS is finished with the batch. As a result, SqlOk() can retrieve the batch-processing result code without having to wait. On the other hand, if SqlDataReady() returns FAIL (0), the DBMS has not yet finished processing the batch and the application should call SqlOk() at a later time, to give the DBMS a chance to finish executing the remaining (unexecuted) statements in the batch.

The syntax of the SqlDataReady() function call is

nRetCode = SqlSend(nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlDataReady() function call. After the function call, the value of nRetCode will be either SUCCEED (1) (meaning the DBMS has finished processing the statement batch) or FAIL (0) (meaning the DBMS is still executing statements in the batch).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSend() to send an SQL statement batch to the MS-SQL Server for execution.

For example, the following code for the Check if Done button on Form1 of the Visual Basic PROJECT405.VBP on the companion CD

Private Sub DataReady_Click() If SqlDataReady(nConnHandle) = SUCCEED Then If SqlOk(nConnHandle) = SUCCEED Then GetBatchResults (nConnHandle) Else MsgBox "The statement batch failed to execute." End If Else MsgBox "The DBMS is still processing the batch." End If End Sub

lets the user check the progress of the statement batch and calls the SqlOk() function only after the DBMS is finished processing the batch, thus allowing the user to do other work while the DBMS executes the batch of SQL statements submitted to it by the SqlSend() function call.

Using SqlCancel() to Stop the Execution of a Statement Batch Sent to an MS SQL Server and Clear the Batch Results Buffer

The SqlCancel() function lets an application tell an MS-SQL Server to stop processing a statement batch. When the DBMS receives the SqlCancel() function's cancel request, it aborts the execution of the current statement in the batch, undoes any work performed by a partially executed statement batch, and clears the batch's results buffer.

The syntax of the SqlCancel() function call is

nRetCode = SqlCancel(nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlCancel() function call. After the function call, the value of nRetCode will be either SUCCEED (1) (meaning the DBMS was able to respond to the cancel request) or FAIL (0).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSend(), SqlExec(), or SqlSendCmd() to send one or more SQL statements to the MS-SQL Server for execution.

Therefore, the expression

nRetCode = SqlCancel(nConnNVBizNet2)

in a Visual Basic (VB) program will tell the MS-SQL Server to which the application is connected by the nConnNVBizNet2 connection handle to stop processing the connection's statement batch and clear any pending results from the connection's results buffer.

In addition to aborting the execution of a statement batch (by calling the SqlCancel() after calling SqlSend()), an application can use SqlCancel() to clear pending results from an executed statement batch by calling the function after calling SqlOk(), SqlExec(), or SqlSendCmd(). As you learned in Tip 403, "Using the SqlResults() Function to Retrieve an SqlExec() Submitted Query's Results Set," a program must call the SqlResults() function repeatedly (once for each statement in a statement batch) until there are no more sets of results to process. However, if you do not want the data produced by the statements in a batch, or if the statements did not produce any query results, you can skip making SqlResults() function calls by calling the SqlCancel() function, which clears all pending results.

The VB project in PROJECT406.VBP on the companion CD adds a Cancel Batch button (which calls the SqlCancel() function) to the project you reviewed in Tip 405, "Using the SqlDataReady() Function to Determine If an MS-SQL Server Has Finished Processing an SQL Statement Batch" (PROJECT405.VBP).

Using the SqlCanQuery() Function to Remove the Remaining (Unprocessed) Rows in the Current Set of Results

While the SqlCancel() function lets you stop the processing of a statement batch and discard all sets of results, the SqlCanQuery() function lets you discard the remaining rows of unprocessed results in the current results set. Suppose, for example, that your Visual Basic (VB) program executes the expression

nRetCode = SqlSendCmd(nConnHandle, "USE Northwind" _ & " SELECT * FROM products USE Pubs " _ & " SELECT * FROM authors "

which sends four SQL statements to an MS-SQL Server for execution. As you learned in Tip 403, "Using the SqlResults() Function to Retrieve an SqlExec() Submitted Query's Results Set," the program must call the SqlResults() function four times (once for each SQL statement sent to the MS-SQL Server) before calling the SqlSendCmd(), SqlSend(), or SqlExec() function again. Moreover, as you learned in Tip 400, "Using the SqlNextRow() Function to Move Forward Through the Rows in a Cursor," the program must call the SqlNextRow() function once for each row returned by the two queries in the current example. If the application does not call the SqlNextRow() function until the function returns the value NOMOREROWS, a subsequent call to the SqlResults() function will fail because all of the rows in the current results set (retrieved by calling SqlResults()) were not processed (by calling SqlNextRow()).

If you want to stop processing rows in the results set generated when the DBMS executes the query

SELECT * FROM products

without calling the SqlNextRow() function once for each row of PRODUCTS in the query's results table, call the SqlCanQuery() function as

nRetCode = SqlCanQuery{nConnHandle)

where:

• Long nRetCode is the integer result returned by the SqlCanQuery() function call. After the function call, the value of nRetCode will be either SUCCEED (1) (meaning the DBLIB was able to clear the remaining, unprocessed rows of query results) or FAIL (0).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSend(), SqlExec(), or SqlSendCmd() to send one or more SQL statements to the MS-SQL Server for execution.

The DBLIB will then discard any unprocessed rows of query results from the SELECT * FROM products query. However, the rows of results from the second query, SELECT * FROM authors, will still be available for retrieval (by calling the SqlNextRow() function) after calling the SqlResults() function twice more—once to prepare the results set for the USE Pubs statement, and a second time to prepare the results set for the SELECT * FROM authors statement.

Using the SqlUse() Function to Set the Current Database for an MS SQL Server Connection

The SqlUse() function lets you select the current database for a connection with an MS-SQL Server. When executing an SQL statement, the MS-SQL Server assumes that it will find the tables referenced in the FROM clause in the session's current database. As such, if you log in to an MS-SQL Server and your initial default database is NORTHWIND, for example, you can execute the statement

SELECT * FROM products

because PRODUCTS is one of the tables in the NORTHWIND database. On the other hand, if you attempt to execute the statement

SELECT * FROM authors

while your current database is still NORTHWIND, the DBMS will abort your query and display an error message similar to:

Server: Msg 208, Level 16, State 1, Line1 Invalid object name 'authors'.

You can correct the error either by typing in the fully qualified object name for the AUTHORS table, as

SELECT * FROM pubs.dbo.authors

or by selecting a new default/current database and then submitting the query again using the table's unqualified name:

USE pubs SELECT * from authors

In the SqlUse() function call syntax

nRetCode = SqlUse(nConnHandle)

the following is true:

• Long nRetCode is the integer result returned by the SqlUse() function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function).

This call performs the same work as the Transact-SQL USE statement. Therefore, your Visual Basic application can select the PUBS database and query its AUTHORS table by executing the following statements:

nRetCode = (nConnHandle, "PUBS") nRetCode = SqlSendCmd(nConnHandle, "SELECT * FROM authors")

Using the Vbsql1_Error() Routine to Display DBLIB Generated Error Messages

After you include the Visual Basic (VB) SQL control (VBSQL.OCX) on one of the forms in your VB project (as you learned to do in Tip 391, "Adding DB-Library [DBLIB] Functionality to Visual Basic [VB]"), you can define an error-handling routine that the DBLIB will execute if it encounters an error during run time. Although a DBLIB function typically returns a value of FAIL (0) when it is unable to complete its work successfully, the error code does not tell you why the function failed to execute. Fortunately, you can use the DBLIB error handler Vbsql1 Error() to display not only the error number and the severity of the error, but also a text message that tells you want went wrong.

To add the DBLIB error handler to your VB project, perform the following steps:

1. Click your mouse pointer on the form to which you added the VBSQL.OCX control. For example, if you worked on the project in Tip 391, you added the VBSQL.OCX control to Form1. Therefore, click your mouse pointer on Form1.frm in the project window near the upper-right corner of the VB application window.
2. Select the View menu Code option from the Standard toolbar. VB will display the code for the subroutines and functions defined for Form1, similar to that shown in Figure 409.1.

   Figure 409.1: The Visual Basic (VB) application window with the Code pane for Form1 of a VB project

3. Click your mouse pointer on the drop-down list button to the right of the Object field (at the top of the Code pane), and select Vbsql1 from the drop-down list.
4. Click your mouse pointer on the drop-down list button to the right of the Procedure field (immediately to the right of the Object field at the top of the Code pane), and select Error from the drop-down list. VB will add the declaration for the Vbsql1_Error() routine to the Code pane for Form1.

Next, add the code you want the application to execute in case of a DBLIB error at run time. For example, to display the error code, severity, and error description, add a MsgBox call to the body of the Vbsql1_Error() subroutine definition as:

Private Sub Vbsql1_Error(ByVal SqlConn As Long, _ ByVal Severity As Long, ByVal ErrorNum As Long, _ ByVal ErrorStr As String, ByVal OSErrorNum As Long, _ ByVal OSErrorStr As String, RetCode As Long) Select Case ErrorNum Case 10007: 'Do not display non-error (info) messages Case Else MsgBox "Error Code: " & ErrorNum & vbCrLf & _ "Severity: " & Severity & vbCrLf & _ "Message: " & ErrorStr, vbOKOnly, _ "DBLib Error In - " & App.EXEName End Select End Sub

After you add the code in the current example to the body of the Vbsql1_Error() routine, your VB application will use a Windows message box to display the error code and description of any DBLIB error encountered during run time.

Using the Vbsql1_Message() Routine to Display MS SQL Server generated Error Messages

In addition to defining an error handler for errors encountered by the DBLIB during run time (as you learned to do in Tip 409, "Using the Vbsql1_Error() Routine to Display DBLIB-Generated Error Messages"), you can also create an error handler for errors reported to the DBLIB by an MS-SQL Server. DBLIB errors include such things as attempting to call the SqlExec() function a second time without calling the SqlResults() function until it returns NOMORERESULTS, or calling the SqlResults() function to retrieve a second results set before calling the SqlNextRow() function until it returns NOMOREROWS first. MS-SQL Server reported errors, on the other hand, would include such things as a SELECT statement that includes an undefined object name in its SELECT clause or an UPDATE statement that attempts to modify the contents of an object when the connection does not have the correct set of privileges to do so. In short, the Vbsql1_Error() routine reports errors that occur within the DBLIB, while the Vbsql_Message() routine reports errors that occur on the MS-SQL Server.

To add the MS-SQL Server error message handler to your Visual Basic (VB) project, perform the following steps:

1. Click your mouse pointer on the form to which you added the VBSQL.OCX control. For example, if you worked on the project in Tip 391, "Adding DB-Library (DBLIB) Functionality to Visual Basic (VB)," you added the VBSQL.OCX control to Form1, so you would click your mouse pointer on Form1.frm in the project window near the upper-right corner of the VB application window.
2. Select the View menu Code option from the Standard toolbar. VB will display the code for the subroutines and functions defined for Form1.
3. Click your mouse pointer on the drop-down list button to the right of the Object field (at the top of the Code pane), and select Vbsql1 from the drop-down list.
4. Click your mouse pointer on the drop-down list button to the right of the Procedure field (immediately to the right of the Object field at the top of the Code pane), and select Message from the drop-down list. VB will add the declaration for the Vbsql1_Message() routine to the Code pane for Form1.frm.

Next, add the code you want the application to execute whenever an MS-SQL Server reports an error to the DBLIB error at run time. For example, to display the message number, error state, severity, and description, add a MsgBox() call to the Vbsql1_Message() subroutine as:

Private Sub Vbsql1_Error(ByVal SqlConn As Long, _ ByVal Message As Long, ByVal State As Long, _ ByVal Severity As Long, ByVal MsgStr As String, _ ByVal ServerNameStr As String, ProcNameStr As String, _ ByVal Line As Long) Select Case Message Case 5701: 'Do not display non-error (info) messages Case Else MsgBox "Error reported by MS-SQL Server: " & _ ServerNameStr & "." & vbCrLf & "Msg: " & _ Message & ", Severity: " & Severity & _ ", State: " & State & ", Line: " & _ Line & vbCrLf & "Message: " & MsgStr, vbOKOnly, _ "MS-SQL Server Reported Error In - " & App.EXEName End Select End Sub

After you add the code in the current example to the body of the Vbsql1_Message() routine, your VB application will use a Windows message box to display the error code and description of any error that an MS-SQL Server reports to the DBLIB during run time.

Using the SqlColType() Function to Determine the Data Type of a Column

As you learned in Tip 399, "Using the SqlData() Function to Retrieve Query Results from a Cursor into an Application," the SqlData() function lets you retrieve a data value from a column in a DBLIB buffer into program an object (such as a variable, field in a structure, field in a form, or an MSFlexGrid) in your application. If the DBLIB buffer column contains data of type BINARY, VARBINARY, or IMAGE, the SqlData() function returns a string of binary data that consists of one character per byte of data from the (DBLIB buffer) results column. For all other data types (both numeric and character/text) the SqlData() function will return a character string of readable characters. While the SqlData() function returns a column's data value as either a bit string or a character string, the SqlColType() function lets you determine the actual data type of the columns data.

The syntax of the SqlColType() function call is

iColType = SqlColType(nConnHandle, iColNo)

where:

• Integer iColType is the integer value constant from the list in Table 411.1 that represents the data type of the results table column specified by the value of iColNo.

  Table 411.1: Visual Basic (VB) INTEGER Constants That Represent Each of the Valid VB and SQL Data Types

  SQL Column Data Type	Numeric Value Returned	SQL Column Data Type	Numeric Value Returned
  binary	SQLBINARY	varbinary	SQLBINARY
  char	SQLCHAR	varchar	SQLCHAR
  datetime	SQLDATETIME	smalldatetime	SQLDATATIME4
  decimal	SQLDECIMAL	numeric	SQLNUMERIC
  float	SQLFLOAT8	real	SQLFLT4
  image	SQLIMAGE	text	SQLTEXT
  int	SQLINT4	smallint	SQLINT2
  		tinyint	SQLINT1
  money	SQLMONEY	smallmoney	SQLMONEY4

• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd(), SqlExec(), or SqlSend() to send an SQL statement (or set of set statements) to the MS-SQL Server for execution.
• Integer iColNo is the number of the DBLIB buffer column whose data type the function is to retrieve. The first column in the DBLIB buffer is number 1 (vs. 0).

Therefore, to find out if the third column of query results in the DBLIB buffer is of data type IMAGE, a Visual Basic program could execute a statement such as:

If SqlColType (nConnHandle, 3) = SQLIMAGE MsgBox "Use Image App. to display image in Col 3." End If

Using the SqlDatLen() Function to Determine the Number of Bytes of Data Stored in a DBLIB Buffer Column

For non-numeric data types (such as TEXT, CHAR, and VARCHAR), the SqlDatLen() function will return the number of characters the SqlData() function can retrieve from a particular column of a row in a DBLIB buffer. If a buffer column contains numeric data (such as SMALL-INT, FLOAT, or MONEY), the SqlDatLen() function will return the maximum printable width (the storage size) for the data type instead of the actual number of digits currently stored in the DBLIB buffer column. (To determine the number of digits stored in a numeric DBLIB buffer column, use the Visual Basic LEN() function instead of calling SqlDatLen().)

The syntax of the SqlDatLen() function call is

iColLen = SqlDatLen(nConnHandle, iColNo)

where:

• Integer iColLen is the number of characters the SqlData() function will return if called to retrieve data from a non-numeric iColNo DBLIB buffer column, or the maximum number of bytes used to store the iColNo column if the data value in column iColNo is one of the numeric data types.
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd(), SqlExec(), or SqlSend() to send an SQL statement (or set of statements) to the MS-SQL Server for execution.
• Integer iColNo is the number of the column whose contents' byte size the function is to determine. The first column in the DBLIB buffer is number 1 (vs. 0).

For example, to display the number of characters in the first column in the DBLIB buffer for the nConnHandle server connection, you would execute a Visual Basic (VB) statement similar to:

MsgBox "The number of letters in""" & _ SglData(nConnHandle,1) & """""" & _ " is " & SqlDatLen(nConnHandle, 1) & "."

Conversely, to determine the number of digits stored in a numeric column, you would use the VB LEN() function instead of the SqlDatLen() function, as in

MsgBox "The number of bytes used to store the decimal" & _ " DISCOUNT value: " & SqlData(nConnHandle, 5) & _ " is " & SqlDatLen(nConnHandle, 5) & "." & vbCrLf & _ "The number of digits retrieved by SqlData() is " & _ Len (SqlData(nConnHandle, 5)) & ".", vbOKOnly, _ "SqlDatLen() Value vs. Len() Value for Numeric Data"

which will display a message box similar to that shown in Figure 412.1.

Figure 412.1: A Windows message box displaying the results of the SqlDatLen() and Len() function calls for numeric (decimal) data retrieved by an SqlData() function call

Assigning NULL Values to Host Variables in a Visual Basic Application

When a Visual Basic (VB) variable definition, such as

DIM iHighQty

does not include a data type, VB will type the variable as data type variant-meaning that the variable may hold different types of data at different times during a program's execution. In fact, a VB application can use a variable of data type variant to hold a value of any standard data type or one of three special values: Empty, Null or Error.

When you first declare a variable of data type variant in a VB program (by omitting the type definition from the variables declaration), the system assigns the Empty value to it. The value Empty is not the same is NULL. Empty indicates that a variable of data type variant has not yet been assigned a value. A VB variant variable will be NULL only if you assign the NULL value to by executing a statement such as:

iHighQty = Null

As you learned in Tip 399, "Using the SqlData() Function to Retrieve Query Results from a Cursor into an Application," the SqlData() function returns the value from any column in a connection's DBLIB buffer as a character string. If the DBLIB buffer column has a NULL value, the SqlData() function will return an empty character string (that is, the character string returned will contain no characters). However, the character string will not be NULL. Therefore, to copy a NULL value from a DBLIB buffer column into a host program variable of data type variant, your application must call the SqlDatLen() function to check the number of bytes of data stored in the DBLIB buffer column whose contents you want to retrieve. If the SqlDatLen() function returns a value of zero (0), set the target variant variable's value to NULL.

For example, the VB code

If SqlDataLen(nConnHandle,3) = 0 Then iHighQty = NULL End If If IsNull(iHighQty) Then MsgBox "The HIGHQTY column for the discount type " & _ SqlData(nConnHandle,3) & " is NULL." End If

will display the text "NULL" whenever the value of third column in the nConnHandle results set has a NULL value. Notice that VB, like SQL, requires that you use a special function, IsNull(), to check whether or not the value of a variable is NULL. As you learned in Tip 317, "Understanding Why the Expression NULL = NULL Evaluates to FALSE," performing an equality (or nonequality) test in which one of the operands is NULL (If NULL = NULL) is never TRUE because the logical test will always evaluate to NULL.

A VB application can assign a NULL value only to variables of data type variant. As such, the cells in an MSFlexGrid (which are, by definition, data type TEXT), cannot hold NULL values. However, a program can put the character string such as NULL into an MSFlexGrid cell to indicate that the value of the cell's underlying DBLIB buffer column is NULL. For example, the VB code

For i = 1 To FlexGrid.Cols If SqlDataLen(nConnHandle, I) <> 0 Then FlexGrid.Text = SqlData(nConnHandle, i) Else FlexGrid.Text = "** NULL **" End If Next i

(extracted from the GetResultsSet() subroutine in Tip413.BAS on the companion CD) assigns the character string ** NULL ** to any cell in the MSFlexGrid when the SqlDatLen() function returns a value of 0 (which indicates that the cell's underlying DBLIB buffer column contains a NULL value).

Using SqlSetOpt() to Set the Size of the Row Buffer in Order to Allow Random Row Retrieval Using SqlGetRow()

In Tip 400, "Using the SqlNextRow() Function to Move Forward Through the Rows in a Cursor," you learned how to use the SqlNextRow() function to move forward (one row at a time) through the rows in the DBLIB buffer that holds the query results set from an MS-SQL Server. By default, the DBLIB allows only forward movement through its buffer. In order to keep its buffer as small as possible, the DBLIB discards the current row in the buffer when the program moves on to the next row by calling the SqlNextRow() function. After all, since the application cannot move backward in a forward-only buffer, there is no reason to keep a row in the buffer after the program has left it to move on to the next row.

The SqlSetOpt() function's SQLBUFFER option tells the DBLIB to maintain a fixed number of rows in its buffer until the application calls the SqlClrBuff() function to delete rows from the buffer. After calling the SqlSetOpt() function with the SQLBUFFER option, the DBLIB will allow an application to call the SqlGetRow() function, which lets the program move to any row in the buffer. You will learn all about the SqlGetRow() function in Tip 416, "Using the SqlGetRow() Function to Select the Current Row in a DBLIB Query Results Buffer." For now, the important thing to understand is that SqlGetRow() lets you make any one of the rows in the DBLIB's buffer the current row-whether the target row is prior to or after the row you are currently on. After calling the SqlGetRow() function to move to a specific row in the DBLIB buffer, you can call the SqlNextRow() function to move forward sequentially through the buffer, or call the SqlGetRow() function again to move to another row (either before or after the current row).

The syntax of the SqlSetOpt() function to set the number of rows the DBLIB is to maintain in its query results buffer is

nRetCode = SqlSetOpt(nConnHandle,SQLBUFFER,iBuffRowCt)

where:

• Long nRetCode is the integer result returned by the SqlSetOpt() function call. After the function call, the value of nRetCode will be either SUCCEED (1) or FAIL (0).
• Long nConnHandle is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() function) that was used by SqlSendCmd(), SqlExec(), or SqlSend() to send the query (or queries) to the MS-SQL Server for execution.
• Integer iBuffRowCt is the number of rows of query results the DBLIB is to maintain in its buffer. The value of iBuffRowCt must be either 0 (meaning no buffering) or in the range 2-32, 767.

Thus, to have the DBLIB maintain a 10-row buffer area for a query's results set, a program would execute a statement similar to:

NRetCode = SqlSetOpt (nConnHandle, SQLBUFFER, 10)

As mentioned previously, after the application calls the SqlSetOpt() function with the SQL-BUFFER option, it can still call the SqlNextRow() function to retrieve data from the "next" row in the buffer. Moreover, the application can also call the SqlGetRow() function any number of times to move to any row in the DBLIB buffer. For example, the following Visual Basic code moves sequentially through the first 3 rows of the current example's 10-row DBLIB buffer.

For iRow = 1 To 3 nRetCode = SqlNextRow(nConnHandle) If (nRetCode <> NOMOREROWS) And (nRetCode <> BUFFULL) Then 'Call SqlData() to retrieve specific data value from 'columns in the "current" row of the query results set End If Loop

If the program were to call the SqlNextRow() function a fourth time, the DBLIB would make the fourth row in the buffer the current row, and the application could call the SqlData() function to retrieve a column value from the DBLIB buffer's fourth (current) row. However, if the program were to call the SqlGetRow() function as

instead, the DBLIB would make the seventh row in the buffer the current row, and a subsequent SqlNextRow() function call would move the current row pointer on to the buffer's eighth row.

Please review the code for the Next 5 Authors button and the five Row buttons in Tip 414. BAS on the companion CD. Each of the Row buttons calls the SqlGetRow() function to move to a specific row in the application's six-row DBLIB buffer. The Next 5 Authors button, meanwhile, calls the SqlClrBuff() function (which you will learn about in Tip 417, "Using the SqlClrBuf() Function to Make Room for Additional Rows in the DBLIB Query Results Buffer") to clear the DBLIB buffer and then makes SqlNextRow() function calls to refill the buffer with another set of five rows from the query's results set.

Understanding the Differences Between Buffer and Cursors

Depending on the DBMS implementation, a cursor consists of either a set of pointers to the rows returned by a query or a copy of the data in the query's results set. When working with a cursor, an application does not have to know whether the cursor contains pointers to its underlying rows of query results or a copy of the data itself. As far as an application is concerned, the cursor contains the data it needs to display or modify because the cursor's methods (the subroutines and function calls the program uses to display or modify the cursor's underlying data) pass column values to the application and accept data values from the application for storage in the database.

A buffer, on the other hand, is a client-side memory-based (and perhaps hard drive-based) storage area where query results data is stored temporarily until a portion (or all) of it is moved into an application's objects (variables, structures, fields on forms, and so on) for display or modification. Unlike a cursor, a buffer is simply a copy of the data extracted from an SQL server (by executing a query), and any changes made on the server after the query's data is extracted to a buffer are not reflected in buffer's data. Conversely, unless an application is using a static cursor, any changes made to a cursor's underlying rows on the server are automatically reflected in the cursor's data.

To work with the data in a buffer, an application must use the available functions and subroutines in an interface program such as the DBLIB. Since a buffer is an RAM (or combination RAM/hard drive) based storage area, there is only one "type" of buffer, and the interface program's options settings determine the way in which an application can "move" among the buffers rows. Cursors, by contrast, are maintained by the DBMS itself and can be any one of three different types, depending on how they store information. The three types of cursors are: static, keyset-driven, and dynamic.

A static cursor contains a query results set based on data values present in the DBMS when the cursor is created. Subsequent changes made to database data after the cursor was created are not reflected in the cursor. However, it is possible to update the database itself through changes made to a static cursor.

The DBMS creates a keyset-driven cursor by placing a pointer to the underlying row for each row in a query's results set into the cursor. Whenever an application requests data from one of the rows in the cursor, the DBMS uses the pointer to retrieve the current data values from the underlying row in the database table(s). As such, a keyset-driven cursor's data will contain any changes made to the cursor's underlying rows even if those changes occur after the cursor was created. As is the case with the two other types of cursors, changes made to a keyset-driven cursor's data will update the underlying data values in the DBMS.

Finally, when maintaining a dynamic cursor, the DBMS not only keeps track of changes made to data values in underlying rows that existed when the cursor was created, but also makes sure that the cursors reflects database row deletions and insertions. As such, the number of rows in a dynamic cursor will change as rows that satisfy the search criteria in the cursor query's results set are inserted into or deleted from the database.

You will learn more about cursors in Tips 426-443. For now, the important thing to understand is that a buffer is a "staging area" for a one-way flow of data values from the DBMS to an application program. A cursor, meanwhile, is a database object that not only allows an application to retrieve database data, but also lets the program change the data on the server by updating the cursor's contents.

Using the SqlGetRow() Function to Select the Current Row in a DBLIB Query Results Buffer

While the SqlNextRow() function lets an application move forward through a DBLIB buffer one row at a time, the SqlGetRow() function lets the program access any row in the buffer at random. Before calling SqlGetRow(), a program must call both the SqlSetOpt() function to set the number of rows of query results that the DBLIB is to maintain in its buffer and the SqlNextRow() function to retrieve rows of query results from the DBMS into the DBLIB buffer.

The syntax of the SqlGetRow() function call is

nRetCode = SqlGetRow (nConnHandle, iRowNo)

where:

• nRetCode (Long) is the integer result code returned by the SqlGetRow() function call. After the function call, the value of nRetCode will be one of the following:
  • REGROW (-1) if buffer row iRowNo is a regular row of query results
  • The ID of the COMPUTE clause if buffer row iRowNo is a COMPUTE row
  • FAIL (0)
  • NOMOREROWS (-2) if row number iRowNo is not currently a row in the DBLIB buffer

• nConnHandle (Long) is the connection handle (returned by the SqlOpenConnection() or SqlOpen() functions) that was used by SqlSendCmd(), SqlSend(), or SqlExec() to send the query (or queries) to the MS-SQL Server for execution.
• iRowNo (Integer) is the number of the row of query results that the function is to select. For example, if iRowNo is 5, then the function will try to find the fifth row from the query results set in the DBLIB buffer.

Therefore, after making the function calls

'Set the DBLIB buffer to 6 rows nRetCode = SqlSetOpt(nConnHandle, SQLBUFFER, 6) 'Read 6 rows of data from the query results set into the 'DBLIB buffer For i = 1 to 6 nRetCode = SqlNextRow(nConnHandle, i) Next i

an application can make the SqlGetRow() function call

nRetCode = SqlGetRow(nConnHandle, 4)

to select the fourth row from the query results set as the DBLIB's current row. Remember, the SqlGetRow() function tells the DBLIB only which row to treat as its current row. To retrieve the data values from the current row into its variables or fields on forms, an application must call the SqlData() function.

You can call the SqlGetRow() function to move to any one of the rows of query results stored in the DBLIB buffer. However, if the buffer is full and the row that you want to select is not currently in the buffer, you must call the SqlClrBuf() routine (which you will learn about in Tip 417, "Using the SqlClrBuf() Function to Make Room for Additional Rows in the DBLIB Query Results Buffer") to discard some of the rows in the buffer and then call the SqlNextRow() routine to refill the buffer until the query result set row that you want to select is in the DBLIB buffer.

For example, if you are using a 6-row DBLIB buffer and you have executed a query that produced 15 rows of results, you can move to the 12th row of query results by executing the following code:

'Read 6 rows of query results into the DBLIB buffer For i = 1 to 6 nRetCode = SqlNextRow(nConnHandle, i) Next i 'Clear the DBLIB buffer to make room for more results SqlClrBuf nConnHandle,6 'Read rows 7 - 12 of query results into the DBLIB buffer For i = 1 to 6 nRetCode = SqlNextRow(nConnHandle, i) Next i 'Select the 12th row of query results nRetCode = SqlGetRow(nConnHandle,12)

Notice that the row number (iRowNo) supplied as a parameter to the SqlGetRow() function refers to a row's position in the set of rows that make up the query's results set, not its position among the rows stored in the DBLIB's buffer.

Using the SqlClrBuf() Function to Make Room for Additional Rows in the DBLIB Query Results Buffer

As you saw in first example in Tip 416, "Using the SqlGetRow() Function to Select the Current Row in a DBLIB Query Results Buffer," the SqlSetOpt() function's SQLBUFFER option lets you specify the number of rows of query results that the DBLIB is to maintain in its buffer. For example, if a program executes the statement

nRetCode = SqlSetOpt(nConnHandle, SQLBUFFER, 10)

the DBLIB will allow the application to retrieve 10 rows of data from the query's results set into the DBLIB buffer by calling the SqlNextRow() function 10 times. If called an llth time, in the current example, the SqlNextRow() function will be unable to add another row to the DBLIB buffer. As a result, the function will not retrieve a row of query results and will return an error code of BUFFULL (-3) instead. The SqlClrBuf() function lets you tell the DBLIB to discard one or more rows from its buffer to make room for additional rows of query results (which you can then call the SqlNextRow() function to retrieve).

The syntax of the SqlClrBuf() function call is

SqlClrBuf nConnHandle, iRowCt

where:

• nConnHandle (Long) is the connection handle (returned by the SqlOpenConnection() or the SqlOpen() functions) that was used by SqlSendCmd(), SqlSend(), or SqlExec() to send the query (or queries) to the MS-SQL Server for execution.
• iRowCt (Integer) is the number of rows of query results to clear from the buffer. If the value of iRowCt is less than 1, the DBLIB ignores the SqlClrBuf() function call. On the other hand, if the value of iRowCt is greater than or equal to the number of rows in the buffer, the DBLIB discards all of the rows in the buffer.

The DBLIB clears the rows in its buffer on a first-in/first-out basis. Therefore, if you have a 10-row buffer and you have called the SqlNextRow() function 10 times to fill it with the first 10 rows of query results, executing the statement

SqlClrBuf nConnHandle, 5

will tell the DBLIB to discard the first 5 rows added to the buffer (by calling the SqlNextRow() function).

Review the code for the Next 5 Authors button on Forml of Tip417.bas on the book's companion Web site at www.premierpressbooks.com/downloads.asp. Each time the user clicks the mouse pointer on the Next 5 Authors button, the application calls the NextAuthors_Click() routine. NextAuthors_Click() uses the SqlClrBuf() function to discard all rows of query results from the DBLIB buffer. Then NextAuthors_Click() calls GetResultsSet(), which uses the SqlNextRow() function to refill the DBLIB buffer with an additional five rows of query results.

Understanding the MS SQL Server FOR BROWSE Clause in a SELECT Statement

When added to a SELECT statement, the FOR BROWSE clause lets you read rows from a table in which another user is inserting, updating, or deleting rows. Normally, the MS-SQL Server's locking mechanisms will prevent you from reading the pages of a table for which there are pending (uncommitted) UPDATE, DELETE, or INSERT statements.

A SELECT statement without a FOR BROWSE clause that tries to read a row with a pending (uncommitted) UPDATE, for example, will "hang," waiting for the user modifying the table's contents to execute either a COMMIT or a ROLLBACK statement. If the user commits (or rolls back) the pending UPDATE before the expiration of the query's timeout interval, the SELECT statement will return its results table. Otherwise, the SELECT statement will abort without retrieving any rows of results. The FOR BROWSE clause tells the DBMS to let the query proceed without waiting for the other user to COMMIT or ROLLBACK a pending UPDATE, INSERT, or DELETE.

In other words, the statement

SELECT * FROM employees

will wait for other users to COMMIT (or ROLLBACK) pending transactions that modify the contents of the EMPLOYEES table. The statement

SELECT * FROM employees FOR BROWSE

will generate a results table without waiting, as long as the EMPLOYEES table has both a TIMESTAMP field and a UNIQUE INDEX.

To use the FOR BROWSE clause in a SELECT statement, the query must contain a single table that has both a TIMESTAMP column and a UNIQUE INDEX. Moreover, as shown in the current example, the FOR BROWSE clause should be the last clause in the SELECT statement. If a SELECT statement with the FOR BROWSE clause does not satisfy these requirements, the DBMS will execute the query as if it did not have a FOR BROWSE clause.

Understanding Why the DBLIB Does Not Support Positioned UPDATE and DELETE Statements

Unlike the ODBC interface, the DBLIB does not support positioned UPDATE and DELETE statements (which you learned about in Tips 384, "Using the SQLSetPos Function SQL_UPDATE Option to Perform a Positioned UPDATE," and 385, "Using the SQLSetPos Function SQL_DELETE Option to Perform a Positioned DELETE"). When an application calls the ODBC driver's SQLFetch() function to retrieve a row of data from a single database table into a cursor, the DBMS maintains a direct correspondence between the current row of query results and its corresponding row in the underlying table. Using this correspondence, an application can build and execute an UPDATE or DELETE statement with a WHERE CURRENT OF clause. When submitted to the DBMS for execution, the system will UPDATE or DELETE the table row that corresponds to the current row in the cursor named in the WHERE CURRENT OF clause. When using the DBLIB interface, an applications calls SqlSendCmd(), SqlSend(), or SqlExec() to send a SELECT statement to the DBMS for execution. The server sends the query's results set the DBLIB, which uses its buffer area to store the data. When a program using calls SqlNextRow(), the function retrieves a row of query results from the DBLIB buffer, not from the database itself. As a result, there is no DBMS-maintained correspondence between the row of query results retrieved into an application from the DBLIB buffer and the row in the database table from which its column values were copied.

The inability to relate the current row retrieved from the DBLIB buffer with a specific row in a database table makes it impossible for the DBLIB to tell the DBMS to execute a positioned UPDATE or DELETE. Because the lack of positioned updates and deletes is a real disadvantage when a user is allowed to browse a set of query results and wants to UPDATE (or DELETE) the data currently being displayed on the screen, the DBLIB provides a set of browse mode functions that an application can use to execute a searched UPDATE (or DELETE) that targets the database row from which the DBLIB's current row was derived.

You will learn about the DBLIB browse mode functions in Tips 420-422.

Understanding the DBLIB Browse Mode Functions

To get around the inability to relate the current DBLIB buffer row with its underlying row in a database table, the DBLIB API includes a set of browse mode functions that a program can use to create a pseudo-positioned update capability (which you will learn about in Tip 422, "Using the SqlQual() Function to Generate the WHERE Clause for a DBLIB Browse Mode UPDATE or DELETE"). The DBLIB browse mode functions are:

• SqlTabCount(nConnHandle), which returns the number of tables, including server work tables, used in the SELECT statement sent to the server using the connection handle passed as the function's only parameter.
• SqlTabBrowse(nConnHandle,iTabNum), which returns the value SUCCEED(1) if the iTabNum table used by the query sent to the DBMS using the connection handle nConnHandle can be updated using DBLIB browse mode procedures, or FAIL (0), if not. The first table in a DBLIB query is table 1 (vs. 0). Thus, after executing the statement

  iCanBeUpdated = SqlTabBrowse(nConnHandle,1)

  the value of iCanBeUpdated will be 1 (SUCCEED) if the first table used in the query can be updated by the DBLIB browse mode routines.

• SqlTabName(nConnHandle,iTabNum), which returns the name of the iTabNum table used in the query sent to the DBMS using the connection handle nConnHandle. The first table in a DBLIB query is table 1 (vs. 0). Therefore, the statement

  sTableName = SqlTabName(nConnHandle,2)

  will place the name of the second table used in a query into the character string sTableName.

• SqlColBrowse(nConnHandle,iColNum), which returns the value SUCCEED(1) if the iColNum column in the query results set can be updated using DBLIB browse mode procedures, or FAIL (0), if not. The first column in the DBLIB results set is column 1 (vs. 0).
• SqlTabSource(nConnHandle,iColNum,iTabNum), which returns the name from which the result set column iColNum was derived and places the table number in the iTabNum parameter. Therefore, after executing the statement

  sTableName = SqlTabSource(nConnHandle,4,iTabNum)

  the variable sTableName will contain the name of the table from which the fourth column in the query was derived, and the value of iTabNum will contain its table number.

• SqlColSource(nConnHandle,iColNum), which returns the name of the table column from which the iColNum column in the query results set was derived.
• SqlQual(nConnHandle,iTabNum,sTabName), which returns a character string WHERE clause that can be used in an UPDATE or DELETE statement to change or remove the current row of query results from the database table identified either by number as iTabNum or by name as sTabName.

Preparing the DBLIB to Perform a Browse Mode UPDATE or DELETE

Before calling any of the DBLIB browse mode functions, an application must open a connection with an MS-SQL Server and send a SELECT statement that includes a FOR BROWSE clause to the DBMS. As you learned in Tip 418, "Understanding the MS-SQL Server FOR BROWSE Clause in a SELECT Statement," only tables that have both a UNIQUE INDEX and a TIMESTAMP column may appear in the FROM clause of a SELECT statement with a FOR BROWSE clause. Therefore, the DBLIB browse mode functions can be used only on tables with both a UNIQUE INDEX and a TIMESTAMP column.

Because the DBLIB browse mode UPDATE or DELETE is actually a searched UPDATE or DELETE, the DBLIB must have a way of identifying the particular row in the table that corresponds with the current row in its buffer. The UNIQUE INDEX ensures that the DBLIB can find the row in the table from which each of the rows in its query results buffer was derived. The table's TIMESTAMP column provides a way of determining whether the underlying row was updated since its contents were copied into the DBLIB's buffer.

When executing a DBLIB browse mode UPDATE or DELETE, you will need two connections to the MS-SQL Server. The application will use one connection to send the SELECT statement (with the BROWSE MODE clause) to the server and then to retrieve the query results set into the DBLIB buffer. Then the program uses the second connection to send UPDATE or DELETE statements to the server.

Tip 423, "Executing a DBLIB Browse Mode DELETE," will show you a sample of code that you can use to execute a DBLIB browse mode DELETE; Tip 424, "Executing a DBLIB Browse Mode UPDATE," goes through the process of executing a DBLIB browse mode UPDATE. The important things to understand now are that target tables for DBLIB browse mode functions must have both a column of data type TIMESTAMP and a UNIQUE INDEX. Moreover, the application must open two connections with the same MS-SQL Server—one to send and retrieve query results, and the other to use when sending UPDATE or DELETE statements to the server.

Using the SqlQual() Function to Generate the WHERE Clause for a DBLIB Browse Mode UPDATE or DELETE

As mentioned in Tip 421, "Preparing the DBLIB to Perform a Browse Mode UPDATE or DELETE," the DBLIB can simulate positioned UPDATE and DELETE statements only because neither the DBLIB nor the DBMS really knows which row in the database table corresponds with the current row in the DBLIB buffer. The DBLIB can, however, call the SqlQual() function to generate a WHERE clause that you can use in an UPDATE or DELETE statement to perform a searched update that will find the DBLIB's current row in the database table from which it was derived.

The syntax of the SqlQual() function is

sWhereClause = SqlQual(nConnHandle,iTabNum,sTabName)

where:

• sWhereClause (String) is the WHERE clause returned by the SqlQual() function. The WHERE clause will include the column name and value for both the UNIQUE INDEX column and the TIMESTAMP column in the table numbered iTabNum or named sTabName that corresponds to the current row in the DBLIB.
• nConnHandle (Long) is the connection handle (returned by the SqlOpenConnection() or SqlOpen() functions) that was used by SqlSendCmd(), SqlSend() or SqlExec() to send the query (or queries) to the MS-SQL Server for execution.
• iTabNum (Integer) is the number of the table in the SELECT statement's FROM clause that is to be the target of the browse mode UPDATE or DELETE. (Tables are numbered starting with 1 as the first table from the left in the FROM clause.) If iTabNum is -1, then the SqlQual() function will use the string in sTabName to identify the table.
• sTabName (String) is the name of the table to be updated by a DBLIB browse mode UPDATE or DELETE. If sTabName is an empty string, then the SqlQual() function will use the value of iTabNum to identify the table.

Therefore, if you call the SqlGetRow() function as

SqlGetRow(nQueryConnHandle,5)

to select the fifth row of results returned by the query as the DBLIB's current row and then you call SqlQual() function as

sWhereClause = SqlQual(nQueryConnHandle,-1,"employees422")

the (String) variable sWhereClause will contain a WHERE clause similar to:

where (emp_ID=5) and tsequal(tstamp,0x00000000000006fc)

You can then add this WHERE clause to a DELETE statement (as you will learn to do in Tip 423, "Executing a DBLIB Browse Mode DELETE") or an UPDATE statement (which you will learn about in Tip 424, "Executing a DBLIB Browse Mode UPDATE") to DELETE or UPDATE the DBLIB's current row in the database.

Review the code in the ShowEmployee() routine in Form1.frm of Project422 on the book's Web site at www.premierpressbooks.com/downloads.asp. When called, the routine uses a Windows message box similar to that shown in Figure 422.1 to display the WHERE clause that the DBMS can use to find the underlying table row from which the current row in the DBLIB buffer was derived.

Figure 422.1: Windows message box with a WHERE clause returned by an SqlQual() function call

Executing a DBLIB Browse Mode DELETE

Before attempting a DBLIB browse mode DELETE, bear in mind that the table from which the current DBLIB buffer row is to be deleted must have both a column of data type TIME-STAMP and a UNIQUE INDEX. After selecting an eligible table, open two connections to the MS-SQL Server—one through which to send a query to the DBMS (and retrieve its results set) and the second for use in sending a browse mode DELETE statement. For example, the Visual Basic (VB) code

Dim sDelStmt As String Dim nRetCode As Long Dim nQueryConnHandle As Long Dim nDelConnHandle As Long nQueryConnHandle=SqlOpenConnection("NVBizNet2","konrad",_ "king","ws-query",App.EXEName) nDelConnHandle=SqlOpenConnection("NVBizNet2","konrad",_ "king","ws-delete",App.EXEName)

will establish two connections with the MS-SQL Server NVBizNet2.

Next, select the database that contains the table with the row you want to DELETE. Then use the query connection handle to send a SELECT statement with a FOR BROWSE clause to the DBMS:

nRetCode = SqlCmd(nQueryCortnHandle, " USE SQLTips") nRetCode = SqlCmd(nQueryConnHandle, " SELECT * FROM employees423 FOR BROWSE") nRetCode = SqlSend (nQueryConnHandle)

After the DBMS executes the FOR BROWSE SELECT statement, retrieve the query's results set by executing VB code similar to:

nRetCode = SqlOk(nQueryConnHandle) nRetCode = SqlResults(nQueryConnHandle) 'Get USE results nRetCode = SqlResults(nQueryConnHandle) 'Get SELECT results nRetCode = SqlSetOpt(nQueryConnHandle, SQLBUFFER, 100) Do Until NOMOREROWS = SqlNextRow(nQueryConnHandle) Loop

Now, suppose that you want to DELETE the EMPLOYEES423 table row that corresponds with the 10th row in the query results set. First, use the SqlGetRow() function to select the query's 10th row of results as the DBLIB's current row:

nRetCode = SqlGetRow(nQueryConnHandle,10)

Then call the SqlQual() function to create the WHERE clause that a searched DELETE can use to find and remove the row from the EMPLOYEES423 table:

sDelStmt = "DELETE FROM employees423 " & _ SqlQua1(nQueryConnHandle,-1,"employees423")

Finally, use the DELETE connection handle in nDelConnHandle to send the DELETE statement to the MS-SQL Server for execution:

nRetCode = SqlSendCmd(nDelConnHandle,sDelStmt)

Review the source code for Project423.vbp on the book's Web site at www.premierpressbooks.com/downloads.asp. The VB program first queries the employees data in the EMPLOYEES423 table and then lets you DELETE individual employees from the table by clicking on one of the five Del Row buttons.

Executing a DBLIB Browse Mode UPDATE

As is the case with a DBLIB browse mode DELETE, you can perform a DBLIB browse mode UPDATE only on a table that has both a column of data type TIMESTAMP and a UNIQUE INDEX. To perform a DBLIB browse mode UPDATE, first open two connections to the MS-SQL Server—one through which to send a query and the second to use when sending an UPDATE statement. For example, the Visual Basic (VB) code

Dim sUpdtStmt As String Dim nRetCode As Long Dim nQueryConnHandle As Long Dim nUpdtConnHandle As Long nQueryConnHandle=SqlOpenConnection("NVBizNet2","konrad",_ "king","ws-query",App.EXEName) nUpdtConnHandle=SqlOpenConnection("NVBizNet2","konrad",_ "king","ws-update",App.EXEName)

will establish two connections with the MS-SQL Server NVBizNet2.

Next, select the database with the table that has the row you want to UPDATE. Then use the connection with the query connection handle to send a SELECT statement with a FOR BROWSE clause to the DBMS:

nRetCode = SqlCmd(nQueryConnHandle," USE SQLTips") nRetCode = SqlCmd(nQueryConnHandle, " SELECT * FROM employees424 FOR BROWSE") nRetCode = SqlSend(nQueryConnHandle)

After sending the FOR BROWSE SELECT statement to the DBMS for execution, retrieve the query's results set by executing the VB code similar to:

nRetCode = SqlOk(nQueryConnHandle) nRetCode = SqlResults(nQueryConnHandle) 'Get USE results nRetCode = SqlResults(nQueryConnHandle) 'Get SELECT results nRetCode = SqlSetOpt(nQueryConnHandle, SQLBUFFER, 100) Do Until NOMOREROWS = SqlNextRow(nQueryConnHandle) Loop

Now suppose that you want to UPDATE the EMPLOYEES423 table row that corresponds with the seventh row in the query results buffer. First, use the SqlGetRow() function to select the query's seventh row of results as the current row in the DBLIB buffer:

nRetCode = SqlGetRow(nQueryConnHandle,7)

Then call the SqlQual() function to create the WHERE clause that a searched UPDATE can use to find the underlying row (in the EMPLOYEES423 table) from which the current row in the DBLIB was derived. For example, to set the POSITION column in the underlying row that corresponds with the DBLIB's current row to NULL, execute VB statements similar to:

sUpdtStmt = "UPDATE employees423 SET position = NULL " & _ SqlQual(nQueryConnHandle,-1,"employees423") nRetCode = SqlSendCmd(nUpdtConnHandle,sUpdtStmt)

Review the source code for Project424.vbp on the book's companion Web site at www.premierpressbooks.com/downloads.asp. The VB program first queries the employees data in the EMPLOYEES424 table and then lets you UPDATE individual rows in the table by transferring the content of the fields on the input form to columns in a table row when you click your mouse pointer on one of the five Update Row buttons.

Performing Dynamic SQL Queries Using the DBLIB API

Although the SELECT statements used in the projects for Tips 417–424 are "hard-coded" in each of the example Visual Basic (VB) programs, the DBLIB functions also let you write applications that let users formulate queries at runtime. This dynamic SQL query capability works especially well with the MSFlexGrid, which lets you define its column names and set its column and row counts on the fly.

For example, if you design a VB form with text field (named Text1) similar to that shown in Figure 425.1, you can call the SqlSendCmd() functions as

Figure 425.1: Visual Basic form with a text field to accept a user's dynamic SQL statement during runtime

nRetCode = SqlSendCmd(nQueryConnHandle,Forml.textl.Text)

to place the contents of the Textl field into the nQueryConnHandle command buffer and send the buffer's contents to the DBMS for execution.

Assuming that the Text1 field on Form1 contains a valid SELECT statement that queries a table to which the VB program's connection has SELECT access, the VB code

Dim i As Integer FlexGrid.Cols = SqlNumCols(nQueryConnHandle) FlexGrid.Rows = 1 FlexGrid.Row = 0 FlexGrid.Col = 0 For i = 1 To FlexGrid.Cols FlexGrid.Text = SqlColName(nQueryConnHandle,i) If FlexGrid.Col < FlexGrid.Cols - 1 Then FlexGrid.Col = FlexGrid.Col + 1 End If Next i

will retrieve the count of columns and their names from the query's results table.

If you then pass the MSFlexGrid with the column names and column count set by the preceding VB code along with the query connection handle to the VB routine

Sub GetResultsSet (nConnHandle As Long, _ FlexGrid As MSFlexGrid) Dim iCol As Integer Dim nRetCode As Long Do Until NOMOREROWS = SqlNextRow(nConnHandle) FlexGrid.Col = 0 FlexGrid.Rows = FlexGrid.Rows + 1 FlexGrid.Row = FlexGrid.Rows - 1 For iCol = 1 To FlexGrid.Cols If SqlDatLen(nConnHandle, iCol) <> 0 Then FlexGrid.Text = SqlData(nConnHandle, iCol) Else FlexGrid.Text = "** NULL **" End If If FlexGrid.Col < (FlexGrid.Cols - 1) Then FlexGrid.Col = FlexGrid.Col + 1 End If Next iCol Loop End Sub

you have a VB application that will allow a user to query any table to which the VB application's connection has the proper access. Moreover, the program's MSFlexGrid object will display the query results with the correct column names, even though the columns selected, number of columns, and number of rows may change with each new dynamic SQL query that the user enters into the Textl field and sends to the MS-SQL Server for execution.

Review Project425.frm and Project425.bas on the book's companion Web site at www.premierpressbooks.com/downloads.asp for a complete listing of a VB program that lets the user write, submit, and display the results from dynamic SQL queries.