Executing non-SELECT statements

By default the connectors in the Qlik ODBC Connector Package do not execute load scripts that do not start with an SQL SELECT statement. For that reason, statements such as UPDATE, INSERT, EXEC, and WITH ... AS cannot be used by default.

To modify or load data by executing scripts that contain non-SELECT statements, the system administrator must first change the allow-nonselect-queries setting in the configuration file QvOdbcConnectorPackage.exe.config from false to true. The QvOdbcConnectorPackage.exe.config file is located in the following locations for installations of Qlik Sense and QlikView:

Qlik Sense: ...\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage

QlikView: ...\Common Files\QlikTech\Custom Data\QvOdbcConnectorPackage

Setting allow-nonselect-queries to true allows you to load data using WITH ... AS or EXEC stored procedures.

Example:  

SQL WITH app_owner AS (SELECT "ID","Owner_ID" FROM public."Apps") SELECT "Owner_ID", COUNT("ID") as "AppCount" FROM "app_owner" GROUP BY "app_owner"."Owner_ID";

If you want to execute a query that contains statements such as UPDATE or INSERT, that do not return data, you can use an !EXECUTE_NON_SELECT_QUERY keyword at the end of the query. In this occasion the connector will execute the statement and then return an empty __skip_loading (string __empty_field) table instead.

Example:  

SQL INSERT INTO [dbo].[Table_1] ([val]) VALUES (123) !EXECUTE_NON_SELECT_QUERY;

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?