Re: Beginner needs help with SQL-forms

In article <4qa0me$7g2_at_pulp.ucs.ualberta.ca>,

   markadam_at_gsb206.srv.ualberta.ca (M Adam) wrote:
>Help! I am using SQL-forms(3.0) to write an application
>for job-description Database. The data looks like this
>
>
>JOB_ID_NUMBER (Primary field starts at 100000 is sequential to 100078)
>[other fields tied to the same block]
>
>[A secondary, detail block of months in which each job is performed]
>
>What I want is for the user to come up in a new form
>fill out the job-id and after hitting [nxtfld] have the form
>go to that job id(if it exists) using GO_RECORD. I was trying
>to use ROWID variables inside the NXTFLD trigger to compute
>the record they should go to, but I keep error
>
>'your variable must be of a valid type or sub-type'
>
>SQL forms does not seem to want to accept ROWID variables,
>despite my copying an example from the Oracle PL/SQL manual(2.0)
>eg DECLARE
> my_row ROWID;
>
>from a anonymous trigger(in a procedure doesn't work either)
>These manuals are all I have to learn from and it is driving me crazy.
>
>If there is something I am missing or a better way
>to do this I would greatly appreciate any advice.

If I understand this correctly, what you really want is for the form to start up in query mode and then perform a query when the user hits the NEXT FIELD key. The first part may be easily accomplished simply by using a KEY-STARTUP trigger to enter query mode (ENTER_QUERY). The second could be done by writing a custom KEY-NEXTFLD trigger in the JOB_ID field, but I would recommend against this for reasons too numerous to mention - some of which are:

1. Do you want it to query *EVERY* time the user does NEXT-FIELD in JOB_ID? If not, how will you prevent it? (It can be done, but... see #2)
2. A very important concept in designing front-ends is consistency! You really don't want to confuse the users by making the same keys behave radically different in different fields or within the same field in different contexts! I have found that even starting some forms in query mode and starting others in normal mode can confuse many users.

  ...

Why not just teach the users to hit EXECUTE QUERY instead of NEXT FIELD?

Several things you mentioned earlier betray your inexperience with SQL*Forms. This is not meant as pointless criticism - I suspect that you need to spend a *LOT* more time with the manuals - and experimenting! (I wrote very complex and sophisticated SQL*Forms for six years and still occasionally find new techniques and bizzare obscurities!)

• How did you ever hope to *COMPUTE* a rowid based on your JOB_ID???????????? (I don't think even God could do that!)
• GO_RECORD takes an integer argument to navigate to an existing record within the block. It will *never* fetch anything from the database! The integer parameter is just the sequence number of the record in the block and is dependent even upon the ORDER_BY clause. It certainly is not even remotely related to rowid! (Purists could argue about the order in which records are fetched with no ORDER_BY clause, but that is *really* esoteric!)
• SQL*Forms uses only PL/SQL 1.x, not PL/SQL 2.0! You can't use everything in PL/SQL 2.0 in SQL*Forms! However, SQL*Forms does understand ROWID very well! That is how it works internally - how it knows which record(s) to update or delete! You too can use the ROWID in SQL*Forms even though you will never see it as a field in the designer or in the .inp file! It is in fact the fastest way to access an on-screen record in the database (if you wanted to conditionally show addition fields in a control block, for example). Try writing a block level KEY-HELP trigger on a table based block that looks something like: "SELECT 'This record had a rowid' INTO :BLOCK_NAME.SOME_CHAR25_NON_DATABASE_FIELD FROM BASE_TABLE_NAME WHERE BASE_TABLE_NAME.ROWID = :BLOCK_NAME.ROWID;" What happens when you hit HELP on a new record (neither retreived nor saved)? What happens when you hit the HELP key on a "freshly" queried record? On an updated record? Try the latter before and after doing a COMMIT. What happens in each case? Why?

   ...The final exam will be when you release the application to the users!

-OraSaurus

PS: I would recommend the book "The Oracle Forms Developers Companion"

    to anyone seriously working with SQL*Forms V3, no matter how     experienced they may be. Received on Thu Jun 27 1996 - 00:00:00 CEST