Execute an SqlQuery into a dbstore

Activity	SQLQuery
Description	Execute a Query in a dbstore

Input :

PropertyName	Rule of use (if revelant)	Type	Required	Default Value	Description
connectionName		STRING	YES		name of the source connection (see DbStore)
QueryFileToken	NB: Use one parameter or the other as input (not both at the same time)	STRING	YES		Filetoken containing the query to execute
Query		STRING	YES		SQL Query to execute
Type		STRING	NO	NonQuery	Method of interactions with the database 3 available options `Scalar` only returns the value from the first column of the first row of your query. `Reader` returns an object that can iterate over the entire result set while only keeping one record in memory at a time. `NonQuery` does not return data at all: only the number of rows affected by an insert, update, or delete.
MaxResults		INT	NO	10	The JSON to load (must be either a JSON Array or a JSON Object)
Params		PARAMETERS ARRAY	NO		Query parameters
CommandTimeout		INT	NO		Mapping between columns and JSON properties

Parameters

PropertyName	Type	Required	Default Value	Description
Name	STRING	YES		name of the parameter
Value	STRING	NO		value of the parameter

Output

PropertyName	Type	Description
Scalar	ANY	Scalar value (for Scalar queries)
Result	DICTIONARY ARRAY	Reader results

Example1: Execute Simple query

Input

JSON

{
   "connectionName": "dbStore1",
   "type": "reader",
   "query":"SELECT name, role FROM employees"
}

Output

JSON

{
  "result":[
      {"name":"Bob","role":"sales"},
      {"name":"Frank","role":"operations"},
      {"name":"Ziggy","role":"board"},
      {"name":"Julia","role":"sales"},
  ]
}

Example2: Execute Scalar query

Input

JSON

{
   "connectionName": "dbStore1",
   "type": "scalar",
   "query":"SELECT COUNT(*) FROM employees"
}

Output

JSON

{
  "scalar": 35
}

Example3: Execute query with parameters

Input

JSON

{
   "connectionName": "dbStore1",
   "type": "reader",
   "query":"SELECT name FROM employees WHERE name like @likename",
   "params":[
       {"name":"likename", "value":"B%"}
   ]
}

Output

JSON

{
  "result":[
      {"name":"Bob"},
      {"name":"Bruno"},
      {"name":"Brock"},
  ]
}