Page 1 of 1
Set Macros in the SQL text (MacroByName)
Posted: 26.08.2014, 11:54
by 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
Re: Set Macros in the SQL text (MacroByName)
Posted: 07.09.2014, 11:26
by EgonHugeist
Hi,
uhh i didn't know this is possible
lorbs (this need to be implemented or) are these macros done by the TStringList?
Re: Set Macros in the SQL text (MacroByName)
Posted: 07.09.2014, 14:42
by androschuk
EgonHugeist wrote: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
Re: Set Macros in the SQL text (MacroByName)
Posted: 07.09.2014, 18:32
by alexs75
For this purpose, I have made the heir of TZQuery - TZMacroQuery.
https://svn.shamangrad.ru/zeos_ex/
Re: Set Macros in the SQL text (MacroByName)
Posted: 08.09.2014, 19:47
by 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?
Re: Set Macros in the SQL text (MacroByName)
Posted: 08.09.2014, 20:29
by alexs75
I implemented the heir from TZQuery. If this functionality will be in TZQuery - it will be good.
Код основан на библитеке RX.
Re: Set Macros in the SQL text (MacroByName)
Posted: 12.09.2014, 20:55
by 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.
Re: Set Macros in the SQL text (MacroByName)
Posted: 06.12.2014, 17:50
by 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.
Re: Set Macros in the SQL text (MacroByName)
Posted: 11.12.2014, 23:14
by 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...