INF: Building Apps with Visual Basic Library for SQL Server

ID: Q80635

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SUMMARY

This article provides the basic information necessary to create a simple SQL Server front-end using the Visual Basic Library for SQL Server (VBSQL).

MORE INFORMATION

While considerably more complex applications can be developed using VBSQL, the following steps provide the necessary framework to make a connection with a SQL Server, execute Transact-SQL statements, and process results:

Run Visual Basic and choose New Project from the File menu.

Add the VBSQL.VBX custom control to the project:
1. From the File menu, choose Add.
2. Select VBSQL.VBX from the \SQL\BIN directory.
3. Once the file has been added to the project, the VBSQL control (an icon resembling a stop sign) should appear on the tool bar.

Add the VBSQL include file containing all the necessary function and routine declarations to the project:
1. From the Window menu, choose Project Window.
2. Select the GLOBAL.BAS module.
3. From the Code menu, choose Load Text.
4. Select the VBSQL.BI file from the \SQL\DBLIB\INCLUDE directory.

Declare a global integer variable for the connection to SQL Server by adding the following line of code to the beginning of the GLOBAL.BAS module:
```
       Global SQLConn%
  
```
NOTE: The handle to the connection is an integer value. While SQLConn% is used in this example, the name of the handle is up to the discretion of the programmer. In addition, while not used in this example, multiple connections could be established by first declaring additional handles (SQLConn2%, SQLConn3%, and so on).

Add the VBSQL error/message handler control to the project:
1. From the Window menu, choose Project Window.
2. Select Form1.
3. Choose the VBSQL Control ("stop sign" icon) from the ToolBox and place it on Form1.
  
  This control must be added to the form in the project prior to calling any VBSQL functions and/or routines. If it is not, unexpected errors may occur when an attempt is made to run the program. In addition, the control must be placed on a form that is always loaded into memory.

Add user-defined code to the error/message handler control by clicking the VBSQL control on Form1 and adding the following code to the error and message procedures:


       Sub SQL_Error (SqlConn As Integer, Severity As
                      Integer, ErrorNum As Integer, ErrorStr
                      As String, RetCode As Integer)

           MsgBox "Error #:  " + Str$(ErrorNum) + Chr$(13) +
                  Chr$(10) + ErrorStr

       End Sub

       Sub SQL_Message (SqlConn As Integer, Message As Long,
                        State As Integer, Severity As
                        Integer, MsgStr As String)

           MsgBox "Message #:  " + Str$(Message) + Chr$(13)
                  + Chr$(10) + MsgStr

       End Sub

These sample error and message handlers represent only the minimum amount of code necessary to identify both DB-LIBRARY (db-lib) and SQL Server error messages. In practice, you may want to enhance the handlers to provide your applications with more robust error-handling routines.

Initialize VBSQL by clicking Form1 and adding the following code to the Form Load code window:
```
       Message$ = SqlInit()
       If Message$ = "" Then
           Beep
           MsgBox "SqlInit Error"
           End
       Else
           MsgBox Message$
       End If
 
```
When the program is run, SqlInit() initializes the user-defined error and message handlers. In addition, it returns a string containing the version number of the DB-LIBRARY dynamic-link library (DLL) that is being used (W3DBLIB.DLL). If an empty string is returned, do not attempt to call any other VBSQL functions and/or routines and verify that both the W3DBLIB.DLL and DBNMP3.DLL files are located in the MS-DOS path. If a version string is returned, it must show that DB-LIBRARY 1.16 or later is being loaded. If not, unexpected results may occur when the program is run.

Add the necessary code to establish a connection with SQL Server:

Click Form1 and add the following code to the Form Load code window:


          Server$ = "servername"
          LoginID$ = "loginID"
          Password$ = "password"
          WorkSta$ = "user"
          AppName$ = "simple"

          SqlConn% = SqlOpenConnection(Server$, LoginID$,
                     Password$, WorkSta$, AppName$)

          If SqlConn% = FAIL Then
              MsgBox "Login failed"
              End
          End If

If you choose, you can use the following code to connect to SQL Server in place of the preceding code:

    
          Login% = SqlLogin%()
          Results% = SqlSetLUser%(Login%, LoginID$)
          Results% = SqlSetLPwd%(Login%, Password$)
          Results% = SqlSetLApp%(Login%, AppName$)
          Rem  Un-remark the following line if you want to use
          Rem  the BCP APIs:
          Rem  Results% = SqlBCPSetL%(Login%, 1)
          SqlConn% = SqlOpen%(Login%, Server$)

          If SqlConn% = FAIL Then
              MsgBox "Logon failed."
              End
          End If

Note that the first method is generally preferred because it is the simplest. However, if you want to use the BCP APIs for bulk copying data into or out of SQL Server, you must use the second method.

Add code to the form to select the desired database on SQL Server by clicking Form1 and adding the following code to the Form Load code window:
```
       Results% = SqlUse(SqlConn%, "pubs")
   
```
This line of code will change the database context from the master database to the pubs sample database.

Add code to the form to execute Transact-SQL statements by clicking Form1 and adding the following code to the Form Load code window:


       Cmd$ = "select au_id, au_lname, au_fname from
               authors"
       MsgBox Cmd$
       Results% = SqlCmd(SqlConn%, Cmd$)
       Results% = SqlExec(SqlConn%)
       Do While (SqlResults%(SqlConn%) <> NOMORERESULTS)
           Do While (SqlNextRow(SqlConn%) <> NOMOREROWS)
               ID$ =  Sqldata(SqlConn%, 1)
               LastName$ = Sqldata(SqlConn%,2)
               FirstName$ = Sqldata(SqlConn%, 3)
               Print ID$ + ":  " + LastName$ + ", " +
                     FirstName$ + Chr$(13) + Chr$(10)
           Loop
       Loop

This query returns rows from the authors table in the pubs sample database. Other Transact-SQL queries could be executed by modifying the Transact-SQL statement in Cmd$ and adding additional Sqldata() statements as necessary.

Add code to the form to close the connection to SQL Server by clicking Form1 and adding the following code to the Form Load code window:
```
       SqlClose(SqlConn%)
   
```
This function must be called selectively for each SQL Server connection opened with SQL Server. If not, sleeping processes may be left on the server. For more information, query on the following words in the Microsoft Knowledge Base:
```
       visual and basic and sleeping and processes
  
```

An alternative method involves calling SqlExit. This procedure may be desirable when working with multiple connections because it effectively closes all open connections.

Uninitialize VBSQL prior to exiting the program by clicking Form1 and adding the following code to the Form Unload code window:
```
 
       SqlWinExit
       End
  
```
NOTE: If SqlWinExit is not called before exiting, you will receive the following error message the next time the application is run:

Error 10001: NULL DBPROCESS pointer encountered

Once the above steps have been completed, the sample program can be run in interpretive mode under Visual Basic or compiled to an executable file run directly from Windows.

Additional query words: dblib

Keywords : kbinterop SSrvProg SSrvVisB
Version : 4.2
Platform : OS/2
Issue type :