Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: Excel Flies Higher with JExcelApi

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    TechTip: Excel Flies Higher with JExcelApi

    ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
    Hey all, I'm experiencing an error when I try to run this command... I'm passing in the sql statement from an RPG program...basically, I'm letting the user select which file they want to download and which months data they want...so I'm building my sql statment in my RPG program and passing a CL the statement... When the user runs this command, they get the following error in their job log: 700 - SQL2JXL SQLSTMT('select usgdated, usgprt#, usgmach, usgamt, usgauom, usguser from caspusg where usgdated between ''2004-09-01'' and ''2004-09-30'' order by usgmach') TOXLS('/excel/caspusg.xls') SHEETNAME('Paint Usage') COLHDRS(*LABEL) TITLE('Paint Usage') TITLEALIGN(*CENTER) 4500 - CALL PGM(SQL2JXLR) /* The CALL command contains parameters */ 9200 - RUNJVA CLASS('SQL2JXL') PARM(X'6185A783859361E2D8D3C9C4F1F2F8F4F1F94B8481A 300' '/excel/caspusg.xls' ' ' 'Paint Usage' 'L' 'Paint Usage' '-1' 'C' 'Y' 'Y' 'N' 'N' 'Y' 'N' 'SYS' 'N' 'NATIVE' 'LOCALHOST' ' ' ' ' 'N') CLASSPATH('/QIBM/ProdData/OS400/jt400/lib/jt400.jar:/excel:/excel/jxl.ja r') OPTION(*NONE) OUTPUT(*NONE) Java program completed with exit code 1 Function check. JVA0122 unmonitored by SQL2JXLC at statement 0000009200, instruction X'0000'. 12200 - SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Error running SQL2JXL') MSGTYPE(*ESCAPE) Error running SQL2JXL. Function check. CPF9898 unmonitored by CASQAQCC at statement 700, instruction X'001A'. CPF9898 received by CASQAQCC at 700. (C D I R) CPF9898 received by CASQAQCC at 700. (C D I R) Is there some kind of special authority a user needs in order to run this command? I'm posting my CL program. Shane
    Code

    Comment


    • #17
      TechTip: Excel Flies Higher with JExcelApi

      ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
      Please enable the Java console JDEBUG(*YES)and run the command again.

      Comment


      • #18
        TechTip: Excel Flies Higher with JExcelApi

        ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
        How do I do that? Shane

        Comment


        • #19
          TechTip: Excel Flies Higher with JExcelApi

          ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
          write SQL2JXL into cmdline and press F4 and then F10. Scroll up to the end. The last parameter is JDEBUG. Put the value *YES.

          Comment


          • #20
            TechTip: Excel Flies Higher with JExcelApi

            ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
            Ok, I enabled it and ran the program in debug...again, it works fine when I run the command...however, when a user runs the command, it comes up with the following error in their job log: 3500 - SQL2JXL SQLSTMT('select rundated, runmach, runtime, runuser from casprun') TOXLS('/excel/casprun.xls') SHEETNAME('Run Time') COLHDRS(*LABEL) TITLE('Run Time') TITLEALIGN(*CENTER) JDEBUG(*YES) 4500 - CALL PGM(SQL2JXLR) /* The CALL command contains parameters */ Error CrtSQLstmt : open() failed. 3401 : Permission denied.. 12200 - SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Error running SQL2JXL') MSGTYPE(*ESCAPE) Error running SQL2JXL. CPF9898 received by CASQAQCC at 3500. (C D I R) What must I do in order to let regular users use this utility in a CL program? Shane

            Comment


            • #21
              TechTip: Excel Flies Higher with JExcelApi

              ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
              I changed my CL to use the SQL2XLS utility instead of the SQL2JXL utility...and it works...is there a difference between the two? Shane

              Comment


              • #22
                TechTip: Excel Flies Higher with JExcelApi

                ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                I have a question. Why do you MONMSG CPF0000 in your CL? rd

                Comment


                • #23
                  TechTip: Excel Flies Higher with JExcelApi

                  ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                  They use a different set of Api's. SQL2XLS and SQL2POI use Jakarta/POI api's that are more powerful while SQL2JXL uses Andy Khan's JExcelApi that is faster. SQL2XLS uses RPG mixed with Java. This is excellent for small xls but cannot be used for large xls. For these ones, the pure Java SQL2POI and SQL2JXL are the best solution.

                  Comment


                  • #24
                    TechTip: Excel Flies Higher with JExcelApi

                    ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                    What do you mean ?

                    Comment


                    • #25
                      TechTip: Excel Flies Higher with JExcelApi

                      ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                      The question was supposed to follow Shane's posting of CL code which has a MONMSG MSG(CPF0000) in it. I was asking why, but if everything is working just disregard. rd

                      Comment


                      • #26
                        TechTip: Excel Flies Higher with JExcelApi

                        ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                        Many people put a MONMSG CPF0000 to catch any extraneous errors from crashing a CL. The proper way would be to have a standard error routine to handle the situation, like MONMSG MSGID(CPF0000 CPA0000) EXEC(GOTO CMDLBL(ERROR)). To see examples of this, do a search on this site for STANDARD ERROR HANDLING. On the search results page look over to the right in the block "Results From the Forums". Funny thing is, the first post shown when I did that was one of mine. -dan

                        Comment


                        • #27
                          TechTip: Excel Flies Higher with JExcelApi

                          ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                          Refer to Dan Stevens post...

                          Comment


                          • #28
                            TechTip: Excel Flies Higher with JExcelApi

                            ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                            OK, that's cool...I changed the CL to use the SQL2XLS and it works fine...So, I guess I'll just use that one...Thanx for all the input guys... Shane

                            Comment


                            • #29
                              TechTip: Excel Flies Higher with JExcelApi

                              ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                              When i enter the command: SQL2JXL SQLSTMT('select * from books') TOXLS('/xlsfiles/books.xls') COLHDRS(*FLDNAM) JDEBUG(*YES) I get this in the Java Shell Display: Connecting to LOCALHOST with "com.ibm.db2.jdbc.app.DB2Driver" ERROR: INTERNAL ERROR: Creation of DB2Driver object for registering with DriverManager failed. Java program completed with exit code 1 Not sure what I am missing here.

                              Comment


                              • #30
                                TechTip: Excel Flies Higher with JExcelApi

                                ** This thread discusses the article: TechTip: Excel Flies Higher with JExcelApi **
                                Maybe this can help: http://www-912.ibm.com/s_dir/slkbase...6?OpenDocument

                                Comment

                                Working...
                                X