File LookUP using UDF and Graphical Variable

Like other LookUp requirements(RFC/JDBC), we sometime get requirements where we need to LookUp a file from the PI/PO mapping and get the required data. The below wiki is a very good example that demonstrate the process to achieve this.

File Lookup in UDF – Process Integration – SCN Wiki

But in case you need to lookup the file where there can be thousands/millions of records and you need to lookup the content of the file for each record. In such cases, the mapping can run for hours if you try to connect the FTP for each record to get the corresponding data!

So what would be the workaround. The  new feature , in the Message Mapping tool of PI 7.1.,called Graphical Variable along with the WIKI mentioned earlier can make our life easy. How? Instead of checking the value for each record, we take all the contents of the File in the Graphical Variable and use them as and when required!

So in this case, the approach would be similar to below.

First we create a Graphical Variable in the target structure.

Map that Variable like below.

Then we write a code like below which is going to pick all the contents from the file(second function in above pic). Please make sure that the execution type is “All Values of Queue” if you use the below code!

String server = “NAME OF THE FTP SERVER“;

int port = “PORT OF THE FTP SERVER“;

String user = “USER NAME“;

String pass = “PASSWORD“;

String seller = “”;

FTPClient ftpClient = new FTPClient();

    try {

         ftpClient.connect(server, port);

         ftpClient.login(user, pass);

         ftpClient.enterLocalPassiveMode();

         String remoteFile1 = “FILE PATH/FILENAME WITH EXTENSION“;

         InputStream inputStream = ftpClient.retrieveFileStream(remoteFile1);

         InputStreamReader reader = new InputStreamReader(inputStream);

    BufferedReader buffer = new BufferedReader(reader);

    String read;

        for(String line=buffer.readLine();line!=null;line=buffer.readLine())

        {

         result.addValue(line);

        }

        buffer.close();

        } catch (IOException ex) {

            System.out.println(“Error: ” + ex.getMessage());

            ex.printStackTrace();

        } finally {

            try {

                if (ftpClient.isConnected()) {

                    ftpClient.logout();

                    ftpClient.disconnect();

                }

            } catch (IOException ex) {

                ex.printStackTrace();

            }

        }

Now once the mapping runs, the Variable has got all the content from the FTP.

Now you can use them as and when required without doing another polling to the FTP server! Below is an example.

In case you need further information on Graphical Variable, please seeSAP PI 7.1 Mapping Enhancements Series: Using Graphical Variable