Duplicate file check at DB table

[Former Member]

Hi Friends,

My scenario is File to JDBC and while insert the file first time to DB, i am fectching the file name dynamically and inserting to DB table.

now my requirement is when i process the files to DB, it should check whether the file was lareday processed or not. So how can i do this in PI70.

i found that we can do that by using UDF in Mapping. if so please provide me the lookup code(Java) to check the file name from DB.

i craeted a look up channel and db table with the field "PI_filename". So please help me with UDF-Java code

Best Regards,SARAN

[PriyankaAnagani]

Hi,

Please try with the below code...If the file is already processed the UDF returns true otherwise false...based on that decide whether you want to process it or not.....

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

String fileName = "";

Query = "Select PI_filename from table";

try{

channel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME");

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(Query);

DynamicConfiguration conf = (DynamicConfiguration) container

.getTransformationParameters().get(StreamTransformationConstants.DYNAMIC_CONFIGURATION);

DynamicConfigurationKey key = DynamicConfigurationKey.create( "http:/"+"/sap.com/xi/XI/System/File","FileName");

fileName = conf.get(key);

for(Iterator rows = resultSet.getRows();rows.hasNext();){

Map rowMap = (Map)rows.next();

if (fileName.equals((String)rowMap.get("PI_filename")))

result.addValue("true");

else

result.addValue("false");

}

}

catch(Exception ex){

result.addValue(ex.getMessage())

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage())

}

}

Please go through the below link...

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

Regards,

Priyanka

[Former Member]

Hi Priyanka,

Thanks for the reply.

while creating a new FM with the code given by you, do i need to select Cache as Queue or Value or Context?

Also how many arguments i have to mention. one argument "fileName" is enough. what are the input field for this function module at mapping? kindly provide details

Best Regards,SARAN

[PriyankaAnagani]

Hi Saran,

This UDF will not require any arguments as we are getting the filename dynamically... It'll get the source filename and compare it with the PIFilename obtained from DB lookup...if the filename exists in the DB returns true else false....

Regards,

Priyanka

[Former Member]

Hi Priyanka,

Thanks for clarification. but once created UDF and then mapped that udf to root node, if i try to save Mapping it shows following syntax error at UDF.

Source code has syntax error:

/usr/sap/XX1/XXXXXXX00...........java:206: ';' expected

} ^

/usr/sap/XX1/XXXXXXX00...........java:214: ';' expected

}

^

2 errors

Seems that we had missed the ; or } for some code lines...please help me to debug.

Also please let me know where we used the "String fileName = ""; "

Best Regards,SARAN

[PriyankaAnagani]

Hi Saran,

sorry...I've missed the ';' in both the catch blocks and and the fileName is used to collect the name of the source file using dynamicConfiguration.

Thanks & Regards,

Priyanka

[Former Member]

message deleted, as answer arrived just before...

[Former Member]

Hi Priyanka,

when i run the interface, UDF is not validating the file name...second time also file data is inserting to DB table...:(

i mapped UDF to root node (test_MT) which is 1-1 occurrence and while creating UDF the Cache value i selected as CONTEXT and removed the argument.....will it be ok...or do i need to select any one of the radio buttons VALUE/QUEUE?

my Target Structure in MM is as follows:

test_MT

-->Statement_1

-

---

>tablename

-

---

>Action

-

---

>Table

-

---

>Access

-

---

>filed1

-

---

>filed2

-

---

>filed3

Best Regards,SARAN

[Former Member]

Saran,

refer to below links-

http://wiki.sdn.sap.com/wiki/display/XI/DifferentwaystokeepyourInterfacefromprocessingduplicate+files

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0718e90-93c9-2e10-528f-f4fdf2505...

[Former Member]

Hi Vishal,

i had followed the same link (solution:2) but UDF was not returning file name.

Could you pelase highlight the lines that i need to change in that code for my requirement...like

in the above code...

--> where i have to mention the DB filed name (which will have file names).

--> DB table name and Lookup channel name.

My DBtable name: Invoc_AM

Field name: File_name_Processed; data type: varchar(2);

lookup Channel name: JDBC_file_lookup_MX

Best Regards,SARAN