topic Re: Differenct connection strings in QlikView

Differenct connection strings

kmswetha — Fri, 24 Jul 2015 11:47:17 GMT

Hi,

I used include file for connection strings.

But my question is, I have different connection strings of different environments, how do I call them intelligently?

Re: Differenct connection strings

tresB — Fri, 24 Jul 2015 11:58:06 GMT

May be like:

If computername()= 'Machine1' Then

....this connection string

ElseIf Computername() = 'Machine2' Then

...that connection string

...

Endif

EndIf

Re: Differenct connection strings

jonathandienst — Fri, 24 Jul 2015 12:23:53 GMT

Another example:

If WildMatch(vEnv, 'DEV') Then

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=*****; Data Source=****\****_DEV];

ElseIf WildMatch(vEnv, 'UAT') Then

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=*******; Data Source=****\****_UAT];

ElseIf WildMatch(vEnv, 'PROD') Then

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=*****; Data Source=*****\****_LIVE];

End If

Re: Differenct connection strings

kmswetha — Fri, 24 Jul 2015 13:01:29 GMT

Hi jonathan,

But how do you assign value for vEnv?

Re: Differenct connection strings

Fri, 24 Jul 2015 13:14:56 GMT

The way I have done in this in the past is each include file contains the connection string for the environment you are in. When the qvw is moved to a different environment, the include file in that environment would have a different string.

example - you have an include file called connection.qvs. The file contains the connection string - you would have 2 versions of this file - one in test, one in production.

When your qvw is in dev, when it includes the file in that environment, it contains the connection string for dev, when it runs in prodction, the connection string is in production.

This does make the assumption that your environments and folder structures are the same across environments

Re: Differenct connection strings

kmswetha — Fri, 24 Jul 2015 13:29:41 GMT

Hi Adam,

Am confused, if I have 2 environments, are you telling to use 2 different files?

Re: Differenct connection strings

Fri, 24 Jul 2015 13:31:15 GMT

basically yes - the file has the exact same name in both environments, the connection string is different - when the file gets included, it will use the one based in the environment you are in containing the appropriate string

Re: Differenct connection strings

kmswetha — Fri, 24 Jul 2015 13:33:58 GMT

Which means, You are changing the connection string based on the environment manually?

Re: Differenct connection strings

Fri, 24 Jul 2015 13:36:37 GMT

no the connection string is in the include file. Each include file, which has the same physical name has a differnt connection string. When you load the application and it includes the file, it will read in the file based on the environment you are in, there bringing in the connection string from that file

Re: Differenct connection strings

kmswetha — Fri, 24 Jul 2015 13:42:55 GMT

Oh I got it. So you create 2 files with same name but different connection strings.

It would pick up which ever it connects?

Re: Differenct connection strings

Fri, 24 Jul 2015 13:45:18 GMT

correct

place each include file in each environment - since they ahve the same name, there are no coding changes required this way when moving from one environment to another

Re: Differenct connection strings

Fri, 24 Jul 2015 13:57:14 GMT

The best way I have done is by having an Excel file with the connection strings in them. You will have to set up the ODBC connections or the the type of connection you are making.

In my data extraction script I load the Excel file and loop through the connections to load the data. This is under the assumption that your data structure in each environment is the same as any changes or additional tables which do not exist in any other environments will throw an error.

To identify which table belongs to which environment, you can create a variable after declaring the while loop which will be the name of your environment. You can add this as a additional column in each of your tables in your structure. This will simply be the variable of the environment. This is will make it easy for identifying environment table.

Hope this helps.

Re: Differenct connection strings

lenka_kruse — Fri, 24 Jul 2015 14:32:38 GMT

Do you have one QV-extraction-application that needs to loop through all the connection-strings to load tables from different machines?

I usually do that by loading the machines/table-combinations from an excel-file and keeping the Connect-Strings in textfiles matching the machine-names, so that I only have to loop through the combinations:

Machine	Table	Load
machine1	table1	1
machine1	table2	1
machine2	table1	1
machine2	table3	1

< Load the machines where Load = 1>

for i = 1 to FieldValueCount('Machine')

let vS_Maschine = FieldValue('Machine',$(i));

$(Include=$(vPath)\cs_$(vS_Maschine).qvs);

for j = 1 to FieldValueCount('Table')

let vS_Table = FieldValue('Table',$(j));

load & store & drop $(vS_Table);

disconnect;

Make sure you disconnect at the end of each connect-loop, just to keep things clean!

I like this solution because a) it opens and closes every connection-string only once, which is good for the performance and b) it makes adding or deleting machines/tables really easy, because you only have to set Load to '1' or '0'.

Or do you have the same QV-application in different environments, so that it uses the same connection-string include-file, but with different paths?

I usually do that by checking via the document-path, whether the application is e.g. in the development or the production environment, and then setting the path accordingly:

if(index(DocumentPath(),'DEV') > 0) then (or something similar)