Macros/Params property

Previous  Top  Next

See Also Properties Methods Events Example
Unit
MySQLDataset

Applies to
TMySQLDataset and TMySQLQuery component

Declaration
property Macros: TParams;
property Params: TParams; // for TMySQLQuery


Description
The TParams collection of Macros found in your SQL statements.

Gives you runtime access to all Macros used in any SQL statements. Please DON'T have the following section confuse you or complicate your life. The TMySQLDataset/Table/Query can be used without using any macros and are fully operational with default settings as is.

That said, macros are actually really simple to use and becomes very powerful if you are an advanced user. Macros basically boils down to nothing more than substitution. Each time you set an SQL property like SQL, SQLUpdate, SQLDelete, SQLInsert or Filter the TMySQLDataset inspects your sql for possible macros and adds it to the Macros (Params in TMySQLQuery) property where you may inspect, change and set default values if you like (more applicable to user defined macros than special macros).

NOTE: In the Options property you can switch macros on and off completely using doMacrosEnabled. While doMacroCheck in Options (or ParamCheck property) determines if the Macro collection gets cleared before it is populated again. This is important, because if you defined your own macro and maybe set a type or value for it, this will be lost if the collection is cleared.

Macros:

Macros enable you to type in your SQL once and then optionally set parts of the SQL statements later using substitution. Macros falls into two categories, special macros (which all start with a $ character) and user defined macros.

Lets look at the SQL property's statement for example of both:
   select [$FIELDS] from [$TABLENAME] [JOIN] [WHERE] [GROUPBY] [ORDER] [LIMIT]

This sql select statement has 7 macros defined. $FIELDS and $TABLENAME are special macros, while the rest like LIMIT are user defined. $FIELDS automatically gets replaced by the persistent fieldnames you defined on your dataset, if you defined any by double clicking the component. If you have none, $FIELDS becomes *. So a good way to use this is to leave persistent fields, open the dataset which will then fetch all columns, create persistent fields you would like to use, close the dataset and just open it again. Now $FIELDS will be replaced by the fieldnames of the persistent fields you created, thus second open will only fetch the columns you wanted this time from the mysql server, thereby saving bandwidth and optimizing your application without you doing anything different than you would normally do.

$TABLENAME gets replace by default by the TMySQLDataset.TableName property value. If you don't specify any values for the other macros, your statement will look like this:
   select * from MyTable

if you however specified a value for one of the user defined macros at design time or runtime using code like this:
   ...
   MySQLDataset1.MacroByName('LIMIT'
).AsString := 'limit 10';
   MySQLDataset1.Open
   ...
the statement sent will look like this:
   select * from MyTable limit 10

NOTE: You can view, log, change or customize each sql statement before it gets sent to the mysql server using the OnExecSQL event of the TMySQLDataset/Table/Query.

When any sql needs to be send to a mysql server, it is inspected for macros. Any macros found gets substituted using the following procedure and order of precedence:

1.All special macros gets replace by their corresponding value, see the table of special macros which follows.  
2.If the macro is not a special macro it gets check for and in the following order of precedence for a value:  
·The macro name is matched to this dataset's fieldnames to see if it matches any fields. If it does, it gets replaced by the corresponding field's value.  
·If no match, the macro gets checked against the fields of the linked dataset via the MasterSource/DataSource property. If a match is found, it gets replaced in the SQL statement by the value of the corresponding field.  
·If still no match, the macro is check for an own value, meaning if you have set a value for it at design or runtime. If so, it gets replaced by this value. Also if you changed it's type, the value gets formatted correctly for mysql, ex. if you set MacroByName('DATE').AsDateTime := Now; the date will get formatted correctly into a mysql complaint date.  
·If no value has been set, the macro gets replaced by nothing, and thus the space it occupied in the sql statement becomes one space.  
3.After all macros has been resolved and if the SQL is the main select SQL, it gets completed by the filter and master/detail conditions  
4.If the dataset is set to cache update, inserts, deletes the SQL is added to the SQLCached list to be send when you close or refresh the dataset. NOTE: Even though the statements are cached, the clientside records in your grid will appeared to be updated.  
5.If not cached, the SQL is ready to be send and the OnExecSQL event is fired, allowing you to view, log and/or change the final SQL.  
6.After the OnExecSQL event fired and you choose to continue the send, it is physically send to the MySQL server (via a seperate thread if you set doThreaded in Options to True).  

Table of Special Macros:
Macro
Description
$DATABASENAME
Will be replaced by the DatabaseName property of your MySQLDataset.
$TABLENAME
Will be replaced by the TableName property of your MySQLDatasset.
$FIELDNAME
Will be replaced by the first field (";" or "," delimited) in the FieldName property of your TMySQLDataset.
$FIELDS
Will be replaced by the persistant fields (the fields you specified by double clicking your dataset) of your dataset, if none where specified this macro will be replaced by the * (asterisk). This is to make sure that if you don't want all the fields from a table in your application, the dataset won't do the extra work and allocate unneccessary memory when querying the MySQL server for unneccessary fields you might like to request seperately like BLOB's for images etc.
$WHERE
Will be replaced with a combination of the MasterFields and Filter Properties:
Example if the MasterFields property was set to "AccountID=AccountID" and Filter property was set to "AccountName like "J%" then the result will be: WHERE (AccountID=1) and (AccountName like "J%")
$MODIFIED$FIELDS&VALUES
Will be replaced by the current record's modified fields, the result will look like:
FieldName1="Value1",FieldName2="Value2",...
NOTE: Values will be Escaped, Empty fields will =NULL, BLOB types will equal =0x874365837546...
$UNMODIFIED$FIELDS&VALUES
Will be replaced by the current record's unmodified fields, the result will look like:
FieldName1="Value1",FieldName2="Value2",...
$MODIFIED$FIELD$NAMES
Will be replaced by the current record's modified fieldnames, the result will look like:
FieldName1,FieldName2,...
$UNMODIFIED$FIELD$NAMES
Will be replaced by the current record's unmodified fieldnames, the result will look like:
FieldName1,FieldName2,...
$MODIFIED$FIELD$VALUES
Will be replaced by the current record's modified field values, the result will look like:
"FieldValue1","FieldValue2",...
$UNMODIFIED$FIELD$VALUES
Will be replaced by the current record's unmodified field values, the result will look like:
"FieldValue1","FieldValue2",...
$OLD$FIELDS&VALUES
Will be replaced by all the fields in the current result set's fieldnames and values for the current record's previous values, the result will look like:
(FieldName1="Value1") and (FieldName2="Value2") and ...
$FIELDNAMES&VALUES
Will be replaced by all the FieldName property's (";" or "," delimited) fieldnames and values for the current record, the result will look like: (FieldName1="Value1") and (FieldName2="Value2") and ...
$PRIMARYFIELDS&VALUES
Will be replaced by all the primarykey fields in the current result set's fieldnames and values for the current record, the result will look like:
(FieldName1="Value1") and (FieldName2="Value2") and ...
NOTE: If there is no primary key fields in the current result, this macro will return the fields and values for the $OLD$FIELDS&VALUES macro.
$UNIQUEFIELDS&VALUES
Will be replaced by all the unique fields in the current result set's fieldnames and values for the current record, the result will look like:
(FieldName1="Value1") and (FieldName2="Value2") and ...
$MULTIFIELDS&VALUES
Will be replaced by all the fields that is part of a multiple field key in the current result set's fieldnames and values for the current record, the result will look like:
(FieldName1="Value1") and (FieldName2="Value2") and ...
$FIELDNAMES&VALUES$COMMA
Will be replaced by all the FieldName Properties (";" or "," delimited) fieldnames and values for the current record, the result will look like:
FieldName1="Value1",FieldName2="Value2",...
$FIELDNAME$VALUE
Will be replaced by the first field (";" or "," delimited) in the FieldName property of you MySQLDataset's current record's value:
"FieldValue1"
$OLD$YourFieldNameHere
Will be replaced by the field by this name's current record's previous value. Case Insensitive:
"FieldOldValue1"
for example. If you need to update a record in a result set with:
update mytable set AccountName="abc" where AccountName="cba"
you change your SQL property to:
update mytable set AccountName=[AccountName] where AccountName=[$OLD$AccountName]


If and when a [Macro] is found in any SQL property it is resolved like this:
1.If the Macro matches one of the special macros listed above it replaced with the value as indicated in the table above, ELSE  
2.If the Macro matches one of this dataset's column/fieldnames, then it is replaced with that field's value, ELSE  
3.If the Macro matches one of this dataset's mastersource/datasource dataset's column/fieldnames, then it is replaced with the master dataset's field's value, ELSE  
4.If the Macro matches one of the Macros in the Macros/Params property, it is replaced with that macro's value if you specified it at design or runtime, ELSE  
5.The macro is replaced with an empty space in the final SQL.