1 Reply Latest reply: Jun 30, 2017 5:00 AM by Wojciech Lisowski RSS

    Script for save tables as QVD files from many databases

    Wojciech Lisowski

      Hi guys,

       

      i'm new to QS and new here so please be forgiving for .

       

      There you have script which i created:

       

      LIB CONNECT TO 'mastermysql';

      Databases:

      LOAD "table_schema" as dbname;

      SQL SELECT distinct(table_schema) FROM information_schema.tables where table_schema != 'information_schema';

       

      LET DatabaseCount = NoOfRows('Databases');

      FOR d = 0 TO $(DatabaseCount) -1

          LET database = Peek('dbname',$(d), 'Databases');

          MyTables:

          LOAD "table_name" as TableNames;

          SQL SELECT table_name FROM information_schema.tables where table_schema= '$(database)';

          LET TableCount = NoOfRows('MyTables');

          FOR t = 0 TO $(TableCount) -1

              LET vMyTableName = '$(database).' & Peek('TableNames', $(t), 'MyTables');

              LET vMyTableNameQVD = Replace(Peek('TableNames', $(t), 'MyTables'), ' ', '');

              $(vMyTableNameQVD):

              SQL SELECT * FROM $(vMyTableName);

              STORE $(vMyTableNameQVD) INTO 'lib://masterqvd/$(database)/$(vMyTableNameQVD).qvd';

              DROP Table $(vMyTableNameQVD);

          NEXT t

      NEXT d

       

      Examle DB's structure:
      db1 with tables y1,y2,y3
      db2 with tables x1, x2, x3

       

      Problem is with somewhere near variable vMyTableName.

      While executing line: SQL SELECT * FROM $(vMyTableName); it tries to mix tables from db2 with tables from db1 - eg. SQL SELECT * FROM db2.y1;

       

      Not sure but i think that QS firstly retrieve all tables (from 2 database) instead of doing it in FOR statement.

      Any ideas?