File to Jdbc: how to get record count from DB table

[Former Member]

Hello,

i have a scenario file to JDBC....ihave to insert input file data to DB table.

now my requirement is ..if i insert a file with 50 records to db table on first time, and then next time if i insert the file with 100 records to db table....the count should start from 51 in db table.

if i insert another file 3rd time, with 25 records, the count shold start from 151...so how can i achieve this functionality....

i think following options:

1. in mapping write lookup to call JDBC sender channel and fetch count and map same to target filed of DB. if this is ok...please provide UDF code..2. db triggers (not suitable for my req.)

So kindly let meknow possible solutions and required UDF codes..please

Thanks in advance...SARAN

[baskar_gopalakrishnan2]

>>>..if i insert a file with 50 records to db table on first time, and then next time if i insert the file with 100 records to db table....the count should start from 51 in db table.

if i insert another file 3rd time, with 25 records, the count shold start from 151...so how can i achieve this functionality....

i think following options:

Suggestions:

1)Create an id column in db table. Talk to DB guy to create oracle sequencer for that column. So, every time you insert record that field will be updated with oracle sequencer. You dont need to handle this.

Refer this link

http://www.techonthenet.com/oracle/sequences.php

2) If you want to handle via pi, following suggestions..

If you use PI 7.1 or above, use jdbc lookup in the mapping and do the query something like this

Refer this link

/people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups

Do select query as below

select count(*) from tablename;

the above query will return number of rows exist in the table. So use that value and map it in the target field.

If you use pi 7.0 or below then use the previous reply and do the UDF implementation for jdbc lookup during mapping. Because jdbc lookup does not support in those versions.

Hope that helps.

Baskar

[Former Member]

Hello,

Thanks for quick reply. i have gone through the documents but as i am using PI70 SP14, i am not able to follow those documents/code. So Could you please provide me the UDF code to fetch the record count from Oracle DB table. Also do i need to create/Call a sender JDBC/receiver JDBC insdie the UDf...please clarify.

Secquence option was not suitable due to i have multiple tables (4) with primary keys.

Thanks in advance....SARAN

[baskar_gopalakrishnan2]

>>So Could you please provide me the UDF code to fetch the record count from Oracle DB table.

Follow this link and code is pretty much given for UDF.

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

In the above link, code is given where sample query string provide as below

String Query = "Select count() from tablename;*

Hope that helps

[Former Member]

Saran,

Consider using an approach suggested in Sudip's white paper below:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/503e9bea-ea09-2e10-ec8a-cdf369de9...

I think this is the lightest and easiest way to implement your requirement.