Intermediate to Advanced Suggestions
Assign a variable in the code to speed up your transformations
Using variables in your code can significantly speed up expression processing. Compare two methods, one without a declared variable, and one with the declared variable.
Example of a Slower, Less Efficient Method
If Len(Trim(Parse(1, FieldAt("/SOURCE/R1/AccountNo"), " "))) = 7 Then
Trim(FieldAt("/SOURCE/R1/AccountNo")) & Space (4)
Else
String ((11 - Len(Trim(Parse(1, FieldAt("/SOURCE/R1/AccountNo"), " ")))) , "0") & FieldAt ("/SOURCE/R1/AccountNo")
End if
Example of a More Efficient Method with a Variable
Dim var
var = Len(Trim(Parse(1, FieldAt("/SOURCE/R1/AccountNo"), " ")))
If var = 7 Then
Trim(FieldAt("/SOURCE/R1/AccountNo")) & Space (4)
Else
String((11 - var))) , "0") & FieldAt("/SOURCE/R1/AccountNo")
End If
Use the Option Explicit statement
Make it a regular practice to use the
Option Evaluate Automatic Statement in all code modules. When
Option Explicit appears in a module, you must explicitly declare all variables using the
Dim,
Private,
Public or
ReDim statements. If you attempt to use an undeclared variable name, an error occurs at compile time.
Many programmers consider using Option Explicit helpful in debugging applications, as it avoids the chance of mistyping a variable name and inadvertently creating new variables (for example, mistakenly typing MyDoxString instead of MyDocString).
Use functions that signal errors in processing
You can easily interpret errors that occur by using functions or error handing statements that halt your transformation. Without these measures, mysterious errors may occur, such as no data being returned to the target file or table. Learn how to send messages to yourself so that these errors can be flagged and recorded in the log.
Example
'Declare an error handler to trap the error generated from the stored procedure.
On Error goto ErrHandler
'Execute the stored procedure and assign the Identity value to the public variable ABnumber.
'This variable can then be used throughout the transformation process to populate the foreign key fields of the tables.
'Execute the stored procedure with no error generated.
myvariable.SqlStatement = "testing_getIdentity 0"
ABnumber = myvariable.FieldAt(1)
FileWrite("Identity value with no errors = " & ABnumber)
'Execute the stored procedure with error generated. Display the error number and the message returned from the stored procedure.
'Then stop the transformation.
myvariable.SqlStatement = " testing_getIdentity 1"
ABnumber = myvariable.FieldAt(1)
FileWrite("Identity value with errors = " & ABnumber)
'The above statement should not execute, but is included as a precautionary measure.
'Perform error handling.
'The mapping log file contains the error message returned from the stored procedure.
'Alternative: perform error handling at the record level using OnError events.
'ErrHandler:
FileWrite("DJ_Error_description = " & err.description)
FileWrite("DJ_Error_number = " & err.number)
Stop()
Use Resume and Resume Next appropriately
A
Resume Statement makes the expression resume at the line that caused the error. A
Resume Next Statement goes to the line
after the one that caused the error. If your error handling ends with the
Resume statement, it could result in an infinite loop.
Use the correct operator for the job
Should you use an = operator, or the Like operator? A logical or a comparison operator?
Learn about flow control for faster processing
Compare expression code and learn how flow control statements can speed up your transformations.
See Also
Last modified date: 12/03/2024