Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys ,
How to load All tables in mysql database and convert into Qvds..i'm searched in forum Load all tables in database dynamically
based on this i'm generated a script but im getting error....
this is my table in the database:
SQL SELECT *
FROM `sbi_apg_db`.`Availability;
This is my script:
SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT DISTINCT "TABLE_NAME"
FROM "sbi_apg_db".COLUMNS;
Let vTableCount = NoOfRows('SQLTableList');
Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';
For i = 0 To $(vTableCount) -1
LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';
LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');
$(vMyTableNameQVD):
SQL SELECT *FROM $(vMyTableName);
STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;
DROP Table $(vMyTableNameQVD);
Next i
this is my error:
see the attachment
Please anyone give syntax..for this script
You should use
select table_name from information_schema.tables where table_schema='<your_database_name>';
Your MySQL database doesn't like the SQL statement SQL SELECT DISTINCT "TABLE_NAME" FROM "sbi_apg_db".COLUMNS. This you have to sort out with your MySQL database. Qlikview only passes that sql statement to the odbc driver. Qlikview doesn't do anything else to or with it.
You should use
select table_name from information_schema.tables where table_schema='<your_database_name>';
You get the table names from the information_schema.tables in mySql:
TTABLE:
select
TABLE_NAME
from information_schema.TABLES
where TABLE_CATALOG = 'SBI_APG_DB' and TABLE_TYPE = 'BASE TABLE';
for lfd = 1 to fieldvaluecount('TABLE_NAME');
let Table = fieldvalue('TABLE_NAME',$(lfd));
[$(Table)]:
select * from $(Table);
let nor=NoOfRows('$(Table)');
if $(#nor)>0 then
store [$(Table)] into [$(sSaveDir)\$(Table).qvd];
endif
drop table [$(Table)];
next
drop table TTABLE;