topic Re: my Sql Database issue in QlikView

my Sql Database issue

Anonymous — Wed, 28 Oct 2015 17:13:20 GMT

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

Re: my Sql Database issue

Gysbert_Wassenaar — Wed, 28 Oct 2015 17:57:56 GMT

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.

Re: my Sql Database issue

Clever_Anjos — Wed, 28 Oct 2015 18:21:36 GMT

You should use

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

Re: my Sql Database issue

cwolf — Wed, 28 Oct 2015 18:22:25 GMT

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

drop table TTABLE;