2 Replies Latest reply: Aug 20, 2013 9:52 AM by Ralf Becher RSS

    Load ODBC-Connection-Path from a database

    Manfred Kuhlemann
      Hello!
      Perhaps someone have a solution for the following problem:
      Today i am loading data from many (+- 100) mdb-tables. In the parts of the script only
      the path of the odbc connection is changing.
      Example:
      ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\ABC\LOG.mdb];
      LOAD ...;

      ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\DEF\LOG.mdb];
      LOAD ...;

      ODBC CONNECT32 TO [VCS Universal;DBQ=O:\Directory1\Subdirectory\GHI\LOG.mdb];

      LOAD ...;

      ...

      The path is also stored in a database. Would it be possible to load this scripts in a loop

      and for every entry in the database of the directory?

       

      Example Path-Directory:

       

      ID, Path

      1, O:\Directory1\Subdirectory\ABC\LOG.mdb

      2, O:\Directory1\Subdirectory\DEF\LOG.mdb

      3, O:\Directory1\Subdirectory\GHI\LOG.mdb

      ...

       

        • Re: Load ODBC-Connection-Path from a database

          Hi manfred

          I dont know if it has a solution. I tried it but I didnt found a solution.

           

          I just know that you can do For each loop. But then you'll have alot of work with 100 Directories. It would look like this:

           

          For each vDirectory in 'O:\Directory1\Subdirectory\ABC\LOG.mdb', 'O:\Directory1\Subdirectory\DEF\LOG.mdb', 'O:\Directory1\Subdirectory\GHI\LOG.mdb'

           

          Load ....

          FROM

          [$(vDirectory)]

          (biff, .....)

           

          NEXT;

          • Re: Load ODBC-Connection-Path from a database
            Ralf Becher

            Hi Manfred,

             

            maybe this helps:

             

            // replace with file or database source:
            Databases:
            LOAD * INLINE [
                ID, Path    
                1, O:\Directory1\Subdirectory\ABC\LOG.mdb
                2, O:\Directory1\Subdirectory\DEF\LOG.mdb
                3, O:\Directory1\Subdirectory\GHI\LOG.mdb
            ];
            
            LET vNoRows = NoOfRows('Databases') -1;
            
            for i=0 to $(vNoRows)
                let vDatabasePath = peek('Path',$(i),'Databases');
                
                ODBC CONNECT32 TO [VCS Universal;DBQ=$(vDatabasePath)]; 
                LOAD..;
            next
            
            
            
            

             

            - Ralf