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';


      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');


          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'), ' ', '');


              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?