topic Re: Find Datasource info In String in QlikView

Find Datasource info In String

Thu, 05 Jan 2012 07:13:27 GMT

Hi There,

Basically want to get the Initial Catalogue value from the connection string in the Load Script.

We set the Connection String in a file then use include which basically drops the contents of the file into the Load Script.

e.g.

$(Include=$(vConnectionFilePath));

For another feature I need to know the initial catalogue and maybe the Data Source name too.

e.g.

SET sConnString = $(Include=$(vConnectionFilePath));

i.e. sConnString would equal

CONNECT TO [Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDBName;Data Source=MyServer;Provider=SQLOLEDB.1;]

I'd like to extract myDBName and MyServer from this variable string in the Load Script itself.

Can I split and loop through the string?

Cheers

Find Datasource info In String

nstefaniuk — Thu, 05 Jan 2012 07:57:52 GMT

Hi. I use the same technic but in hte file I have "Let vConnString = [Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDBName;Data Source=MyServer;Provider=SQLOLEDB.1;]"

and in the load I do

$(Include=$(vConnectionFilePath));

CONNECT TO $(vConnString);

And so I can work on vConnString later in script, and recall the connection if needed.

The dangerous part of this method is that the connection string is stored in the application and so users can see the connection string and use it in a local Qlikview. So we use 2 applications : 1 for load and 1 for display or we "clean" connection strings at the end of the string.

Find Datasource info In String

Wed, 11 Jan 2012 04:15:31 GMT

Cheers for that.

Actually it's not an issue getting it as a string but really what I wanted was some code to strip the Initial Catalogue value out of the connection string.

e.g.

Array = Split(sConnectionString, ";")

Then Loop Array

Until LEFT(String, LEN("Initial Catalog=")) = "Initial Catalog="

Then Grab the text LEFT of Initial Catalog= i.e. myDBName

I guess I'm just not that good at writing code in QlikView yet.

Would love some string manipulation examples if they exist.

Thanks

Re: Find Datasource info In String

nstefaniuk — Wed, 18 Jan 2012 08:08:23 GMT

Oh sorry, I haven't understood the issue.

You can try this code that will create a table with the parameters and their values. You can modify this code if you prefer to have all the parameters in columns.

vConnectionString = '[Integrated Security=XXXX;Persist Security Info=False;Initial Catalog=myDBName;Data Source=MyServer;Provider=SQLOLEDB.1;]';
Tmp1:
Load
'$(vConnectionString)' As CONNECTION_STRING
AutoGenerate(1);
Tmp2:
Load
subfield(rtrim(ltrim(mid(CONNECTION_STRING,2,len(CONNECTION_STRING)-2))), ';') As PARAMETER
resident Tmp1;
Tmp3:
Load
PARAMETER,
rtrim(ltrim(mid(PARAMETER, 1, Index(PARAMETER, '=')-1))) As PARAMETER_NAME,
rtrim(ltrim(mid(PARAMETER, Index(PARAMETER, '=')+1, len(PARAMETER)-Index(PARAMETER, '=')))) As PARAMETER_VALUE
resident Tmp2;

Find Datasource info In String

Thu, 19 Jan 2012 01:04:46 GMT

Excellent. Works beautifully. Thanks for that!