Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Find Datasource info In String

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

Tags (3)
1 Solution

Accepted Solutions
nstefaniuk
Contributor III

Re: Find Datasource info In String

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;

4 Replies
nstefaniuk
Contributor III

Find Datasource info In String

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.

Not applicable

Find Datasource info In String

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

nstefaniuk
Contributor III

Re: Find Datasource info In String

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;

Not applicable

Find Datasource info In String

Excellent. Works beautifully. Thanks for that!

Community Browser