Macros/Params property
|
Previous Top Next |
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).
|
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] |
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.
|