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.
SYSTEM_STATUS_MSG as SysMessage,
APP_STATUS_MSG as AppMessage
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
//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';
//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?