FRENDS⁴ Common Components : ~SQL.ExecuteQueryToJSON (removed in version 2.0.1.1)

This task takes an SELECT SQL query, executes it against a given database with parameters and returns the resulting data as an JSON document.

Input Parameters

• SQLQuery
  • The SELECT query that will be executed to the database.
  • The following restrictions apply to the SQL query:
    • COMPUTE BY or FOR BROWSE are not allowed
    • GROUP BY and aggregate functions are not allowed
    • CURSOR are not allowed
    • User defined functions or stored procedures are not allowed
• RootElementName
  • The root element name of the resulting JSON document of the SELECT query, the results will be in an array inside this element.
• Parameters
  • A list of parameters to be executed in the query.
• ConnectionString
  • The connection string of the database the query is executed to.
• TimeoutS
  • The timeout of the query in seconds.

Output Result

• String
  • A string containing JSON document resulting from the query.

Example Usage

SQLQuery

SELECT TOP 1 [Title]
  ,[Artist]
  ,[Country]
  ,[Company]
  ,[Price]
  ,[Year]
FROM [dbo].[CDInformation]
WHERE Title = @TitleParameter

RootElementName RootElement

Parameters TitleParameter = Empire Burlesque

Returns the following JSON document:

{
  "RootElement": {
    "dbo.CDInformation": {
      "Title": "Empire Burlesque",
      "Artist": "Bob Dylan",
      "Country": "USA",
      "Company": "Columbia",
      "Price": "10.90",
      "Year": "1985"
    }
  }
}

Errors

The task will return an exception The query returned no results. if the SQL query returned 0 records.