Starfish Scripting Class: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
Line 191: Line 191:


newdt = FormatDate(olddt, “yyyy-MM-dd hh:mm:ss”)
newdt = FormatDate(olddt, “yyyy-MM-dd hh:mm:ss”)
This function uses the .NET DateTime formatting notations found here: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.
|}
|}



Revision as of 04:05, 23 May 2018

From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Starfish” class. The tables below define its usage. This class may also be used with C# scripting.

Stage Variables

You can use these stage variabes like you would use a variable in your code. For example, you could have VBScript where you wanted to see if a stage resulted in an Update or Insert, so you could do If "@@STG:0,#Action@@" = "Update" Then...

Property Name Description
Action Use in scripting as: "@@STG:0,#Action@@" for the first stage. Mostly used in an After Operation script. It will return if the stage was an Update, Insert, Ignore, or Error.

Class Properties

You can use these class properties like you would use a variable in your code. For example, you could have VBScript where you wanted to see if you were running or Previewing, so you could do If PreviewMode = True Then...

Property Name Description
CurrentStageName Read-only. Returns the name of the current stage. Useful during “Repeat Each Stage” Exec Operations to determine path of execution/next steps.
TotalRows Returns the total number of Origin Rows in the job.
CurrentRow Returns the current row number.
ReturnValue Sets the return value to the calling application. Useful for passing an output back when the Job was called with an argument.
Origin ( String ) Returns the current Origin row’s value for the field specified.
PreviewMode This boolean variable can be used to determine if you are Previewing or Running a Job. Returnes True of False. Ex: If PreviewMode <> True Then...

Class Methods

Custom Engine Settings & Variables Class Methods

These values can be seen and modified on the General tab in Starfish. The values are stored in a local SQLite database, C:\inetpub\wwwroot\StarfishEngine\engine.db and are persistent across jobs and runs.

Method Name Description
GetSetting(name As String) As String Use this to retrieve an engine variable.
SaveSetting(name As String, value As String) As Boolean Use this to set an engine variable.

Starfish Stage & Job Control Class Methods

Method Name Description
GotoStage ( String ) Directs Starfish Engine to go directly to a Stage (skipping any others before it). String Parameter is the Name of the stage to go to. Once GotoStage has been called within a Job, it is considered in a “manual operation mode”. No more stages will be called in order and it will be up to the user to implement a After Each Stage Exec Operation to control stage flow. If no After Operation is called, it will go to the next row after the stage runs.
GotoJob ( String ) Directs Starfish Engine to go directly to a Job. Pass in the GUID of the Job found on the General Tab, "Current Job ID". Please use with caution.
GotoNextRow () Skips all Stages and moves to the next Origin row.
EndJob ( String ) Cancels the Job execution immediately, ending with an error message as passed into the String param.

Lookup and data retrieval Class Methods

Method Name Description
GetFields ( String ) Executes a SQL SELECT statement returns all field values from the first row, comma-delimited. VBScript Split() function may be used to convert to an array within your function. Can improve performance when needing multiple fields from a single table.
ExecScalar ( String, String(Optional) ) Executes a SQL SELECT statement on the Destination database. Returns the first column of the first row returned by the query. Compatible with OLE DB destinations.

There is an optional parameter to query the origin. To query the origin, use the second parameter of "ORIGIN"EX: ExecScalar("select id from company", "ORIGIN")

ExecScalarCache ( String ) Same as ExecScalar, except that results are cached and before a query is executed, a search is performed on a locally-stored set of results to prevent execution of identical queries, thus boosting performance. Should only be used for queries where multiple identical queries may be run (such as looking up user id’s or picklist values) – otherwise could consume resources needlessly.
ExecSQL ( String, String(Optional) ) Executes a non-query statement, such as UPDATE or DELETE against the destination database. Returns number of rows affected. Compatible with OLE DB destinations. First parameter is SQL Statement. Second parameter is optional connection to run again. "DESTINATION" is default, other option is "ORIGIN".
SmartLookup ( String, String, String, [Boolean], [String], [String] ) Performs a lookup against the destination database, and returns one field value based on supplied filter.

String – Table to perform lookup in

String – Name of field to return

String – Filter code (such as Where clause, not including keyword “Where”)

Boolean – Default False. Enable cache for this lookup.

String – Default Blank. Order By

String – Default "DESTINATION". Use "ORIGIN" to query the Origin.

SmartQuery ( String, [String], [String], String(Optional)) Performs a lookup query against the destination database, returns all columns and all rows from query as a multi-dimensional array.

String – Table to perform the query in

String – Filter code

String – Default All Columns. Comma-separated list of columns to return

String – Default "DESTINATION". Use "ORIGIN" to query the Origin.

If the filter and columns parameters are left blank, you may pass the full SQL statement into the first parameter.

EX: arr = SmartQuery("team_sets_teams","deleted = 0 and team_set_id = '@@ORG:team_set_id@@' and team_id <> '@@ORG:team_id@@'","team_id","ORIGIN")

SmartQueryDT ( String, [String], [String], String(Optional)) Same as above, except returns the results as a System.Data.DataTable object. C# only.

Other Class Methods

Method Name Description
LogMessage ( String, MsgBoxStyle ) Appends a custom message to the Log which will always be displayed (even if Logging Level is set to None). Message is the passed in String parameter. Second parameter is a valid MsgBoxStyle (vbInformation, vbQuestion, vbExclamation, vbCritical)

C# Example: Starfish.LogMessage("foo");

SendEmail ( String, String, String, [String], [String], [String], [String], [Boolean] ) Sends an email directly from within the VBScript function/procedure. SMTP Server settings are pulled from the values entered on the General tab.

Parameters:

(String) To_Addr – Email Recipient Address(es), separate multiples with ";"

(String) Subject – Subject of the Email

(String) Body – Body of the Email

(Optional, String) From Address (default is what's entered on the general tab)

(Optional, String) From Display (default is what's entered on the general tab)

(Optional, String) CC Address(es)

(Optional, String) BCC Address(es)

(Optional, Boolean) Body Format HTML (default is false - plain text)

ParseName ( String, String ) Parses as string containing a person’s name, and returns the part of the name requested.

Parameters:

String – Input String containing entire name

Part – Part of the name to return. Valid parts are “Title”,”First”,”Last”,”Middle”,”Pedigree”,”Degree”.

RemoveIllegalXMLCharacters ( String ) This function removes Illegal XML Characters from a string. This function can be used on a string before inserting it into the Target to resolve the "error in msg parsing: XML error parsing SOAP payload on line 32: Invalid character" error.
GetStageValue ( Integer, String) Returns the destination value used on a previous stage.

Integer – Index number of the stage, 0-based.

String – Field name of the value from this stage to return.

For some of the CRM Native API’s (SData, Microsoft), generated ID’s will be automatically stored in a field named “Key”.

SetLastRunDate This will write the Last Run Date/Time to a local Xref. Note that this Method MUST be run in an After Operation and Executed as "Once After Conn". Use this to Retrieve Records Modified After Last Run DateTime.

Use @@VAR:LastRunDate@@ in your origin to use the stored value. Note that you may run into TimeZone issues. Please see Retrieve Records Modified After Last Run DateTime for workarounds.

IsEmailValid ( String ) Returns True if supplied email address is in a valid email format; otherwise false.
FormatDate ( String, String ) Returns date in supplied format.

String – Date to format

String – Format String

Example:

newdt = FormatDate(olddt, “yyyy-MM-dd hh:mm:ss”)

This function uses the .NET DateTime formatting notations found here: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.

File Class Methods

Method Name Description
MoveFile ( String, String ) Moves a file on the server.

String – From filename path

String – To filename path

CopyFile ( String, String ) Copies a file on the server.

String – From filename path

String – To filename path

DeleteFile ( String ) Deletes a file on the server.

String – Filename path

ReadFile ( String ) Reads a file in text mode and returns the contents as a string.

String – Filename path

WriteFile ( String, String ) Writes the supplied contents to a file in text mode. Entire file is overwritten.

String – Filename path

String – contents to write to the file

Compression Class Methods

Method Name Description
ExtractZipToDirectory ( String, String ) Extracts a zip archive to the specified path.

String – Source zip full path & filename

String – The path to extract to

CreateZipFromDirectory ( String, String ) Creates a new zip archive with the contents of the specified folder.

String – The path to the contents to be zipped

String – Full path & filename for the new zip archive

Xref Class Methods

Method Name Description
XRefInitWrite ( String ) Initializes a cross-reference list for writing. If list exists, new data will be appended to it.

String – cross-reference list name

XRefInitRead ( String ) Initializes an existing cross-reference for reading. This reads all ids into memory so they will be available within your job.

String – cross-reference list name

XRefWrite ( String, String, String ) Writes a new ID reference entry to an initialized list.

String – cross-reference list name

String – ID 1 ("Old ID": the ID you’ll look up by in future jobs)

String – ID 2 ("New ID": the ID you’ll want returned by future lookups)

EX: XRefWrite "XrefName", "OldID", "NewID"

XRefRead ( String, String ) Reads an ID entry from an initialized list. (Returns ID 2 from above)

String – cross-reference list name

String – ID 1 ("Old ID")

Xref-Flag Class Methods

Only compatible with database Xref format. These methods allow user to set a 'processed' flag against each entry within a Xref list. Useful in applications where you need to keep track of whether a particular records was processed or not. For example, if you need to detect whether records were removed from a source list where they previously existed. To query a list of unprocessed xref entries, it is recommended to set your Origin to use the SQLite Connector, connect to your engine.db, and read directly form the xref table.

Method Name Description
XrefSetFlag ( String, String, Boolean ) Sets the flag value for a given entry within a list.

String – cross-reference list name

String – ID 1 (the "Old ID" value to look up the entry by)

Boolean - value to set the flag to (True/False)

XrefGetFlag ( String, String ) Gets the current Boolean flag value (True/False) for a given entry within a list.

String – cross-reference list name

String – ID 1 (the "Old ID" value to look up the entry by)

XrefClearFlag( String ) Sets the flag to False for all entries within a given list.

String – cross-reference list name

MS Dynamics CRM Specific Methods

Method Name Description
GetMCRMPicklistValue( String, String, String ) Returns the integer value for a supplied picklist string.

String – Entity for the picklist (“account”)

String – Fieldname of the picklist (“accounttype”)

String – Value for the picklist (“Prospect”)

MCRMSetRelationship ( String, String, String, String, String ) Sets a relationship between two entites.

String – Relationship Name

String – Entity 1 Name

String – Entity 1 ID

String – Entity 2 Name

String – Entity 2 ID

SugarCRM (SOAP) Specific Methods

Method Name Description
SugarSetRelationship (String, String, String, String ) Sets a relationship between two modules. Returns True or False based on the success of creating a relationship.

String – Module 1 Name

String – Module 1 ID

String – Module 2 Name

String – Module 2 ID

SugarSetRelationshipv4 (String, String, String, String, Number(Optional) ) Sets a relationship between two entities, compatible also with custom modules. Returns True or False based on the success of creating a relationship. Also able to remove a relationship between two entities with the optional fifth parameter.

String – Module 1 Name

String – Module 1 ID

String – Relationship Name

String – Module 2 ID

String – [Delete] (Optional) – Default is 0 (false). Set to 1 (true) to Delete a relationship.

SugarGetRelationships ( String, String, String ) Gets comma-separated a list of ID’s related to an entity

String – Module 1 Name

String – Module 1 ID

String – Module 2 Name

SugarGetRelationshipsv4 ( String, String, String ) Gets comma-separated a list of ID’s related to an entity

String – Module 1 Name

String – Module 1 ID

String –Relationship Name

Email (IMAP) Specific Methods

Method Name Description
MoveIMAPMessage ( String ) Moves the current email message to the specified folder on the server.

String – Name of IMAP folder to move message to

Conversion Methods

Method Name Description
ConvertRTFToText ( String ), ConvertHTMLToText ( String ), ConvertBase64ToBytes ( String ) Converts the String supplied.

SQLite Class Methods

Method Name Description
SQLiteInsert Returns boolean, true if successful: b = SQLiteInsert("test_table", "field1,field2", "'@@ORG:DirectoryRelative@@','@@ORG:Name@@'")
SQLiteSelect Returns a multi-dimensional array: b = SQLiteSelect("test_table", "field1,field2", "field2='@@ORG:Name@@'")

EX:

if UBound(b)>-1 then    'there is data
                logmessage b(0,0)
                logmessage b(0,1)
else
                logmessage "no data"
end if
SQLiteSelectDT Same as above, but returns data as a .NET DataTable object.
SQLiteUpdate Returns boolean, true if successful: b = SqliteUpdate (table, update_values, where)

EX: SqliteUpdate ("blah", "textfield1='hey', numfield3=45", "name=@@ORG:Name@@")

SQLiteDelete Returns boolean, true if successful: b = SQLiteDelete("test_table", "field2='@@ORG:Name@@'")

JSON Methods

Method Name Description
ParseJSON( String ) Loads the passed in string into the JSON parser.

Note that as of February 2018, the ParseJSON values do not get reset between each row. You have to run ParseJSON("{}") in a before operation if you want to reset the JSON for GetJSON back to empty.

GetJSON( String, [String], [String], [String], [String], [String], [String], [String], [String] ) Returns a given section from the supplied JSON. The optional strings are for accessing nested values. For example, if you had this JSON, which was borrowed from https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html, (Thanks Adobe!):
{
     "id": "0001",
     "type": "donut",
     "name": "Cake",
     "ppu": 0.55,
     "batters":
           {
                 "batter":
                       [
                              { "id": "1001", "type": "Regular" },
                              { "id": "1002", "type": "Chocolate" },
                              { "id": "1003", "type": "Blueberry" },
                              { "id": "1004", "type": "Devil's Food" }
                       ]
           },
     "topping":
           [
                 { "id": "5001", "type": "None" },
                 { "id": "5002", "type": "Glazed" },
                 { "id": "5005", "type": "Sugar" },
                 { "id": "5007", "type": "Powdered Sugar" },
                 { "id": "5006", "type": "Chocolate with Sprinkles" },
                 { "id": "5003", "type": "Chocolate" },
                 { "id": "5004", "type": "Maple" }
           ]
}

And you wanted to get the type of the 2nd batter, you would do this with 0-based indexing:

val = GetJSON("batters","batter",1,"type")

val would equal “Chocolate”.

JSONArrayCount( ) Returns the number of elements within the JSON array loaded using ParseJson.