Multi database SQL query

[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

[frickler]

You may look into https://www.devart.com/unidac/docs/unisql.htm.

[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

[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", "", "");