Open topic with navigation

TIBCO Flogo® Connector for Oracle MySQL 2.4.5

# MySQL Insert

Use this activity to execute an SQL Insert to insert the records into the Oracle MySQL database.

## Settings

The Settings tab has the following field.

Field | Description
--- | ---
Connection | Name of the Oracle MySQL database connection from where information is retrieved. You can select a connection from the Connection dropdown list.

## Input Settings

The Input Settings tab has the following field.

Field | Description
--- | ---
Insert Statement | An SQL statement used to insert a record in the table. You can construct prepared SQL queries by using substitution variables (or parameters) of the form `?<fieldname>` in the insert query statement. Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at run time. If the query contains parameters then only parameter metadata is populated. End the query with semicolon (;) to fetch the metadata in the table at the bottom of the query. When substitution variables used in the VALUES clause are identical to the column names in the table, those variables are displayed under the Values array and otherwise displayed under Parameters on the Input tab. Use Values to insert multiple records at once and use Parameters to insert only one record at a time. Map JSON data from previous activity for insert when you use: Values - https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/Mapping-Arrays/array.forEach.sample Parameters - https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/Flow-Design-Concepts/loops.sample For more details about Data Mappings, see the "Mapping JSON Data with the json.path() Function" section in TIBCO Flogo Enterprise User's Guide. The following example represents a typical insert query:
    INSERT INTO ADVISOR (s_id, i_id) VALUES (?s_id, ?i_id);
For the above insert query, there is no output field, and input field is generated for `s_id` (VARCHAR) and `i_id`(VARCHAR) under Values[] node as its part of the values clause. Also, the mapped value for the field price and name is substituted into the substitution variable `?s_id` and `?i_id`. It is also possible to enter multi-row queries. For example:
    INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);
Value substitution variables can also be used in multi-row values. For example:
    INSERT INTO products VALUES (1, 'Cheese', ?price), (2, 'Bread', ?price), (3, 'Milk', ?price);
3. If multi-row values are used, it is important to note the following points: A value substitution variable must appear in all the rows at the same position. You cannot have some rows with the value variable and other rows without it. If the value row is mapped to the output values from previous activities, then the output data from the previous activity must contain the same number of records as there are rows entered in the insert query definition. For multiple record insertion, mixing of value substitution variables and parameter substitution variables in each record entry is not recommended. Either they must all be value substitution variables, or they must be all parameter substitution variables. They should not be a combination of both. For example, the following use is discouraged:
       INSERT INTO products (ProductNo, Name, Price)
       VALUES (?ProductNo, ?Name, ?Price), (?myproduct, ?myname, ?myprice);
   - Note: To insert null value in MySQL table, the value has to be passed as "" (null string). Call Procedure and Call Functions Syntax: With no parameters
         Call  Procedure_Name( );

         Select Function_Name( );
   - With prepared parameters
         Call  Procedure_Name(?Param1 , ?Param2);

         Select Function_Name(?Param1, ?Param2);
Manually Configure Metadata | Set the Manually Configure Metadata field to `True` to fetch table metadata manually. For more information, see Manually Configuring Metadata.
Fields | Displays the database column metadata.

## Input

This tab displays the input schema of the activity in a tree structure format. The information in the schema varies based on the insert query provided. Substitution parameters in the query, which exactly match column names, are presented in the Values node and substitutions, which do not match, appear in the Parameters node.

## Output

This tab displays the output schema of the activity in a tree structure format. The output tab displays rowsAffected and lastInsertId. rowsAffected equals the number of rows inserted and lastInsertId is set to the ID of the last server generated unique key value if the table is defined that way, otherwise -1.

## Loop

If you want this activity to iterate multiple times within the flow, enter an expression that evaluates the iteration details. Select a type of iteration from the Type menu. The default type is None, which means the activity does not iterate.

Refer to the "Using the Loop Feature in an Activity" topic in TIBCO Flogo® Enterprise User Guide.

## Retry on Error

For more information about Retry on Error tab, see the "Using the Retry on Error Feature in an Activity" section in the TIBCO Flogo® Enterprise User Guide.

For more information, see the Using the Retry on Error Feature in an Activity section in the Flogo® app documentation.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO Flogo® Connector for Oracle MySQL

Copyright © 2020-2024. Cloud Software Group, Inc. All Rights Reserved. Confidential & Proprietary.