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

    Determine database type in script



      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,


        • 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


                 use Oracle statements



            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';


            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



              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 :






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





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







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


              Resident allTables

              where TABLE_TYPE = 'TABLE'

              and TABLE_NAME LIKE '*_CC_*'; ! !