SQL templates (xlyEntry/templesql/)

The framework keeps a small library of SQL scaffolds under xlyEntry/src/main/resources/templates/templesql/. They are not used at runtime to generate SQL on the fly — they're templates an engineer copies and fills in when authoring a new stored procedure that the framework will invoke through generic procedure dispatch.

The scaffolds shipped

Eight files ship in the live tree (xlyEntry/src/main/resources/templates/templesql/):

File	What it scaffolds
sSaveProName.sql	A standard save procedure with the conventional parameter list and OUT codes
sSaveProNameBefore.sql	A pre-save validation procedure
sDeleteProName.sql	A standard delete procedure
sProcName.sql	A generic logic procedure (audit/calc/check)
sButtonParam.sql	A procedure invoked by a button press, parameterised through gdsconfigformslave.sButtonParam
sp_btn_calc.sql	A button-driven calculation procedure
sProTempleByDb.sql	A procedure that switches behaviour by database / tenant
sSqlStr.sql	A SELECT-shape scaffold for gdsconfigformmaster.sSqlStr

Placeholder convention

Every scaffold uses {placeholderName} syntax (single braces, no escaping). A real-world example from sSaveProName.sql:

DROP PROCEDURE IF EXISTS {sProName};
CREATE PROCEDURE {sProName}(
    IN sGuid VARCHAR(100),
    IN sFormGuid VARCHAR(100),
    IN sLoginId VARCHAR(100),
    IN sBrId VARCHAR(100),
    IN sSuId VARCHAR(100),
    OUT sCode INT(1),
    OUT sReturn LONGTEXT
)
BEGIN
    -- … placeholder logic …
    Update {sMasterTableName} AS A
        JOIN {sSlaveTableName} AS B
        ON A.sId = B.sProductId
    SET A.dProductQty = B.dProductQty
    WHERE B.sId = sGuid
      AND B.sBrandsId = sBrId
      AND B.sSubsidiaryId = sSuId;
END

The engineer copies this file, fills in {sProName}, {sMasterTableName}, {sSlaveTableName}, replaces the placeholder logic with the real business rules, and applies the resulting SQL to the target schema.

What the placeholders are for

• {sProName} — the procedure's name (e.g., Sp_Sales_Save).
• {sMasterTableName}, {sSlaveTableName}, {sAllTableName} — the document family the proc operates on.
• Other placeholders depending on the scaffold.

Why this is a "template" library and not a code generator

The framework does not auto-generate procs from these templates based on metadata. The scaffolds exist because xly's procs follow a common conventional shape; copying the scaffold ensures the new proc:

• Accepts the standard parameter list (sGuid, sFormGuid, sLoginId, sBrId, sSuId) that generic dispatch can call.
• Returns success/error via the standard OUT sCode INT, OUT sReturn LONGTEXT.
• Honours the multi-tenant filter sBrandsId = sBrId AND sSubsidiaryId = sSuId.

A proc that doesn't follow these conventions cannot be invoked through generic dispatch and would have to be called from custom Java code instead.

Two loaders

There are two classes named FileSqlUtil in the codebase, with very different reliability:

Loader	What it points at	State
xlyFlow/src/main/java/com/xly/sqltemplate/util/FileSqlUtil.java	The 8 scaffolds above	All 8 files exist — this is the loader BACK's script-authoring screen actually uses.
xlyApi/src/main/java/com/xly/api/util/FileSqlUtil.java	7 different template constants: sInSqlStrTemple.sql, sOutSqlStrTemple.sql, sDataSqlTmpDef.sql, sDataSqlTmp.sql, sJsonSqlTmp.sql, sDbPro.sql, sJsonSqlTmpOut.sql	None of these files exist in the live tree. The constants are dead source — likely from an older xlyApi codepath that no longer ships templates. Calls through them will throw FileNotFoundException at runtime.

A maintainer touching the xlyApi FileSqlUtil should treat it as broken-by-default and either restore the missing templates or remove the loader.

See also

• Generic procedure dispatch — how the framework invokes the procs once they're authored.
• Slice 5 (per-customer SQL override) — when a customer needs a replacement of a standard proc, which reuses the same template structure but DROPs and CREATEs an override on the target schema.