SQL Query
Enter the Connection string and choose database provider name from dropdown list (the database administrator should give you both).
SQL Query executes the SQL statement (Select) or stored procedure. Type in the SQL statement or choose the stored procedure. Optionally (in Properties window), in the Stored Procedure Return Value textbox one can put the variable that will store the value retrieved from the procedure. 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
| Parameter Name | Parameter Type | In/Out | Description |
|---|---|---|---|
| Command Text | String | Input | SQL statement or stored procedure (string expression) |
| Command Type | Dropdown | Input | select type of command from the dropdown list |
| Connection String | String | Input | phrase that allows to connect to database |
| Database Provider | String | Input | one of a list: MS SQL Server, PostgreSQL, MySQL, Oracle, ODBC |
| Display Name | Literal | Input | changes display name of the activity |
| Exclude from Transaction Check | Boolean | Input | checkbox; default is "off" |
| Parameters | Collection | Input | parameters to database query |
| Result | Boolean | Output | returns a Boolean value. True if no error in activity, otherwise will return False |
| Result Code | Int32 | Output | Result Code will return 0 if there is no error in activity, otherwise in most cases it will return 1, the extended result code will show only in several chosen activities |
| Stored Procedure Return Value | Int32 | Output | variable to store procedure value (optional parameter) |