| Author |
Message
|
| kaushik.godugunur |
 Posted: Wed Jun 05, 2013 4:26 am Post subject: Query about FileInputNode |
 |
|
Newbie
Joined: 27 Feb 2013
Posts: 9
|
Hi,
I've a flow which reads a csv file and loads it into a DB. Before the dataload, the DB is flushed of existing data and then the insert happens. We are using the record by record processing with the file input node as my file size is around 8MB with 85000 records currently and is expected to increase. I'm not able to find a way to make sure that the DB flush happens only before the first record is executed and does not happen for subsequent records.
Kindly suggest a way to get this done.
Thanks in advance
Kaushik |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jun 05, 2013 4:29 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| kaushik.godugunur |
| Posted: Wed Jun 05, 2013 4:35 am Post subject: |
|
|
Newbie
Joined: 27 Feb 2013
Posts: 9
|
Thank you lancelotlinc for the reply!!
But since the control returns to the compute node from FileInputNode after the first record is processed, where im performing the DELETE FROM DATABASE, the flag is being reset everytime a new record is being processed and hence the DELETE.
I hope i understood what you actually meant. Because, as i can see it
SET Environment.Variables.flag = 'TRUE'
IF (Environment.Variables.flag = 'TRUE') THEN
DELETE FROM DB;
SET Environment.Variables.flag = 'FALSE';
END IF;
This above set of statements is executed for every record.
Please let me know if my understanding is wrong!!
Thanks again!! |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jun 05, 2013 4:36 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Your on the right track. You might think about using PROPAGATE - RETURN FALSE (in combination with some logic) rather than only RETURN TRUE.
The solution is within your grasp. Keep poking at it until you get it just right.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Tech1621 |
| Posted: Wed Jun 05, 2013 4:44 am Post subject: |
|
|
Novice
Joined: 29 May 2013
Posts: 23
|
| kaushik.godugunur wrote: |
This above set of statements is executed for every record.
Please let me know if my understanding is wrong!!
Thanks again!! |
What I think from your information provided is after every record processed
add this :
SET Environment.Variables.flag = 'TRUE'
and while flushing the DB use this:
IF (Environment.Variables.flag = 'TRUE') THEN
DELETE FROM DB;
SET Environment.Variables.flag = 'FALSE';
END IF;
I think you also suggested the same.. 
_________________
Anything is easy if you believe in yourself.. |
|
| Back to top |
|
|
| kaushik.godugunur |
| Posted: Wed Jun 05, 2013 4:59 am Post subject: |
|
|
Newbie
Joined: 27 Feb 2013
Posts: 9
|
Hi Tech1621, lacelotlinc,
I apologize by i just don't see it happening.
My requirement is such that when i receive the file with say 100 records and yesterdays file has say, 50 records, I need to delete the 50 existing records and insert the new 100 records. The table does not have any primary key.
Now, I process my file line by line, as in, i set the below mentioned properties in my FileInputNode,
FileInoutNodeProperties -> Records and Elements -> Record detection "Delimited", Delimiter "Custom Delimiter(Hexadecimal)" , Custom delimiter (hexadecimal) "0a", and Delimiter type "Postfix".
Hence for each record of my new 100 record input, my control flows to the following Compute Node where i set the flag as i mentioned before.
So from a logical point of view, setting a flag is not working for me as it is reset everytime and for my current record insertion, the previous record is being deleted.
I was wondering if there is some property which might be able to tell me that the FileInputNode is processing the first record or the second record or so on so that i can add a condition where i check my current record and perform the DELETE.
Kindly correct me if my approach is incorrect or is it just me complicating things here.
Thanks
Kaushik |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jun 05, 2013 5:13 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kaushik.godugunur wrote: |
| I was wondering if there is some property which might be able to tell me that the FileInputNode is processing the first record or the second record or so on so that i can add a condition where i check my current record and perform the DELETE. |
Something like the Record property described here?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| stevarg |
| Posted: Wed Jun 05, 2013 5:29 am Post subject: |
|
|
Novice
Joined: 20 Nov 2012
Posts: 24
|
| The timestamp when the file is read is a unique property on which you could decied on clearing the database. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jun 05, 2013 6:58 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| kaushik.godugunur wrote: |
| I was wondering if there is some property which might be able to tell me that the FileInputNode is processing the first record or the second record or so on so that i can add a condition where i check my current record and perform the DELETE. |
| stevarg wrote: |
| The timestamp when the file is read is a unique property on which you could decied on clearing the database. |
Agreed! Use this property along with Record property. Save the Timestamp in a EXTERNAL variable and reset it when the Record is 1. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jun 05, 2013 7:01 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kash3338 wrote: |
| stevarg wrote: |
| The timestamp when the file is read is a unique property on which you could decied on clearing the database. |
Agreed! Use this property along with Record[i] property. Save the [i]Timestamp in a EXTERNAL variable and reset it when the Record is 1. |
Why? If the requirement is to flush the db before each and every load, then it's valid to flush it when you read the first record of the new load file, which by definition is a new load. What value does the timestamp give?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| stevarg |
| Posted: Wed Jun 05, 2013 7:08 am Post subject: |
|
|
Novice
Joined: 20 Nov 2012
Posts: 24
|
The timestamp holds the value when the first record was read...
and it remains the same for each and every record.
A change in timestamp guarantees that its a new file read by the file input node... |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jun 05, 2013 7:08 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| Vitor wrote: |
| Why? If the requirement is to flush the db before each and every load, then it's valid to flush it when you read the first record of the new load file, which by definition is a new load. What value does the timestamp give? |
 True. I was a bit confused with the timestamp option suggested above. Hence combined both.  |
|
| Back to top |
|
|
| reddy2078 |
| Posted: Wed Jun 05, 2013 7:10 am Post subject: @kaushik.godugunur |
|
|
Newbie
Joined: 21 Apr 2013
Posts: 7
|
| Code: |
IF(InputLocalEnvironment.File.Record = 1) THEN
DELETE FROM Database.
END IF; |
hi kaushik |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Jun 05, 2013 7:10 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| stevarg wrote: |
| A change in timestamp guarantees that its a new file read by the file input node... |
Even the Record value of 1 guarantees that  |
|
| Back to top |
|
|
| kaushik.godugunur |
| Posted: Thu Jun 06, 2013 12:34 am Post subject: |
|
|
Newbie
Joined: 27 Feb 2013
Posts: 9
|
Hi,
Thank you everyone!!
IF( InputLocalEnvironment.File.Record = 1) THEN
DELETE FROM Database;
END IF;
This did the job for me.
Apologies, the broker was down the whole of yesterday for maintenance so I could get a confirmation just now.
Thanks and Regards
Kaushik |
|
| Back to top |
|
|
|
|
