Update/Insert to a Database Table from QlikSense via ODBC Connection

[knightjc]

Good day QlikSense community!

My company is a long time QlikView shop and trying to dip our toes into the QlikSense world with our first application.  One of the requirements of any BI tool we use is that it be able to log statuses back to our master scheduler environment for monitoring.  That requires a simple insert statement to be executed from the QlikSense load script.  In QlikView we simply add the "mode is write" option to an ODBC connection string and it works flawlessly.  QlikSense does not seem to have that option available.  I have a ticket open with Qlik Support now, and hopefully that will result in an answer, but I wanted to post this to the community to see what options you may know of.

My hope is that someone on this forum knows where that "hidden checkbox" might be inside of QlikSense that would make this effort simple.

Thank you in advance,

Jake

[hacr]

Is it something like this?

https://community.qlik.com/docs/DOC-19167

[knightjc · Accepted solution]

Hans, you really helped to get my gears turning.  Oracle by default does not insert from a function, but there is a parameter we can add "PRAGMA AUTONOMOUS_TRANSACTION;" that will enable an Oracle function to perform an insert.  I would not call this a "solution", as writing data back to a database is a basic function for any enterprise application, but will get us going for the time being. 

I will modify the  Function listed in your link to one that works for Oracle databases:

CREATE OR REPLACE FUNCTION writeback (i_writeback_message IN VARCHAR2)

    RETURN INTEGER

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO xtmp_writeback_table (FIELD1) VALUES (i_writeback_message);

    COMMIT;

    RETURN SQLCODE;

END;

Thank you,

Jake

[hacr]

Happy to hear it was useful!