Skip to content

HTMLDataTable.md

Latest commit

 

History

History
36 lines (26 loc) · 1.44 KB

HTMLDataTable.md

File metadata and controls

36 lines (26 loc) · 1.44 KB

HtmlDataTable

The CGI executable produced by [[CGISharp]] — takes HTTP query strings, runs parameterised SQL from JSON config files, and returns columnar JSON.

Repo: ~/Git/CGI-Sharp URL: https://dw.ramsden-international.com/tiny02/cgi-bin/HTMLDataTable.exe

HtmlDataTable is invoked via ?json=<name>&param1=value1. It loads a JSON config from C:\RI Services\JsonParams\cgi\<name>.json, connects to the specified database (PostgreSQL, MySQL, [[DuckDB]], [[Dbisam]]/[[ExportMaster]], or any [[ODBC]] DSN), runs the SQL with parameter substitution, and returns results as columnar JSON. Deployed on [[Dw.ramsdenInternational.com]].

JSON config format

Each config file is an array of query definitions:

[
  {
    "dsn": "DW",
    "sql": ["SELECT col FROM table WHERE key = @param"],
    "paramcount": 1
  }
]

Key fields: dsn (connection target), sql (inline array) or sqlfile (external .sql filename without extension), paramcount (expected parameters), plus optional host/port/database/user/password for MySQL, credentials (path to creds JSON), and useWindowsAuth.

Output format

{
  "headers": ["col1", "col2"],
  "types": ["string", "number"],
  "rows": [["val", 42], ["val2", 17]]
}

Types map from .NET: string, datetime (ISO 8601), integer, number, boolean, object. Nulls are JSON null. [[PowerQueries]] contains Power Query M modules for consuming this format in Power BI and Excel.