topic Re: Determine database type in script in App Development

Determine database type in script

Mon, 08 Aug 2016 19:28:34 GMT

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

Anonymous — Tue, 09 Aug 2016 10:04:57 GMT

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

kjhertz — Tue, 09 Aug 2016 11:40:28 GMT

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

Tue, 09 Aug 2016 12:01:04 GMT

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

Re: Determine database type in script

Wed, 10 Aug 2016 06:39:53 GMT

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.