1 2 Previous Next 25 Replies Latest reply on Jan 28, 2016 7:52 PM by Manish.Sharma_82

    Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.

    Manish.Sharma_82

      Local string &xquery = "Testing Apache";

      Local string &xquery1 = "Testing sheet";

      Local JavaObject &XSSFWorkbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbook");

      Local JavaObject &jClass = GetJavaClass("java.lang.Class");

      Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));

      Local JavaObject &jCompileMethod = &XSSFWorkbook.getClass().getDeclaredMethod("createSheet", &jCompileArgTypes);

       

      Local JavaObject &sheet1 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery));

      Local JavaObject &sheet2 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery1));

       

      Local JavaObject &int = GetJavaClass("java.lang.Integer");

      Local JavaObject &IntType = GetJavaClass("java.lang.Integer").TYPE;

      Local JavaObject &jIntArgTypes = CreateJavaObject("java.lang.Class[]", &IntType);

       

       

      Local JavaObject &jcreateRow = &sheet1.getClass().getDeclaredMethod("createRow", &jIntArgTypes);

       

       

      Local number &i, &k;

      &k = 1;

      For &i = 1 To 40000

         Local JavaObject &Row = &jcreateRow.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", &i));

         Local JavaObject &jcreatecell = &Row.getClass().getDeclaredMethod("createCell", &jIntArgTypes);

         Local JavaObject &cell = &jcreatecell.invoke(&Row, CreateJavaObject("java.lang.Object[]", &k));

         Local JavaObject &setCellValue = &cell.getClass().getDeclaredMethod("setCellValue", &jCompileArgTypes);

         Local JavaObject &cellvalue = &setCellValue.invoke(&cell, CreateJavaObject("java.lang.Object[]", "Interesting this is nice"));

      End-For;

      rem Local JavaObject &autoSizeColumn = &sheet1.getClass().getDeclaredMethod("autoSizeColumn", &jIntArgTypes);

      rem Local JavaObject &autoSize = &autoSizeColumn.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", 0));

       

      Local JavaObject &out = CreateJavaObject("java.io.FileOutputStream", "/psoft/fs9devt/UserUpload/ap/comments12.xlsx", True);

      &XSSFWorkbook.write(&out);

      &out.close();

       

      getting below Error while executing the above code through Application Engine but it worked for 1 line printing, but when doing it in Loop it throws this error.

      Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.

        • 1. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
          Jim.Marion-Oracle

          You are a brave person to travel down the reflection abyss.

           

          I work around this by using JavaScript in PeopleCode. I usually store my JavaScript in a separate table so I can maintain it with an embedded JavaScript online editor. Alternatives include Message Catalog Entry and HTML definitions (only available for online PeopleCode).

           

          Here are some code samples.

           

          PeopleCode:

           

          Local string &descr; Local string &script;

          REM ** Replace with Message catalog if storing there; SQLExec(SQL.JM_GET_SCRIPT, "CREATE_WORKBOOK", &script); MessageBox(0, "", 0, 0, &script); Local JavaObject &manager = CreateJavaObject("javax.script.ScriptEngineManager"); Local JavaObject &engine = &manager.getEngineByName("JavaScript"); REM ** Evaluate a simple JavaScript; &engine.eval(&script); REM ** Access the value of the JavaScript variable named result; Local string &result_text = &engine.get("result").toString(); MessageBox(0, "", 0, 0, &result_text);

           

          JavaScript:

           

          var result = (function() {
              // import statements
              var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
              var FileOutputStream = Packages.java.io.FileOutputStream;
          
          
              // variable declarations
              var workbook = new XSSFWorkbook();
              var sheet = workbook.createSheet("Countries");
              var fileName = "c:/temp/countries.xlsx";
            
              var row = sheet.createRow(0);
              var cell = row.createCell(0);
          
          
              cell.setCellValue("United States of America");
              cell = row.createCell(1);
              cell.setCellValue("USA");
          
          
              row = sheet.createRow(1);
              cell = row.createCell(0);
              cell.setCellValue("India");
              cell = row.createCell(1);
              cell.setCellValue("IND");
          
          
              row = sheet.createRow(1);
              cell = row.createCell(0);
              cell.setCellValue("Denmark");
              cell = row.createCell(1);
              cell.setCellValue("DNK");
          
          
              var fos = new FileOutputStream(fileName);
              workbook.write(fos);
              fos.close();
            
              return "Created workbook " + fileName;
          
          
          }());
          

           

          If you need to make data available to the ScriptEngine manager, then use &engine.put("variable_name", "string_value"). I usually use JSON to send structured data and then JSON.parse in the JavaScript.

          1 person found this helpful
          • 2. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
            Sasank Vemana

            Jim - I was thinking about your presentation when this question was raised!

             

            "You are a brave person to travel down the reflection abyss." - LOL!

             

            Not meaning to hijack this conversation, but I am interested in finding how you integrated the javascript online editor. I don't recall the exact editor name you mentioned in your session. The other question I have is, how did you manage the transfer of the editor contents into your table during a save (how does it get from the component buffer to the database on save)?

             

            Thanks!

            • 3. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
              Jim.Marion-Oracle

              It would be nice to fork a thread instead of hijack.

               

              I use the ACE code editor. I use Ajax to marshall the content. The page is fronted by a component for searching, but is really an iScript for display and an iScript for saving.

              • 4. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                Sasank Vemana

                Thank you Jim! Apologize for the adhoc/unrelated question. I will give it a try!

                • 5. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                  Manish.Sharma_82

                  Hi Jim,

                   

                  Really Thanks a Ton for your code snippet, you saved more of my days from this JAVA reflections which i have worked on to get it right. as your Java script code works great and the file created too, but my doubt still exists, as how to pass the 100 thousand rows from a query suppose for java reflection like this in peoplecode

                   

                  Local SQL &Sql1 = CreateSQL("SELECT VENDOR_ID FROM PS_FZAP_OPPAY_DMOV ");

                  While &Sql1.Fetch(&VENDOR_ID)

                     Local JavaObject &jcreateRow = &sheet1.getClass().getDeclaredMethod("createRow", &jIntArgTypes);

                     Local JavaObject &Row = &jcreateRow.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", &i));

                     Local JavaObject &jcreatecell = &Row.getClass().getDeclaredMethod("createCell", &jIntArgTypes);

                     Local JavaObject &cell = &jcreatecell.invoke(&Row, CreateJavaObject("java.lang.Object[]", &k));

                     Local JavaObject &setCellValue = &cell.getClass().getDeclaredMethod("setCellValue", &jCompileArgTypes);

                     &cellvalue_result = &setCellValue.invoke(&cell, CreateJavaObject("java.lang.Object[]", &VENDOR_ID));

                     &i = &i + 1;

                  End-While;

                   

                  and then on later stage go back and pick up the file again and enter some total on the Excel file  in last or enter  more cells in the last for some adjustment summary. like we have that ability to use through

                  &FZAP_WorkApp = CreateObject("COM", "Excel.Application");

                  &FZAP_WorkApp.DisplayAlerts = "False";

                  &FZAP_WorkBook = ObjectGetProperty(&FZAP_WorkApp, "Workbooks");

                   

                  as we can save it and then on later stage open that worksheet and modify some cell.

                   

                  &FZAP_WorkSheet = &FZAP_WorkApp.Worksheets("Domestic");

                   

                  as still we have a report like this where we have used a Excel template placed at our app server, then our AE runs and pull that template from the app server and it will save it as a new file at different location and start filling the data in that report in different tabs, and it worked great.

                   

                  But since we have upgraded to Ptools8.54, this report is not generating  with MSExcel 2013, it get stuck. and as its a Third part issue(MS Excel)

                  So we have raised the issue with Microsoft about it but got the response as they cant help much at this moment of time because the product is getting stable with PT 8.54. So i have decided to do this with Apache POI. but not getting an idea as how to replicate the same thing with this.

                   

                  Thanks

                  manish

                  • 6. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                    Manish.Sharma_82

                    Hi Jim,

                     

                    Really Thanks a Ton for your code snippet, you saved more of my days from this JAVA reflections which i have worked on to get it right. as your Java script code works great and the file created too, but my doubt still exists, as how to pass the 100 thousand rows from a query suppose for java reflection like this in peoplecode

                     

                    Local SQL &Sql1 = CreateSQL("SELECT VENDOR_ID FROM PS_FZAP_OPPAY_DMOV ");

                    While &Sql1.Fetch(&VENDOR_ID)

                       Local JavaObject &jcreateRow = &sheet1.getClass().getDeclaredMethod("createRow", &jIntArgTypes);

                       Local JavaObject &Row = &jcreateRow.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", &i));

                       Local JavaObject &jcreatecell = &Row.getClass().getDeclaredMethod("createCell", &jIntArgTypes);

                       Local JavaObject &cell = &jcreatecell.invoke(&Row, CreateJavaObject("java.lang.Object[]", &k));

                       Local JavaObject &setCellValue = &cell.getClass().getDeclaredMethod("setCellValue", &jCompileArgTypes);

                       &cellvalue_result = &setCellValue.invoke(&cell, CreateJavaObject("java.lang.Object[]", &VENDOR_ID));

                       &i = &i + 1;

                    End-While;

                     

                    and then on later stage go back and pick up the file again and enter some total on the Excel file  in last or enter  more cells in the last for some adjustment summary. like we have that ability to use through

                    &FZAP_WorkApp = CreateObject("COM", "Excel.Application");

                    &FZAP_WorkApp.DisplayAlerts = "False";

                    &FZAP_WorkBook = ObjectGetProperty(&FZAP_WorkApp, "Workbooks");

                     

                    as we can save it and then on later stage open that worksheet and modify some cell.

                     

                    &FZAP_WorkSheet = &FZAP_WorkApp.Worksheets("Domestic");

                     

                    as still we have a report like this where we have used a Excel template placed at our app server, then our AE runs and pull that template from the app server and it will save it as a new file at different location and start filling the data in that report in different tabs, and it worked great.

                     

                    But since we have upgraded to Ptools8.54, this report is not generating  with MSExcel 2013, it get stuck. and as its a Third part issue(MS Excel)

                    So we have raised the issue with Microsoft about it but got the response as they cant help much at this moment of time because the product is getting stable with PT 8.54. So i have decided to do this with Apache POI. but not getting an idea as how to replicate the same thing with this.

                     

                    Thanks

                    manish

                    • 7. Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                      Jim.Marion-Oracle

                      Manish, there are a couple of options:

                       

                      1. Convert query results to JSON and pass that through as a variable using &engine.put(...). I don't recommend this because the data could be large and difficult to convert.
                      2. Invoke a function through the JavaScript script engine interface. With this scenario, the anonymous part of the script would create the spreadsheet and put the worksheet, etc into variables. This would happen through &engine.eval(&script). Then you would invoke a JavaScript function for each row, and that function would add the rows and cells to the spreadsheet. This can be painful because Java would cast &engine to Invocable, which you can't do in PeopleCode, taking you back into reflection (see: https://docs.oracle.com/javase/8/docs/technotes/guides/scripting/prog_guide/api.html).
                      3. Do it all in JavaScript. Pretty much everything you want to accomplish in PeopleCode, you can do in Java (or JavaScript). From what I can tell, you can't access the component buffer, but you do have all of the API's and functions.

                       

                      With #3, if I wanted to access SQLExec, I would do this:

                       

                      var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
                      //...
                      SQLExec("INSERT INTO PS_JM_XLROLE_STAGE VALUES(:1, :2, :3, SYSTIMESTAMP)", [guid, roleName, descr]);
                      

                       

                      To access a record, I would use:

                       

                      var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
                      var Name = Packages.PeopleSoft.PeopleCode.Name;
                      var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
                          
                      var rec = CreateRecord(new Name('RECORD', 'JM_SCRIPT_TBL'));
                      

                       

                      One thing to keep in mind is LCM and PeopleTools. JavaScript is outside the standard LCM, so you want to avoid putting names of definitions in JavaScript. Instead, you might declare them in PeopleCode and then "put" them into the engine as variables. That way "Find definition references..." and other LCM tools work properly

                      1 person found this helpful
                      • 8. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                        Manish.Sharma_82

                        Hi Jim,

                         

                        i am very thankful to your suggestions, and i have tried option#3 for my report.

                        Sorry to bug you again but in the javascript i am facing few issues.

                         

                        i = 4;

                        var CreateSQL = Packages.PeopleSoft.PeopleCode.Func.CreateSQL;

                        var SQL = Packages.PeopleSoft.PeopleCode.SQL;

                        var VENDOR_ID=[];

                        SQL = CreateSQL("SELECT VENDOR_ID FROM PS_FZAP_OPPAY_DMOV WHERE ROWNUM<50");

                        while (SQL.Fetch([VENDOR_ID])) {

                            row = sheet.createRow(i); 

                            cell = row.createCell(0); 

                            cell.setCellValue("Vendor"); 

                            cell = row.createCell(1); 

                            cell.setCellValue(String(VENDOR_ID[1]));

                        i=i+1;

                        }

                         

                         

                        getting the output as below, where instead of value from the query results its showing undefined, even i have used[0] also.

                         

                        Vendorundefined
                        Vendorundefined
                        Vendorundefined

                         

                        and if i remove the rownum criteria from the SQL and run it for all 111800 rows the process error out as below.

                         

                        Java Exception: java.lang.OutOfMemoryError: GC overhead limit exceeded: during call of javax.script.ScriptEngine.eval. (2,763) FZPO_TEST.MAIN.GBL.default.1900-01-01.Step03.OnExecute PCPC:635 Statement:5

                         

                        Thanks

                        Manish

                        • 9. Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                          Jim.Marion-Oracle

                          I don't have an example to test and confirm, but it looks like the problem is with the VENDOR_ID variable. The declaration makes it an array, but then when calling SQL.Fetch, the VENDOR_ID array is wrapped in an anonymous array, which is later discarded. Here is how I would rewrite this:

                           

                          //...
                          var columns = [null] // one 'null' item in the array for each SQLExec selected column.
                          
                          //...
                          while (SQL.Fetch(columns)) {
                              row = sheet.createRow(i); 
                              cell = row.createCell(0); 
                              cell.setCellValue("Vendor"); 
                              cell = row.createCell(1); 
                              cell.setCellValue(String(columns[0])); // I am not sure if String(...) is necessary
                              i = i + 1;
                          }
                          

                           

                          When declaring the columns array, I initialized the array length by including a null item. I am not sure if that is necessary. First, get it working, then try removing the "null" in the initialization.

                           

                          Give this a try. If it doesn't work, I'll see if I can work up a quick test case.

                          • 10. Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                            Manish.Sharma_82

                            i tried this and other different option but still no success,i have a doubt about the CreateSQL and SQL class in javascript as per the package

                              public native boolean Fetch(Object[] paramArrayOfObject) of  SQL Class; will return a Boolean, paramArrayOfObject which we are passing should get loaded.but its not happening and the value its printing through cell.setCellValue(String(columns[0])); is null or whatever we initialize.

                             

                            So does its just getting boolean and entering the While loop until its true and not loading the value from CreateSQL object or we have to call another function of these classes to get  the value from the sql which we executed through sql in each iteration.

                             

                            still working on this more, i got this doubt so thought of discussing with you.

                             

                            Thanks

                            Manish

                             

                            • 12. Re: Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                              Jim.Marion-Oracle

                              I ran into the same thing as you. I did a lot of experimenting and found this to work:

                               

                              var result = (function() {
                                  var ReflectiveArray = java.lang.reflect.Array;
                                  var CreateSQL = Packages.PeopleSoft.PeopleCode.Func.CreateSQL;
                                  var columns = ReflectiveArray.newInstance(java.lang.Object, 1 /* number of selected columns */);
                                  var results = [];
                                  
                                  SQL = CreateSQL("SELECT OPRDEFNDESC FROM PSOPRDEFN WHERE ROWNUM < 10");
                                  while (SQL.Fetch(columns)) {
                                      results.push(columns[0]);
                                  }
                                  
                                  return results.join();
                                  
                              }());
                              

                               

                              It appears to have been a translation problem between the JavaScript array and the Java Array Object[]. I resolved this by using java.lang.reflect.Array.newInstance to create a new instance of Object[1].

                               

                              Next issue to deal with is your memory issue. From the error message, it seems to have something to do with Garbage Collection and lots of temporary objects. Did you declare row and cell using var row = ...  and var cell = ...? If not, then they are in the global namespace and are properties of the global object. At the end of each loop iteration, use delete row and delete cell. If you used var to declare them, then set their value to null at the end of each iteration. Based on your code, I would expect each time through the loop would drop the reference count, but I've seen where explicitly setting to null helps. The only other temporary object I see is the value in the array. Just for fun, I added this at the end of each loop and it ran just fine:

                               

                              columns[0] = null;
                              
                              1 person found this helpful
                              • 13. Re: Re: Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                                Jim.Marion-Oracle

                                Let me know if you continue to get the Garbage Collection memory issue after making the changes above. I'm wondering if a strategically placed System.gc would help.

                                • 14. Re: Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.
                                  Manish.Sharma_82

                                  Hi Jim,

                                   

                                  Great!!Thanks a Lot for your guidance and support! this thing works the way you describe and the data is now populating in the file from the query. but unfortunately i am still facing an issue with the out Of Memory. and its not processing more than 20K rows. i have asked our sysAdmin to increase the Java Heap size on Weblogic to see if it helps or not. and will analyze it more to get rid of this error. as want to create that Excel report with this approach.

                                   

                                  here is the final code which i have used as you told

                                   

                                  ************************************************************ In App Engine the Peoplecode is ************************************************************

                                   

                                  Local string &descr;

                                  Local string &script;

                                   

                                   

                                  REM ** Replace with Message catalog if storing there;

                                  SQLExec("select FZPO_COMMENTS from PS_FZPO_JS_CREATE where FIELDNAME=:1", "CREATE_WORKBOOK", &script);

                                  MessageBox(0, "", 0, 0, "&script   " | &script);

                                  Local JavaObject &manager = CreateJavaObject("javax.script.ScriptEngineManager");

                                  Local JavaObject &engine = &manager.getEngineByName("JavaScript");

                                  REM ** Evaluate a simple JavaScript;

                                  &engine.eval(&script);

                                  REM ** Access the value of the JavaScript variable named result;

                                  Local string &result_text = &engine.get("result").toString();

                                  MessageBox(0, "", 0, 0, &result_text);

                                   

                                  ************************************************************ and inserted in the table as below***************************************

                                  insert into PS_FZPO_JS_CREATE  

                                     SELECT 'CREATE_WORKBOOK',

                                  'var result = (function() { 

                                      // import statements 

                                      var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook; 

                                      var FileOutputStream = Packages.java.io.FileOutputStream; 

                                   

                                   

                                      // variable declarations 

                                      var workbook = new XSSFWorkbook(); 

                                      var sheet = workbook.createSheet("Countries"); 

                                      var fileName = "/psoft/fs9devt/UserUpload/ap/test123.xlsx"; 

                                     

                                      //var row = sheet.createRow(0); 

                                      //var cell = row.createCell(0);

                                       

                                    i = 1; 

                                   

                                      var ReflectiveArray = java.lang.reflect.Array; 

                                      var CreateSQL = Packages.PeopleSoft.PeopleCode.Func.CreateSQL; 

                                      var columns = ReflectiveArray.newInstance(java.lang.Object, 1 /* number of selected columns */); 

                                      var results = []; 

                                       

                                      SQL = CreateSQL("SELECT VENDOR_ID FROM PS_FZAP_OPPAY_DMOV where rownum <25000"); 

                                      while (SQL.Fetch(columns)) { 

                                          results.push(columns[0]);

                                           

                                      var row = sheet.createRow(i); 

                                      var cell = row.createCell(0);

                                      cell.setCellValue("Vendor");  

                                      cell = row.createCell(1);

                                      cell.setCellValue(String(columns[0]));

                                      i = i + 1; 

                                      cell=null;

                                      row=null;

                                      columns[0] = null;

                                      }

                                        

                                      var fos = new FileOutputStream(fileName); 

                                      workbook.write(fos); 

                                      fos.close(); 

                                     

                                      return "Created workbook " + fileName;

                                   

                                  }());'from DUAL;

                                  1 2 Previous Next