FILE TO JDBC -->JDBC RESPONSE TO DB Table

[Former Member]

Hi Experts,

My Scenario is FILE TO JDBC:

File -

---

>DB(has to update 2 DB tables){one for inserting correct data records and the other for inserting error data records}

-

---

>DB response to update into another table (number of records has been inserted into DB in the above flow)

I have a file to be transfered through PI and store the data into Database.The file has different kinds of rows like header,payment and detail.

Now my requirement is:

1)it should use a control table to store when they ran, records processed, etc.

2)If some rows have wrong information (I.e. invalid number of fields) the entire row(error) should be stored into error tables field.

3)how do i capture number of records that has been inserted into DB and insert into a seperate table.

Could anybody suggest in which way i can proceed to fulfill the requirement.

Thanks in advance for your help.

Regards,

Kalam.

[baskar_gopalakrishnan2]

To take care all your 3 requirements, only stored procedure is the solution.

1 and 3 can be done with normal jdbc statement procedure. But 2)based on errror, save it in error table ...not possible with the normal way.

[Former Member]

where you wanted to check for the error record????other for inserting error data records??

give more details on this....

u either call single stored procedure and based on the error or successful message u insert the records in any of the table also u can update the total count after both the tables are updated...

Stored procedure will have a code to do all the above process.

chirag

Edited by: Chirag Gohil on Feb 10, 2011 9:14 PM

[Former Member]

Hi Chirag,

Thank you very much for your quick reply.

Correct me if i am wrong....I am planning to do the error validation at runtime(in mapping root node level).Please suggest me which is the better way for error validation for my scenario.

-->I am very new to stored procedure concept could you please explain this concept in detail.

-->Do we have any other possibility with out using stored procedure..if yes please suggest.

Thanks and Regards,

Kalam.

[Former Member]

wat type of validation check u wanted to perform.. is it like field length or charvach check.....

its a piece of oracle code which will be called from PI and that piece of code will do all the process which u have just mentioned.

I dont think there is any simple solution than this.

give ur requirement to oracle developer he can build for u n in PI u just need to call that stored procedure and pass the data.

Check this for how to call stored procedure from PI.

http://help.sap.com/saphelp_nw70/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

chirag

Edited by: Chirag Gohil on Feb 10, 2011 10:36 PM