Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hello to all

I'm not able to optimize my SQL statements in QlikView becouse I'm not able to use feature from DB2 database which is called "GLOBAL TEMPORARY TABLE". Becouse QlikView works only in read-only I cannot send any insert or update statement using SQL via ODBC connection. In program WInSQL (maybe some of you know this program) I'm able to send any SQL statement I want so it's not limitation from ODBC driver.

In fact I'm able to use insert/update when I use MS SQL server os MySQL so this "read-only" isn't so true in this case.

When I run my SQL statement in QV (which declares temporary table in DB2 database) i get such error:

SQL Error:[IBM][System i Access ODBC Driver]Statement violates access rule: Connection is set to read only.

SQL Scriptline:

SQL State:37000

SQL

DECLARE GLOBAL TEMPORARY TABLE TEMP

  (MMITNO      CHAR(15)     NOT NULL)

Anybody can help ?

1 Solution

Accepted Solutions
fortuinw
New Contributor

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi Pawel

Had a similar issue in Oracle and did the following in the CONNECT statement. Check to see if this works for a DB2 connection.

ODBC CONNECT TO [XXXXXXXX] (XUserId is XXXXXXXX, XPassword is XXXXXXXX, Mode is write);

Regards

Wayne

10 Replies

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi Pawel,

You can declare in your ODBC connection to set it to read-write as well. Go to the Control Panel, Administrative Tools, Data Sources, and set there the corresponding setting.

In addition, go to the Script Editor window, select from the bottom the tab Settings and ensure that "Open Databases in Read and Write mode" is enabled.

But apart from that, the user specified in the ODBC must have permissions in the database to create temporary tables.

Hope that helps.

Miguel

Not applicable

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi Miguel

The thing is that my ODBC conncection is set for read and write. I use this connection in another program and it works fine but in QV I'm not able to send any write statement at all.

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi,

Did you set the Script Editor as mentioned? Are you using the same user in your ODBC and the other computer? I only see here two issues: first is the ODBC configuration, second is QlikView configuration, but QlikView should allow you to write if you specify so.

Miguel

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Is there an key AllowProcCalls in the registry for that datasource with the value 1? Should be under

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ or HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\

If not try adding it.


talk is cheap, supply exceeds demand
Not applicable

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi

I get the same error even when I've checked option Open Database in Read and Write mode.

Maybe QlikView has problems to send write statements using iSeries ODBC driver (official from IBM for DB2).

Not applicable

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Yes I have it and it's set to "0". When I've changed it to "1" nothing has changed when I tried to run QV script

Not applicable

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

I'm not sure but I think there is a problem when QV sends SQL statement with Declare clause at beginning. Maybe this causes a problem between QV and ODBC driver. I use "System i Access for Windows ODBC data source" driver. It works fine when I use it under WinSQL but then when I use the same ODBC connection I get error. I don't know who should I ask about it. This is very important for me becouse I need to optimize my SQL statements. In couple of my QV apps. I load data from many tables and limit their data using data from another table but now I need to SELECT this table many times in my script. With Temporary Tables I would be able to load it only once and use in during whole script execution.

fortuinw
New Contributor

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi Pawel

Had a similar issue in Oracle and did the following in the CONNECT statement. Check to see if this works for a DB2 connection.

ODBC CONNECT TO [XXXXXXXX] (XUserId is XXXXXXXX, XPassword is XXXXXXXX, Mode is write);

Regards

Wayne

Not applicable

Re: I'm not able to use GLOBAL TEMPORARY TABLE (QV is read-only)

Hi Wayne

That's it !!!!

Thanks man

You have bunch of beers from me.

Community Browser