topic Re: Update/Insert to a Database Table from QlikSense via ODBC Connection in App Development

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

knightjc — Thu, 19 Oct 2017 16:23:51 GMT

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

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

hacr — Fri, 20 Oct 2017 08:23:25 GMT

Is it something like this?

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

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

knightjc — Mon, 23 Oct 2017 20:23:39 GMT

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

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO xtmp_writeback_table (FIELD1) VALUES (i_writeback_message);

COMMIT;

RETURN SQLCODE;

END;

Thank you,

Jake

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

hacr — Tue, 24 Oct 2017 06:37:12 GMT

Happy to hear it was useful!