Using Variables in Job Runner XML
Hint Ref: 021104010007
Hint Date: 01/04/2011
Hint Details:
There are two methods for using Variables in Job Runner XML; By NAME and by SUBSTITUTION.
By Substitution
In this case the Variable name is surrounded by {}. If the Variable is to be used as a string, then you will need to surround the whole thing with single quotes, thus
'{Variable1Name}'
For example
Insert into Table1 (CharField1) values ('{Variable1Name}');
If the Variable contains a number or a numeric string value you want to use as a number then you should use just the Variable name surrounded by {}, thus
{Variable1Name}
For example
Set ThisCount = {Variable1Name};
NOTE: However, it is VERY important that, with this method, the Variable does not contain any illegal characters such as NULLS (char(0)) or Apostrophes (single quotes).
Let us assume that the Variable contains the name O'Brian;
'{Variable1Name}' would be substituted as 'O'Brian' which would cause SQL to return the error 'Missing Right Quote'.
If the Variable is likely to contain characters that will cause problems, you MUST use the Variable by NAME.
By Name
In this case the Variable name is preceded by ':', thus
:VariableName
For Example
Insert into Table1 (CharField1) values (:Variable1Name);
Also use Variables by NAME if the data TYPE of the Variable is important, such as with DATEs.
NOTE: It is always advisable to surround the :Variable1Name with brackets thus
(:Variable1Name)
For Example
Insert into Table2 (CharField1, DateField1) values ((:Variable1Name),(:Variable2Name));
Insert into Table3 (CharField1, DateField1) values ('{Variable1Name}',dateadd(day,2,(:Variable2Name)));
NOTE: Using a variable by name is ONLY available with SQL statements (and is the preferred method), it cannot be used elsewhere in XML Scripts. Use the Substitution method instead.
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)