1089-1091

Previous Table of Contents Next

Page 1089

 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 
CAUTION
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 runtime error.

As evident from Listing 48.2, the more readable form of accessing the Fields collection is by name . When there are only a few columns in the result set, this point does not seem significant. However, if there are 20 or 30 columns in the result, it becomes very difficult to identify a column by index. Also, any changes to the SQL that created the result set may cause the indexes to reference different columns . Although 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.

TIP
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 a 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.

Although 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'

Page 1090

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 runtime using a statement like the one below:

 Data1.Connect=szConnect 

This example assumes that szConnect was constructed as in Listing 48.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

Page 1091

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 persist 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 48.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 effect 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 48.3 demonstrates the use of the ExecuteSQL method to call an Oracle stored procedure.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net