Create table dynamically

[Anonymous]

Hello,
I need to create table dynamically by reading a txt file(Tab delimited). The first line of the text file will have the column names and all datatypes are Char.
Database: MySQL
example:
File name:test.txt
Organization Name IP Domain
xxxxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx
Table sturcture should be
Name: test
Column Name Datatype
Organization Name varchar
IP varchar
Domain varchar

Is there any way to do this?
Thanks,
Sangi

[Anonymous]

Hello Sangi,
What do you mean by "dynamically" ?
When you read an input file, the number of column is never fixed ? Names of field aren't the same ?

[Anonymous]

Number of the columns and name of the columns are not fixed.
Any solution?

[Anonymous]

I think you have to develop a routine to perform this task.
Create a method reading the input file:
- When you read the first line (the header line), open a DB connection.
Then write a "create table" query. The SQL field description corresponds to your header columns in your file.
- Then fetch the file to insert data in your new table (SQL insert query).

[Anonymous]

as you said,
I wrote a routine to do the task. But I have question. How can execute this routine in a job. Which Component should I use to call the routine as a part of a job.
Thanks,
sangi

[Anonymous]

Hello

I wrote a routine to do the task. But I have question. How can execute this routine in a job. Which Component should I use to call the routine as a part of a job.

You can call the routine on tJava/tJavaRow/tMap or any text filed. For example, there is a routine called: TalendDate, supplied by Talend, then you call it on tJava like this:
Date date=TalendDate.getCurrentDate();
In your case, you can only create the tables dynamically with the same schema, but different table name, as the schema should be created on the design time, not the run time.
Best regards

shong

[Anonymous]

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
public class BuildQuery {
public static String test_query() throws IOException {
BufferedReader in = new BufferedReader(new FileReader("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\test.tsv"));
String str;
while ((str = in.readLine()) != null) {
System.out.println("FirstLine : " + str);
break;
}
String Col[]=str.split("\\t");

StringBuilder sb = new StringBuilder("create table demo(");
for(int i = 0 ; i< Col.length ; i++){
sb.append(Col );
sb.append(" ");
sb.append("varchar(100)");
sb.append(",");
}
sb.delete(sb.lastIndexOf(","),sb.lastIndexOf(",")+1 );
sb.append("); ");
return sb.toString() ;

}

public static String main(String[] args) throws IOException {
return test_query();
}
}

This is my routine.
and uploading the image. I tried calling the method as you said. but giving following error.
String str=BuildQuery.main();
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
BuildQuery cannot be resolved
at test.test.test.tJava_1Process(test.java:127)
at test.test.test.runJobInTOS(test.java:257)
at test.test.test.main(test.java:176)
I am not a professional java guy.
and I am sure about mapping. I am using only one tJava component in the mapping. which calls a routine and the routine returns a String (Query). I need to capture the Query and pass it to the MySQL database to create the table.
What will be the probable mapping?

Please help me!!!

[Anonymous]

Hello
add the package name:
package routines;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
Best regards
shong

[Anonymous]

Hello Shong,
Its working fine. The routine is now giving the SQL Query (Create table) as a return. How can I use this query to create a table? Which component should I use?
Thanks,
sangi

[Anonymous]

Hello
With the tDBxxxRow(like tMysqlRow), you can execute the standard SQL statement.
Best regards

shong