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]

Thank you!!! I am storing the query into a file. Now, this query has to go to tMysqlRow!!! how is this possible?

[Anonymous]

Hello Sangi,
See the attachment.

More details :
In the input file, I create a column named "query", corresponding to the create table statement in your file.
Then Link your input file containing your query to the tMysqlRow component.
By default, the link called row1.
In tMysqlRow settings:
call the statement in query textbox : "linkName"."inputColumnName"

[Anonymous]

Hi catounz,
Thank you so much!!! Job is done!!!

[Anonymous]

Thanks to one and all...
I am almost done with the job.
Now job is reading the file, building the query and creating the table with the same name.
I need to improve the job to one level above, so that, it picks the file and runs the process as and when a tsv file creates in the folder. For this, I am using the tWaitForFile component, but its not picking the file from the specified folder.
Could you please tell me the tWaitForFile use, how can I use this component.
I will be having more than one file at a time, in that case the job should pick all the files and need to create the respective table.
How can I achieve this?
Many Thanks,
sangi

[Anonymous]

Hello sangi
The tWaitForFile is used to scan one folder, if there is a new file created or delete a exist file in the folder, it will trigger one event. I have created two jobs: myJob1 and myJob2. In myJob1 job, I use the tFileList to iterate all the files in the folder if there are more than one new file created at a time. In myJob2 job, there is only one tJava component, it call the routines. You must run the myJob1 job first, then run the myJob2 Job.
Please see the screenshot.
Best regards

shong

[Anonymous]

Hey shong,
Thanks for everything!!!
The task to be done in one go!!!
1. We will get the tsv file.
2. We need to create a query for every tsv file.
3. Execute each query to create a table in the database.
for this I created a single job!!!
I added tWaitForFile and tFileList to existing job and changed the code in tJava. Rest are same!!!
Please find the attachments.
Here is my final Java routine:
package routines;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
public class BuildQuery {
public static String test_query(String fn) throws IOException {
String filePath=new String("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\"+fn);
BufferedReader in = new BufferedReader(new FileReader(filePath));
String str;
String fileName=filePath.substring((filePath.lastIndexOf('\\')+1), filePath.lastIndexOf('.'));

while ((str = in.readLine()) != null) {
break;
}
String Col[]=str.split("\\t");

StringBuilder sb = new StringBuilder("create table "+ fileName+"(");
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("); ");

BufferedWriter out = new BufferedWriter(new FileWriter("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\query.txt"));
out.write(sb.toString());
out.close();

return sb.toString() ;

}

public static void main(String[] args) throws IOException {

}
}

Hey shong and catounz once again thanks!!!
Today I will sleep happily!!!

bye
sangi