Script for save tables as QVD files from many databases

Thu, 29 Jun 2017 13:01:22 GMT

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?

Re: Script for save tables as QVD files from many databases

Fri, 30 Jun 2017 09:00:50 GMT

I've added DROP Table MyTables; between NEXT t and NEXT d fixed the problem.

Hope that script will be usefull for someone .