4 Replies Latest reply: Jan 18, 2012 8:04 PM by Niels van Diermen RSS

    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

        • Find Datasource info In String
          Nicolas Stefaniuk

          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

              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
                  Nicolas Stefaniuk

                  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;