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 formYYYY-MM-DDand a real calendar day. -
BOOL:YorN(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
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
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
'+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
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
-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
-
GLOBwith character classes covers simple format rules without a regex extension. - Do not trust
date(x)alone to reject a day; comparedate(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)