DB V2 - usage scenarios
Learn more about how you can use the component in some scenarios.
Last updated
Learn more about how you can use the component in some scenarios.
Last updated
Take a look at the supported usage scenarios:
Let's say you have a configured database and a data table and that you want to invoke a DB V2 with a query with the SELECT command. With it, you'll have access to the operation result in the pipeline through JSON.
Example
1. Create a pipeline and add DB V2 2. Open the configurations of the component 3. Select the QUERY operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 6. Define a SQL STATEMENT (eg.: SELECT * FROM CLIENTES) 7. Click on CONFIRM to save the configurations of the component 8. Connect the trigger to DB V2 9. Execute a test in the pipeline (you can use the CTRL + ENTER command) 10. The result of the query will be presented:
The result of the query returns a JSON with 3 properties on its root: data, updateCount and rowCount.
data: object array representing the lines returned from the database according to the defined query. Each element of this array has properties that represent the columns defined in the SELECT operation. If the declaration was "SELECT name FROM CLIENTS", there'd be only one property in each of the objects in data the array.
updateCount: indicates how many lies of the table were affected by the executed query. With this example the value would be zero - once SELECTED was the operation, no table was affected.
rowCount: indicates how many lines were returned by the query.
Let's say you have a configured database and a data table and that you want to invoke a DB V2 with a query with the UPDATE operation. With it, you'll have access to the result of this operation through JSON.
Example
1. Create a pipeline and add a DB V2 2. Open the configurations of the component 3. Select the QUERY operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 6. Define a SQL STATEMENT (eg.: UPDATE CLIENTES SET uf='SP' WHERE uf is null) 7. Click on CONFIRM to save the configurations of the component 8. Connect the trigger to DB V2 9. Execute a test in the pipeline (you can use the CTRL + ENTER command) 10. The result of the query will be presented:
updateCount: with this example, the change of 5 lines of the table is confirmed.
Let's say you have a configured database and a data table and that you want to invoke a DB V2 with a query with the DELETE operation. With it, you'll have access to the result of this operation in the pipeline through JSON.
Example
1. Create a pipeline and add a DB V2 2. Open the configurations of the component 3. Select the QUERY operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 6. Define a SQL STATEMENT (eg.: DELETE FROM CLIENTES WHERE codigo='111111') 7. Click on CONFIRM to save the configurations of the component 8. Connect the trigger to DB V2 9. Execute a test in the pipeline (you can use the CTRL + ENTER command) 10. The result of the query will be presented:
updateCount: with this example, the exclusion of 1 line of the table is confirmed.
Let's say you have a configured database and a data table and that you want to invoke a DB V2 with a query with the INSERT operation. With it, you'll have access to the result of the operation in the pipeline through JSON.
Example
1. Create a pipeline and add a DB V2 2. Open the configurations of the component 3. Select the QUERY operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb 6. Define a SQL STATEMENT (eg.: INSERT INTO CLIENTES (name, email) VALUES ('My Name', 'myname@email.com')) 7. Click on CONFIRM to save the configurations of the component 8. Connect the trigger to DB V2 9. Execute a test in the pipeline (you can use the CTRL + ENTER command) 10. The result of the query will be presented:
updateCount: with this example, the creation of 1 line in the table is confirmed.
Let's say you have a database incompatible with JDBC 4.0 or that it fails to keep a trustable connection and that you want to invoke DB V2 with any query, but 20 minutes after making the same call. With it, the connection with the database isn't lost.
The option ADVANCED > CONNECTION TEST QUERY allows to define a query that is executed before the query defined in SQL STATEMENT to make sure the connection is established with the database and avoid errors.
This option will allow a new connection to be opened if there's an error in the connection test. It will avoid problems of "stuck" connections and timeouts caused by previous queries that received the error.
IMPORTANT: this option must be considered only for older databases (with support version to jdbc < 4.0).
1. Create a pipeline and add a DB V2 2. Open the configurations of the component 3. Select the QUERY operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 6. Define a SQL STATEMENT (eg.: INSERT INTO CLIENTES (name, email) VALUES ('My Name', 'myname@email.com')) 7. Connect the trigger to DB V2 8. Select the option ADVANCED (more options will be shown) 9. Define a CONNECTION TEST QUERY (eg.: SELECT version()) 10. Click on CONFIRM to save the configurations of the component 11. Execute a test in the pipeline (you can use the CTRL + ENTER command) 12. The result of the query will be presented
Let's say you have a configured database and a data table and that you want to invoke a DB V2 with a QUERY or PROCEDURE in case the operation fails to interrupt the execution of the pipeline.
The option FAIL ON ERROR allows the pipeline to interrupt its execution in case of an error when invoking DB V2.
Let's say you have a configured database and a table with BLOB and/or CLOB data type and that you want to invoke a DB V2 with a QUERY or PROCEDURE. With it, you can not only write files available in the pipeline, but also read BLOB and/or CLOB data from a database column as if they were files.
The BLOB AS FILE/CLOB AS FILE option allows to inform a file path for the operations with BLOB-type and CLOB-type columns respectively - which means, invoking the INSERT command and providing the path of files available in the pipeline will make these file to be written in the database.
In case of choosing the SELECT command, the BLOB/CLOB content will become a file inside the pipeline.
Example: step 1 (read file)
1. Create a pipeline and add a DB V2 2. Open the configurations of the component 3. Define the name as 'Read-file' 4. Select the QUERY operation 5. Choose an ACCOUNT to be able to authenticate in the database 6. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 7. Define a SQL STATEMENT (eg.: SELECT blob FROM CLIENTES WHERE id=78) 8. Activate the BLOB AS FILE and/or CLOB AS FILE options (if the option CLOB AS FILE is activated, a new field CHARSET will be displayed to inform the file encoding) 9. Click on CONFIRM to dabe the configurations of the component 10. Connect the trigger and DB V2 (Read-file) 11. Execute a test in the pipeline (you can use the CTRL + ENTER command) 12. The result of the query will be presented:
Two files named 'E4G17F.file' and 'H2K89G.file' were generated and made available in the pipeline.
Example: step 2 (write file)
1. Add a DB V2 2. Open the configurations of the component 3. Define the name as 'Write-file' 4. Select the QUERY option 5. Choose an ACCOUNT to be able to authenticate the database 6. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 7. Define a SQL STATEMENT (eg.: INSERT INTO CLIENTES (blob, clob) VALUES ( {{ message.data.blob }}, {{ message.data.clob }} ))
Keep in mind that the Double Braces expressions {{ message.data.blob }} and {{ message.data.blob }} were used.
Through them you can access the result of the last component - in this, that has the query result was accessed. After that, blob and clob, which are the names of the generated files, were accessed. In other words, the files paths for the 'E4G17F.file' file and the 'H2K89G.file' were provided in the INSERT command for the blob and clob field.
8. Activate the BLOB AS FILE and/or CLOB AS FILE options (if the option CLOB AS FILE is activated, a new field CHARSET will be displayed to inform the file encoding)
9. In the TYPE PROPERTIES field, click on the ADD button
10. A section will be displayed to define the fields:
Key as 0 (each Double Brace has an index and it refers to the first one)
TYPE as BLOB
OUT PARAMETER NAME in blank
PARAMETER TYPE as IN
Key as 1 (each Double Brace has an index and it refers to the second one)
TYPE as CLOB
OUT PARAMETER NAME in blank
PARAMETER TYPE as IN
11. Click on CONFIRM to save the configurations of the component 12. Connect the DB V2 (Read-file) to DB V2 (Write-file) 13. Execute a test in the pipeline (you can use the CTRL + ENTER command) 14. The result of the query will be presented:
The result indicates that a line from the table was affected. In this case, the first component (Read-file) read the bob and clob content of the database and created the files. After that, the component (Write-file) took these available files in the pipeline and inserted it in the database in a new register.
Let's say you have a database configured with an existing procedure and that you want to invoke a DB V2 in PROCEDURE mode. With it, you can execute an existing procedure and obtain the result of the operation in the pipeline through JSON.
Example
The following procedure must exist in the database for the mentioned example:
1. Create a pipeline and add a DB V2 2. Open the configuration of the component 3. Select the PROCEDURE operation 4. Choose an ACCOUNT to be able to authenticate in the database 5. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 6. Define a SQL STATEMENT (eg.: call proc_sum_one({{ message.num }})) Keep in mind we use the Double Braces expression: {{ message.num }} Through it you can access the result of the last component. 7. On the TYPE PROPERTIES field, click on the ADD button 8. A section will be shown as the image above for you to define the fields:
Key as 0 (each Double Braces has an index and it refers to the first one)
TYPE as INTEGER
OUT PARAMETER NAME as result (defines the result parameter of procedure)
PARAMETER TYPE as INOUT
9. Click on CONFIRM to save the configurations of the component 10. Connect the trigger to DB V2 11. Open the Execution panel and provide the entry value (eg.: { "num": 3 }) 12. Execute a test in the pipeline (you can use the CTRL + ENTER command) 13. The result of the query will be presented:
Let's say you have a configured database and that you want to invoke a DB V2. With it, you can specify personalized connection properties.
Example:
1. Open the configurations of the component 2. Select the QUERY option 3. Choose an ACCOUNT to be able to authenticate in the database 4. Define a DATABASE URL (eg.: jdbc:mysql://myserver/mydb) 5. Define a SQL STATEMENT (e.g.: SELECT * FROM CLIENTES) 6. Define the CUSTOM CONNECTION PROPERTIES field (eg.: { "connectTimeout": 1 }) 7. Click on CONFIRM to dabe the configurations of the component 8. Connect the trigger to DB V2 9. Execute a test in the pipeline (you can use the CTRL + ENTER command) 10. The result of the query will be presented:
In this case, the specific connection property connectTimeout was defined as 1 (one second) to demonstrate it was considered and generated a connection error.
Let's say you have a configured database and that you want to invoke a DB V2. With it, you can choose if you want to keep the connection with the database active for 5 or 30 minutes.
The option KEEP CONNECTION allows you to extend the standard 5 minutes to 30 minutes of active connection with the database.
Let's say you have a configured database with a table that contains a custom type and that you want to invoke a DB V2 to view this data.
Example:
You have a table created with the following structure:
And the OWNER custom type is created like:
Executing the query below:
You’ll have the following result:
If the custom type uses others custom types in its structure, you’d have the result with data nested as the following way:
IMPORTANT: custom types don’t expose the fields' names configured inside the custom type, but only the order of those fields. In the example above, the first element of the "owner" array corresponds to the field "name" and the second element to the field "email", both defined in this respective order inside the type OWNER.When reading custom types through PROCEDURES, the OUT PARAMETER NAME property in TYPE PROPERTIES must be configured with the custom type name, so in this example above it would be OWNER.
Let's say you have a configured database with a table that contains a custom type (Struct) and that you want to invoke a DB V2 to insert data into this table.
IMPORTANT: the component DB V2 doesn’t support Double Braces to resolve the custom data (Struct) using the commands INSERT and UPDATE.
Example
You have the custom types and the table created with the following structures:
CREATE OR REPLACE TYPE CONTACT AS OBJECT
CREATE OR REPLACE TYPE PERSON AS OBJECT
CREATE OR REPLACE TYPE DEPARTMENT AS OBJECT
CREATE TABLE EMPLOYEE
To insert data into this table, the following structures can be used:
Data to be inserted:
Through INSERT from a QUERY result
Through PROCEDURE:PROCEDURE previously created in the database
PROCEDURE call
Through SQL Script
Suppose you have an integration flow that needs to perform two different queries on a database depending on some business rules:
QUERY 1:
QUERY 2:
DB V2 has the Raw SQL Statement feature, which allows you to reference a full query through Double Braces declaration. In this way, the same reference can apply different values.
To use this feature, the queries must be previously configured by another component such as Template Transformer and then referenced in DB V2.
Example
1. Create a pipeline and add Template Transformer and DB V2;
2. Open the configurations of the Template Transformer component;
3. Enable Preserve Original option;
4. Define the body:
5. Click on Confirm to save the configurations of the component;
6. Open the configurations of the component DB V2;
7. Select the Query operation;
8. Choose an account to be able to authenticate in the database;
9. Define a database URL (eg.: jdbc:mysql://myserver/mydb);
10. Define the SQL statement as {{ message.query }};
11. Activate the Advanced option;
12. Activate the Raw SQL Statement option;
13. Click on Confirm to save the configurations of the component;
14. Connect the Template Transformer o DB V2;
15. Connect the trigger to the Template Transformer;
16. Execute a test in the pipeline passing an input payload:
Payload for QUERY 1:
Payload for QUERY 2:
17. The result is displayed based on the executed query as follows:
Result for QUERY 1:
Result for QUERY 2:
The result of the query returns a JSON with 3 properties in the root: data, updateCount and rowCount.
data: object array representing the lines returned from the database according to the executed query. Each element of this array has properties representing the columns defined in the Select operation. If the declaration was "SELECT name FROM TABLE_1 WHERE ID = 123" or "SELECT fullName FROM TABLE_2 WHERE CODE = 'ABC'", there would be only one property in each of the objects in the data array.
updateCount: indicates how many lines of the table were affected by the executed query. In the example above, the value would be zero; once Select was the selected operation, no table was affected.
rowCount: indicates how many lines were returned by the query.
IMPORTANT: the query to be referenced through Double Braces on DB V2 can be previously set by other components such as JSON Generator or Transformer (JOLT). However, by using Template Transformer you can combine it with FreeMarker technology to validate the data before executing the target query and also apply mechanisms to prevent SQL Injection attacks on the pipeline. The Double Braces declaration is not resolved by Template Transformer but by DB V2, which by default configures and validates the parameters of the SQL statement beforehand (PreparedStatement). This means that using Template Transformer in addition to DB V2 is a safer and easier way to create queries compared to other components.
