my Sql Database issue

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[Clever_Anjos · Accepted solution]

You should use

select table_name from information_schema.tables where table_schema='<your_database_name>';

[cwolf]

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;