topic Database Connection Issue in QlikView

Database Connection Issue

Fri, 06 Jan 2012 13:37:26 GMT

Hi All!

This time, im consulting about database connections.

Lets suppose that i have an application that use some odbc or oledb connection to a database and i run the refresh of this application on a bat file.

Sometimes, some refresh freeze on the load in the connection time. So, sometimes, the procces remains paused because the connection was not successfull.

The problem its that, i cannot permit that those procces keeps stopped because other procces must run.

So, the question is, theres any way to check if some procces have some connection problem and stop it?

Thanks for all!

Germán

Database Connection Issue

rbecher — Tue, 10 Jan 2012 09:55:58 GMT

Hi Germán,

maybe you can look for vendor specific time out parameters in the ODBC/OLEDB dialog to shorten the freeze time and then catch the script error:

set ErrorMode=0;

CONNECT ...;

if ScriptError > 1 then

//no connect, do something else...

end if

set ErrorMode=1;

- Ralf

Database Connection Issue

Tue, 10 Jan 2012 12:03:41 GMT

Hi Ralf

Thanks for your answer but the problem is in the coonection line.

When you cannot connect, the script doesnt go on. So, i cannot enter into the If clausule to check if error...

There`s any workaround like timeout or something?

Thanks!!!

Database Connection Issue

rbecher — Tue, 10 Jan 2012 12:20:53 GMT

Which database vendor and which ODBC driver you're using?

Database Connection Issue

Tue, 10 Jan 2012 12:25:16 GMT

In fact its an ODBC connection to a Visual FoxPro file. (a dbf file)

I Know... its old... but... theres some systems that still hav e this kind of information sources...

Thanks

Database Connection Issue

rbecher — Tue, 10 Jan 2012 12:43:57 GMT

MS says VFPODBC driver is no longer supported, you should use OLE DB:

http://msdn.microsoft.com/de-de/vfoxpro/bb190233.aspx

Btw. can you send me a VFP test file to do some research on this?

- Ralf

Database Connection Issue

rbecher — Tue, 10 Jan 2012 13:48:57 GMT

I did an example with OLE DB on a local Visual FoxPro file. It gets no error on the Connect if the file is not there (can not connected) but if you make a select. This can be catched as followed:

// connect

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=C:\PROGRAM FILES (X86)\MICROSOFT VISUAL FOXPRO OLE DB PROVIDER\SAMPLES\NORTHWIND;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

set ErrorMode=0;

// select

customers:

SQL SELECT *

FROM customers;

// error handling if not connected

if IsNull(NoOfRows('customers')) then

trace not connected;

exit script;

end if

// else further script execution

set ErrorMode=1;

- Ralf

Database Connection Issue

Tue, 10 Jan 2012 14:31:53 GMT

the cut into the connection its in the oledb string, or not?

Thats why you dont get stoped?

Database Connection Issue

rbecher — Tue, 10 Jan 2012 14:47:33 GMT

What you mean with "the cut into the connection"?

At least the folder of the Data Source must exist. If it not exist the OLE DB Provider will open a dialog.

- Ralf

Database Connection Issue

Tue, 10 Jan 2012 14:58:57 GMT

I undertud.

The problem its that when the connection call its launched, sometimes doesnt return any feedback.

The folder exist, the file exist, but the connection doesnt returns anything...

Thats why i need to stop the procces if the connection its freeze...

Thanks

Database Connection Issue

rbecher — Tue, 10 Jan 2012 15:13:47 GMT

I presume this will not occur with the OLE DB Provider (instead of ODBC)..

- Ralf

Database Connection Issue

Tue, 10 Jan 2012 15:30:19 GMT

Thanks Ralf... i'll try it!

Database Connection Issue

rbecher — Tue, 10 Jan 2012 15:31:48 GMT

I have downloaded it here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14839

- Ralf

Database Connection Issue

Tue, 10 Jan 2012 15:44:06 GMT

Yes... thanks for this... i tryed many connectors... in fact...

Database Connection Issue

Wed, 16 May 2012 17:08:15 GMT

Hey Ralph, so I'm attempting to connect to the FoxPro tables, and I'm able to create the connection based off your example. I can then read the table, but it does not pull in any data. And I know for sure that ther is some there as I can open the table and see data. What could I be missing?

Here is my script.

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=C:\Users\Public\Documents\MFHS\Abra\Abra\Data;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

employees:

LOAD a_empno,

a_hours;

SQL SELECT *

FROM HATTRAN;

Database Connection Issue

rbecher — Wed, 16 May 2012 17:22:44 GMT

Unfortunately this is not working anymore with my newest QlikView versions v10 SR4 and v11 SR1 on 64bit. It is just crashing qvconnect.exe

So, I started to use our QlikView JDBC Connector (http://bit.ly/A98Sdi) with the HXTT DBF driver (http://bit.ly/K7uNkT) which runs very smoothly now..

- Ralf

Re: Database Connection Issue

Wed, 16 May 2012 17:52:16 GMT

Yeah, it was just crashing the qvconnect.exe for me too. But then I through in your modified script at it pulled the table, but just not any data.

Alex Peasley

Business Intelligence Architect

Core BTS

608-661-7769

Re: Database Connection Issue

rbecher — Wed, 16 May 2012 19:53:06 GMT

Hi Alex,

this is the JAVA runtime config:

this is the JDBC driver config:

This is the QV script connect:

Just contact me directly if you have any question: ralf.becher@tiq-solutions.de

- Ralf