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.
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.
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.
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.
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.
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.
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.
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;
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.
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.