Sql Query


Executes given SQL statement.

Double click the activity. Click upon the word Configure (Connection). It opens Configure Database Connection window. Enter the Connection string and Provider name (the database administrator should provide you both).

Click upon the word Edit (Command). It opens the Edit Command window. SQL Query executes the SQL statement (Select) or stored procedure. Type in the SQL statement or choose the stored procedure. Optionally, in the Return value textbox one can put the variable that will store the value retrieved from the procedure. Click on OK and close the Edit Command window. Now you should choose the Wizlink activity to execute for each row value (record) retrieved from database.

The variable record is DataReader object. You can access each column of the returned row by series of methods:

  • using number of the column – record(0) retrieves the value from first column, as object;
  • using name – record(“Name”) retrieves the value from the column that is called Name, as object;
  • using special method of DataReader object – record.GetString(0) retrieves the value of the first column, as string. (Methods available: GetBoolean, GetString, GetChar, GetDateTime, GetDecimal, GetDouble, GetFloat, GetInt16, GetInt32, GetInt64).

More information about connection:

Provider name: choose one of the list: MS SQL Server, PostgreSQL, MySQL, Oracle or ODBC.

Connection string: string of the characters that allows you to connect to database; in general: “Data Source=xxxx; Database=db; User Id=user1 ;Password=pass” (however it may vary depending on database). More usefull information on: https://www.connectionstrings.com.

Connection string formats:

  • MS SQL Server

Data Source=my_server_address; Database=my_database_name; User Id=my_user_name; Password=my_pass

More information: https://www.connectionstrings.com/sqlconnection/

  • MySQL / MariaDB

Server=my_server_address; Port=my_port; Database=my_database_name; User=my_user_name; Password=my_pass

  • PostgreSQL

Server=my_server_address; Port=my_port; Database=my_database_name; User Id=my_user_name; Password=my_pass

More information: https://www.connectionstrings.com/npgsql/

  • Oracle

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=my_host)(PORT=my_port))) (CONNECT_DATA=(SERVER=DEDICATED)(SID=my_sid))); User Id=my_user_name; Password=my_pass;

More information: https://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/

  • ODBC

connection string depends on type of ODBC driver for database

Required parameters

Parameter type Parameter name Description
Misc CommandText Command to be executed. It can be SQL statement, stored procedure or directly table name.
Misc CommandType Type of the statement. Available options are: Text, StoredProcedure, TableDirect
Misc ConnectionString The connection string.
Misc ProviderName Name of the data provider.

Optional parameters

Parameter type Parameter name Description
Misc DisplayName
Misc StoredProcedureReturnValue Variable specified here will store the value returned by stored procedure used in the query, if there was one.
Misc ConnectionConfigurationName
Misc Parameters

k-93-4

The connection string and data provider window.

k-93-5

Command window. It can be chosen whether SQL statement or stored procedure is used.

k-93-6

Stored procedure’s parameters window.

k-93-2