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
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:
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...
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_' */
where TABLE_TYPE = 'TABLE'
and TABLE_NAME LIKE '*_CC_*'; ! !