12 Replies Latest reply on Dec 7, 2015 10:36 AM by Francisco Amores

    Executing SQL statement in JYTHON : SQL string is not Query error

    EBD

      Hello Experts,

       

      I've been trying to execute the following jython script in FDMEE 11.1.2.4 for a few days with no luck. I believe it has something to do with the way the sql statement is formatted based on the return error from ODI however i'm unsure of how to fix it. Any advice?

       

      Script

      --------------------------------------------------------------------------------------------------------------------------------

      import java.sql as sql

      import com.hyperion.aif.scripting.API as API

       

      insertStmt = """

      INSERT INTO HYPFDMEE.aif_open_interface (

      batch_name,

      year,

      period,

      period_num,

      dataview,

      col01,

      col02,

      col03,

      col04,

      col10,

      col05,

      col06,

      col07,

      col08,

      col09,

      currency,

      amount,

      amount_ytd,

      amount_ptd

      )

      """

      fdmAPI = API()

      sourceConn = None

      sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@server:port etc"

       

      selectStmt =(

      "DECLARE"

      "l_period_name VARCHAR2(30);"

      "l_batch VARCHAR2(30);"

      "BEGIN"

      "SET l_period_name := fdmContext[""PERIODNAME""]"

       

      "SELECT to_char(SYSDATE,'YYYYMMDD_HH24MISS') dtime "

      "INTO l_batch"

      "FROM dual;"

       

      "DELETE FROM HYPFDMEE.aif_open_interface;"

       

      "SELECT"

      "gb.period_name period,"

      "gb.period_num period_num,"

      "'YTD' dataview,"

      "gcc.segment1 legal_entity,"

      "gcc.segment2 sbu,"

      "gcc.segment3 site,"

      "gcc.segment4 center,"

      "gcc.segment5 ||'_' ||gcc.segment6 concat_account,"

      "gcc.segment7 product_category,"

      "gcc.segment8 region,"

      "gcc.segment9 affiliate,"

      "gcc.segment10 future1,"

      "gcc.segment11 future2,"

      "gb.currency_code currency,"

      "(begin_balance_dr-begin_balance_cr)+(period_net_dr-period_net_cr) amount,"

      "(begin_balance_dr-begin_balance_cr)+(period_net_dr-period_net_cr) amount_ytd,"

      "(period_net_dr-period_net_cr) amount_ptd"

      "FROM"

      "xxgaia_tic_gl_balances gb,"

      "xxgaia_tic_gl_code_combs gcc,"

      "xxgaia_tic_gl_ledgers gl"

      "WHERE 1=1"

      "AND gb.code_combination_id=gcc.code_combination_id"

      "AND gl.ledger_id= gb.ledger_id"

      "AND gl.ledger_category_code= 'PRIMARY'"

      "AND gl.currency_code=gb.currency_code"

      "AND (period_net_dr-period_net_cr) <> 0"

      "AND gb.period_name= l_period_name;"

       

      "DELETE FROM HYPFDMEE.xxtic_aif_open_interface_a;"

       

      "INSERT INTO HYPFDMEE.xxtic_aif_open_interface_a"

      )

      stmt = sourceConn.prepareStatement(selectStmt)

      stmtRS = stmt.executeQuery()

      while(stmtRS.next()):

        params = [l_batch, l_period_name]

      fdmAPI.executeDML(insertStmt, params, False)

      fdmAPI.commitTransaction()

      stmtRS.close()

      stmt.close()

      sourceConn.close()

       

      Error from ODI

      -------------------------------------------------------------------------

      ODI-1217: Session COMM_LOAD_BALANCES (128501) fails with return code 7000. ODI-1226: Step FAILED - Invalid Source System fails after 1 attempt(s). ODI-1232: Procedure COMM Finalize Process execution fails. Caused By: org.apache.bsf.BSFException: exception from Jython: Traceback (most recent call last): File "<string>", line 13, in <module> File "<string>", line 162, in finalizeProcess RuntimeError: ODI-1226: Step COMM Pre Import Data fails after 1 attempt(s). ODI-1232: Procedure COMM Pre Import Data execution fails. Caused By: org.apache.bsf.BSFException: exception from Jython: Traceback (most recent call last): File "<string>", line 7, in <module> File "<string>", line 4578, in preImportData File "<string>", line 485, in executeScript File "\\tais-hyp-p01.na.toshiba-dpg.local\Data\FDMEE\TIC_HFM/data/scripts/event/BefImport.py", line 79, in <module> stmtRS = stmt.executeQuery() at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1464) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671) at sun.reflect.GeneratedMethodAccessor646.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597)

      java.sql.SQLException: java.sql.SQLException: SQL string is not Query


      Any help would be greatly appreciated!

        • 1. Re: Executing SQL statement in JYTHON : SQL string is not Query error
          Robb Salzmann

          Your second query is fairly complex.  Try validating it in a stand alone client like Toad or WInSQL first.

          • 2. Re: Executing SQL statement in JYTHON : SQL string is not Query error
            Francisco Amores

            Your SQL is not a query as it includes deletes for example.

            Therefore you cannot execute it with executeQuery call

             

            stmt = sourceConn.prepareStatement(selectStmt)

            stmtRS = stmt.executeQuery()

             

            I would either review the process or implement that in stored proc and then use fdmAPI.executeDML to call the the stor proc

            • 3. Re: Executing SQL statement in JYTHON : SQL string is not Query error
              EBD

              Thanks Francisco for the response. I'll try this out.

               

              -E

              • 5. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                EBD

                Hi Francisco,

                 

                Still working on this. I'm fairly new to Jython and I've tried to implement the stored procedure however no luck thus far. How would you alter the code to use fdmAPI.executeDML to execute the stored proc?

                 

                Thanks,

                 

                E

                • 6. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                  Balakumar7

                  H,

                  With FDM API, you cannot execute stored procedure. From what i see, these are just individual SQL statements. Execute these statements sequentially via FDM API.

                   

                   

                  Thanks,

                  Bala

                  • 7. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                    Francisco Amores

                    You can execute stored procs using DML API:

                     

                    # List for Stores procedure   

                        listStoreProc = [

                            'dbo.SP_GENERATE_DATA_GROUPED_BY_PRODUCT',

                            'dbo.SP_GENERATE_DATA_GROUPED_BY_CUSTOMER',

                            'dbo.SP_GENERATE_DATA_GROUPED_BY_DESTINATION'

                        ]

                      

                        # Execute all stored procedures in the dictionary

                        for spName in listStoreProc:

                            spSQL = "EXECUTE %s ?" % spName

                            params = [loadID]

                            try:

                                fdmAPI.executeDML(spSQL, params, False)

                                fdmAPI.commitTransaction()

                                fdmAPI.logInfo("Stored procedure %s executed" % spName)           

                            except sql.SQLException, err:

                                fdmAPI.logFatal("Stored procedure %s failed. \n%s" % (spName, err))

                                delete_tdataseg_t_data("ALL", fdmAPI,  loadID)

                                fdmAPI.showCustomMessage("Import process failed."

                                                         "\nPlease contact your Administrator")

                                break

                    • 8. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                      EBD

                      Hello All,

                       

                      Thank you for your assistance! We decided to approach this problem from another angle by passing the period that is selected in the POV in a BefImport script in FDMEE to a staging table in our database. Instead of executing the SQL script in FDMEE I decided to execute the script in the database itself using a trigger. So once the data load is executed FDMEE will send the period to the database and execute the trigger which houses and executes SQL script and pull the data from the source and import it into the AIF_OPEN_INTERFACE table. This improves performance since the sql script is being executed directly in the database and simplifies the coding.

                       

                      FDMEE Code:

                       

                      import java.sql as sql

                       

                      pInsertStmt = "insert into xxtic_run_ctrl(period_name) values(?)"

                      params = [fdmContext["PERIODNAME"]]

                      fdmAPI.executeDML(pInsertStmt, params, False)

                       

                      fdmAPI.commitTransaction()

                       

                      Thank you,

                       

                      E

                      • 9. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                        user17

                        Hi E,

                         

                        I understand that passing the period value in to the column in staging table executes the sql query. Can you provide more insight on how does passing a period can help in trigger the query?

                        • 10. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                          EBD

                          Our client had an Oracle EBS instance that was being used by 10 different operating companies and therefore wouldn't let us connect directly to EBS via the pre-packaged ODI packages(which would have made our lives a whole lot easier) or push any data to any table so we had to write the "pull" code to pull the data from that view and into the AIF_OPEN_INTERFACE table. So we created a SQL View that housed the data that we needed and accessed it through a DBLINK.

                           

                          A SQL trigger responds to an event which in our case is the executeDML function. If you look at the code I posted above, the pInsertStmt is a sql command that INSERTs the PERIODNAME parameter into our temp table. Once the DML statement is executed in FDMEE, this fires the trigger which houses the code that executes the data pull from our source.

                           

                          This means that our database is doing the heavy lifting of executing the code and not FDMEE. Also, this helps to solve the problem of translating lines and lines of SQL code into JYTHON and parsing our SELECT statements from INSERT statements which have to executed by different JYTHON functions(SELECT by executeQuery and INSERT, DELETE, UPDATE by executeDML). Here's our SQL database code:

                           

                          **this code is housed in the xxtic_run_ctrl table which is the table that is referenced in the SQL code in FDMEE.

                           

                          CREATE OR REPLACE TRIGGER trigger_name

                          AFTER INSERT --this is referring to our pInsertStmt that is executed by the executeDML, essentially saying that after the periodname is inserted, execute the code below

                             ON xxtic_table_name FOR EACH ROW --execute it on this table name for each row in that table

                          DECLARE --declare our variables

                           

                            l_period_name VARCHAR2(20); -- parameter for period_name

                            type XX_TEMP_TYPE is table of XXTABLE_NAME_V%ROWTYPE index by BINARY_INTEGER; -- type of the xx_table_name

                            l_temp_tbl xx_temp_type; -- temp table

                            g_fetch_size NUMBER := 500; -- constant for fetch size

                           

                            -- cursor to get data from db linked view

                            CURSOR c_table_name

                            IS

                              SELECT * FROM xx_table_name WHERE period = l_period_name;

                             

                          BEGIN

                           

                            L_PERIOD_NAME := :NEW.period_name;

                           

                            --Deleting all data from interafce table in case there is any unprocessed record

                            --since we need to load only latest snapshot into FDMEE

                            DELETE

                            FROM xx_table_name;

                           

                            --open cursor

                            OPEN c_table_name;

                            LOOP

                              -- bulk fetch(read) operation

                              FETCH c_table_name BULK COLLECT

                              INTO L_TEMP_TBL LIMIT G_FETCH_SIZE;

                             

                              EXIT WHEN L_TEMP_TBL.COUNT = 0;

                             

                              -- bulk Insert operation

                              FORALL i IN INDICES OF l_temp_tbl SAVE EXCEPTIONS

                              INSERT INTO xxx_table_name VALUES l_temp_tbl (i);

                              --COMMIT;

                            END LOOP;

                           

                            CLOSE c_table_name;

                           

                            -- deleting data from the backup table

                            DELETE

                            FROM xx_table_name;

                           

                            --inserting the open interface data into the backup table as is

                            INSERT

                            INTO xx_table_name_archive

                            SELECT * FROM xx_table_name

                           

                            --committing

                            --COMMIT;

                           

                          EXCEPTION

                            --if any exception, rollback

                            WHEN OTHERS THEN

                              NULL;

                              --ROLLBACK;

                          END;

                           

                          Hope this helps!

                           

                          E

                          • 11. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                            user17

                            Thank You so much for the detailed explaination!!!

                            • 12. Re: Executing SQL statement in JYTHON : SQL string is not Query error
                              Francisco Amores

                              Hi,

                               

                              Another option is to use ODI with open interface adapter where you can easily execute the remote query in the source.


                              Regards