Microsoft KB Archive/80635

{|
 * width="100%"|

-

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:

 From the File menu, choose Add. Select VBSQL.VBX from the \SQL\BIN directory. 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:

 From the Window menu, choose Project Window.</li> Select the GLOBAL.BAS module.</li> From the Code menu, choose Load Text.</li> Select the VBSQL.BI file from the \SQL\DBLIB\INCLUDE directory.</li></ol> </li>  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:

<pre class="CODESAMP">      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). </li> Add the VBSQL error/message handler control to the project:

 From the Window menu, choose Project Window.</li> Select Form1.</li> Choose the VBSQL Control (&quot;stop sign&quot; 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.</li></ol> </li>  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:

<pre class="CODESAMP">      Sub SQL_Error (SqlConn As Integer, Severity As                      Integer, ErrorNum As Integer, ErrorStr                      As String, RetCode As Integer)

MsgBox &quot;Error #: &quot; + 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 &quot;Message #: &quot; + 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. </li>  Initialize VBSQL by clicking Form1 and adding the following code to the Form Load code window:

<pre class="CODESAMP">      Message$ = SqlInit If Message$ = &quot;&quot; Then Beep MsgBox &quot;SqlInit Error&quot; 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. </li> Add the necessary code to establish a connection with SQL Server:

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

<pre class="CODESAMP">         Server$ = &quot;servername&quot; LoginID$ = &quot;loginID&quot; Password$ = &quot;password&quot; WorkSta$ = &quot;user&quot; AppName$ = &quot;simple&quot;

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

If SqlConn% = FAIL Then MsgBox &quot;Login failed&quot; End End If   </li>  If you choose, you can use the following code to connect to SQL Server in place of the preceding code:

<pre class="CODESAMP"> 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 &quot;Logon failed.&quot; End End If      </li></ol>

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.</li>  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:

<pre class="CODESAMP">      Results% = SqlUse(SqlConn%, &quot;pubs&quot;) This line of code will change the database context from the master database to the pubs sample database. </li> <li> Add code to the form to execute Transact-SQL statements by clicking Form1 and adding the following code to the Form Load code window:

<pre class="CODESAMP">      Cmd$ = &quot;select au_id, au_lname, au_fname from authors&quot; 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$ + &quot;: &quot; + LastName$ + &quot;, &quot; + 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. </li> <li> 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:

<pre class="CODESAMP">      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:

<pre class="FIXEDTEXT">      visual and basic and sleeping and processes </li></ol>

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

<ol> <li> Uninitialize VBSQL prior to exiting the program by clicking Form1 and adding the following code to the Form Unload code window:

<pre class="CODESAMP"> 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

</li></ol>

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 :
 * }