0983-0987 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 983

CHAPTER 42

The Oracle Data Control

IN THIS CHAPTER

Using the ODC in Visual Basic 984
Using the ODC and OO4O Automation Server Objects 986

Page 984

The Oracle data control (ODC) is an OCX control that provides the same functionality the Microsoft data control in Visual Basic offers.

The ODC uses the OO4O Automation Server to handle the flow of information between the data-bound controls in Visual Basic and the Oracle databases.

By binding visual controls in Visual Basic to the Oracle data control, you can display and browse the result set of queries in a variety of ways and without any programming.

Figure 42.1 shows an example of a data-aware grid control bound to the ODC. This example displays the records from the emp table in the database in a grid control. The grid control and the ODC cooperate to accomplish this task; the grid handles the visual aspect of the operation, and the ODC performs the fetching and updating of rows in the Oracle database.

Figure 42.1.
A data-aware grid control.

Using the ODC in Visual Basic

In this section we will look at two examples that demonstrate the ease with which the ODC is used in Visual Basic. The first example illustrates how the example shown in Figure 42.1 is constructed , and the second describes the steps involved in binding the ODC to edit controls in Visual Basic.

To build the application shown in Figure 42.1, follow these steps:

Make sure that the ODC and data-bound grid control appear on the toolbar of Visual Basic. Choose Project Component and enable the ODC and the Microsoft FlexGrid Control 5.0 checkboxes. Your toolbar now should include these two controls, as shown in Figure 42.2.
Place the ODC and the grid on a form in your project. To place a control on a form in Visual Basic, click the control in the toolbar, hold down the left mouse button somewhere on the form, and drag it until the control is the size you want. Your form should look similar to Figure 42.3.

Page 985

Figure 42.2.
Visual Basic toolbar.

Figure 42.3.

Binding the ODC to a data-aware grid control.

Change the Connect property of the ODC to scott/tiger, change the Database property to the name of the database where the emp table resides (ExampleDB, in this case), and set the RecordSource property to select * from emp.
Select the grid control and set its DataSource property to ORADC1. ORADC1 is the default name for the ODC.
Run the application; you should see all the emp rows in DBGrid.

Figure 42.4 shows an example that binds three Visual Basic edit controls to the ODC. These controls are connected to the ename, empno, and sal columns of the result set generated by
"select ename, empno, sal from emp".

The RecordSource property in this example is set to the select ename, empno, sal from emp query. Again, you should set the Connect and Database properties to scott/tiger and ExampleDB, respectively.

Page 986

Figure 42.4.
Binding the ODC to edit controls.

To bind the edit control to a particular column of the result set, you first specify the DataSource property to be the Oracle data control (ORADC1) and then set its DataField property to the
column you select from the drop-down list that shows all the column names of the query result set.

Using the ODC and OO4O Automation Server Objects

The implementation of the ODC uses the OraDynaset interface in the OO4O Automation Server. All the properties of an OraDynaset therefore appear on the property sheet for the ODC in Visual Basic. The OraDynaset interface in the OO4O Automation Server is discussed in detail in Chapter 41, "Oracle OLE Automation Server."

You can specify the OraDynaset properties at runtime (just as you can the properties of other controls in Visual Basic). You can use the following code fragment to set the RecordSource, DatabaseName, and Connect properties at runtime:

 Private Sub Form_Load()    ORADC1.RecordSource = "select * from dept"    ORADC1.DatabaseName = "ExampleDB"    ORADC1.Connect = "scott/tiger" End Sub

Now look at another example that uses the Refresh method of ORADC1.

After you type select * from emp and click Execute, all the rows are fetched from the emp table, as shown in Figure 42.5.

The ODC is bound to the Microsoft flex grid control, and its Connect and Database properties are set to the usual scott/tiger and ExampleDB values. The Execute button Click method looks like the following:

 Command1_Click()     ORADC1.RecordSource = QueryField.Text     ORADC1.Refresh End Sub

Page 987

Figure 42.5.
Output from a dynamic query.

QueryField is the name of the edit control where the query text is typed. After the Execute button is clicked, the ODC's RecordSource property is set to the typed-in query, and the ODC's Refresh method is called. In this case, calling the Refresh method creates a new OraDynaset for the ODC each time it is called.

In the following code, the Refresh method is called to connect to the database only:

 OraData1.Connect = "scott/tiger" OraData1.Databasename = "ExampleDB" `Get a connection to the database OraData1.Refresh

Notice that you do not need to set the RecordSource property in order for the Refresh method to be called.

The following example demonstrates how you can use parameters with the ODC:

 `Add an OraParamter OraData1.Database.Parameters.Add "NAME", "SMITH", 1 `Set the RecordSource so I can create the OraDynaset OraData1.RecordSource = "select * from emp where ename = :NAME" OraData1.Refresh

This example uses the Database property of the ODC to add parameters. Remember that the Database property of the ODC is an OraDatabase interface in the OO4O Automation Server.

In this case, you're adding one parameter (NAME) and setting its initial value to "SMITH". The RecordSource property of the ODC then is set, and the Refresh method is called to execute the query that contains the placeholder "ENAME".

You can execute the same query again later using a different parameter value ("JONES"):

 `Change the value of the OraParameter and refresh the data control OraData1.Database.Parameters("NAME").value = "JONES" OraData1.RecordSet.Refresh

Similarly, you can use the Session property of the ODC to get a reference to the OraSession interface and perform tasks such as transaction control, as shown in this code:

 `Begin a transaction ORADC1.Session.BeginTrans

Previous Table of Contents Next