- Why should I save my Forms in the database?
Forms should always be saved in the database because:
- Only Forms stored in the database can be documented with the provided facilities;
- To reference or copy a Form's objects (procedures, triggers, blocks, fields, text pages) that Form must be stored in the database; and
- Forms stored in the database are automatically backed up with the database.
- Why is a List of Values so slow?
Unlike a normal SQL*Forms query, which performs buffering, a List of Values (LOV) will read all of the records queried from the database before displaying them.
LOV's can be replaced with separate Forms to reduce the number of records transmitted from the database and to save huge amounts of memory.
- Why shouldn't I overuse SYSDATE?
Remember "form_field_x := SYSDATE;" will translate into a
SELECT SYSDATE FROM DUAL;
statement to be issued against the database.The same apply to USER, UID and USERENV.So, it is much better to use:
screen_field_a := SYSDATE;
screen_field_b := screen_field_a;
screen_field_a := SYSDATE;
screen_field_b := SYSDATE;
- Why should I use database integrity constraints?
Use database integrity constraints on all your entities (tables) to denote primary and foreign key's, uniqueness and check values.With "DEFAULT BLOCK GENERATION" this information will be used by Forms to generate some of the validation and triggers on your behalf.
- In what triggers can't I use DML statements?
Do not use DML (Data Manipulation Language) statements such as INSERT, UPDATE, and DELETE in any triggers apart from commit time transactional triggers (PRE-INSERT, POST-UPDATE...).
They can actually be used without causing a syntax error in most triggers, but this is generally to be avoided as it can de-synchronize the state of records in SQL*Forms and rows in the database, and can cause unexpected behavior.The reason for this is that if you perform any DML the status of the Form does not change as it would, had you performed the operation on a default block.So if you exit the form without performing a commit, all your changes are lost as Forms does not know about them, and will not prompt you to COMMIT/ROLLBACK.Unlike DML commands, DDL (Data Definition Language) and DCL (Data Control Language) are not legal in any SQL*Forms triggers.
- Why can't one use DDL statements in a Form?
DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not supported in SQL*Forms because your Form is not suppose to manipulate the database structure.
- In what triggers anyone can use restricted package procedures?
All triggers can do SELECTs and call unrestricted packaged procedures but only KEY triggers and the ON-NEW-FIELD-INSTANCE trigger can call restricted package procedures.
- Should anyone use SQL or SQL*Forms statements?
Try not to issue SQL statements if the goal can be accomplished with SQL*Forms statements (eg.field assignment, DEFAULT_VALUE, etc.).Because SQL*Forms statements executes within Forms, it is much faster and does not use cursors.
- Why shouldn't I use KEY-NXTFLD and KEY-PRVFLD triggers?
Replace KEY-NXTFLD and KEY-PRVFLD with ON-VALIDATE-FIELD, ON-VALIDATE-RECORD and ON-NEW-FIELD-INSTANCE triggers as KEY-NXTFLD and KEY-PRVFLD will neither trigger in a block mode (mainframe) environment nor with mouse movement in a GUI bit-mapped environment.
- Why and how should I use database packages, procedures and functions?
One should write DATA API's (Application programming interfaces) or packages on the database utilizing stored functions and procedures.This API should then be used to replace all SQL access to the database.It can be build in an Object Oriented fashion based on your Entity Relationship Diagram or Function Hierarchy Diagram.
The names you choose should be readable and reflect functionality.
Some of the advantages of this approach are:
- Ensure that code is centrally maintained and not duplicated in more than one Form;
- Different application interfaces can be written in different languages (English, Greek, etc.) utilizing different GUI builders (Forms, C++, SQLWindows, etc.) because the client interface doesn't contain any complicated logic;
- This will hide database complexities from the Forms designers;
- Will always ensure data integrity because tables can't be directly accessed (Insert/Update);
- This method is particularly suited for Client/Server deployment because it minimize network I/O;