5 Replies Latest reply: Aug 16, 2016 4:51 PM by Don Callaway RSS

    SQL Update works, but not quite

    Don Callaway

      Hi,

       

      I work in a very large enterprise where EDX is not available so I rolled my own external triggers using a Qlik Parameters table in Oracle. With the Parameters table I can also cause my script in production to do different things on reload. For example if I needed to do just a partial reload of one table in the data model, I can set a flag to do a partial reload for, say work hours, and the script determines which external script file to load with the instructions to do a partial reload for work hours.

       

      So back to the trigger. I need an external trigger that can not be scheduled. It's one of those end-of-month-as-soon-as-its-ready things. When the externally triggered extractor completes, there is a cascade of dashboards that use the extracted QVDs and that should now reload.

       

      No one except Central Admin has access to the QMC in production so we do not have the ability to manually initiate the 1st task to start the cascade. Using the following setup, I can implement an ad hoc manual trigger and oh so much more.

       

       

      QPARAM:
      LOAD
        RELOAD_STATUS,
        SYSTEM_STATUS_MSG   as SysMessage,
        APP_STATUS_MSG   as AppMessage
      ;
      SQL   Select
        DASH_ID,
        RELOAD_STATUS,
        SYSTEM_STATUS_MSG,
        APP_STATUS_MSG
      From "QLIK_PARAMETERS"
        where DASH_ID like 'SAFETY_STATS‘

      ;

      Let vReload = Num(Peek('RELOAD_STATUS', 0, 'QPARAM')); 
      Let vMessage1 = Peek('SysMessage', 0, 'QPARAM'); 
      Let vMessage2 = Peek('AppMessage', 0, 'QPARAM'); 

      //drop Table QPARAM;

      if vReload <> '1' then
        SQL Select * from CAUSE_ERROR_ON_PURPOSE//Invalid table causes script to error…
        exit Script when ScriptError <>0;    //…which will cause dependent tasks not to run
      end if 

          //Forcing ScriptError will NOT be necessary if flags can be written to Oracle

       


      SQL Update "QLIK_PARAMETERS" set RELOAD_STATUS = '0' where DASH_ID like 'SAFETY_STATS';

      {…Run extraction}

      //Set flag for CORP_DASH to reload
      SQL Update "QLIK_PARAMETERS" set RELOAD_STATUS = '1' where DASH_ID like 'CORP_DASH';

       

       

       

      When I run this on my local machine, no problems, both statements execute. When I run it on the server, only one of the statements will execute. Doesn't mater which one, both statements work, but only if the other is commented out.  A second SQL statement causes the following error.

       

      Error Message from log file:

      Error: SQL##f - SqlState: S1000, ErrorCode: 1456, ErrorMsg: [Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

       

      I've tried different combinations of SQL Commit. With, without, twice (once after each statement), once after both statements.  Commit doesn't seem to be necessary at all. The Qlik server was originally set up with ODBC but I did get the Qlik server admin to setup an OLEDB connection but it didn't work either. There is a tnsnames.ora  file being used on the Qlik server to configure connects to Oracle host names.

       

      Since one SQL Update statement works, I can conclude the following:

      • Permissions for the user defined in the connection string are Read Write
      • The database table allows Read Write
      • The ODBC (mode is write) and OLEDB connections allow Read Write
      • The script engine allows Read Write connection (In script editor Settings tab is checked 'Open Databases in Read and Write mode')
      • The Qlik server has no restrictions on Read Write connections

       

      Anyone have this problem before?

        • Re: SQL Update works, but not quite
          Marcus Sommer

          Maybe there is a timing-issue which could be solved by a sleep-statement between your sql-loads. In a similar way might a disconnect- and a renewed connect-statement work.

           

          - Marcus

            • Re: SQL Update works, but not quite
              Don Callaway

              Thanks for the suggestions Marcus.

               

              I forgot to mention in the OP I had tried putting a pause between the SQL statements. I tried 1 second and 5 seconds. No luck.

               

              But I like the 2nd idea. I will try dropping and re-establishing the connection prior to the 2nd SQL Update command and report back.

              • Re: SQL Update works, but not quite
                Don Callaway

                Marcus,

                 

                Unfortunately dropping and re-connecting didn't work. I even tried making the subsequent connection with  different database drivers. 

                 

                Then, I created a single purpose qvw with an associated dependent task to run on success of the extractor task to make a connection and update the Oracle field.  No luck.

                  • Re: SQL Update works, but not quite
                    Marcus Sommer

                    I think that there is any security-issue which is blocking the execution probably from the database and/or the network (firewall, group policies) to the IP or the network from your qlikview-server.

                     

                    Are you sure that your first statement really worked and not that there is simply no error-message which then came by the second attempt. If it's really worked then I think there are any timing-settings which allow only one statement in a certain time or that the blocking-flag of the table after the first access isn't released through anything ... it's only guessing but maybe it helped.

                     

                    - Marcus

                      • Re: SQL Update works, but not quite
                        Don Callaway

                        I agree it's looking like some issue with the network connectivity. The tasks run on a server in Germany and Oracle is somewhere in New Jersey, while I am on the Texas coast. Issues like this can be tricky and time consuming to resolve because many hands in different time zones are required to coordinate to test even simple things.

                         

                        Yes, updating an Oracle table  with SQL Update really works.  I am able to watch the Oracle table change in real time as the script runs. My QLIK_Parameters table sits off to the side in our schema along with but separated from the rest of the department's business data, so I don't think transaction limits or time limits are the cause. Having said that, I tried pausing  between updates anyway to no avail.