• 48
• The Oracle Data Control
• Understanding ODC
• What Is a VBX?
• What Type of VBX Is the Oracle Data Control?
• Reviewing the Relationship Between the ODC and the OLE Server
• Object Creation
• Using SQL Parameters
• Controlling Transactions
• Using ODC in Visual Basic
• Using ODC in a New Project
• Using ODC with a Third Party Data Aware Control
• Comparing ODC with the Visual Basic Data Control
• Functionality
• ODC Properties Not Found in Visual Basic
• Limitations of the ODC
• Migrating from the MS Data Control/ODBC to the ODC/OLE Server
• Summary

48

The Oracle Data Control

The Oracle Data Control (ODC) is a level III Visual Basic Extension (VBX) designed to be compatible with the Visual Basic 3.0 data control. The ODC uses the OLE Server to access an Oracle7 database. Unlike some data controls, the ODC does not ship with its own controls such as a text box, list box, combo box, grid, etc. The ODC theoretically can have any other third party controls bound to it.

Understanding ODC

Oracle Objects for OLE (OO4O) provides an OLE Server for programmatic development. Because Visual Basic 3.0 is a popular environment and because data aware controls make development even easier, the Oracle Data Control is an excellent helper tool and logical addition to Oracle Objects for OLE. The inclusion of the Oracle Data Control makes Oracle Objects for OLE a reasonable replacement for the native database access in Visual Basic 3.0.

What Is a VBX?

VBX is short for Visual Basic Extension and is basically a Windows Dynamic Link Library (DLL) with a specific interface. Visual Basic 3.0 contains a control development kit that specifies the various interfaces a VBX must support. A VBX is used to extend the functionality of Visual Basic (and possibly other applications) by adding functionality not contained in the original product.

What Type of VBX Is the Oracle Data Control?

The Oracle Data Control is a level III custom control. Although the Oracle Data Control appears to be a data control, it is not. The control development kit has no specification for writing a data control. The control development kit explains how to write a data aware control (a control that uses a data control) so the kit does discuss the behavior of the Microsoft data control and therefore implies how to write a data control.

Reviewing the Relationship Between the ODC and the OLE Server

The Oracle Data Control provides an easy way to create an OraDynaset and all other needed objects. After you refresh the data control, all objects are available using properties of the data control.

Object Creation

Refreshing the Oracle Data Control, assuming you have set certain properties correctly, causes an OraDynaset to be created. Examining the programmatic interface shows that an OraSession and OraDatabase must be created before an OraDynaset. This rule is consistent with the Oracle Data Control having Database (OraDatabase) and Session (OraSession) properties. All objects used to refresh the data control are then available as properties of it. One drawback is that the OraSession created or used by any data control is the default OraSession; you can never change the default. Along the same lines, all the object properties of the data control are read-only; you cannot assign a particular OraSession, OraDatabase, OraDynaset, etc. to a data control.

After you have refreshed the Oracle Data Control successfully, you can use all the objects it creates to mix and match usage of the data control with the programmatic interface. The following example demonstrates this procedure:

Dim OraSession as Object

Dim OraDatabase as Object

'Set properties needed to create the OraSession, OraDatabase

' and OraDynaset objects.

OraData1.Connect = "scott/tiger"

OraData1.Databasename = "t:prod:orcl"

OraData1.RecordSource = "select name, number from addressguide"

OraData1.Refresh

'Use the OraSession object of the data control to create a

' second named OraSession to be used for DDL statements. Remember

' that DDL executed within the same OraSession and using the same

' Oracle Connection will commit pending transactions.

Set OraSession = OraData1.Session.CreateNamedSession("DDLSession")

Set OraDatabase = OraSession.OpenDatabase("scott/tiger@t:prod:orcl",0&)

OraDatabase.DbExecuteSQL("create table temp (col1 number)")...

...

Using SQL Parameters

The OraParameters collection is a property of the OraDatabase object. You must create an OraParameter after the OraDatabase but before an OraDynaset. At first you may think you cannot use OraParameters with the data control, but you can. You can refresh the Oracle Data Control after you set the Connect and DatabaseName properties. If those properties are valid, the OraSession and OraDatabase objects that the data control needs are created. Again, this practice is consistent with the programmatic interface in that those values are the only ones needed to create an OraSession and OraDatabase.

After these objects have been created, you can add OraParameters to the OraDatabase object before creating the OraDynaset. After the OraParameters have been created, set the RecordSource property and refresh the data control again. This step causes the OraDynaset to be created. Just as with the programmatic interface, you can change an OraParameters value and refresh the OraDynaset. If this explanation sounds confusing, review this example:

'Set properties needed to create the OraSession and OraDatabase

OraData1.Connect = "scott/tiger"

OraData1.Databasename = "t:prod:orcl"

OraData1.Refresh

'Add an OraParamter

OraData1.Database.Parameters.Add "NAME", ÒSMITHÓ, 1

'Set the RecordSource so I can create the OraDynaset

OraData1.RecordSource = "select name, number from addressguide where name = :NAME"

OraData1.Refresh

'Change the value of the OraParameter and refresh the data control

OraData1.Database.Parameters("NAME").value = ÒJONESÓ

OraData1.RecordSet.Refresh

...

Controlling Transactions

You initiate transactions using the OraSession object, and you need to initiate them before creating the OraDynaset. At first you may think that you cannot use transactions with the data control, but you can. You can refresh the Oracle Data Control after setting the Connect and DatabaseName properties. If those properties are valid, the OraSession and OraDatabase objects that the data control needs are created. Again, this practice is consistent with the programmatic interface in that those values are the only ones needed to create an OraSession and OraDatabase. After these objects are created, you can start a transaction. After the transaction is started, set the RecordSource property and refresh the data control again. This step causes the OraDynaset to be created. Just as with the programmatic interface, you can change, commit, or roll back data. If this explanation sounds confusing, review this example:

'Set properties needed to create the OraSession and OraDatabase

OraData1.Connect = "scott/tiger"

OraData1.Databasename = "t:prod:orcl"

OraData1.Refresh

'Add an OraParamter

OraData1.Database.Parameters.Add "NAME", ÒSMITHÓ, 1

'Begin a transaction

OraData1.Session.BeginTrans

'Set the RecordSource so I can create the OraDynaset

OraData1.RecordSource = "select name, number from addressguide where name = :NAME"

OraData1.Refresh

'Update any data here.

...

'Commit the changes

OraData1.Session.CommitTrans

'Roll back the changes

'OraData1.Session.Rollback

...

Using ODC in Visual Basic

Using the Oracle Data Control in Visual Basic 3.0 is just like using any other VBX. Choose the File, Add File menu option and add the file ORADC.VBX (normally installed in \WINDOWS\SYSTEM).

Using ODC in a New Project

Using the Oracle Data Control is almost as easy as using the Microsoft data control or any other custom control. Follow these steps:

1. Start Visual Basic 3.0 and begin a new project (File | New Project).
   
   
2. Add the Oracle Data Control to this project (File | Add File). The Oracle Data Control is named ORADC.VBX and is normally located in the \WINDOWS\SYSTEM directory. After adding ODC, the tool palette looks something like Figure 48.1.
   
   

Figure 48.1. The tool palette.

1. Click on the Oracle Data Control and proceed to draw the control on a new form as you would with any other control.
   
   
2. After you draw the control, click it so the property palette is displayed. (See Figure 48.2.)
   
   

Figure 48.2. Oracle Data Control and property palette.

1. Set the Connect, DatabaseName, and RecordSource properties of the Oracle Data Control to valid values such as "scott/tiger," "AddressDb," and "select name from addressguide."
   
   
2. Click the text edit control icon on the tool palette and draw the control on the form.
   
   
3. Click the text edit then go to the property palette and set the DataSource property to be the same as the Name property of the Oracle Data Control ("oradata1" by default). Set the DataFields property to be ÒNameÓ, the name of the column to bind to this control.
   
   
4. Your form should look something like Figure 48.3.
   
   

Figure 48.3. Oracle Data Control and text edit on a form.

1. Run the project (Run | Start).
   
   

If you properly set the necessary properties of the Oracle Data Control and the text edit, then data appears in the text edit. Notice that the arrows on the control move the records forward and backward and that you can edit the data to update the database (assuming the table is updatable). You can stop the application using Run | End.

Using ODC with a Third Party Data Aware Control

Using the Oracle Data Control with a third party data aware control is almost as easy as using that control with the Microsoft data control. Follow these steps:

1. Start Visual Basic 3.0 and begin a new project (File | New Project).
   
   
2. Add the Oracle Data Control and the data aware control to the project (File | Add File).
   
   The Oracle Data Control is named ORADC.VBX and is normally located in the \WINDOWS\SYSTEM directory. This example uses a data aware control named TRUEGRID.VBX developed by Apex Software Corporation. After you have added both controls, the tool palette looks something like Figure 48.4.
   
   

Figure 48.4. Oracle Data Control, TrueGrid, and the tool palette.

1. Set the Connect, DatabaseName, and RecordSource properties of the Oracle Data Control to valid values such as "scott/tiger," "AddressDb," and "select name from addressguide."
   
   
2. Click the TrueGrid control icon on the tool palette and draw this control on the form.
   
   
3. Click the TrueGrid then go to the property palette and set the DataSource property to be the same as the Name property of the Oracle Data Control ("oradata1" by default). The form should resemble Figure 48.5.
   
   

Figure 48.5. Oracle Data Control and TrueGrid on a form.

1. Run the project (Run | Start).
   
   

If you properly set the necessary properties of the Oracle Data Control and the TrueGrid, then rows and columns appear in the TrueGrid. You can stop the application using Run | End.

Comparing ODC with the Visual Basic Data Control

The Oracle Data Control is designed to be a replacement for the Microsoft data control in Visual Basic 3.0. The Oracle Data Control provides basically the same properties and methods as the Microsoft data control.

Functionality

The Oracle Data Control provides a number of extra properties not found in the Microsoft data control.

ODC Properties Not Found in Visual Basic

The AllowMoveLast property determines whether the user can click the rightmost (MoveLast) button of the data control when running an application. When a MoveLast() is executed, all the records satisfying the query are fetched and stored locally, and the operation cannot be interrupted. This operation could potentially involve a large amount of data and take a long time, so the application designer can use this property to disable that button. This feature is useful only for the user, because a developer can still execute a MoveLast(). Also, disabling the MoveLast button does not prevent the user from repeatedly clicking the MoveNext button.

The AutoBinding property is the same as the NoAutoBind flag of the options parameters in the CreateDynaset() method.

The TrailingBlanks property is the same as the NoTrailBlanks flag of the options parameters in the CreateDynaset() method.

The HelpContextID property is used to set a help context id number that is accessed (in the application's help file) when the user presses F1 and the Oracle Data Control has focus.

The Session and Database properties of the Oracle Data Control refer to the OraSession and OraDatabase objects used to create the OraDynaset object that the RecordSet property refers to.

The DatabaseName and Connect properties are similar in nature to those of the Microsoft data control, but they have a much different syntax. For the Oracle Data Control, set the Connect property to the username, a forward slash, and the password (as in "scott/tiger"). Set the DatabaseName to the Oracle SQL*Net connect string for that database. This identifier varies depending on the version of Oracle SQL*Net you are using (V1 or V2), so consult the Oracle documentation for more details (a V1 example is something like "t:prod:orcl"). When using DAO/JET/ODBC, the Microsoft data control uses the Connect property to specify all the connection information using a keyword equal value (keyword = value) format.

Limitations of the ODC

Although the Oracle Data Control works like a data control, it does not (nor could it) support every detail or unsupported feature that the Microsoft data control does. The Control Developer's Kit has no specification of a data control; you find only information on what to expect from the data control given certain situations.

The Oracle Data Control doesn't appear as a data control to Visual Basic so you cannot autoselect it from the drop-down list of data sources that some data aware controls have.

The Oracle Data Control does not support getting its Hwnd (Window Handle) property so that you can access it from another form, nor does the ODC work with controls that rely on undocumented features of the Microsoft data control.

As far as features go, the Oracle Data Control can only read images, not write them. This point is not mentioned in the product documentation, but you can easily test the feature by setting a bitmap to a picture control and then trying to update.

The product documentation lists a number of differences between the Oracle Data Control and the Microsoft data control as well as problems that some data aware controls have when trying to use the Oracle Data Control.

Migrating from the MS Data Control/ODBC to the ODC/OLE Server

Moving from the Microsoft data control to the Oracle Data Control requires a small number of steps, but some of those steps could cause code redesign. Before moving, make sure to see if the Oracle Data Control is compatible with any data aware controls your application requires.

You should consider all of the following items when migrating from the Microsoft data control to the Oracle Data Control:

• After adding the Oracle Data Control to the project, modify property values that are similar but have different values such as the Options property, AllowMoveLast, TrailingBlanks, AutoBinding, Connect, and DatabaseName. You may need to modify the RecordSource property if the SQL your application is using contains JET-specific syntax.
  
  
• You need to rewrite any code that uses dynaset Find*() methods because Oracle Objects does not support these methods. Remove any Close() methods because Oracle Objects doesn't need them.
  
  
• Declare the objects of Oracle Objects for OLE as type "object" and not as native Visual Basic data types such as database, dynaset, etc.
  
  
• You may need to prefix some method names with "Db" because Visual Basic considers names invalid if you use them from an object that is not a native data type. Review the product documentation topic called "Method and Property Name Conflicts."
  
  
• Review your application's use of the FieldSize(), GetChunk(), and AppendChunk() methods because these methods are implemented differently than in DAO/JET. Again, consult the product documentation on these methods' behavior.
  
  
• Error handling is significantly different when using Oracle Objects. The product documentation contains a topic describing error handling.
  
  
• Read the product documentation topics entitled "Coding Techniques" and "Tuning and Customization" to enhance performance.
  
  

Summary

The ODC is a good replacement for the Microsoft data control of Visual Basic 3.0. Unlike other similar controls, the Oracle Data Control is designed to work with any third party data aware control.

Unfortunately, though, some fundamental differences exist that may require modifications to third party controls. The product documentation concedes that testing of third party controls was not thorough, because many data aware controls are available. Some testing was done, and the Oracle Data Control does appear to work with a variety of popular controls.

The best advice is to test the Oracle Data Control with any data aware controls that your application may require, because compatibility is not guaranteed.