I’m a longtime user of Carl’s CW Assistant. I’ve wished it had some basic SQL generation capability.
I just used Claude to create a Database Reference for my huge DCT that I can host on our website for internal use and for some power users. It can create basic SELECT statements for tagged fields in a selected table. Feel free to have the AI expand upon this, as I will be doing as well.
Just upload your app’s .dctx XML file and this prompt and update the prompt to include your APP NAME:
I've attached my Clarion application's data dictionary (.dctx, and optionally the
.txd version of the same dictionary). Please build me a single self-contained HTML
file that acts as a browsable, searchable reference for the whole database schema —
something my team can host as a static page and use day to day.
APP NAME: <<< INSERT YOUR APPLICATION/DATABASE NAME HERE, e.g. "Naviteer" >>>
Use the .dctx as the source of truth (it's clean XML); the .txd is the same data in
Clarion's native format and can be ignored if .dctx is present.
WHAT TO EXTRACT
- Every real <Table> element: name, description, prefix, driver (MSSQL / TOPSPEED /
MEMORY / ASCII / BASIC / none), physical path (e.g. dbo.TableName for SQL Server
tables), and usage.
- <Alias> elements are alternate names for a real table (used for self-joins etc.) —
don't list them as separate tables in the browser, since that's just noise. Instead
fold each one into a small "also referenced in code as: X, Y" note on the real table
it belongs to.
- Fields, recursively — Clarion GROUP fields contain nested child <Field> elements
(used to reinterpret the same memory region under different sub-fields). Extract
these recursively and let a GROUP row expand to reveal its children in the UI.
- Watch for a pipe-delimited suffix in ExternalName, e.g. "SHP_ID | READONLY" — the
part before "|" is the real physical column name; anything after it is a flag
(READONLY usually means an identity/computed column). Split these apart and surface
the flag as a small badge rather than treating it as part of the name.
- Keys/indexes per table, with their component fields resolved (Key > Component >
FieldId is a GUID reference to a Field within the same table) in the correct
composite order, noting which key is Primary and which are Unique.
- Relationships: top-level <Relation> elements reference PrimaryTable/ForeignTable/
PrimaryKey/ForeignKey and PrimaryMapping/ForeignMapping field pairs, all by GUID.
Resolve these into two lists per table — "links to" (this table's FK to a parent)
and "linked from" (child tables pointing back at this one) — each showing the join
field(s) and the delete rule. Some relations in real-world dictionaries are missing
a resolvable ForeignKey/mapping (legacy/alias-based definitions) — still show these,
just flagged as incomplete rather than dropped or crashing the build.
NAMING CONVENTION
Clarion refers to a field as PREFIX:FieldName (colon); the physical column name (for
SQL Server–backed tables) is typically PREFIX_FieldName (underscore) — that's the
cleaned ExternalName from above. Not every table has a SQL Server equivalent (TopSpeed
ISAM files, in-memory queues, flat files don't), so don't assume one exists.
UI REQUIREMENTS
- One HTML file, no build step, no server — embed the extracted schema as inline JSON.
- Page header (top left) should read "<APP NAME> | DATABASE REFERENCE" using the
app name given above — e.g. "Naviteer | DATABASE REFERENCE" — and the browser tab
title should follow the same convention.
- Two-pane layout: a searchable/filterable table list (by name, description, prefix,
or field name) plus a detail pane.
- Detail pane shows Fields / Keys & Indexes / Relationships as an expandable
drilldown — collapsible sections, with GROUP fields expanding to their children.
- Clicking a relationship jumps straight to the related table.
- Deep-linkable URLs (so a specific table can be bookmarked/shared) with a visible
"Copy link" button — don't rely on people noticing the address bar changed.
- A small copy-to-clipboard icon next to every field name and every table's physical
path, so someone can grab the SQL column/table name for an email or a query without
retyping it. For tables/fields with no SQL Server equivalent, fall back to copying
the qualified Clarion name instead.
- Query builder — ONLY if the dictionary contains at least one SQL Server–backed
table: checkboxes next to fields that have a real SQL column, and a live-updating
SELECT statement (with Select All / Clear / Copy SQL) pinned to the bottom of the
screen as fields are checked. Skip this feature entirely (don't just disable it) if
the dictionary has no SQL Server tables at all — there's nothing to query.
- Mobile-responsive and reasonably print-friendly. Should look like a proper internal
engineering tool — not a generic template.
Before handing it back, actually verify it works: confirm the embedded JSON parses,
there's no leftover template placeholder text, and that search, table selection,
relationship navigation, and the copy buttons all function.
Enjoy!
Greg Berthume
www.datafortress.app
