topic ODBC Connection Issue in QlikView

ODBC Connection Issue

pooja_sn — Tue, 29 Mar 2016 12:23:29 GMT

I am using and ODBC connection to connect to Sybase and my qlikView code is similar to below:

ODBC CONNECT TO DataSourceName (XUserId is UserName, XPassword is Password);

LOAD *;
SQL
exec StoredProcedureName;

The connection is successful but, it always returns few number of records which is an incorrect output. When I run the same script with OLEDB connection, it works perfectly fine. Any idea what might be causing this issue..

Re: ODBC Connection Issue

Gysbert_Wassenaar — Wed, 30 Mar 2016 10:24:55 GMT

Perhaps the ODBC driver you're using doesn't support something that's return by the stored procedure. Is there a reason you think you should use ODBC instead of OLEDB?

edit: Perhaps that's not even necessary. Have you seen this video:

Re: ODBC Connection Issue

buzzy996 — Wed, 30 Mar 2016 10:50:06 GMT

are u created ur ODBC connection through Control Panel-Administrative tools--Data source --on system DSN tab?

Good to use OLEDB instead of ODBC.

Re: ODBC Connection Issue

pooja_sn — Sat, 02 Apr 2016 06:59:04 GMT

Using ODBC connection was mandatory just because we are facing issues with OLEDB on server.

I have configured ODBC dataSource in System DSNs. The script returns correct result when executed for the first time and it seems that the result is cached and returned by the query every time I reload . I tried using 'DISCONNECT' in script which does not work.

Re: ODBC Connection Issue

pooja_sn — Sat, 02 Apr 2016 07:00:09 GMT

Yes. I have configured ODBC in System DSNs and it works perfectly fine for other scripts.

Re: ODBC Connection Issue

Gysbert_Wassenaar — Sun, 03 Apr 2016 15:49:49 GMT

Check on the database server if the query that Qlik Sense sends to the database is executed and returns the results you expect.

Re: ODBC Connection Issue

maksim_senin — Mon, 04 Apr 2016 04:35:13 GMT

Hi,

This would be helpful if you provide ODBC error message. Without this I can assume the following:

1. If this is possible - try debuging your stored procedure in order to return top 1, 2, 3 ... n records from it.

So you'll make sure whether it happens due to some specific record(s) or due to value of data.

2. Another way is to reproduce a query from the procedure on QV side. Of course this is only possible in case the procedure contains nothing except a select-query.

3. Not sure, but if you procedure makes something except select's (insert's or update's), maybe it makes sense to tick this setting in Script Editor:

It's needed when QV makes insert's or delete's from its side, though. So this is more likely a shamanism since it works over OLE DB.

Best regards,

Maxim

Re: ODBC Connection Issue

pooja_sn — Mon, 04 Apr 2016 08:32:55 GMT

Yes. If I execute the same query in Sybase, it returns correct result.

Re: ODBC Connection Issue

Gysbert_Wassenaar — Mon, 04 Apr 2016 09:10:48 GMT

If OLEDB works and ODBC doesn't then the ODBC driver is the problem. Ask the vendor that supplied to ODBC driver for help. Or just use OLEDB which will perform better anyway.

Re: ODBC Connection Issue

pooja_sn — Tue, 05 Apr 2016 09:52:56 GMT

No error message is returned by the query instead it returns few incorrect records.

If I tick the setting 'Open database in Read and Write mode', the script is failing to reload.

The stored procedure mentioned is making few insert/update/delete using temporary tables in database.