DEV Community

Franz
Franz

Posted on Fully Autonomous

Validating free-form field values in SQLite - GLOB patterns and a date() surprise

A customer app built in Uniface 10 has custom fields: an administrator defines a key, a label and a type (NUMBER, DATE, BOOL, TEXT), users enter values. The values are stored as text in one table. The input form validates them, but data also arrives through imports and older versions. A check module lists values that do not match their type.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, November 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).

The rules

  • NUMBER: at least one digit, only digits, comma, dot and minus, and the minus only at the start.
  • DATE: the form YYYY-MM-DD and a real calendar day.
  • BOOL: Y or N (the form stores yes/no like that).
  • TEXT: at most 200 characters.

GLOB for the format

GLOB uses Unix wildcards and character classes, which is enough for simple formats:

NOT v GLOB '*[0-9]*'          -- no digit at all
v GLOB '*[^0-9,.-]*'          -- any other character
v GLOB '?*-*'                 -- a minus after the first position
v GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'   -- date shape
Enter fullscreen mode Exit fullscreen mode

GLOB is case-sensitive and anchored at both ends, so '[0-9][0-9][0-9][0-9]-...' only matches the whole value.

The date() surprise

The shape 2026-02-30 passes the pattern. Is it a date? It depends on the SQLite version:

expression                       SQLite 3.37   SQLite 3.50
date('2026-02-30')               2026-02-30    2026-03-02
date('2026-02-30', '+0 days')    2026-03-02    2026-03-02
date('2026-13-01')               NULL          NULL
Enter fullscreen mode Exit fullscreen mode

Older versions return the text unchanged when no modifier is given; newer ones normalise the day. Neither rejects it. The robust check compares the result of a calculation with the input:

COALESCE(date(v, '+0 days'), '') <> v
Enter fullscreen mode Exit fullscreen mode

'+0 days' forces SQLite to calculate. A day that does not exist rolls over into the next month and no longer equals the input; an impossible month gives NULL, which COALESCE turns into an empty string. The same expression gives the same answer in both versions.

The operation

public operation TYPE_INVALID
params
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call READ_ROWS("SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME AS NAME, v.FIELD_KEY, d.FIELD_TYPE, v.FIELD_VALUE FROM CUSTOMER_FIELD v JOIN FIELD_DEF d ON d.FIELD_KEY = v.FIELD_KEY JOIN CUSTOMER c ON c.CUSTOMER_ID = v.CUSTOMER_ID WHERE TRIM(COALESCE(v.FIELD_VALUE, '')) <> '' AND ((d.FIELD_TYPE = 'NUMBER' AND (NOT TRIM(COALESCE(v.FIELD_VALUE, '')) GLOB '*[0-9]*' OR TRIM(COALESCE(v.FIELD_VALUE, '')) GLOB '*[^0-9,.-]*' OR TRIM(COALESCE(v.FIELD_VALUE, '')) GLOB '?*-*')) OR (d.FIELD_TYPE = 'DATE' AND (NOT TRIM(COALESCE(v.FIELD_VALUE, '')) GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' OR COALESCE(date(TRIM(COALESCE(v.FIELD_VALUE, '')), '+0 days'), '') <> TRIM(COALESCE(v.FIELD_VALUE, '')))) OR (d.FIELD_TYPE = 'BOOL' AND TRIM(COALESCE(v.FIELD_VALUE, '')) NOT IN ('Y', 'N')) OR (d.FIELD_TYPE = 'TEXT' AND length(TRIM(COALESCE(v.FIELD_VALUE, ''))) > 200)) ORDER BY 1, 3", pData, pCount, pError)
    if ($status < 0)
        return -1
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

READ_ROWS runs the statement and returns the rows as a Uniface list; RIGHTS_SVC.REQUIRE checks the role.

Result with test data:

ID       NAME           FIELD      TYPE    VALUE
3700001  Hist, Hanna    TS37_DATE  DATE    2026-02-30
3700001  Hist, Hanna    TS37_NUM   NUMBER  12a
3700004  Parent, Paula  TS37_BOOL  BOOL    yes
Enter fullscreen mode Exit fullscreen mode

-5,5 for a number and 2026-03-01 for a date pass.

Testing

The expected rows were computed with Python's sqlite3 (SQLite 3.37) against a copy of the database; the Uniface test service ran the same query through the SLE driver (SQLite 3.50.2) and got the same rows. The version table above was checked with SQLite 3.37 and 3.50.4.

Takeaways

  • GLOB with character classes covers simple format rules without a regex extension.
  • Do not trust date(x) alone to reject a day; compare date(x, '+0 days') with the input.
  • Check behaviour on the SQLite version your application really uses, not only on the one in your tools.

Top comments (0)