topic Re: SQL Update works, but not quite in QlikView

SQL Update works, but not quite

Wed, 10 Aug 2016 16:00:15 GMT

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 — Thu, 11 Aug 2016 05:40:22 GMT

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

Thu, 11 Aug 2016 13:35:02 GMT

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

Mon, 15 Aug 2016 15:17:44 GMT

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 — Mon, 15 Aug 2016 15:44:54 GMT

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

Tue, 16 Aug 2016 20:51:51 GMT

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.

Re: SQL Update works, but not quite

Alison — Mon, 29 Apr 2019 09:24:29 GMT

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