Dynamic table creation in mule flow

Question

in my requirement i am receiving data from file(excel) and inserting it into database. But the table name i am getting during processing of the file based on some business logic. I am supposed to check whether the table exists with the name, if exists then update it else create and insert data into it. Is this requirement possible to achieve without custom java code using the mule studio provided componets or endpoints? Thanks in advance.

Answer 1

I don't think it's possible with standard components.

Saying you have 3 tables that can be mapped by three xls you could define three datamappers statically and call them upon certain logic on a choice component. It's pretty straightforward to check if a table exist with groovy/java, for example you can use:

java.sql.DatabaseMetaData dbm = con.getMetaData();
            rs = dbm.getTables(null, null, "TableName", null);
            if (rs.next()) {
                System.out.println("TableName found");
            }else{
                System.out.println("TableName NOT found");
} 

Answer 2

Alternately you can also use the following query in your Groovy script to check if the database exists and create it if not :-

if not exists (select * from sysobjects where name='YourTableName' and xtype='U')create table YourTableName(ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)

But yes, this if not exists query is not supported by stranded Mule Database component and in that case you can use the Groovy component.
This is a simple query and you can modify this as per your need and very useful to create the table if it not exits, and as you can see you can use create table command here directly in the single line