Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
knightjc
Contributor
Contributor

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

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

1 Solution

Accepted Solutions
knightjc
Contributor
Contributor
Author

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

View solution in original post

3 Replies
hacr
Creator
Creator

Is it something like this?

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

knightjc
Contributor
Contributor
Author

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
Creator
Creator

Happy to hear it was useful!