topic Re: Set database path in a variable.? in QlikView

Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 05:10:36 GMT

I have a connection string to fetch data from access database. Now, I want to allow the user to change the path in front end, so i have created a variable (vFile) and stored the path into it. But it showing error whenever i try to reload. Can anyone point me where I am wrong.?

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;

Extended Properties="DBQ=$(=vFILE);DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV;

Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=$(=vFILE).dsn;

MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";

Initial Catalog=$(=vFILE)](XPassword is CDfSPWBOUTcCWRNNFTceJ);

Re: Set database path in a variable.?

amit_saini — Wed, 27 May 2015 05:18:42 GMT

Tamil,

You need to establish a new connection string based on new location of your .accdb file.

Thanks,

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 05:24:32 GMT

Hello Amit,

I just want to replace the path with a variable, so that the user can change it in front end. Below connection string is working perfectly, but I have hard code the path in the string.

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;

Extended Properties="DBQ=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb;DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV;

Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb.dsn;

MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";

Initial Catalog=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb] (XPassword is CDfSPWBOUTcCWRNNFTceJ);

I have replaced the above path by using variable. But I got error message at the time of reload. Can you help me to correct the syntax please.

Re: Set database path in a variable.?

tresB — Wed, 27 May 2015 05:32:43 GMT

Try without '=' before variable like:

...DBQ=$(vFILE);.....

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 05:37:51 GMT

Tresesco,

Re: Set database path in a variable.?

tresB — Wed, 27 May 2015 05:44:16 GMT

How do you define/input vFILE variable?

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 05:47:43 GMT

I have included a text box in the dashboard where the user can paste the path in that box. I am just assigning a variable to store it.

Re: Set database path in a variable.?

tresB — Wed, 27 May 2015 05:51:53 GMT

Could you clarify these:

How exactly you are placing the variable in the script
What exactly you are pasting in the variable
And then, what excatly the error message you get

Re: Set database path in a variable.?

jonathandienst — Wed, 27 May 2015 05:52:06 GMT

Doing a variable expansion inside the connection string appears not to work with or without the = sign (at least as of 11.20 SR9).

But you can replace the entire connection string like this:

Set vPath = 'ZZZZZZ\YYYYYYY';

Set vConn = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXXXXX;Data Source=$(vPath)';

OLEDB CONNECT TO [$(vConn)];

Adapt this logic to your situation.

Re: Set database path in a variable.?

amit_saini — Wed, 27 May 2015 05:52:25 GMT

Like this???

Thanks,
AS

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 06:04:49 GMT

How exactly you are placing the variable in the script.

Added a new variable in input box property like below.

What exactly you are pasting in the variable.

As I said, the variable taking the input from the input box (Path).

And then, what excatly the error message you get.

I got error message like "Could not find file. 'C:\Users\Tamilarasu.Nagaraj\Desktop\QV\$(vFile).mdb'

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 06:10:28 GMT

Amit: Yes

Re: Set database path in a variable.?

jonathandienst — Wed, 27 May 2015 06:27:49 GMT

Still getting the same error message.

It worked for me in a small test app. I suggest you post the code that you used to assign the connection string and the value of any variables that you tested.

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 06:39:36 GMT

Could you see check the below files (Just a sample).

Thanks a lot.

Re: Set database path in a variable.?

simospa — Wed, 27 May 2015 07:01:21 GMT

Hi,

I tried to write the connectionString on the same row, and it works

Set vConn = 'Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Extended Properties="DBQ=$(vPath);DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=$(vPath).dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";Initial Catalog=$(vPath)]';

You wrote the vConn in many rows, it seems it is considered as different statements...

Helps me use TRACE statement 🙂

Let us know.

Re: Set database path in a variable.?

tamilarasu — Wed, 27 May 2015 07:25:57 GMT

Well spotted. By the way I don't know how to check and find the mistake by using trace statement. Could you guide me.

Thanks for all whoever replied here. Have a nice day.

Re: Set database path in a variable.?

simospa — Wed, 27 May 2015 08:04:59 GMT

Good 🙂

If you use, at the end of your instruction, TRACE $(vPath) writes a content to log file.

I notice that more rows were written with timestamp on left, but I expected only 1.

Have a nice day.