Overview of the sample Visual Basic application

The sample Visual Basic application shows you how to access DB2 Everyplace data using Visual Basic. You can develop applications that have the same application logic and user interface on both Pocket PC (WinCE) and Win32 operating systems. Two Visual Basic sample applications are provided with DB2 Everyplace. One is for the Pocket PC (WinCE) operating system and the other is for Win32 operating systems. The application logic and user interface for both these sample applications are the same. The file db2evb.bas, which contains the application logic, is common between the two operating systems. See *** for more details.

Files included in the sample application

The Visual Basic project directory, which contains the sample application, is located under the directory where you installed DB2 Everyplace. For Pocket PC (WinCE), you can find the files in \db2everyplace\clients\wince\database\visualbasic. For Win32 operating systems, you can find the files in \db2everyplace\clients\win32\database\visualbasic.

The sample Visual Basic application includes the following files:

db2evb.bas
The db2evb.bas file contains the sample Visual Basic application. You can use the sample application to help you write your own Visual Basic application.

db2ecli.bas
The db2ecli.bas file is the Visual Basic interface that connects to the DB2 Everyplace database. It also defines various DB2 Everyplace constraints that are found in sqlcli.h, sqlcli1.h, sqlext.h, and sqlsystm.h. Only the most commonly used constraints are in this file. You can add other constraints from sqlcli.h, sqlcli1.h, sqlext.h, and sqlsystm.h if you need to.

DB2eForms (extensions vary depending on the operating system)
Application user Interface file.

DB2eSample.exe (For WinCE, DB2eSample.vb)
Application executable file.

DB2eSample.vbw
Application project file.

DB2eSample.vbp ( For WinCE, DB2eSample.ebp )
Application project file.

Visual Basic example: db2evb.bas

The major steps used in the sample application (db2evb.bas) are:

Connect to the DB2 Everyplace database.

Access DB2 Everyplace data.

Terminate the application application.

Uwaga:
Make sure that the application closes the connection to the DB2 Everyplace database before exiting.

Comments have been added to this example to illustrate the sample application steps. 

Option Explicit
 
Public henv    As Long          ' Environment handle
Public hdbc    As Long          ' Database handle
Public hstmt   As Long          ' Statement handle
Public rc      As Integer       ' Return code
 
Public dbpath As String        ' filesystem path where DB2e will create tables.
Public userid As String        ' Userid: not used by DB2 Everyplace.
Public pass   As String        ' Password: not used by DB2 Everyplace
 
'--------------------------------------------------------------------------------
' Function: DB2eTest
'
' Description: Function illustrating how calls to DB2 Everyplace can be made.
'
'--------------------------------------------------------------------------------
 
Public Function DB2eTest() As Integer
    Dim errmsg As String
    Dim numCols As Integer
    Dim i As Integer
    Dim retLen As Long
    Dim data As String
    Dim crtStmt As String
    Dim insStmt1 As String
    Dim insStmt2 As String
    Dim selStmt As String
    On Error Resume Next 'Important: don't ask me why, but this line is needed
                          'in every function that calls functions from db2e.dll
                          'otherwise visual basic does strange mysterious things.  
    '
    dbpath = ""
    userid = ""
    pass = ""
    '
    crtStmt = "CREATE TABLE x(a INT, b TIMESTAMP)"
    insStmt1 = "INSERT INTO x VALUES(1, CURRENT TIMESTAMP)"
    insStmt2 = "INSERT INTO x VALUES(2, CURRENT TIMESTAMP)"
    selStmt = "SELECT * FROM x"
    '
    data = String(80, " ")
    ' Step 1: allocate an environment handle.
    '
    
    DB2eForm.DB2eText.Text = vbCrLf & vbCrLf & " Allocating an environment handle" 
  
    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, henv)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
   
    '
    ' Step 2: allocate database handle
    '
    
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & 
                             " Allocating a database handle"
    
    rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
                
    '
    ' Step 3: connect to the database
    '
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & 
                             "  Connecting to the database"
    
    rc = SQLConnect(hdbc, dbpath, SQL_NTS, userid, SQL_NTS, pass, SQL_NTS)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
    
    '
    ' Step 4: allocate a statment handle.
    '
    
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & 
                             "  Allocating a statement handle"
    
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, hstmt)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
 
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf
    
    '
    ' Now we can use CLI function calls to execute SQL statements.
    '
    ' Step 5: Create a table
    '
    
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & "  " & crtStmt
    rc = SQLExecDirect(hstmt, crtStmt, SQL_NTS)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
    
    '
    ' Create the same table again to force an error message and
    ' see if DB2eError works.
    '
    'rc = SQLExecDirect(hstmt, "create table p(a int)", SQL_NTS)
    'If (rc <> 0) Then
    '   testmsg = MsgBox("BLA1", 1, "DB2 Everyplace Visual Basic")
    '   rc = DB2eError()
    '   testmsg = MsgBox("BLA2", 1, "DB2 Everyplace Visual Basic")
    '   rc = DB2eTerminate()
    '   testmsg = MsgBox("BLA3", 1, "DB2 Everyplace Visual Basic")
    '   Exit Function
    'End If
    '
    
    '
    ' Step 6: Insert data into the table.
    '
    
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & "  " & insStmt1
    rc = SQLExecDirect(hstmt, insStmt1, SQL_NTS)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
 
    End If
 
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & "  " & insStmt2
    rc = SQLExecDirect(hstmt, insStmt2, SQL_NTS)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
 
    End If
 
 DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf
 
    '
    ' Step 7: Retrieve data from table.
    '
    
    DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf & "  " & selStmt 
                            & vbCrLf
    
    rc = SQLExecDirect(hstmt, selStmt, SQL_NTS)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
    
    
    rc = SQLNumResultCols(hstmt, numCols)
    If (rc <> 0) Then
        rc = DB2eError()
        rc = DB2eTerminate()
        Exit Function
    End If
    
    Do While (SQLFetch(hstmt) = SQL_SUCCESS)
        For i = 1 To numCols
            rc = SQLGetData(hstmt, i, SQL_C_CHAR, data, 80, retLen)
            DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & "  " & data & vbCrLf
            If (rc <> 0) Then
                rc = DB2eError()
                rc = DB2eTerminate()
                Exit Function
            End If
        Next
        data = String(80, " ")
        DB2eForm.DB2eText.Text = DB2eForm.DB2eText.Text & vbCrLf
    Loop
    
    '
    ' Step 8: Close connection to DB2e database before application terminates.
    '
    rc = DB2eTerminate()
    
    DB2eTest = 0
End Function

Zadania pokrewne