MySQL Query

Use this activity to execute a simple or a complex SQL Query on an Oracle MySQL database. The MySQL Query activity returns information in the form of rows.

Configuration

The Configuration tab has the following fields:

Field Description
Connection Name of the Oracle MySQL database connection from which to retrieve information.

Input Settings

Field Description
Query An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form ?<fieldname> in the query statement. For example select * from classroom where building like ?building;
Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at runtime. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters used in the WHERE clause, and output parameters used in the SELECT clause, and their corresponding type information is automatically fetched from the database using the selected connection for the entered query. Input and output fields on the Input and Output tabs of the activity are also automatically generated.
Note: Be sure to include the semicolon (;) at the end of the query. This activity expects a query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging. Typically, semi-colon is entered after the query has been entered in full, not before to prevent unnecessary query validation. When the semi-colon is entered the query field treats the query as complete. Upon successful processing of the query, the table field at the bottom of the query entry field is populated with column metadata information
Following are some examples of simple and complex queries:

  • Simple query example:

    SELECT * FROM classroom;

    For the above query, the output fields are generated from the columns of the table classroom. 

    SELECT name, dept_name, tot_cred  
    FROM university.student 
    WHERE dept_name = ?dept_name and tot_cred >= ?tot_cred order by tot_cred;

    For the above query, output fields are generated for name, dept_name and tot_cred and input fields are generated for dept_name and tot_cred. Also the mapped values for the fields dept_name and tot_cred are substituted into the substitution variables ?dept_name and ?tot_cred at runtime.

  • Nested query example: 

    SELECT firstname, lastname, total_quantity
    FROM (SELECT buyerid, sum(qtysold) total_quantity
            FROM sales
            GROUP BY buyerid
            ORDER BY total_quantity desc limit 10) Q, users
    WHERE Q.buyerid = userid
    ORDER BY Q.total_quantity desc;
Manually Configure Metadata

If you see an error for a valid SQL statement in the Query field, set the Manually Configure Metadata field to True to fetch table metadata manually using simple SELECT statement. For more information, see Manually Configuring Metadata.

Fields Displays the database column metadata.

Input

This tab contains the input schema. The fields that were selected on the Input Settings tab are available in the schema. You can either hard code their values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Output

This tab displays the activity output schema in a tree structure format. The output of an activity is displayed for informational purposes only and cannot be modified or altered. The information in this schema varies depending on the fields that you selected on the Input Settings tab.

The properties that are displayed on the Output tab schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.

Related concepts
Related reference