topic Re: Dynamic connection for ODBC in QlikView

Dynamic connection for ODBC

ashishtams — Mon, 18 Jan 2016 09:30:21 GMT

Hi All,

I am using odbc connetion to DB2 database to access the tables.

we need qlikview to connect automatically to that environment where the dashboard is. Like if its in QA then it should connect to QA database and if the dashboard is in prod then it should connect to prod database.

so what i have is below statement in dev environment.

ODBC CONNECT TO DEV_UNIT

when i move the dashboard to prod i have to change this to below statement manually and then move the dashboard to prod

ODBC CONNECT TO PROD_UNIT

how can i make this dynamic?

Re: Dynamic connection for ODBC

tresB — Mon, 18 Jan 2016 09:34:24 GMT

try like:

If Computername() ='YourDEV' Then

ODBC CONNECT TO DEV_UNIT

Else If Computername() ='YourPROD' Then

ODBC CONNECT TO PROD_UNIT

End If

Re: Dynamic connection for ODBC

quwok — Mon, 18 Jan 2016 10:14:53 GMT

Assuming the user details to connect to the database are the same. You can change the ODBC connection name on all servers to be the same, then the script wouldn't need to worry which server it is on.

Re: Dynamic connection for ODBC

avinashelite — Mon, 18 Jan 2016 10:20:41 GMT

Try like this:

While creating the ODBC connection in Test or PRD have same name for both the ODBC DSN connection in the server i.e. in the PRD with PRD credentials and in test with test credentials and remove the string part after the connection name ..so the ODBC names remains same and no need to change

Re: Dynamic connection for ODBC

dgreenberg — Mon, 18 Jan 2016 14:01:31 GMT

You can also use an include statement to read unique connection info from each server. This would require a unique file with the same name on each server.

Re: Dynamic connection for ODBC

ashishtams — Mon, 01 Feb 2016 09:27:34 GMT

Hi,

I used your suggestion and it worked for me

Now, i want to use the same thing in the FROM clause in the script.

LOAD

EMP,

DEPT,

SAL

FROM

\\abcd\123\PROD\qvd\file.QVD

(qvd);

now to make this dynamic when I connect to PROD the above statement is good.

but when i connect to QA then the from clause should be like

\\abcd\123\QA\qvd\file.QVD

and for DEV it should be like

\\abcd\123\DEV\qvd\file.QVD

so the folder is changing in the path where the QVD files are stored as per the environment.

i tried to put the entire path in a variable using LET and then use IF ELSE after the FROM clause. but thats not working for me

please suggest

Re: Dynamic connection for ODBC

tresB — Mon, 01 Feb 2016 09:41:14 GMT

Try putting the entire load statement in the IF clause, like:

If Computername()=.... Then

Load

From Path1;

Else

Load

From Path2;

End If

Re: Dynamic connection for ODBC

ashishtams — Mon, 01 Feb 2016 09:49:26 GMT

i know it works that way, but the LOAD statement is too big and the same load statement is repeated three time in my case, the only change is one folder name in the path.

If there is a way to only change the FROM clause.

Can we put the LOAD statement as a string and execute in IF-ELSE statement using variables. just like we execute in PL/SQL

regards.

Re: Dynamic connection for ODBC

tresB — Mon, 01 Feb 2016 10:24:55 GMT

You can alternatively try like:

Let vPath= If(ComputerName()='XXX', 'Path1' , 'Path2');

Load

From '$(vPath)';