Dynamic connection for ODBC

[ashishtams]

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?

[tresesco · Accepted solution]

try like:

If Computername() ='YourDEV' Then

     ODBC CONNECT TO DEV_UNIT

     Else If  Computername() ='YourPROD' Then

          ODBC CONNECT TO PROD_UNIT

     End If

End If

[quwok]

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.

[avinashelite]

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

[dgreenberg]

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. 

[ashishtams]

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.

like

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

[tresesco]

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

If Computername()=.... Then

     Load

               *

     From Path1;

Else

     Load

               *

     From Path2;

End If

[ashishtams]

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.

[tresesco]

You can alternatively try like:

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

Load

          *

From '$(vPath)';