Determine database type in script

[#1]

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.

[Anonymous]

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.

[kjhertz]

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...

[#4 · Accepted solution]

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_*'; ! !

[#5]

Thanks for your answer.

I didn't know you could specify the dialect used by Qlik. I'll consider using this if I need to do a specific query in the future.

Olivier.