User's Guide

Part V. The SQL Anywhere Programming Interfaces

Chapter 38. The WSQL HLI Interface

Visual Basic 3.0 introduced ODBC support. By using ODBC, you can develop a Visual Basic application that uses SQL Anywhere that can easily use another ODBC data source sometime in the future. WSQL HLI is still a good alternative when using ODBC does not make sense for your application.

It is very easy to use DLL's from the Visual Basic environment. All that is necessary is for the functions to be declared in the global code area or a general-declarations area. Since Visual Basic does not support callback functions, the wsqlregisterfuncs function cannot be used. Host variables are not used in Visual Basic.

To declare WSQL HLI functions, put the following statements in your project's global code area

     Declare Function wsqlexec Lib "wsqlcall.dll" (ByVal cmd$) As Long

     Declare Function wsqlgetfield Lib "wsqlcall.dll" (ByVal cur$, ByVal i As Integer, ind As Integer, ByVal buf$, ByVal l As Integer) As Long

     Declare Function wsqlgetcolumnname Lib "wsqlcall.dll" (ByVal cur$, ByVal i As Integer, ind As Integer, ByVal buf$, ByVal l As Integer) As Long

     Declare Function wsqllasterror Lib "wsqlcall.dll" (ByVal buf$, ByVal i As Integer) As Long

     Declare Function wsqlquerytomem Lib "wsqlcall.dll" (ByVal buf$, ByVal cur$, ByVal dta As Integer, ByVal names As Integer) As Long

These declarations can also be found in the Test Application's global area. The filename is WSQLDLLT.GLB.

It is important that all string buffers that are to be filled in by WSQL HLI be fixed-length strings. WSQL HLI will not work properly with variable-length strings. Also, all returned character string buffers will be terminated with a null (zero) character.

Visual Basic example

The following code fragments are part of a Visual Basic application that uses WSQL HLI.

In the global module or form, declare the DLL or QuickLibrary functions which are going to be used. Now the five functions can be used in other subroutines, such as the following code fragment:

     ret = wsqlexec("START USING DATABASE sademo AS Con1 USER dba IDENTIFIED BY sql")
     ret = wsqlexec("SET CONNECTION Con1")
         ' We are now connected to the
         ' sample database engine
     ret = wsqlexec("PREPARE stmt1 FROM 'SELECT * FROM department' ")
         ' The single quotes are optional.
     ret = wsqlexec("DECLARE cur1 CURSOR FOR stmt1")
         ' The cursor name 'cur1' and the statement name     'stmt1' are now
         ' reserved.
     ret = wsqlexec("OPEN cur1")
         ' The cursor is now open and positioned before the     ' first row of the query.
     
     ret = wsqlexec( "CLIP BOTH cur1" )
     
     MyTextBox.Text = ClipBoard.GetText( )
         ' MyTextBox now contains the query results.
         ' cur1 is positioned just after the last
         ' row of the query.
     
     ' ---------------------------------------------
     Dim ret As Long
     Dim ind%
     Dim buf As String * 100
     
     ret = wsqlexec("FETCH ABSOLUTE 1 cur1")
         ' cur1 is now positioned on the
         ' first row of the query.
         '
         ' buf is a 100 byte string, ind% is an integer:
     
     ret = wsqlgetfield("cur1", 1, ind%, buf, 100)
         ' translation - put the first 100 characters of the
         ' second column of the current row of cursor "cur1"     into
         ' the string buf, and if there are more than 100
         ' characters, put how many characters
         ' there are in     ind%.
         ' buf now contains "R & D", ind contains zero.
     
     ret = wsqlgetcolumnname("cur1", 0, ind%, buf, 100)
         ' translation - put the first
         ' 100 characters of the name
         ' of the first column of the result
         ' set of cursor "cur1"
         ' into the string buf$, and if there
         are more than 100 characters, put how many
         ' characters there are in ind%.
         ' buf$ now contains "dept_id", ind% contains zero.
     
     ret = wsqlexec("CLOSE cur1")
     ret = wsqlexec("DROP STATEMENT stmt1")
         ' the cursor and statement are no longer valid.
         '
         ' two ways of executing a statement:
         ' method one:
     ret = wsqlexec("EXECUTE IMMEDIATE INSERT INTO department VALUES ('220','Eastern Sales', 502)")
     
         ' method two:
     ret = wsqlexec("PREPARE stmt2 FROM INSERT INTO department VALUES ('230','Western Sales', 502)")
     ret = wsqlexec("EXECUTE stmt2")
     ret = wsqlexec("DROP stmt2")
     
     ' handling errors:
     ret = wsqlexec("EXECUTE IMMEDIATE DROP TABLE BogusTable ")
         ' There is no such table - ret is set to -141.
         ' Get more information:
     ret = wsqllasterror(buf, 100)
         ' buf now contains "table 'BogusTable' not found",
         ' a more meaningful error message
     
     ret = wsqlexec("STOP USING DATABASE Con1")
         ' database connection is terminated.

The sample application

There is a Visual Basic sample application (both source and executable) included with WSQL HLI that demonstrates some features of WSQL HLI. The filename is WSQLDLLT.EXE, and you can take a look at the source by loading the project WSQLDLLT.MAK from Visual Basic. (Visual Basic is not included with the SQL Anywhere software.)

This example is found in the ACCXMP\VB subdirectory of the SQL Anywhere installation directory (usually C:\SQLANY50).

Running the sample application

The sample application is a simple display utility which shows the contents of the employee table, one record at a time. Start the sample database and the test application. Once the database engine and the test application have started, press the Connect button. After a moment, a slider bar will appear, the Connect button will have become the Disconnect button, and the text boxes will be displaying the first record in the employee table.

Use the slider bar to examine any of the one hundred entries in the employee table. When you are done, press the Disconnect button and close the application.