DEV Community

Franz
Franz

Posted on

A customer maintenance form in Uniface 10, part 1 - from an empty IDE to working CRUD on SQLite

#database #tutorial #programming #softwaredevelopment

Most "how to build a CRUD screen" tutorials use a framework that ships with a scaffolding command. This one does not. This is Rocket Uniface 10.4 Community Edition, a 4GL that still runs a lot of insurance, healthcare and public-sector software, and that almost nobody blogs about.

The goal for this series: a customer maintenance form, stored in a local SQLite file, that behaves like a business application rather than a demo. Over three parts it grows into:

  • Part 1 (this post): the database, the assignment file, a modeled entity, the painted form, and a component script that can search, create, update and delete.
  • Part 2: the things that make it usable - safe ID assignment, "you have unsaved changes" handling, a real contains-search with sorting, a duplicate check.
  • Part 3: moving the business rules into a service and writing an automated ProcScript test suite - plus a two-stage bug that only shows up once you start clicking around.

Everything runs in the Community Edition, which is free, so you can follow along.

What it looks like in the end 

+--------------------------------------------------+
| Search: [ meier          ] ( Search )            |
| 2 customers                                      |
+--------------------------------------------------+
| Last name | First name | E-mail                  |
| Meier     | Lars       | lars@example.com        |
| Müller    | Anna       | anna.mueller@example.com|
+--------------------------------------------------+
| Customer ID: [ 5 ]   Created at: [ 20-sep-26 ]   |
| Last name:   [ Meier ]  First name: [ Lars ]     |
| E-mail:      [ ... ]    Phone:      [ ... ]      |
+--------------------------------------------------+
| ( New ) ( Save ) ( Delete ) ( Cancel )           |
+--------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

Four building blocks:

CUSTOMER_FRM (Form)            CUSTOMER.CUSTOMER_MDL (modeled entity)
 - search / grid / detail  -->  - maps to the CUSTOMER table via an ASN path
 - buttons call entries         - field syntax = validation

CUSTOMER_SVC (Service)         CUSTOMER_TST_SVC (Service)
 - business rules (part 3) <--  - automated tests (part 3)
Enter fullscreen mode Exit fullscreen mode

Step 1: the database

Nothing exotic - a plain SQLite file, created with any SQLite client:

CREATE TABLE IF NOT EXISTS CUSTOMER (
  CUSTOMER_ID INTEGER      NOT NULL PRIMARY KEY,
  LAST_NAME   VARCHAR(50)  NOT NULL,
  FIRST_NAME  VARCHAR(50)  NOT NULL,
  EMAIL       VARCHAR(100),
  PHONE       VARCHAR(30),
  CREATED_AT  DATETIME     NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

One thing to know up front: the Uniface IDE can execute SQL for you (Actions -> EDIT SQL on a path), but that editor sends the whole buffer as one script and does not commit automatically. If your CREATE TABLE seems to vanish, add an explicit COMMIT; at the end of the script.

Step 2: the assignment file - where Uniface learns about your database

This is the part that trips up everyone who comes from a framework with a connection string in a config file. In Uniface, the mapping "entity -> database" lives in an assignment file (.asn), and the IDE uses its own: ide.asn.

Two sections matter:

[PATHS]
$CUSTOMERS   SLE:C:\Uniface_Projects\CustomerApp\customers.db

[ENTITIES]
*.CUSTOMER_MDL   $CUSTOMERS:*.*
Enter fullscreen mode Exit fullscreen mode
  • $CUSTOMERS is a logical path - a named database connection.
  • SLE is the driver mnemonic for SQLite. You do not invent it; the shipped common\adm\dbms.asn defines the mnemonics (SLE for SQLite, with create db = on, which is why Uniface happily creates the file if it does not exist).
  • *.CUSTOMER_MDL $CUSTOMERS:*.* means: every entity of the model CUSTOMER_MDL lives in $CUSTOMERS, table name = entity name.

Three practical notes:

  1. In the Community Edition, ide.asn sits under C:\Program Files\...\uniface\adm\, which is write-protected. Editing it in Notepad looks like it works and then silently does nothing. Prepare the file somewhere else and copy it over with an elevated confirmation - and keep a .bak.
  2. Close the IDE before editing, and restart it afterwards. Assignment files are read at startup.
  3. For running the app outside the IDE, put the same lines in your own customers.asn in the project folder and add the shipped DBMS definitions: 
#file usyscom:adm\dbms.asn

[PATHS]
$CUSTOMERS   SLE:C:\Uniface_Projects\CustomerApp\customers.db

[ENTITIES]
*.CUSTOMER_MDL   $CUSTOMERS:*.*
Enter fullscreen mode Exit fullscreen mode

Step 3: the modeled entity

In Uniface the application model is where a table's structure and its validation rules live - not in the form. Create a model CUSTOMER_MDL and an entity CUSTOMER in it:

Field Data type / interface Field syntax
CUSTOMER_ID Numeric / I4 primary key
LAST_NAME String / C50 LEN(0-50)
FIRST_NAME String / C50 LEN(0-50)
EMAIL String / C100 LEN(0-100)
PHONE String / C30 LEN(0-30)
CREATED_AT Combined date/time / E -

Two settings on the entity itself:

  • Database Behavior: In Database.
  • Database Path: leave it at DEF. The field only accepts three characters, which is a nice trap if you try to type CUSTOMERS there. The real assignment happens through [ENTITIES] in the ASN, as above.

Pitfall 1: field syntax LEN(1-n) on optional fields

My first version used LEN(1-100) for the optional e-mail, reading it as "1 to 100 characters". It is not a "if filled, then" rule - an empty field fails it. Saving a customer without an e-mail produced:

store status -1, $procerror -300  (UVALERR_SYNTAX)
Enter fullscreen mode Exit fullscreen mode

LEN(0-100) is the correct syntax for "optional, at most 100 characters".

Pitfall 2: the generated read trigger throws

When you create a modeled entity, Uniface fills its triggers from templates. The read trigger template starts with:

trigger read
throws
  read
end
Enter fullscreen mode Exit fullscreen mode

throws means "exceptions from this trigger propagate". Retrieving from an empty table then produced an uncaught exception and killed the running form:

Uncaught exception: -2 <UIOSERR_OCC_NOT_FOUND>
Enter fullscreen mode Exit fullscreen mode

Removing the single word throws from the entity's read trigger fixed it. Worth remembering: the template triggers are suggestions, not infrastructure.

Step 4: painting the form

A Uniface form is painted, not laid out in code. CUSTOMER_FRM contains four entities:

  • SEARCH_DMY.NOMODEL - a non-database entity holding SEARCH_TEXT (edit box), BTN_SEARCH (button) and RESULT_INFO (static text for the hit count).
  • LIST_DMY.NOMODEL - the result grid with L_LAST_NAME, L_FIRST_NAME, L_EMAIL.
  • CUSTOMER.CUSTOMER_MDL - the detail area, one occurrence visible.
  • ACTION_DMY.NOMODEL - the four buttons.

The .NOMODEL suffix is how you paint an entity that has no model behind it; you type the name including the suffix in the paint dialog.

A few things I only learned by doing it:

  • Grid column headers stay empty unless each grid field has an associated label - a label object whose name is identical to the field name (L_LAST_NAME label for the L_LAST_NAME field). The label's text becomes the column header.
  • Grid fields should have the syntax NCR,NED (no carriage return, no entry) so the grid is a read-only list.
  • CUSTOMER_ID and CREATED_AT get NED - the user never types them.
  • Widget properties only stick when set through the "..." dialog. Typing a ;-separated list like BACKCOLOR=#2563EB;FORECOLOR=#FFFFFF directly into the property cell looks fine and has no effect, because the stored format uses Uniface's own separator (&uSEP;), not a semicolon. Extra parameters that have no row in the dialog (for example HEADERCOLOR on a grid) hide behind a "More..." button in that dialog.
  • Inside the IDE, do not press Ctrl+A. It is mapped to ^ACCEPT and closes the dialog you are in. Use Ctrl+Home / Ctrl+Shift+End to select text.

The buttons themselves contain no logic at all - each detail trigger just calls a component entry:

trigger detail
    call DO_SAVE
end
Enter fullscreen mode Exit fullscreen mode

That single habit is what later makes the logic easy to move into a service.

Step 5: the component script

The whole behaviour of the form sits in its component script: one operation exec, a quit trigger and a handful of entries. Here are the load-and-display parts.

Reading and filling the list 

operation exec
    $sortField$ = "LAST_NAME"
    $sortDir$ = "a"
    call LOAD_LIST
    edit
end

entry LOAD_LIST
variables
    string vWhere, vProps
endvariables
    clear/e "CUSTOMER"
    vProps = ""
    activate "CUSTOMER_SVC".BUILD_SEARCH_WHERE(SEARCH_TEXT.SEARCH_DMY, vWhere)
    if (vWhere != "")
        putitem/id vProps, "WHERE", vWhere
    endif
    $entityproperties(CUSTOMER) = vProps
    retrieve/e "CUSTOMER"
    if ($status < 0 & $status != -1 & $status != -2)
        clear/e "CUSTOMER"
        clear/e "LIST_DMY"
        RESULT_INFO.SEARCH_DMY = ""
        message/error $concat("The customers could not be read (error ", $procerror, ").")
        return -1
    endif
    setocc "CUSTOMER", -1
    if ($totdbocc(CUSTOMER) < 1)
        clear/e "CUSTOMER"
    else
        sort "CUSTOMER", $concat($sortField$, ":", $sortDir$, " ci")
    endif
    call FILL_LIST
    return 0
end
Enter fullscreen mode Exit fullscreen mode

(The BUILD_SEARCH_WHERE call and the WHERE property are the subject of part 2 - in the first version this was a plain retrieve/e.)

Pitfall 3: retrieve returns -1 and -2 as normal results

My first version treated any negative $status after retrieve/e as an error and showed a message box. That is wrong: -1 and -2 mean "nothing found", not "broken". So the check is $status < 0 & $status != -1 & $status != -2. Without that, an empty search result pops up an error dialog - which looks exactly like a bug to a user.

Mirroring the hitlist into the grid

The grid is a separate, non-database entity, so it has to be filled occurrence by occurrence. Keeping the indexes aligned is what makes "click a row -> show that customer" trivial later:

entry FILL_LIST
    clear/e "LIST_DMY"
    if ($totdbocc(CUSTOMER) > 0)
        forentity "CUSTOMER"
            creocc "LIST_DMY", -1
            L_LAST_NAME.LIST_DMY  = LAST_NAME.CUSTOMER
            L_FIRST_NAME.LIST_DMY = FIRST_NAME.CUSTOMER
            L_EMAIL.LIST_DMY      = EMAIL.CUSTOMER
        endfor
        setocc "CUSTOMER", 1
        setocc "LIST_DMY", 1
    endif
    call UPDATE_COUNT
    return 0
end

entry UPDATE_COUNT
variables
    numeric vCount, vCur
endvariables
    vCur = $curocc(CUSTOMER)
    vCount = 0
    forentity "CUSTOMER"
        if ($dbocc(CUSTOMER) > 0)
            vCount = vCount + 1
        endif
    endfor
    if (vCur > 0)
        setocc "CUSTOMER", vCur
    endif
    if (vCount = 0)
        RESULT_INFO.SEARCH_DMY = "No customers found"
    elseif (vCount = 1)
        RESULT_INFO.SEARCH_DMY = "1 customer"
    else
        RESULT_INFO.SEARCH_DMY = "%%(vCount) customers"
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

Note $dbocc(CUSTOMER) - it tells you whether the current occurrence exists in the database. It is the honest way to count real rows (an empty new occurrence does not count), and in part 3 it turns out to be the cleanest way to answer "is this a new record?".

Note also "%%(vCount) customers": %%() is ProcScript's string substitution. It keeps $concat calls short, which matters - $concat takes a limited number of arguments, and with six or more you have to nest calls.

Selecting a row by ID 

entry SELECT_BY_ID
params
    string pId : IN
endparams
    if (pId = "")
        return 0
    endif
    forentity "CUSTOMER"
        if (CUSTOMER_ID.CUSTOMER = pId)
            setocc "LIST_DMY", $curocc(CUSTOMER)
            return 0
        endif
    endfor
    setocc "CUSTOMER", 1
    setocc "LIST_DMY", 1
    return 0
end
Enter fullscreen mode Exit fullscreen mode

Deleting, with a confirmation 

entry DO_DELETE
variables
    numeric vOcc, vStatus, vError
endvariables
    if ($dbocc(CUSTOMER) < 1)
        message/info "This customer has not been saved yet. Use Cancel to discard it."
        return 0
    endif
    askmess/question $concat("Do you really want to delete the customer ", FIRST_NAME.CUSTOMER, " ", LAST_NAME.CUSTOMER, "?~Delete customer"), "Yes,No"
    if ($status != 1)
        return 0
    endif
    vOcc = $curocc(CUSTOMER)
    remocc "CUSTOMER"
    store/e "CUSTOMER"
    vStatus = $status
    vError  = $procerror
    if (vStatus < 0)
        rollback
        message/error $concat("The customer could not be deleted (status ", vStatus, ", error ", vError, ").")
        call LOAD_LIST
        return -1
    endif
    commit
    setocc "LIST_DMY", vOcc
    remocc "LIST_DMY"
    setocc "LIST_DMY", $curocc(CUSTOMER)
    call UPDATE_COUNT
    message/info "The customer was deleted."
    return 0
end
Enter fullscreen mode Exit fullscreen mode

askmess/question "<text>~<title>", "Yes,No" gives you a real message box; $status is the 1-based index of the clicked answer. Three answers work just as well ("Save,Discard,Stay") - part 2 uses that.

Pitfall 4: rollback resets $procerror

Look closely at the error handling above:

    store/e "CUSTOMER"
    vStatus = $status
    vError  = $procerror
    if (vStatus < 0)
        rollback
Enter fullscreen mode Exit fullscreen mode

The status and the error code are captured before the rollback. I originally wrote the natural-looking version:

    if ($status < 0)
        rollback
        message/error $concat("... error ", $procerror, ").")
Enter fullscreen mode Exit fullscreen mode

and every failure reported error 0. rollback is itself a statement that sets $status/$procerror. Same applies to any other statement between the failure and the message.

Closing with unsaved changes 

trigger quit
    if ($instancedbmod = 1)
        askmess/question "There are unsaved changes. Do you want to close without saving?~Close", "Yes,No"
        if ($status != 1)
            return -1
        endif
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

$instancedbmod is "anything in this component instance differs from the database". Its per-entity sibling is $occdbmod(ENTITY) for the current occurrence. Returning -1 from quit cancels the close.

Step 6: compiling, and the warnings you will see

Compiling the form gives:

Compile Form: 'CUSTOMER_FRM'
Phase 2: Model definitions
  warning: 1016 - (Fields for) entity SEARCH_DMY not found in application model, generating now...
  warning: 1016 - (Fields for) entity LIST_DMY  not found in application model, generating now...
  warning: 1016 - (Fields for) entity ACTION_DMY not found in application model, generating now...
Phase 4: Form definitions
  warning: 1076 - Path to painted entity CUSTOMER.CUSTOMER_MDL not found.
Compilation done: [info 2, warnings 4, errors 0]
Enter fullscreen mode Exit fullscreen mode

Both are harmless here and worth understanding:

  • 1016 appears for every .NOMODEL entity. There is no model definition, so Uniface generates one on the fly. Expected.
  • 1076 says the compiler cannot resolve the database path of the painted entity at compile time - because it is resolved at runtime through [ENTITIES] in the assignment file. If you ever get a runtime error instead, that is when this warning was real.

What part 1 gives you

At this point the form does the full CRUD cycle: search, pick a row, edit, save, delete, cancel, and it refuses to close silently with unsaved changes. Mandatory-field and e-mail checks run in DO_SAVE before the store.

What it does not do yet is behave well under pressure: two users creating customers at the same second will fight over IDs, clicking another row throws your edits away without asking, the search is an exact-match affair, and nothing stops you from entering the same customer twice.

That is part 2.

Quick reference: the errors from this post

Symptom Cause Fix
Uncaught exception -2 <UIOSERR_OCC_NOT_FOUND> throws in the generated read trigger, empty result set remove throws
Error box on an empty search retrieve returns -1/-2 for "nothing found" treat -1/-2 as a normal result
store fails with -300 <UVALERR_SYNTAX> LEN(1-n) on an optional, empty field use LEN(0-n)
Error message always shows error 0 rollback resets $procerror capture $status/$procerror first
Grid columns have no headers no associated labels add labels named exactly like the fields
Widget colours have no effect typed ; list is not the stored format set them in the "..." dialog
A dialog closes while you type Ctrl+A is ^ACCEPT in the IDE use Ctrl+Home / Ctrl+Shift+End

Top comments (3)

Collapse
 
elijahbrown profile image
Elijah Brown •

The email and phone columns on CUSTOMER are the contact half. For sample rows while you build parts 2 and 3, use reserved fiction phones (US 555-0100 to 555-0199, UK 020 7946 0xxx) and addresses on domains you control, so a shared screenshot or leftover dump cannot publish a real person.

Collapse
 
f345345dfg profile image
Franz •

Good point, thanks. The sample addresses in the series use example.com (reserved by RFC 2606), and I've just switched the one example.de address in part 3 to example.com. I'll use the reserved fiction ranges (555-0100 to 555-0199, 020 7946 0xxx) for phone numbers in the next parts and in the test data, so nothing in a screenshot or a leftover dump can point to a real person.

Collapse
 
elijahbrown profile image
Elijah Brown •

Thanks for making that change. Using reserved values throughout the later parts and test data should keep the examples safe and reproducible.