Configure Database Connector Data Types Examples - Mule 4

The following examples show how to configure Anypoint Connector for Database (Database Connector) data types by using the Parameters types field to specify the type of each input parameter in the query and the Column types field to set custom data types when connected to a particular database provider.

Configure the Parameter Types Field in Studio

If your JDBC driver either cannot determine the type of the input, or a parameter is of a custom type, configure the Parameter types field to specify the type of each input parameter. The following example shows how to configure the Parameter types field in the Bulk insert operation:

  1. In your Studio flow, select the Bulk insert operation.

  2. 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.

  3. Specify the database connection information and click OK.

  4. Set the SQL Query Text field to the SQL query to execute, for example: INSERT INTO PLANET(POSITION, NAME) VALUES (:position, :name)

  5. In the operation configuration screen, click the Advanced tab.

  6. Set the Parameter types field to Edit inline.

  7. Click the green plus sign (+) to create a new parameter type.

  8. In the parameter type window, set the Key field to name and the Type field to VARCHAR.

  9. Click Finish.

  10. Repeat Step 7.

  11. In the parameter type window, set the Key field to position and the Type field to INTEGER.

  12. Click Finish.

The following screenshot shows the configuration in Studio:

Set the Parameter types field to Edit Inline and then click the plus sign to create a new parameter
Figure 1. Parameter Types field configuration

In the XML editor, the <db:parameter-types> configuration looks like this:

<db:bulk-insert queryTimeout="0" queryTimeoutUnit="SECONDS">
     <db:sql>INSERT INTO PLANET(POSITION, NAME) VALUES (:position, :name)</db:sql>
     <db:parameter-types>
         <db:parameter-type key="name" type="VARCHAR" />
         <db:parameter-type key="position" type="INTEGER" />
     </db:parameter-types>
 </db:bulk-insert>

Configure the Column Types Field in Studio

Each connection provider uses exclusive and common parameters, such as Derby and Oracle. A child element of the connection provider element defines custom data types parameters you can use when connected to a particular provider.

The following table provides the JDBC Data Type codes and IDs that you can use to define custom data types:

JDBC Data Type Code id

ARRAY

2003

BIGINT

-5

BINARY

-2

BIT

-7

BLOB

2004

BOOLEAN

16

CHAR

1

CLOB

2005

DATALINK

70

DATE

91

DECIMAL

3

DISTINCT

2001

DOUBLE

8

FLOAT

6

INTEGER

4

JAVA_OBJECT

2000

LONGNVARCHAR

-16

LONGVARBINARY

-4

LONGVARCHAR

-1

NCHAR

-15

NCLOB

2011

NULL

0

NUMERIC

2

NVARCHAR

-9

OTHER

1111

REAL

7

REF

2006

REF_CURSOR

2012

ROWID

-8

SMALLINT

5

SQLXML

2009

STRUCT

2002

TIME

92

TIME_WITH_TIMEZONE

2013

TIMESTAMP

93

TIMESTAMP_WITH_TIMEZONE

2014

TINYINT

-6

VARBINARY

-3

VARCHAR

12

The following example shows how to configure the Column types field in the Derby global configuration connection element:

  1. In Studio, navigate to the Global Elements tab.

  2. Click Create.

  3. In the Global Type window, filter by typing database.

  4. Select Database Config and click OK.

  5. Set the Connection field to Derby Connection.

  6. Configure the required libraries for the Derby JDBC Driver and the additional connection fields.

  7. Click the Advanced tab.

  8. Set the Column types field to Edit inline.

  9. Click the green plus sign (+) to create a custom column type.

  10. In the parameter type window, set the Id field to 2000 and the Type field to CONTACT_DETAILS.

  11. Click Finish.

The following screenshot shows the configuration in Studio:

Set the Column types field to edit inline and then click the plus sign to create a column type
Figure 2. Column Types field configuration

In the XML editor, the <db:column-type> configuration looks like this:

<db:config name="dbConfig">
   <db:derby-connection url="jdbc:derby:muleEmbeddedDB;create=true">
       <db:column-types>
           <!-- Derby uses JAVA_OBJECT for UDT-->
           <db:column-type typeName="CONTACT_DETAILS" id="2000"/>
       </db:column-types>
   </db:derby-connection>
</db:config>