Description
The SQL Server Query component allows you to execute query statements against SQL Server to retrieve data.
It is compatible with SQL Server 2005 and above.
Output payload
| Output Payload | Description |
|---|---|
cv.Payload | Outputs a record in the output format specified by the property for each record that matches the condition. |
Example of output in array format
When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and "array" in the output format property, the output payload is generated as follows.
Output Example
[1, "abc"]
[2, "def"]
[3, "hij"].Example of output in object format
When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and "object format" in the output format property, the output payload is generated as follows.
Output Example
{"column1": 1, "column2": "abc"}
{"column1": 2, "column2": "def"}
{"column1": 3, "column2": "hij"}Component properties
| Property Name | Description |
|---|---|
| SQL | Specify SQL statement to execute |
| Output Format | Select array or object format |
| Host | Specify the SQL Server host. |
| Port | Specify the SQL Server port |
| User Name | Specify the SQL Server user name. |
| Password | Specify the SQL Server password. |
| Database | Specify the database. |
| Encrypt | Specify communication encryption from disable (no SSL/TLS connection), true (SSL/TLS connection), and false (encryption for login only) |
| TrustServerCertificate | Select whether to trust SQL Server's server certificate. If on, trusts the certificate sent by SQL Server. If Off, validates SQL Server certificates against trust store certificates |
| Root Certificate | Specify the root certificate to use; if you are using AWS Aurora or RDS, you must use a server certificate issued by AWS. This will be the root certificate for that server certificate. |
Placeholder
You can use a placeholder in the SQL statement to be executed to use the value of cv.Playload in the SQL statement.
SELECT * FROM table1 WHERE id=?? The value of cv.Payload is used for the placeholder specified in cv. When cv.Payload contains 1, the record with id 1 can be retrieved.
More than one placeholder can be specified.
SELECT * FROM table1 WHERE name=? AND age=?To specify values for more than one placeholder, specify the values in an array in cv.
["First Last", 20]Text2SQL
Text2SQL is a function that allows you to generate SQL statements from the Japanese you enter in the prompt for the data you wish to retrieve using OpenAI's ChatGPT functionality.
Please refer to the Text2SQL Usage Guide in the Setup Guide for how to use it.