wehub-resource-sync
d718c5a372
CI / compile_and_lint (push) Failing after 0s
CI / docker_build (linux/amd64, -linux-amd64-duckdb, duckdb) (push) Failing after 0s
CI / docker_build (linux/arm64, -linux-arm64, minimal) (push) Failing after 2s
CI / docker_build (linux/arm64, -linux-arm64-duckdb, duckdb) (push) Failing after 1s
CI / docker_build (linux/amd64, minimal) (push) Failing after 1s
CI / test (, sqlite, sqlite::memory:) (push) Has been skipped
CI / test (mssql, mssql, mssql://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / test (mysql, mysql, mysql://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / test (oracle, oracle, Driver=Oracle 21 ODBC driver;Dbq=//127.0.0.1:1521/FREEPDB1;Uid=root;Pwd=Password123!) (push) Has been skipped
CI / test (postgres, odbc, Driver=PostgreSQL Unicode;Server=127.0.0.1;Port=5432;Database=sqlpage;UID=root;PWD=Password123!, true) (push) Has been skipped
CI / test (postgres, postgres, postgres://root:Password123!@127.0.0.1/sqlpage) (push) Has been skipped
CI / playwright (push) Has been skipped
CI / docker_build (linux/arm/v7, -linux-arm-v7, minimal) (push) Failing after 0s
CI / hurl_examples (push) Failing after 8s
deploy website / deploy_official_site (push) Failing after 1s
CI / hurl (${{ matrix.example }}) (push) Has been skipped
CI / docker_push (duckdb) (push) Has been cancelled
CI / docker_push (minimal) (push) Has been cancelled
CI / windows_test (push) Has been cancelled
35 lines
2.0 KiB
Markdown
35 lines
2.0 KiB
Markdown
# Handling json data in Microsoft SQL Server
|
|
|
|
This demonstrates both how to produce and read json data from a SQL query
|
|
in MS SQL Server (or Azure SQL Database), for creating advanced forms.
|
|
|
|
This lets your user interact with your database with a simple web interface,
|
|
even when you have multiple tables, with one-to-many relationships.
|
|
|
|
|
|
|
|
## Documentation
|
|
|
|
SQLPage requires JSON to create multi-select input (dropdowns where an user can select multiple values).
|
|
The result of these multi-selects is a JSON array, which also needs to be read by SQL queries.
|
|
|
|
This example demonstrates how to consume [JSON](https://en.wikipedia.org/wiki/JSON) data from a SQL Server database,
|
|
using the [`OPENJSON`](https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql)
|
|
function to parse the JSON data into a table,
|
|
and [`FOR JSON PATH`](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server)
|
|
to format query results as a JSON array.
|
|
|
|
|
|
This demonstrates an application designed for managing groups and users, allowing the creation of new groups, adding users, and assigning users to one or multiple groups.
|
|
|
|
The application has the following sections:
|
|
|
|
- **Create a New Group**: A form where users can enter the name of a new group.
|
|
- **Groups Display**: A list of existing groups.
|
|
- **Add a User**: A form where users can enter the name of a new user and select one or multiple groups to assign to this user.
|
|
- **Users Display**: A list of existing users and their associated group memberships.
|
|
|
|
When users submit the form, their selections are packaged up and sent to the database server. The server receives these selections as a structured JSON array.
|
|
|
|
The database then takes this list of selections and temporarily converts it into a format it can work with using the `OPENJSON` function, before saving the information permanently in the database tables. This allows the system to process multiple selections at once in an efficient way.
|