Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
nstefaniuk
Creator III
Creator III

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;

View solution in original post

4 Replies
nstefaniuk
Creator III
Creator III

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
Author

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
Creator III
Creator III

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
Author

Excellent. Works beautifully. Thanks for that!