I have a file I need to process. Based on a value in the file, I need to check in the database if it finds based on the key, then generate an UPDATE statement with values from the current row in the file, otherwise INSERT statement with values from the current row in the file. the output files can be two different (for example inserts.sql and updates.sql) files. I am doing this using sqlite. I am thinking of creating the process like this. FileInput-->SQLiteInput-->MAP-->FileOutputARFF would this work? Thanks, Ravi
hmm! I am not finding a good example of the process how to accomplish this. for example, i have a file that has two columns, and multiple rows. id, desc ------------------ 1 sample1 2 samepl2 database table has two columns with Id as PK --------------------------------------------------- id, desc ------------------ 1 old sample1
now, the file has two entries, and database has one entry I start processing the file, pick up first record, query it in database (select * from table where id = <id from file>), if finds, it I need to write update statement (UPDATE table set desc = <desc from file> WHERE id= <id from file>) otherwise it need to write INSERT statement (INSERT INTO table (id, desc) values (<id from file>, <desc from file>) into two separate files or one file. /IF FOUND, write UPDATE statement as above to a file / file -> dbquery (pass id as input param) / \ \ \NOT FOUND, write INSERT statement as above to a file
i want to pass two parameters to MSsqlinput (ie from_date,to_date) how ? my code below and outstanding_from_date >=" + TalendDate.formatDate("yyyy-MM-dd",context.parama) " and outstanding_to_date <= " + TalendDate.formatDate("yyyy-MM-dd",context.paramb) this gave error"delete this token,can you please guide,thanks in advance.
The error is because you're missing the + needed to concatenate the string containing the second part of your condition. But you also haven't included the single quotes that SQL Server uses to delimit dates:
and outstanding_from_date >='" + TalendDate.formatDate("yyyy-MM-dd",context.parama) + '" and outstanding_to_date <= '" + TalendDate.formatDate("yyyy-MM-dd",context.paramb) + "'"
Thanks alevy but still error'invalid character constant' ,the below sql in tMSSQlInput ,pls guide. "select count(1) from pf_account a ,pf_os b where a.perform_id = b.Perform_id and (a.gl_account_id not like '080%' or a.gl_account_id not in (select gl_account_id from pf_gl_code_exclusion)) and outstanding_from_date >='" + TalendDate.formatDate("yyyy-MM-dd",context.parama) + '" and outstanding_to_date <= '" + TalendDate.formatDate("yyyy-MM-dd",context.paramb) + "'"
"Exception in component tMSSqlInput_1 java.sql.SQLException: Conversion failed when converting datetime from character string." This eror comes when i execute the talend job and the parameters are string,if they are passed as date the job runs with success. The code in tmssqlinput_1 (parama/b are string if they are dates no issue) "select * from fn_sas_ext( '" + TalendDate.parseDate("yyyy-MM-dd",context.parama) +" ' , '" + TalendDate.parseDate("yyyy-MM-dd",context.paramb) +" ' )" Please guide
Thanks Alevy ,i have to take dates from the user input as dd/mm/yyyy and then pass the dates to mssqlinput in (mm/dd/yyyy) as this format is default for ms sqlserver ,i have taken string as input but it gives error "java.lang.RuntimeException: java.text.ParseException: Unparseable date: "01-02-2011"" please guide, thanks.
If it's stating 'Unparseable date: "01-02-2011"' then it's clearly not in dd/mm/yyyy format. You need to understand what you're trying to do, which is to construct a SQL statement that looks the same as if you were using it directly in SQL Server. To that end, if you have a String date, it must be in the correct format to append to the rest of the SELECT statement. If it is not in the correct format, you need to parse it to a Date (using it's actual format) and then format it back to a String in the correct format. Obviously, if you already have a Date you only need to format it to a String in the correct format. e.g. '" + TalendDate.formatDate("yyyy-MM-dd",TalendDate.parseDate("dd/MM/yyyy",context.parama)) + "'
one more issue ,is oracle global temporary table no use in talend as the data cannot be inserted in another table in the same job,e.g i have a table tbl_stag which is GTT and in the same job through a procedure i am trying to insert into another table but no rows inserted into normal table.please guide.