Need help: TUniQuery descendand and "AutoUpdate" function

[Moehre]

Hi, I am just trying to move my programs from ODAC to UniDAC. I am used to write Oracle SQL's and there are some "special" SQL statements which differ from database to database. So I want to use a TUniQuery descendant where before every OPEN or EXECSQL command the SQL statement is "parsed" for use with the actual provider (with "Oracle" SQL as base statment ... f.e. the Oracle "NVL" (null value) has to be translated to "COALESCE" for MySQL).

How can I implement my own "Oracle SQL to other SQL" converter in the TUniQuery (or better TUniConnection?) Should I overwrite the "BeforeOpen" and "BeforeExecute" Events? (How?

Hope you understand what I mean ... thx in advance!

[AlexP]

Hello,

To use the same code of a query with specific operators in different databases, you should use macros. E.g., for implementation of the NVL operator, you can use the following code:

Code: Select all

  UniConnection1.Macros.Add('NVL', 'coalesce(hiredate, current_date) as HIREADTE', 'MySQL');
  UniConnection1.Macros.Add('NVL', 'NVL(Hiredate, TRUNC(sysdate)) as HIREADTE', 'Oracle');
  UniConnection1.Connect;
  UniQuery1.SQL.Text := 'select ename, {NVL} from emp';
  UniQuery1.Open;  

When adding a macro, the first parameter becomes the macro name, which will be used in the query (in the query, the macro name is enclosed in curly braces).
The second parameter is the function, which will substitute the query depending on the database.
The third parameter is the database, for which the query is written.
When executing the provided code, depending on the DB you are using at the current moment, the required query will be generated.
The other procedures can be implemented in a similar way.