Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

pooja_sn
Contributor

ODBC Connection Issue

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

9 Replies
MVP & Luminary
MVP & Luminary

Re: ODBC Connection Issue

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:


talk is cheap, supply exceeds demand
buzzy996
Honored Contributor II

Re: ODBC Connection Issue

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

Good to use OLEDB instead of ODBC.

pooja_sn
Contributor

Re: ODBC Connection Issue

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.

pooja_sn
Contributor

Re: ODBC Connection Issue

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

MVP & Luminary
MVP & Luminary

Re: ODBC Connection Issue

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


talk is cheap, supply exceeds demand
maxim_senin
Contributor III

Re: ODBC Connection Issue

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:

2016-04-04_143240.jpg

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

pooja_sn
Contributor

Re: ODBC Connection Issue

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

MVP & Luminary
MVP & Luminary

Re: ODBC Connection Issue

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.


talk is cheap, supply exceeds demand
pooja_sn
Contributor

Re: ODBC Connection Issue

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.