Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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_*'; ! !
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.
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...
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_*'; ! !
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.