Page 1 of 1
Multi database SQL query
Posted: Fri 09 Nov 2018 18:46
by jjeffman
Hello,
Someone has told me that UniDac supports querying different database systems with the same SQL text.
How can I do that? For example SQL Server uses
[datafield]
Oracle do not.
Is there any way of enable parts of a complex SQL query to make it compatible to each database system the application is connected ?
Thank you very much.
Jayme Jeffman
Re: Multi database SQL query
Posted: Mon 12 Nov 2018 07:56
by frickler
Re: Multi database SQL query
Posted: Mon 12 Nov 2018 14:31
by jjeffman
Thank you very much for answering me.
I have attemted your sugestion but I am getting "empty macro error" when running the code bellow
Code: Select all
HST2_OCOR->SQL->BEGINUPDATE();
HST2_OCOR->SQL->CLEAR();
HST2_OCOR->SQL->ADD("SELECT OCORRENCIA_ID \
,INICIO \
,ALARMEIN \
,TERMINO \
,DURACAO \
,ENVIOABERTURA \
,ENVIOFECHAMENTO \
,INTERVBUSCAINI \
,INTERVBUSCAFIM \
,BUSCAFALINF \
,BUSCAFALSUP \
,TRATAR \
,ENVIAREMAIL \
,MODULO_ID \
{ IF ORACLE } \
FROM HST2_OCORRENCIA \
{ELSE} \
{IF DBSTO } \
FROM [DBSTO].[DBO].[HST2_OCORRENCIA] \
{ELSE} \
FROM [DBO].[HST2_OCORRENCIA] \
{ENDIF} \
{ENDIF} \
WHERE TRATAR = 'S'");
HST2_OCOR->MACROBYNAME("ORACLE")->CLEAR();
HST2_OCOR->MACROBYNAME("DBSTO")->CLEAR();
IF( SERVERSTO == L"ORACLE" )
HST2_OCOR->MACROBYNAME("ORACLE")->VALUE = "ORACLE";
ELSE
{
IF(! EMPRESA.SQL_DATABASESTO.ISEMPTY() )
HST2_OCOR->MACROBYNAME("DBSTO")->VALUE = EMPRESA.SQL_DATABASESTO ;
}
HST2_OCOR->SQL->ENDUPDATE();
// This line raises exception
I have tried using &Oracle and &DBSTO and it also fails.
Can you please point me an example of its usage a little bit more complex then the ones placed in the documentation.
Thank you very much.
Jayme Jeffman
Re: Multi database SQL query
Posted: Tue 13 Nov 2018 15:18
by Stellar
Oracle is one of the predefined macros that helps to solve the most common differences in SQL syntax. Therefore, there is no need in adding this macro, it will be automatically added when using the Oracle provider. The list of predefined macros: devart.com/unidac/docs/unisql.htm#macroref
If you want to use the custom macro "DBSTO" for conditional jumps, then before executing the query you need to add it to the macro list, for example:
UniConnection1->Macros->Add("DBSTO", "", "");