|
Microsoft Visual Basic has become the most popular development tool for Windows applications. Its easy to use integrated IDE, ANSI standard programming language, and extensive third party product support have contributed largely to its success.
While Visual Basic lacks many of the object-oriented features of its competitors, the simplicity of the programming language and the development environment make it a frequent choice for rapid prototyping and client/server development. Third-party
custom controls, many of which would take months to develop, can be added to a project instantly. Visual Basic provides connectivity to any ODBC data source, as well as support for other common Windows services, including OLE and DDE.
Among the most comprehensive Third-party applications which can quickly jump start your project is Rapid Application Foundation (RAF) from Advanced Information Systems. RAF is a structured approach to Rapid Application Development. RAF leverages several
of the industries best development products. These products coupled with RAF, provide a robust development platform while preserving ease of programming. The RAF foundation offers a development path well suited for Rapid Application Development, while
preserving ease of maintenance.
RAF includes a set of source code libraries consisting of pre-developed code and pre-coded form templates. The RAF Libraries simplify the effort of building any application while providing robust features only found in professional software
applications.
RAF enables applications to be prototyped in minutes instead of weeks. The benefit begins as the RAF prototype is optimized with pre-developed foundation libraries, enabling development of world class client/server applications in record time. Built-in
functionality enables your prototype to immediately connect to all ODBC compliant databases. Connections may be made directly through database procedures, ODBC, or other pre-coded techniques. Inter-form communication allows an unlimited number of screens,
pop-up windows, notes, combo list boxes, charts, graphs, and spreadsheets to efficiently communicate between applications automatically.
The RAF approach delivers rapid applications for the Windows environment, simplifying window creation through the use of pre-coded form templates. RAF provides a framework that unifies application coders into a maintainable programming model. RAF
greatly minimizes the complexity of code maintenance, and learning to develop under the Windows environment.
Advanced Information Systems Inc. provides on-site skills transfer courses. These courses offer classroom sessions, as well as project development. AIS Trainers customize the Lab Exercises to directly build your in-house project applications. AIS can be
reached at (800)327-9725.
The majority of this appendix focuses on Visual Basic's ODBC interface, which has relatively few features but is extremely easy to use. In addition, the final section includes a brief summary of the Visual Basic environment and language structure, as
well as its strengths and weaknesses.
No additional configuration is required to connect to an ODBC data source from Visual Basic. Any drivers and data sources installed and configured using the ODBC administration program are accessible. The Visual Basic Database object is used to
establish connections through its OpenDatabase method. The OpenDatabase method takes four arguments, three of which apply only to local desktop databases:
- database name an empty string for ODBC data sources
- open exclusive Boolean, always set to False for ODBC data sources
- read only Boolean, always set to False for ODBC data sources
- connect string the ODBC connect string, includes "ODBC;", the data source name, user ID, password, and any additional database specific parameters.
To connect to Oracle, the connect string requires no special parameters. The code fragment in Listing C.1 uses values from a generic ODBC login form to build a connect string:
Listing C.1. The Database object is used to connect to the data source.
Dim dbOracle As Database
Dim szConnect As String
szConnect = "ODBC;DSN=" & lstDSNs.Text & ";"
szConnect = szConnect & "UID=" & txtID.Text & ";"
szConnect = szConnect & "PWD=" & txtPassword.Text & ";"
Set dbOracle = OpenDatabase("", False, False, szConnect)
The connect string constructed in Listing C.1 might look like:
"ODBC;DSN=ORACLE;UID=scotty;PWD=tiger;"
The methods of the Database object are used to retrieve results, apply SQL transactions, and call stored procedures. In most cases, the application should declare one Database object globally.
The primary means of retrieving results from an ODBC data source is the Snapshot object. The Snapshot object is created using the CreateSnapshot method of the Database object. The CreateSnapshot method takes two arguments: a SQL SELECT statement, and a
numeric constant used to control processing of the SQL. Unless the application needs to be portable to different RDBMSs, this numeric constant should be set to DB_SQLPASSTHROUGH, (64), which sends the statement directly to the server for processing. This
mode allows the developer to use the native syntax of the RDBMS, and prevents the local Microsoft Access engine from attempting to parse and process the SQL. The following code fragment provides a simple example of the use of the CreateSnapshot method:
Dim dsContacts As Snapshot
Set dsContacts = dbOracle.CreateSnapshot("SELECT a.last_name, a.first_name, b.phone_nbr FROM individual a, phone b WHERE a.ID = b.IndividualID(+) ORDER BY 1, 2",
DB_SQLPASSTHROUGH)
The example assumes that the Database object has already connected to the data source. Note that if DB_SQLPASSTHROUGH is not specified, a syntax error results because the local Access engine attempts to parse the SQL and does not recognize the outer
join syntax.
After applying the SQL and creating the result set, there are numerous methods that can be applied to position the record pointer in the cursor. The MoveFirst, MoveLast, MoveNext, and MovePrevious methods are the most commonly used, and their purposes
should be self-explanatory. Visual Basic provides the additional method FindFirst to position the record pointer at the first record matching specific criteria. For example, assuming that the record pointer is positioned at the first record, the following
line would find the first individual with the last name Smith, based on the result set returned by the previous example:
dsContacts.FindFirst("last_name = 'Smith'")
The criteria supplied as the argument to FindFirst are syntactically equivalent to a WHERE clause in SQL. The additional methods FindNext, FindPrevious, and FindLast operate on the same basis.
After positioning the record pointer, an application can assign variables to result set column values using the Fields property of the Snapshot. The Fields collection is simply a representation of the columns in the result set. They can be accessed by
name or by a zero-based index, starting with the left-most column. Listing C.2 demonstrates methods for assigning application variables to result set data.
Listing C.2. The Fields collection is used to access result set data.
Dim iRow As Long
iRow = 0
dsContacts.MoveFirst
While Not dsContacts.EOF
ReDim Preserve szLast(iRow) As String
ReDim Preserve szFirst(iRow) As String
ReDim Preserve szPhone(iRow) As String
szLast(iRow) = dsContacts.Fields("last_name").Value
szFirst(iRow) = dsContacts.Fields(1).Value
szPhone(iRow) = dsContacts.Fields("phone_nbr").Value
dsContacts.MoveNext
Wend
dsContacts.Close
 When assigning values from the Fields collection to variables, the application should always check for null values, as below:
If (IsNull(dsContacts.Fields("last_name")) = False) Then
szLast(iRow) = dsContacts.Fields("last_name").Value
End If
Assigning a null value to an application variable will produce a run-time error.
As evident from listing C.2, the more readable form of accessing the fields collection is to access them by name. When there are only a few columns in the result set, this point does not seem significant. However, if there are twenty or thirty columns
in the result, it becomes very difficult to identify a column by index. Also, any changes to the SQL which created the result set may cause the indexes to reference different columns. While it may save a few keystrokes to use numeric indexes, in the
interest of writing maintainable code, the fields collection should be accessed by column name whenever possible.
When the columns of the result set are not static at design-time, the Count property of the Fields collection can be used to determine the number of columns, and the Name property , (which applies to each individual field), can be used to create column
headings.
 A single Visual Basic object, such as a Database or Snapshot object, cannot be passed as a parameter to a function, but an array of objects can. When dealing with single object, it can be declared as a single
element array so that it can be passed to functions and subroutines, and generic methods can be written to operate on these objects.
While Visual Basic also provides Dynaset, Table, and QueryDef objects for retrieving result sets, these objects are not commonly used to communicate with ODBC data sources. The primary difference between a Snapshot and the Dynaset and Table objects in
ODBC environments is that the Snapshot creates a read-only result set, while the Dynaset and Table objects' result sets can be edited. However, these objects should not be used for database transactions, for reasons that will be discussed next.
The Dynaset is similar to the Snapshot, except that its result set is refreshed every time a change is made to one of its underlying tables. This is true only for local database files. With ODBC connections, the Dynaset object is, in essence, identical
to the Snapshot object, except that the Dynaset can be used for in-place updates, deletes, and insertions.
While Table objects could be used by applications accessing ODBC data sources, it would be unacceptable to do so in most situations. Using the OpenTable method is not as readable as a SELECT statement in which the columns are clearly identified and
referenced by name, and when accessing Oracle, the Table object is the equivalent of a Dynaset created with the SQL SELECT * FROM table_name. In both cases, the column list is expanded, and the statement is sent to the server using the API function
SQLExecDirect().
A QueryDef object is similar to a view that is created and stored by the application. The QueryDef object simply stores frequently used SQL so that it can be read from the database and executed as needed. QueryDefs cannot be created over ODBC
connections. Views are a better alternative, because they are stored in the database with a bound access plan, and the SQL does not need to be executed from the client application.
The Visual Basic DataControl provides another option for retrieving a result set through ODBC. The DataControl is used to bind controls to result set columns. To retrieve a result set using a DataControl, its Connect property must be set to the full
connect string, as previously described for the OpenDatabase method. This property can be set at run-time using a statement like the one below:
Data1.Connect=szConnect
This example assumes that szConnect was constructed as in Listing C.1. The RecordSource property of the DataControl is used to retrieve the result set. If this property is set at design-time, results will be retrieved as soon as the Connect property is
assigned and a connection is established. If the Connect property is also assigned at design-time, results will be fetched when the object is instantiated. The RecordSource property can consist of a table name, view name, or a SELECT statement. The
DB_SQLPASSTHROUGH option is not available to the DataControl, so if a SELECT statement is used as the RecordSource property, it may not contain an outer join or any other Oracle-specific syntax. In order to use a DataControl with a complex SELECT
statement, a view should be created so the DataControl can simply use the view as the data source.
Controls can be bound to result set columns through the DataControl. A text box, for example, can be bound to a DataControl by setting its RecordSource property to the name of the DataControl and setting its DataField property to the name of the column
in the result set that it should contain. When the result set is retrieved, the text box is then automatically populated with the value of the specified column at the current record position. Numerous third-party vendors provide custom controls that can be
bound to result sets in this manner. The standard controls that can be bound to a result set through the DataControl are limited to text boxes, checkboxes, labels, images, and picture boxes.
The Recordset property of the DataControl is nearly identical to a Dynaset object. The MoveFirst method and other positioning methods apply to the Recordset property of the DataControl, as well as the BOF and EOF properties, and the Fields collection.
Consequently, the DataControl's result set can be accessed programmatically, in addition to being accessed by bound controls. Using bound controls is generally not the best approach to developing client/server applications, however. The nature of bound
controls requires that the cursor to which they are bound persists for the life of the bound controls. For most applications, it is preferable to read the data, populate the necessary controls programmatically, and close the cursor. Note that in Listing
C.2, the Snapshot object is used to populate a Visual Basic array, and then it is immediately closed, thereby freeing the cursor on the server. In heavily used systems with a large number of clients, this can have a significant impact on performance.
While the DataControl can be used to perform inserts as well as in-place updates and deletions, it is strongly recommended to use these methods through ODBC. Unfortunately, when Visual Basic establishes an ODBC connection, the ODBC AutoCommit connection
option is enabled, and Visual Basic does not provide a method to disable this option. As a result, transaction control is not possible. Even if the transaction involves only a single table and a single operation, the DataControl is a bad choice for
applying transactions. The DataControl always updates every column. If there are unbound columns, these values must be set manually, which complicates the entire process. For example, if an application needs to supply a transaction timestamp with every
update, there is no clean way to do this. The DataControl does not support passthrough mode, so the Oracle system variable, sysdate, cannot be supplied as a value. The application has to supply a time based on the local workstation's clock, which would not
only require an assignment to a member of the Fields collection, it would almost certainly introduce inaccuracies. These same problems apply to the Dynaset and Table objects because they use the same methods for applying transactions.
There are three possible solutions to overcome the AutoCommit problem. One solution would be to use Oracle stored procedures exclusively for transaction processing. The ExecuteSQL method of the Database object can be used to call Oracle stored
procedures, providing an easy and safe way to communicate transactions to the database. Using stored procedures also simplifies the development of the client application, by freeing it from the responsibility of generating dynamic SQL and controlling
transactions. The ExecuteSQL method requires a single argument. The argument is a text string of the SQL to be executed. This method uses passthrough mode by default, so any SQL that can be evaluated by Oracle can be supplied, including the ODBC syntax for
calling Oracle procedures. Listing C.3 demonstrates the use of the ExecuteSQL method to call an Oracle stored procedure.
Listing C.3. The ExecuteSQL method can be used to call Oracle stored procedures.
Dim iRows As Integer
Dim szStmt As String
szStmt = "{call insert_individual('" & txtLastName.Text & ", '"
szStmt = szStmt & txtFirstName.Text & ", '" & txtNotes.Text
szStmt = szStmt & ", '" & txtDateOfBirth.Text & "')}"
iRows = dbOracle.ExecuteSQL(szStmt)
The ExecuteSQL method returns the number of rows affected by the transaction, and this value can be tested to determine the success or failure of the operation.
Unfortunately, because Visual Basic does not support prepared execution of SQL statements, it cannot call Oracle stored procedures that use output parameters, or add parameters to the procedure call. The statement must be built dynamically, as in
Listing C.3.
A second possible means of overcoming the AutoCommit problem is to use a third-party product, such as Oracle Objects for OLE. This product provides direct replacements for the Visual Basic objects and methods for communicating with Oracle databases. For
example, the following code fragment establishes an Oracle session through VB that can be used for transaction control:
Dim Session As Object
Dim dbOracle As Object
Set Session = CreateObject("OracleInProcServer.XOraSession")
Set dbOracle = Session.OpenDatabase("ORACLE", "scotty/tiger", 0&)
The preceding example uses the Visual Basic generic (OLE) Object data type and the CreateObject function to request a new XOraSession object from Oracle's OLE server. The methods of the Session object can then be used to create other objects, and to
manage transactions, using the BeginTrans, CommitTrans, and Rollback methods. A full discussion of the features of Oracle Objects for OLE is beyond the scope of this appendix. However, it is important to note that it can provide Visual Basic applications
with the capability to control transactions, add parameters to SQL and procedure calls, and make use of output parameters when accessing Oracle databases, among other things.
The potential shortcoming of the previous two means of bypassing Visual Basic's AutoCommit behavior is that they are not portable. If a client application needs to access different RDBMSs, these solutions may not be feasible. A third approach to
overcoming the AutoCommit problem is to use the ODBC API directly, which opens numerous possibilities, including the creation of a truly portable client application.
A small subset of the ODBC API can be used to provide transaction control in Visual Basic applications accessing ODBC data sources. The declarations in Listing C.4 should be placed in a module, and will provide access to all functions needed to connect,
apply transactions through embedded SQL, and rollback or commit them, as needed.
Listing C.4. ODBC API declarations for Visual Basic.
Declare Function SQLAllocConnect Lib "odbc.dll" (
ByVal hEnv As Long, hDBc As Long) As Integer
Declare Function SQLAllocEnv Lib "odbc.dll" (
hEnv As Long) As Integer
Declare Function SQLAllocStmt Lib "odbc.dll" (
ByVal hDBc As Long, hStmt As Long) As Integer
Declare Function SQLDisconnect Lib "odbc.dll" (
ByVal hDBc As Long) As Integer
Declare Function SQLDriverConnect Lib "odbc.dll" (
ByVal hDBc As Long, ByVal hWnd As Integer,
ByVal szCSin As String, ByVal cbCSin As Integer,
ByVal szCSOut As String, ByVal cbCSMax As Integer,
cbCSOut As Integer, ByVal f As Integer) As Integer
Declare Function SQLExecDirect Lib "odbc.dll" (
ByVal hStmt As Long, ByVal SQLString As String,
ByVal SQLStringLen As Long) As Integer
Declare Function SQLFreeConnect Lib "odbc.dll" (
ByVal hDBc As Long) As Integer
Declare Function SQLFreeEnv Lib "odbc.dll" (
ByVal hEnv As Long) As Integer
Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hStmt As Long,
ByVal EndOption As Integer) As Integer
Declare Function SQLSetConnectOption Lib "odbc.dll" (
ByVal hDBc As Long, ByVal fOption As Integer,
ByVal vParam As Long) As Integer
Declare Function SQLTransact Lib "odbc.dll" (
ByVal hEnv As Long, ByVal hDBc As Long,
ByVal fnType As Integer) As Integer
Global Const SQL_NTS = 3
Global Const SQL_DRIVER_NOPROMPT = 0
Global Const SQL_COMMIT = 0
Global Const SQL_ROLLBACK = 1
Global Const SQL_MAX_MESSAGE_LENGTH = 512
Global Const SQL_AUTOCOMMIT = 102
Global Const SQL_DROP = 1
After establishing a connection for retrieving results with the OpenDatabase method, the application should establish a second connection to the database using the API functions—for applying transactions. After establishing this connection,
SQLSetConnectOption() should be used to disable AutoCommit. Listing C.5 demonstrates how this might be accomplished.
Listing C.5. Establishing a database connection using the ODBC API.
Dim hEnv As Long
Dim hDBc As Long
Dim szConnectString As String
Dim iError As Integer
Dim hWnd As Integer
Dim iLenCSOut As Integer
Dim szCSOut As String * 254
szConnectString = "ODBC;DSN=ORACLE;UID=scotty;PWD=tiger;"
hWnd = frmMDIFrame.hWnd
' Allocate environment
iError = SQLAllocEnv(hEnv)
' Allocate connection
iError = SQLAllocConnect(hEnv, hDBc)
' Load driver & connect to ODBC data source
iError = SQLDriverConnect(hDBc, hWnd, szConnectString, SQL_NTS,
szCSOut, 254, iLenCSOut, SQL_DRIVER_NOPROMPT)
' Disable autocommit
iError = SQLSetConnectOption(hDB, SQL_AUTOCOMMIT, 0)
Obviously, in practice, the connect string would not be hard-coded and the return value of each function should be checked. The example in Listing C.5 is intended only to demonstrate the use of the ODBC API to establish a database connection.
Once a connection has been established, the application can apply transactions using SQLExecDirect() after allocating a statement handle with SQLAllocStmt(). SQLTransact() can then be used to commit or rollback a transaction based on the return value of
SQLExecDirect(). After applying the transaction, the application should call SQLFreeStmt() to free the resources allocated to the statement handle. Listing C.6 provides an example of the calls to these functions.
Listing C.6. Applying a transaction using embedded SQL.
Dim hStmt As Long
' Allocate a statement handle
iError = SQLAllocStmt(hDBc, hStmt)
For i = 0 To iStmts
' Apply SQL
iError = SQLExecDirect(hStmt, szSQL(i), SQL_NTS)
If iError Then
' Rollback
iNextErr = SQLTransact(hEnv, hDB, SQL_ROLLBACK)
Exit For
End If
71501,2744Next i
If (iError = 0) Then
' Commit
iError = SQLTransact(hEnv, hDB, SQL_COMMIT)
End If
' Free the statement handle
iError = SQLFreeStmt(hStmt, SQL_DROP)
The example in Listing C.6 assumes that the environment and connection handle, hEnv and hDBc, are valid and connected to the data source, and that szSQL is an array of iStmts SQL statements. If any statement in the transaction fails, a
rollback is issued and processing of the transaction is discontinued. If all statements are processed without errors, the entire transaction is committed. Regardless of whether the transaction is committed or rolled back, the application frees the
statement handle.
When the application exits, it needs to disconnect from the data source and free all resources allocated to the environment and connection handles. This can be accomplished with three functions calls, as illustrated in the following lines:
iError = SQLDisconnect(hDB)
iError = SQLFreeConnect(hDB)
iError = SQLFreeEnv(hEnv)
The full capabilities of ODBC are far beyond the scope of this discussion, but many additional capabilities can be provided, and an application can be constructed in a manner that is completely database-independent using embedded SQL. However, if an
application does not need to be portable to other database platforms, it may be easier to use stored procedures or a third-party product, such as Oracle Objects for OLE, to apply transactions from Visual Basic.
It is unfortunate that Visual Basic does not provide a means by which AutoCommit can be disabled for connections established internally. This is a limitation of the implementation of the Jet Database Engine that Visual Basic uses.
One of the most powerful, (and potentially dangerous), features of Visual Basic is the means by which errors are trapped, using the On Error statement. This statement is well-used in Visual Basic programming because all untrapped run-time errors are
fatal. The On Error statement is used to specify an error handler to which execution jumps if an error occurs. The error handler can be specified using GoTo, or Resume Next can be specified to allow program execution to continue normally. The system
variables Err and Error$ store a numeric code and a text description for the error that occurred most recently. The fact that only the most recent value is stored is what makes On Error Resume Next a somewhat dangerous way to trap and handle errors. It is
very convenient to use, but the value of Err should be checked wherever an error requiring a specific action occurs.
A common mistake in Visual Basic programming is to misunderstand its array dimensions. Consider the following declaration, for example:
ReDim szStrings(2) As String
Most would assume that this declares an array of two strings. However, the previous statement actually allocates an array of three strings. The subscript in an array declaration sets the upper bound, not the number of elements in the array, so
szString(0), szString(1), and szString(2) are all valid, based on the declaration. The base subscript can be changed to 1, using the Option Base statement:
Option Base 1
If this statement appears in the same module as the previous array declaration, the array would have only two elements, szString(1) and szString(2). In this case, szString(0) would be an invalid reference.
The Visual Basic programming language is particularly easy to learn and use. Nearly all developers have some experience with BASIC, and many of the language constructs have been in place since the very first versions of what is now referred to as BASIC
A.
Despite some idiosyncrasies, a lack of object-oriented features, and relatively weak database support, Visual Basic is an extremely useful development tool. Its shortcomings are offset by the simplicity of its IDE and language, and extensive third-party
support. The uncluttered and completely non-modal IDE, a flexible ANSI-standard programming language, and a wide variety of pre-built objects make Visual Basic an excellent choice for projects requiring rapid development.
| 
