4 Replies Latest reply: Aug 10, 2016 2:39 AM by Olivier Hoff RSS

    Determine database type in script

    Olivier Hoff

      Hello,

       

      We are currently developping an application for our customers in QlikSense . We would like this app to work on different DB Engines (mainly SQL Server and Oracle).

       

      The problem is that we have in the script a statement that uses tables specific to the engine (oracle or SQLServer); For instance 'select blabla from sys.tables'.

       

      Is there a way to determine the DB behind an OLE DB connection? That way I may be able to use one query or another according to the DB Type.

       

      I searched quite a lot but didn't find anything that would work...

       

      If you guys could help, it would be great !

       

      Thanks a lot,

      Olivier.

        • Re: Determine database type in script
          Bill Markham

          For Oracle this SQL will return one row giving the Oracle version

           

          SELECT * FROM v$version

          WHERE banner LIKE 'Oracle%';


          For SQL Server it would fail, but I expect there would be a SQL server equivalent.

           

           

          From the results returned you should be able to suss whether Oracle or SQL Server and with some IF logic and then execute your desired SQL.


          • Re: Determine database type in script
            Jonas Hertz

            If they are different Libs you could state the connection in a variable and use that to determine which statement to use when loading data throughout the app:

             

            let vConnection = 'sql';

            LIB CONNECT TO $(vConnection);

             

            if vConnection = 'sql' then

                 use sql statements

            else

                 use Oracle statements

            endif

             

            Another solution is to test which engine you are connected to by setting errormode = 0 to prevent the script from stopping if your test fails by doing something like:

             

            set ErrorMode=0;

            let ScriptErrorCount = 0;

             

            First 1 Load * From your SQL database with SQL statement;


            if $(ScriptErrorCount) = 0

                 let vConnection = 'SQL';

            endif


            Then do the same test for Oracle but with Oracle specific statement and use vConnection variable to determine which LOAD statement to use. Remember to set ErrorMode back to 1.

             

            I would prefer option 1, it's never pretty to crash your script on purpose...

            • Re: Determine database type in script
              Olivier Hoff

              Hello,

               

              Thanks for your answer.

               

              I actually found a 'clean' way to achieve what I wanted to do. I use the 'SQLTABLES' fonction to get the table list, this way it's compatible with any engine :

               

              allTables:

              load

                TABLE_NAME,

                  TABLE_TYPE,

                  SubField(TABLE_NAME, '_CC_' ,1) AS Master_Table;

              SQLTABLES;

               

               

              /*

                Restriction de la liste aux tables contenant '_CC_' et calcul du nom de la table de donnée liée

               

               

              */

              charTablesList:

              load

                  TABLE_NAME,

                SubField(TABLE_NAME, '_CC_' ,1) AS Master_Table, /* Recuperation de la partie avant le '_CC_' */

                  TABLE_TYPE

              Resident allTables

              where TABLE_TYPE = 'TABLE'

              and TABLE_NAME LIKE '*_CC_*'; ! !