6 Replies Latest reply: Nov 22, 2012 8:40 AM by Dave Riley RSS

    How to load all tables from sql DB?

    Göran Hofstedt

      Hi

      How can I make QV to load all tables from a SQL DB?

      Something like

      Load *;

      SQL SELECT *

      FROM KQ0.*

       

      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;

        • Re: How to load all tables from sql DB?
          jagan mohan rao appala

           

          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.

            • Re: How to load all tables from sql DB?
              Göran Hofstedt

              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?

                • Re: How to load all tables from sql DB?
                  jagan mohan rao appala

                   

                  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.

                  • Re: How to load all tables from sql DB?
                    Dave Riley

                    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.

                      • Re: How to load all tables from sql DB?
                        Göran Hofstedt

                        Thanks. How can I do to load all tables in KQ0. **** ?

                        ODBC CONNECT TO {connection};

                        SQLTableColumns:

                        SQLCOLUMS in KQ0....

                          • Re: How to load all tables from sql DB?
                            Dave Riley

                            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