Set Macros in the SQL text (MacroByName)

[androschuk]

Instruments:
ZEOSDBO-7.1.3a-stable
Lazarus v1.2.4-fpc-2.6.4-win32
Database: Personal Oracle Database 11g Release 11.2.0.3.0 - 64bit

When I create query I'm using oracle schema, and my query looks like:

Code: Select all

 select t1.* 
 from 
  &schema.table_name t1
 where 
  t1.id = :p1
  &filter

How I can set &schema and &filter in query.

Code: Select all

 
var
 Query: TZQuery;
...
Query.SQL.Text :=' select t1.*  
 from  &schema.table_name t1
 where 
  t1.id = :p1
  &filter'
  ParamByName('p1').Value := 10;
  MacroByName('schema').Value := 'MySchema';
  MacroByName('filter').Value := ' AND t1.Name = ''''somename''';

As a result, I have to have the following sql text:

Code: Select all

 select t1.* 
 from 
  MySchema.table_name t1
 where 
  t1.id = :p1
  AND t1.Name = 'somename'

Now I'm using StringReplace to set &schema and &filter in query.
Maybe in the components have similar functionality macros

[EgonHugeist]

Hi,

uhh i didn't know this is possible

lorbs (this need to be implemented or) are these macros done by the TStringList?

[androschuk]

Hi,
lorbs (this need to be implemented or) are these macros done by the TStringList?

I think it is necessary to implement.

At now I using StringReplace

[alexs75]

For this purpose, I have made the heir of TZQuery - TZMacroQuery.
https://svn.shamangrad.ru/zeos_ex/

[EgonHugeist]

Lorbs my browser don't like this link ????

Could you attach a zip? If you're willing to share you code-changes of course. Than i will check and merge your changes and tag it as "alexs75". What do you think?

[alexs75]

I implemented the heir from TZQuery. If this functionality will be in TZQuery - it will be good.
Код основан на библитеке RX.

[EgonHugeist]

Hi Alex,

well i did study the proposal a bit. First: the whole team did agree going beta, which means such features have to wait a bit.
Creating a descendant of TZQuery isn't what i want. So i'll invest some more time later to do it in a "Zeos-way" (we've a tokenizer which allready determines the Params why shouldn't we add a step for such Macro-supports too?)

So be patient, please.

[patyit]

Hi EgonHugeist, Alex !

I'm just want to confirm that the Macro functionality is relay good thing and it will bring ZeosDBO up to new level of usability.
Years a go I use MyDAC and lot of things can do with Macros. If I remember Zeos has macros in earlier versions, can't remember
when it was gone ... but it doesn't meter. Implementation will be similar like Params, except that when using
MacroByName('somemacro').AsString = ' House' that will substitute string with quotes, and
MacroByName('somemacro').Value = ' AND id IN(2,5,9)' substitutes without quotes like as is into SQL.Text.
Like Params, Macro properties can be used in form Macro[0].Value = ' AND id IN(2,5,9)' …
I'm showing example: Macro[0].Value = 'AND id IN(2,5,9)' because this construction with Params
newer be possible: SQL.Add('AND id IN(:idlist)') ---> Params[0].AsString = '2,5,9' –-> Error !
Because Params adding quotes to strings ! Eventually if adding in form Params[0].Value = '1,5,9' to substitute string as is ??!
But the best is to separate logic from Params. Params char is ':', Macro cahr is '&' by default.

Thanks, Patyi.

[EgonHugeist]

I think it (was omited because of)/is a problem by keeping and using Prepared-Statements we prefere.
Suing Marcos for Object identifiers will unprepare current stmt and prepare a new one....

Hard to judge. Of cours it might be an advantage for "simple use" but is against all performance except we would cache all the stmts...