Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Production & development

Does anyone know if it is possible to set a database connection as a variable to allow the switching of data sources? something like my example below but works

SET vConnection = 1;

IF vConnection = 1 THEN
//Dev

SET vDBName = 'CONNECT TO [Provider=SQLOLEDB.1;Integrated Security.............................

ELSE

//Prod

SET vDBName = 'CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=............................
END IF


SQL SELECT *
FROM $(vDBName).dbo.Table1;

This seems like something people would of cracked a long time ago but I can't find much on the subject any ideas?

Cheers

1 Solution

Accepted Solutions
nathanfurby
Valued Contributor

Production & development

You can store the connection strings in an external file and use them like this:

Connections:
LOAD ConnectionNumber,
ConnectionName,
ConnectionString
FROM

(biff, embedded labels, table is Sheet1$);

Let vConnection = Peek('ConnectionString',vConnectionNumber);

ODBC CONNECT TO $(vConnection);

Just create a variable called vConnectionNumber and assign it value of 0 - could then change this via Input Box etc. and even think of a clever way to link the connection number from the Connections table to the variable.

5 Replies
vgutkovsky
Honored Contributor II

Production & development

Yes, you can do that I believe. I think the problem with you syntax is the Connect statement. A Connect statement just goes once in the beginning of the script to connect to the DB Server. You can encode the DB name itself in a variable and use variable expansion to get it. For example:


ODBC CONNECT TO [Provider=SQLOLEDB.1] //etc
SET vDBName = myDatabase;
SQL SELECT *
FROM $(vDBName).dbo.Table1;


Regards,

Not applicable

Production & development

Normal 0 false false false MicrosoftInternetExplorer4

Thank you for your response Vlad.

Are you aware of any method to encode different connect statements to variables or something similar to allow me to switch between data sources on different servers by changing the variable value?

My aim is to have replicated production and development data environment and i want to do something like change the value of a variable to decide which server is used as the source for the reload.

Again thank you.

nathanfurby
Valued Contributor

Production & development

You can store the connection strings in an external file and use them like this:

Connections:
LOAD ConnectionNumber,
ConnectionName,
ConnectionString
FROM

(biff, embedded labels, table is Sheet1$);

Let vConnection = Peek('ConnectionString',vConnectionNumber);

ODBC CONNECT TO $(vConnection);

Just create a variable called vConnectionNumber and assign it value of 0 - could then change this via Input Box etc. and even think of a clever way to link the connection number from the Connections table to the variable.

Not applicable

Production & development

Normal 0 false false false MicrosoftInternetExplorer4

Thanks Nathan that's a really good idea, much appreciated.

nathanfurby
Valued Contributor

Production & development

No problem.

BTW - love the fact you are using IE4 Geeked

Community Browser