Databases
Studio Pro activities > Programming > Databases. Includes "Connect to Database" and "Execute query".
Activities
Connect to Database | Execute query |
---|---|
Establish the connection to DBMS | Executes a database query |
Connect to Database
Description
This activity allows you to establish the connection to any of the following DBMS types: MySQL, PostgreSQL, MSSQL, and Oracle. After the connection is established, you can execute a query.
Parameters
DBMS type
Allows you to specify the database management system (DBMS) to be used.
List of supported DBMS:
- MySQL
- MSSQL
- PostgreSQL
- Oracle (64-bit versions are supported, 32-bit versions may be unstable)
Set up method: Basic parameters
DBMS host
Allows specifying the server address of the database that is being connected. For example, 127.0.0.1
or localhost
.
DBMS port
Allows specifying the port of the database that is being connected. For example, 3306
.
Database name
Allows specifying the name of the database that is being connected. For example, db_Employees
.
Login and password
Allows specifying the user credentials of the database. Note that the "Login" and "Password" values will be passed to the {{login}}
and {{password}}
variables in the "Connection string" parameter.
Note
If you want to use the values of login and password from variables in the activity “Connect to database“, you need to use “Connection string“ as a method of connection to DB with the following syntax:
mssql://${username}:${password}@localhost?database=TESTDB2&request%20timeout=25000
whereusername
- is the variable that contains the value of the login;password
- is the variable that contains the value of the password.
In the mandatory parameter “Login and password“ of this activity you can set any value, it won't affect the performance of the activity.
Connection string
- Calculate a value: this allows you to use a special formula or a special method to set the query text.
For example, you can use the connection string to set the required timeout value:
mssql://{{login}}:{{password}}@127.0.0.1?database=demo&request%20timeout=25000
When using this string, make sure to change the parameter host 127.0.01
, the parameter database demo
and the parameter timeout 25000
to required values.
![](https://files.readme.io/4b91a73-image.png)
Here, look at another example of passing a connection string to the Oracle database. In the "Calculate the Value" field add localhost:port/SID
.
Important
If you retrieve an error message after using the localhost:port/SID format, you can pass a connection string to the Oracle database using
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))
.
![](https://files.readme.io/2c357cd-image.png)
Note
Oracle does not support the ability to pass a username and password in the connection string.
Comment
Contains an annotation of the activity. The input text will be displayed above the activity name.
Execute query
Description
Executes a database query.
Parameters
Query text
- Set a value: enables you to directly provide a query in the text form, for example,
SELECT \* from developers or INSERT INTO 'managers' (name, birth date, salary) VALUES ('Jane Doe', '1988/05/13', '3000')
- Save the previous step result: chooses the previous function result as a query.
- Calculate a value: enables you to use available properties and methods to form a query in the text form.
Comment
Contains an annotation to the activity. The input text will be displayed above the activity name.
Updated 12 months ago