/
GetSqlQueryResultAsRecordSetObject

GetSqlQueryResultAsRecordSetObject

Oct 01, 2015

What to use it for

Prerequisites for usage

Essential properties to set

  • Only relevant if you want to interact with a database.
  • Use a GetSqlQueryResultAsRecordSetObject to get a set of records from a database (i.e. a table), to use these records as objects (that use the columns as properties).
  • Access to a database.
  • Connection String
  • Command Type
  • SQL Query
  • Parameters

<T>

Activities with a <T> will ask you to indicate what the data type is of the data that will be affected. Depending on the data type, you will get a different Template (<T>) with configuration options.

 

 Learn more about <T>

Activities with a <T> will ask you to indicate what the data type is of the data that will be affected. A database contains data of several types, and the <T> indicates that configuring that Activity starts with setting the targeted data type.

Depending on the data type, some configuration options will (dis)appear, depending on their relevancy.

When this Activity will affect this type of database data..Then select this option
BooleanBoolean
Whole numbersInt32
Text and/or numbersString
Object (with properties)Object
SmartBridge documentTie.MessagePortal.Shared.Entities.Document
Content of a SmartBridge documentTie.MessagePortal.Shared.Entities.DocumentContent
State of a SmartBridge documentTie.MessagePortal.Shared.Entities.DocumentState
A list of data of a specific data typeArray of (T)
Data specific to SmartBridgeBrowse for types...

Options for SmartBridge 3

OptionWhat is itHow to use it

Connection string

A connection string is a line of code for connecting with and accessing a database.

 Read more

About connection strings

A connection string is a line of code for connecting with and accessing a database. An example:

"Data Source=localhost;Initial Catalog=TieEArchive;Integrated Security=SSPI"

Make sure you use double quotes around the connection string.

Learn about connection strings

 

Write a connection string (see explanation), between double quotes.

Command type

The type of SQL query.

Indicate what type of query you will be using:

  • Text - To execute a standard query (default)
  • Stored procedure - To execute a reusable SQL code that is stored in the database.

SQL query

The actual SQL query to request the relevant data from the database.

Enter the SQL query, between double quotes.

Use a parameterized query when using a text query, to prevent being vulnerable to SQL injection attacks.

 Read more

Example of a parameterized query:

select * from user where age = @age

Instead of entering the actual age, you should use the name of the variable ('parameter') that represents the actual value.

Define these parameters using the 'Parameters' option.

Parameters

Instead of hardcoding values in your SQL queries a better way is to use parameters: A parameter is a variable in an SQL query that represents a predefined value. Use the current 'Parameters' option to create these predefined values.

Provide a name for your new variable, and a corresponding value.

 How to use query parameters

About query parameters

To prevent SQL injection attacks it is recommended to make use of parameterized queries instead of hardcoding values.

Using query parameters

Example case 1

Say you want to get a column containing a specific value, such as 'age = 45'.

Create the following parameter
NameDirectionTypeValue
ageinint3245
Use the parameter in your query
select * from user where age = @age
Example case 2

Say you want to retrieve the age of user 'Jane'.

Create the following parameter
NameDirectionTypeValue
nameinstringJane
Use the parameter in your query
select age from user where name = @name

Direction

DirectionRelevant forUse when your value will be
In
  • Parametrized query
  • Stored procedure
Fixed value, or variable
Out
  • Stored procedure
Argument
In/Out
  • Stored procedure
Special return value

Result

Option to save the results to a variable, for use later in the Workflow.

 Click here to expand...

The query returns data, which should be stored into an object for further use in the Workflow. To store this data into an object, provide the name of an object in the result field.

In this case, store the data into an object representing a database table or record set, which contains a collection of records (table rows), which in turn contains a collection of columns (table cells).

Enter the name of a variable of the type of record set.

Display nameName of the Activity.Leave at the default, or enter a name that is a better reflection of the type of query that is executed.

 

All SQL query Activities