Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
sivaprakash_t
New Contributor

my Sql Database issue

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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: my Sql Database issue

You should use

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

3 Replies

Re: my Sql Database issue

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.


talk is cheap, supply exceeds demand
Employee
Employee

Re: my Sql Database issue

You should use

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

chrwolf64
Contributor III

Re: my Sql Database issue

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;

Community Browser