• 44
• Oracle Basic
• Overview of Oracle Basic
• How to Write, Test, and Compile Oracle Basic Code
• Manipulating Data from a Record Manager Using Oracle Basic
• Enforcing a Limiting Condition on a Record Manager
• Performing Validation
• EXEC SQL Operations: Select, Insert, Update, Delete, and Commit
• Stored Procedures, Functions, and PL/SQL Blocks
• Extending the Functionality of Power Objects
• Summary

44

Oracle Basic

Power Objects uses Oracle Basic as its programming language. Oracle Basic is fully compatible with ANSI BASIC with the additions of object extensions and Structured Query Language (SQL) extensions. Oracle chose Basic as the programming language in order to appeal to the widest range of developer experience. This chapter discusses when to use Oracle Basic; how to write, test, and compile code; and how to extend the functionality of Power Objects through Oracle Basic. Refer to the included CD-ROM for Appendix E, "Oracle Power Objects Programming Reference," for a reference on the syntax and purpose of the majority of all the commands and functions comprising Oracle Basic.

Overview of Oracle Basic

Oracle Basic is fully compatible with ANSI BASIC and is therefore also compatible with Microsoft Visual Basic. You don't have to write Basic code in order to use Power Objects effectively, but if you find that the default functionality of a method or process does not exactly meet your requirements, you can overwrite the method to modify the default behavior using Oracle Basic code.

Using Oracle Basic, you can create user-defined functions and methods. You can declare variables, external commands, and functions and obtain direct access to the data contained in visual and non-visual objects. Oracle Basic gives the developer the capability to create those features in the Oracle Power Objects product that he finds are missing or incomplete.

How to Write, Test, and Compile Oracle Basic Code

In order to use Oracle Basic effectively, you must understand how to write code, how to test your code, and how to compile the final application. Oracle Basic code always exists within a method definition, subroutine, or function. You expand the method where you want to place code by clicking the method name. A small editor window opens up, and you can begin typing code in the window. Looking at the btnPick object from the Oracle Power Objects Sample Launch application, you find the following code in the Click() method:

Application.SetCursor( 1 )

' This method determines which application to run based on the value of its

' Bitmap property.

DIM vResult AS Integer

DIM vDisplay AS Integer

DIM vApp AS String

DIM vMsg AS String

' If the current platform is Windows, then ...

IF SystemName() = "Windows" THEN

  ' Determine which application to run based on the Bitmap property value.

  SELECT CASE Bitmap

    CASE BITMAPS.bmpNotepad: vApp = "notepad"

    CASE BITMAPS.bmpWrite:   vApp = "write"

    CASE BITMAPS.bmpPaint:   vApp = "pbrush"

    CASE BITMAPS.bmpCalc:    vApp = "calc"

    CASE ELSE

      vApp = NULL

  END SELECT

  IF IsNull( vApp ) THEN

    MSGBOX( "The Programmer has made a fundamental error! Ò & &

ÒPlease check the code.", 48, Application.Label )

  ELSE

    vResult = WinExec( vApp, 1 )

    vDisplay = TRUE

    SELECT CASE vResult

      CASE 0:  vMsg = "Out of Memory."

      CASE 2:  vMsg = "File not found."

      CASE 3:  vMsg = "Path not found."

      CASE 5:  vMsg = "Attempt to dynamically link to a task."

      CASE 6:  vMsg = "Library requires separate data segments for each task."

      CASE 10: vMsg = "Incorrect Windows version."

      CASE 11: vMsg = "Invalid EXE file."

      CASE 12: vMsg = "OS/2 application."

      CASE 13: vMsg = "DOS 4.0 application."

      CASE 14: vMsg = "Unknown EXE type."

      CASE 15: vMsg = "Attempt to load a Windows application Ò & &

Òfor an earlier version of Windows."

      CASE 16: vMsg = "Attempt to load an additional instance of an .EXE file."

      CASE 17: vMsg = "Attempt to load a second instance of an Ò & &

Òapplication in a large-frame EMS mode."

      CASE 18: vMsg = "Attempt to load a protected-mode application in Ò & &

Òreal mode."

      CASE ELSE

        vDisplay = FALSE

    END SELECT

    IF vDisplay THEN MSGBOX( vMsg, 48, Application.Label )

  END IF

ELSE

  MSGBOX( "Currently, Launching Other Applications is implemented on Ò & &

ÒWindows platforms ONLY!", 48, Application.Label )

END IF

If you use the default Click() method functionality, the only action that the btnPick button performs is to visually go down and up. By adding the preceding code, you enable the btnPick button to execute another Microsoft Windows program and return the resulting code for launching that program.

Testing the code is as simple as clicking the RunForm icon on the toolbar. Once the form is running, you can click the btnPick button, and the application that is displayed begins running. Compiling is just as easy. Clicking the Generate Application icon on the toolbar brings up a dialog box that asks whether you want a separate application file or a stand-alone application. After you finish this dialog box, you are asked to supply the filename for the compiled application.

Manipulating Data from a Record Manager Using Oracle Basic

The default record manager manipulation available to a user consists of insert, delete, and edit operations through the run-time menu or run-time toolbar and scroll operations through the associated scrollbar. Usually, these record managers are visually represented by objects such as the form, embedded form, repeater, class, list box, combo box, or popup.

Although the form, embedded form, or repeater require little to no actual programming, the other objects often require code. In fact, it is impossible to have data associated with a list box, combo box, or popup without using some Oracle Basic code. You place this code in either the translation or valuelist property as appropriate. For a popup to display employee names while storing employee IDs, you use the following translation for a session called OracleSession pointing to database objects owned by the Oracle database account SCOTT/TIGER:

=AT OracleSession SELECT ename, empno FROM emp ORDER BY ename ASC

A more formidable task is to have a repeater displaying employee names with a field to enter in a name to search for. The field should position the focus of the repeater to the record that most closely matches the text string entered into the field. Figure 44.1 illustrates what the repeater might display when you enter the value SH in the field.

Figure 44.1. Employee name lookup field.

SH highlights SCOTT because there is no partial match; the next name is SMITH. You place the following code in the PostChange() method of the field to enforce the lookup.

Value = UCASE( Value )

DIM rec AS Object

DIM test AS String

rec = repeater1.GetRecordSet()

j = LEN( Value )

IF NOT ISNULL( Value ) THEN

  FOR i = 1 to rec.GetRowCount()

    rec.SetCurRow( i )

    whole = rec.GetColVal( "ENAME" )

    test = LEFT( whole, j )

    IF whole > Value THEN

      IF test <> Value THEN

        IF i > 1 THEN rec.SetCurRow( i - 1 )

      END IF

      EXIT FOR

    ELSEIF test = Value THEN

      EXIT FOR

    END IF

  NEXT i

END IF

The preceding code searches through the records in a record manager one at a time until it finds a match or the test case is greater than the search case. This code illustrates many Oracle Basic fundamentals including visual record manager manipulation, string operations, and conditional logic.

Enforcing a Limiting Condition on a Record Manager

There are five different, yet related, methods for imposing or enforcing a limiting condition on a record manager. The first method is through the master-detail linking relationship using the LinkDetailColumn, LinkMasterColumn, and LinkMasterForm properties. This method forces the detail record set to display records only where the columns specified in the LinkDetailColumn property have values that match the values of the columns specified in the LinkMasterColumn property. The second method is through a shared record source by setting the RecordSource property of a repeater2 object to the repeater1 object containing the actual record source to be shared (repeater2.RecordSource is assigned the value =repeater1). This method forces the record managers of each repeater to effectively use the single database cursor allocated to the repeater1 object.

The third method is to set the DefaultCondition property of the object having the record manager. This method is equivalent to specifying a WHERE clause in an SQL statement during design time. The fourth method is to use the built-in Query-By-Form (QBF) capability of Oracle Power Objects. This method is equivalent to specifying a DefaultCondition during runtime. The fifth and final method for imposing or enforcing limitations on a record manager is to call the QueryWhere() method of the object with a limiting condition. The limiting condition is exactly like the DefaultCondition except that it is programmatically defined during runtime.

Performing Validation

Power Objects performs various levels of field and row level validation by default. These validations verify that dates are valid, numeric fields contain numbers, and database constraints are enforced. If the developer wants to impose validation rules other than these defaults, he must add code to the Validation() method of the field and the ValidateRow() method of the record manager object. Figure 44.2 illustrates the code necessary to implement a business rule where a value must be less than or equal to 10. The message that is displayed is shown in the ValidationMsg property for field1 during design time and in the Oracle Power Objects modal dialog box window during run time.

Figure 44.2. Enforcing a field value.

EXEC SQL Operations: Select, Insert, Update, Delete, and Commit

Although Power Objects provides all the functionality of the SQL operations Select, Insert, Update, Delete, and Commit for the default behavior of the application environment, a developer often wants to make changes to the database independent from the visual interface. In this section, I briefly discuss each of these commands and refer to the search terms for the online help.

Select is an operation to retrieve data from the database into local variables. Oracle Power Objects cannot select data directly into an object or a record manager. It must select data into a variable and move it into the object or existing record manager. The Select statement can retrieve multiple values from the database with one statement. For more information on the syntax of the Select statement, enter Select (SQL) as the search phrase and topic from the online help.

Insert is an operation to store data in the database from local variables. The Insert statement cannot store arrays of data with one statement; a single statement relates to a single record. For more information on the syntax of the Insert statement, enter Insert (SQL) as the search phrase and topic from the online help.

Update is an operation to change data stored in the database from local variables. The Update statement cannot change arrays of data with one statement; a single statement relates to a single record. For more information on the syntax of the Update statement, enter Update (SQL) as the search phrase and topic from the online help.

Delete is an operation to remove data from the database using local variables to specify limiting conditions. The Delete statement can delete multiple rows of data with one statement. For more information on the syntax of the Delete statement, enter Delete (SQL) as the search phrase and topic from the online help.

Commit is an operation to cause all changes made since the previous Commit to be flushed to the database, which causes the changes to take effect. The Commit statement is not related to any specific data records. For more information on the syntax of the Commit statement, enter Commit (SQL) as the search phrase and topic from the online help.

Stored Procedures, Functions, and PL/SQL Blocks

Some database environments, such as Oracle, support the concept of stored procedures and functions, which are often written using procedural language extensions (PL) to standard SQL. A serious developer can write his own stored procedures and functions. You should look up the required syntax in an appropriate manual such as the Oracle7 PL/SQL manual. The following code fragment is a PL/SQL block that builds a table and populates it with data:

EXEC SQL AT Oracle CREATE TABLE MyTest (EMPID NUMBER)

EXEC SQL AT Oracle BEGIN &

  FOR i IN 1..10 LOOP &

    INSERT INTO MyTest(EMPID) VALUES (i*2); &

  END LOOP; &

  COMMIT; &

END;

The following code fragment demonstrates how to create a stored procedure in Oracle and then reference that procedure from Oracle Basic:

REM Declare the DOIT() procedure.

EXEC SQL AT Oracle CREATE OR REPLACE PROCEDURE doit &

  ( t_id IN NUMBER, amount IN NUMBER ) AS &

  BEGIN &

    UPDATE MyTest SET EMPID = amount WHERE EMPID = t_id; &

  END;

REM Now Reference DOIT() with appropriate values.

EXEC SQL AT Oracle BEGIN doit( 2, 13 ); COMMIT; END;

Extending the Functionality of Power Objects

Oracle designed Power Objects to promote ease of use with existing standards. Power Objects can embed and interact with OCX controls in an application. It can also link to dynamic link libraries (DLLs) under Microsoft Windows and to external commands (XCMDs) and functions (XFCNs) on the Macintosh. Oracle will add OpenDoc support in a future release to promote externally reusable code resources across OS/2 Warp and Macintosh. Power Objects supports object linking and embedding (OLE 2.0) under Microsoft Windows but not OLE automation. Limited OLE automation is planned for a future release.

Oracle Power Objects has a built-in mechanism for extending its functionality through the use of classes. A developer can create a class to perform a basic function not inherent in the basic Power Objects environment. Multiple applications can share and reuse the classes.

To demonstrate its commitment to standards and extensibility, Oracle Power Objects Version 2.0 will either include bundled OCX controls or will supply information on how to obtain some off the Internet. Another reflection of Oracle's commitment to extensibility is that Oracle is placing a ObjectShop or MarketPlace on its World Wide Web site (http://www.oracle.com) where developers can upload or purchase and download objects for use in Power Objects. Refer to Chapter 36, ÒIntroduction to Oracle Power Objects,Ó for more information.

Summary

Oracle Power Objects provides a wide range of constants, commands, and functions for the application developer to use when writing custom code to solve his database application needs. The constants, commands, and functions maintain consistency with ANSI BASIC and provide the necessary object-oriented and SQL-related extensions to ensure that Power Objects is a powerful and flexible database application development environment.

Oracle Power Objects provides numerous built-in SQL and custom data-related operations. You can enhance these default behaviors through Oracle Basic. Ultimately, the strength of a visual tool is measured in the strength of the underlying programming language available to the developer. With object and SQL extensions, Oracle Basic is powerful and flexible. Through its use of dynamic link libraries, Apple Script, OCXs, and soon OpenDoc, Power Objects is truly a powerful application development environment.