Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I hope you can help me. I'm new working with TOS and with databases
The source of my data are some files (.DBF format) in a folder located in my PC (C:\), it's names can change so I need that my query take every file in that folder. Now, as I know the name files, I have this query:
"SELECT * FROM 20180222AL
union all
SELECT * FROM 20180223AL
union all"
Where "20180222AL" and "20180223AL" are the names of my files.
But, as I said before, in the real life, the names are going to change and I need that my query take all the files in the folder.
¿What can I do?
Thanks!!
you could split job into 2 parts - prepare sql, and run sql
part 1:
tFileList - and iterational prepare statements (need fix final code):
in tJavaFlex code:
if (context.sql_text.equals("")){ context.sql_text="SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); } else { context.sql_text=context.sql_text+" \nUNION ALL \n"+"SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); };
after first part context.sql_text will contain sql code:
SELECT * FROM cp1251.dbf UNION ALL SELECT * FROM dbase_30.dbf UNION ALL SELECT * FROM dbase_8b.dbf UNION ALL SELECT * FROM dbase_f5.dbf
for all files in folder
in a second part just put instead sql code
context.sql_code
without double quotes around
UPD:
instead of ((String)globalMap.get("tFileList_1_CURRENT_FILE")), you can use ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")) for include full path for files
you could split job into 2 parts - prepare sql, and run sql
part 1:
tFileList - and iterational prepare statements (need fix final code):
in tJavaFlex code:
if (context.sql_text.equals("")){ context.sql_text="SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); } else { context.sql_text=context.sql_text+" \nUNION ALL \n"+"SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); };
after first part context.sql_text will contain sql code:
SELECT * FROM cp1251.dbf UNION ALL SELECT * FROM dbase_30.dbf UNION ALL SELECT * FROM dbase_8b.dbf UNION ALL SELECT * FROM dbase_f5.dbf
for all files in folder
in a second part just put instead sql code
context.sql_code
without double quotes around
UPD:
instead of ((String)globalMap.get("tFileList_1_CURRENT_FILE")), you can use ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")) for include full path for files
Hi vapukov,
Thank you for your answer. I have been trying to solve my problem with your suggestion since you answered me but I just couldn’t, so here I am again.
I put in my job the tFileList component with these basic settings:
Then, the tJavaFlex with these basic settings:
When I run the job, I get this error (sql_text cannot be resolved or is not a field):
What I’m doing wrong?
In addition, I don't know how to set the component tFixedFlowInput. I did it like this, but I don’t know if I did it right:
I'm really sorry to be so confused, but as I was saying, I'm just beginning to learn from TOS and java programming. Thanks for your help!
hi,
sql_text cannot be resolved or is not a field
it means - sql_text context variable (and any other), must be declared before used
you must define sql_text in context tab: