Execute DDL Statements Example - Mule 4

Anypoint Connector for Database (Database Connector) Execute DDL operation enables to execute DDL queries against a database. Data Definition Language (DDL) statements, are special types of SQL statements that do not directly access or modify data. Instead, they create, modify, or destroy data structures, stored procedures, and so on. You can also use DDL statements to create or modify data within tables or other data structures.

Configure the Execute DDL Operation in Studio

The following example shows how to configure the Execute DDL operation that creates a new table named electronic with columns named id, name, description, price, and discount:

  1. In the Mule Palette view, search for database and select the Execute DDL operation.

  2. Drag the Execute DDL operation onto the Studio canvas.
    The operation creates a table and returns 0 unless there is an error.

  3. In the General tab of the operation configuration screen, click the plus sign (+) next to the Connector configuration field to access the global element configuration fields.

  4. Specify the database connection information and click OK.

  5. Set the SQL Query Text field to the SQL query to execute, for example:

CREATE TABLE electronic(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(255),
    price SMALLINT,
    discount TINYINT

The following screenshot shows the configuration in Studio:

The Execute DDL operation configuration in Studio
Figure 1. Execute DDL configuration

In the XML editor, the <db:execute-ddl> configuration looks like this:

<db:execute-ddl config-ref="dbConfig">
    <db:sql>
        CREATE TABLE electronic(
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100),
            description VARCHAR(255),
            price SMALLINT,
            discount TINYINT
        )
    </db:sql>
</db:execute-ddl>