Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
How can I make QV to load all tables from a SQL DB?
Something like
Load *;
SQL SELECT *
Somekind of loop function?
Edit:
I found and tryd this but did not work. All I get is the TABLE_NAME table.
Sub LoadTableNames
SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT "TABLE_NAME"FROM KQ0."INFORMATION_SCHEMA".COLUMNS;
End Sub;
Call LoadTableNames;
Let vTableCount = NoOfRows(‘SQLTableList’);
Sub LoadTableData
For i = 0 To $(vTableCount) -1
LET vMyTableName = Peek(‘TableNames’, $(i), ‘SQLTableList’);
$(vMyTableName):
SQL SELECT *FROM KQ0.KQ0$(vMyTableName);
STORE $(vMyTableName) INTO $(ExtractQVD)$(vMyTableName).QVD;
DROP Table $(vMyTableName);
Next i
End Sub;
Call LoadTableData;
Good timing, I've been doing something very similar today, this is my code. You can't filter the data returned by SQLCOLUMNS or SQLTYPES directly. You have to run the code then move the data to another table and filter at that point ...
ODBC CONNECT TO {connection};
AllODBCTables:
Load *;
SQLCOLUMNS;
ODBCDataTypes:
Load *;
SQLTYPES;
DISCONNECT;
ODBCTypes:
Load
DATA_TYPE as dtDataType,
concat(TYPE_NAME,',') as dtTypeNameList //I have multiple options so am combining!!
resident ODBCDataTypes
group by DATA_TYPE;
ODBCTables:
LOAD
//TABLE_QUALIFIER as tblQualifier, //no data
TABLE_OWNER as tblOwner,
TABLE_NAME as tblName,
COLUMN_NAME as tblColName,
DATA_TYPE as tblColDataType,
//TYPE_NAME as tblColTypeName, //no data
PRECISION as tblColPrecision,
LENGTH as tblColLength,
SCALE as tblColScale,
RADIX as tblColRadix,
NULLABLE as tblColNullble,
//REMARKS as tblColRmrks, //no data
//EXTENDED_ID as tblColExtID, //no data
DATA_SOURCE as tblDSource
resident AllODBCTables where etc, etc;
left join (ODBCTables)
LOAD
dtDataType as tblColDataType,
dtTypeNameList
resident ODBCTypes;
DROP TABLES AllODBCTables, ODBCDataTypes, ODBCTypes;
This isn't loading any data, just the table structures. Hope this helps ...
flipside
Hi,
This is not good practice to load all tables in a loop, because you don't actually require all the tables for report, also all columns in the tables are not required.
Also in For loop the execution will be very slow. In my view writing and loading the data manually from each table is best option.
Hope this helps you.
Regards,
Jagan.
Hi
Maybe I should told why I would like to do this. I´m searching for the table/s that holds the plain text. Most of the other fields in DB are just numbers. When extracting the "Table_Name" table and searching after the fields I´m looking for I can´t find them.
I understand that the operation will be slow and not best practice but the db aint so big so I beliwe it would work.
So back to my orginal question, anyone sees why the above loop don´t work?
HI,
Try this script.
SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT DISTINCT "TABLE_NAME"
FROM "INFORMATION_SCHEMA".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
Regards,
jagan.
Hi,
If you try this code, it will bring back the DataType of each column it finds in the connection ...
ODBC CONNECT TO {odbc name};
SQLCOLUMNS;
Is this what you are looking for?
flipside
EDIT: Just add a preceding load to create a table you can then use in some dynamic script.
ODBC CONNECT TO {connection}; //or oledb
SQLTableColumns:
load *;
SQLCOLUMNS;
You CAN also use a named table without the preceding load ...
SQLTableColumns: SQLCOLUMNS;
... Script Editor shows this as an error but it does run.
Thanks. How can I do to load all tables in KQ0. **** ?
ODBC CONNECT TO {connection};
SQLTableColumns:
SQLCOLUMS in KQ0....
Good timing, I've been doing something very similar today, this is my code. You can't filter the data returned by SQLCOLUMNS or SQLTYPES directly. You have to run the code then move the data to another table and filter at that point ...
ODBC CONNECT TO {connection};
AllODBCTables:
Load *;
SQLCOLUMNS;
ODBCDataTypes:
Load *;
SQLTYPES;
DISCONNECT;
ODBCTypes:
Load
DATA_TYPE as dtDataType,
concat(TYPE_NAME,',') as dtTypeNameList //I have multiple options so am combining!!
resident ODBCDataTypes
group by DATA_TYPE;
ODBCTables:
LOAD
//TABLE_QUALIFIER as tblQualifier, //no data
TABLE_OWNER as tblOwner,
TABLE_NAME as tblName,
COLUMN_NAME as tblColName,
DATA_TYPE as tblColDataType,
//TYPE_NAME as tblColTypeName, //no data
PRECISION as tblColPrecision,
LENGTH as tblColLength,
SCALE as tblColScale,
RADIX as tblColRadix,
NULLABLE as tblColNullble,
//REMARKS as tblColRmrks, //no data
//EXTENDED_ID as tblColExtID, //no data
DATA_SOURCE as tblDSource
resident AllODBCTables where etc, etc;
left join (ODBCTables)
LOAD
dtDataType as tblColDataType,
dtTypeNameList
resident ODBCTypes;
DROP TABLES AllODBCTables, ODBCDataTypes, ODBCTypes;
This isn't loading any data, just the table structures. Hope this helps ...
flipside