SQL Update works, but not quite

[#1]

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?

[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

[#3]

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.

[#4]

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.

[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

[#6]

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.

[Alison]

Hi all,

I'd like to "re-open" this discussion since we're facing exactly this issue since we updated our testing environment to the latest QV version 12.30.

We have a script performing a delete and after that an insert into a Oracle database. The delete works fine but the subsequent insert results in the above mentioned "ORA-01456" error statement.

The script has worked just fine up to QV 12.20.

Is anybody already running 12.30 and has experienced the same behaviour?

Kind regards

Eric