Generic procedure dispatch

When the metadata names a stored procedure — via columns like gdsmodule.sSaveProName, sSaveProNameBefore, sDeleteProName, sCalcProName, sProcName, or gdsconfigformslave.sButtonParam — the framework dispatches that proc by name. sSaveProName and sSaveProNameBefore are hooks: they run as post-save / pre-save phases on top of the always-running base add/update path (BusinessBaseServiceImpl.addBusinessData / updateBusinessData), invoked by checkUpdate(...,"sSaveProName") at BusinessBaseServiceImpl.java:1824 and :1778. The other columns drive on-demand calls: sCalcProName for button-press calculations, sProcName for custom-fetch flows, etc. The same generic-dispatch machinery handles all of them.

The handler is GenericProcedureCallController in xlyEntry/com/xly/web/businessweb/.

Endpoint shape

The frontend POSTs to /procedureCall/doGenericProcedureCall with:

• The proc name (often resolved from gdsmodule or gdsconfigformslave.sButtonParam).
• The parameter values (frontend supplies them, the framework injects tenant identity).

The handler:

1. Loads the proc's signature (parameter names and types) from information_schema.PARAMETERS.
2. Composes CALL <proc>(?, ?, …) with parameters bound positionally.
3. Augments the call with sBrandsId, sSubsidiaryId, sLoginId, sLanguage from RequestAddParamUtil.
4. Executes via MyBatis.
5. Returns either the proc's result-set (for SELECTs inside the proc) or its OUT parameters (for save/calculate procs).

Why dynamic proc dispatch matters

With well over a thousand stored procedures and hundreds of functions in the schema, hard-wiring each one in Java would be untenable. Dispatch by name lets the framework call any proc the metadata names without a code change. The framework treats the proc as a black box: name in, parameters in, result out.

That convenience comes with substantial costs that are worth being explicit about:

• No compile-time check on proc names. A typo in gdsmodule.sSaveProName produces a runtime "proc not found" error, not a compile error. Refactoring a proc name requires hand-grepping the metadata; the IDE can't help.
• No type safety on parameters. The framework binds parameters positionally from a Map<String, Object>. A proc whose signature changed but whose callers didn't is a runtime crash with no IDE warning.
• No call-site discoverability. "Which Java code calls Sp_SalSalesCheck?" can't be answered by IDE find-usages because no Java code does — gdsmodule rows do. Maintainers must search both metadata tables and the SQL bodies of other procs that may invoke this one.
• Effectively no static analysis. Side effects of any given proc are invisible to anyone who hasn't read the proc body. A Sp_SalSalesCheck named in gdsmodule.sProcName could be a read-only SELECT or could be doing INSERTs and UPDATEs across a dozen tables; the framework treats them identically.
• Stack traces that stop at the boundary. Java errors thrown from inside a proc surface as a generic BadSqlGrammarException or MySQLSyntaxErrorException. To get the real error you have to enable MyBatis SQL logging and re-run.

A more honest framing: hard-wiring 1000+ procs in Java would be painful, but most of that pain comes from xly having 1000+ procs in the first place. Dynamic dispatch made it cheap to keep adding them, which made the pile grow, which made the pile harder to audit. The mechanism is what it is; the amount of behaviour pushed into the SQL layer is the more interesting design question.

The conventions procs follow

xly's procs share a calling convention to make generic dispatch possible:

• Common parameters in a fixed order: typically (IN sLoginId, IN sCustomerId, IN sBrId, IN sSuId, …). Cross-cutting identity always comes first.
• Tenant-aware: every proc accepts sBrId (= sBrandsId) and sSuId (= sSubsidiaryId) and uses them in every internal predicate to stay tenant-safe.
• Output via OUT params: save / calc procs return success / error through OUT sCode INT, OUT sReturn LONGTEXT — see xlyEntry/src/main/resources/templates/templesql/sSaveProName.sql for the canonical scaffold.
• Dynamic SQL inside: many procs build SQL strings with CONCAT
  • PREPARE + EXECUTE. This is xly's escape hatch for runtime shape variability and the reason jSqlParser is in the dependency tree (the framework occasionally inspects these strings).

The companion file: templesql/

xlyEntry/src/main/resources/templates/templesql/ holds scaffolds for the standard proc kinds (eight files in the live tree):

• sSaveProName.sql
• sDeleteProName.sql
• sProcName.sql
• sSaveProNameBefore.sql
• sButtonParam.sql
• sProTempleByDb.sql
• sp_btn_calc.sql
• sSqlStr.sql

These are templates an engineer fills in to author a new proc — they are not used by the runtime to generate procs on the fly. See SQL templates for the loader and the placeholder syntax.

Proc-name molds in the live schema

The 1687 procedures in the live DB cluster around a few naming molds beyond the bare Sp_* family:

Mold	Approx count	Role
Sp_*	most	The dominant family, dispatched by sSaveProName / sCalcProName / sProcName etc.
Sp_*_BeforeSave	~62	Pre-save hooks. Pair with sSaveProNameBefore.
Sp_*_AfterSave / Sp_*_SaveReturn	~62 / ~54	Post-save hooks; _SaveReturn writes back into the parent transaction.
Sp_*_Calc	~178	Calculation procs invoked by button-press flow (sCalcProName / sButtonParam).
sp_btn_*	~65	Button-event sub-family — typically sp_btn_calc* / sp_btn_validate* (lowercase by convention).
PRO_ERPMERGE*	~11	Data-migration utilities. Not dispatched by the runtime — engineer-only.
PRO_* (other)	~12	Other one-off utilities.
Get_*, del_*, Cal*, Tj_*	small handfuls	Legacy / domain-specific helpers. Not part of the generic-dispatch contract.

A typo in any of the dispatched columns gets an Sp_*-shaped target, so other molds never resolve via sSaveProName / sCalcProName etc. The non-Sp_* procs are reachable only via direct invocation in mapper XML or other procs.

The function layer

The schema also ships 177 user-defined functions following parallel naming molds: Fun_* (~150), Fn_* (~8), get_* (~10).

These are not Java-dispatched. They are invoked from inside other procedures, view definitions, and mapper-XML SELECT statements. There is no gdsmodule.sFunctionName column or analogous metadata — functions are picked up by the SQL that mentions them. A maintainer investigating a slow report should grep procs and views for Fun_* / Fn_* / get_* references; the framework's Java side does not see them at all.

Failure modes to watch

1. Mismatched parameter order. Generic dispatch binds positionally; a proc whose IN parameters are reordered is a runtime explosion.
2. Missing tenant filter. A proc that forgets sBrandsId / sSubsidiaryId in any internal predicate is a multi-tenant leak. Procs are NOT auto-filtered by the framework — they have to do it themselves.
3. Long-running procs blocking the request thread. Long calculations should run on a separate worker (see xlyErpTask, though it's currently disabled in settings.gradle).