SQLite Connector
DateTime Fields
Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html. Creatio Connect's FormatDate function can help with this. See this vbscript for an example:
Sub VBScriptProcedure
'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss")
'LogMessage FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")
If PreviewMode <> True Then
dim b
b = SQLiteUpdate("contacts_to_update_from_leads","processed='1', date_modified='"&FormatDate(Now, "yyyy-MM-dd HH:mm:ss")&"'","contactid='@@ORG:contactid@@' and leadid='@@ORG:leadid@@' and (processed<>'1' or processed is null)")
dim d
d = SQLiteDelete("contacts_to_update_from_leads","processed='1' and date_modified<'"&FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")&"'")
End If
End Sub
Levenshtein
The SQLite connector has the Levenshtein function! A couple of examples:
SELECT LEVENSHTEIN( 'Microsoft', 'Technology Advisors, Inc.' )
Result: 21
SELECT LEVENSHTEIN( 'Technology Advisors Inc', 'Technology Advisors, Inc.' )
Result: 2
Comparing 2 strings is obviously less than useful, so you would want to do something like:
SELECT * FROM TABLE WHERE LEVENSHTEIN('Technology Advisors, Inc.', TABLE.NAME) < 5
Issues
If your source SQLite table has a DateTime field, you may get the error: “String not recognized as a valid datetime”. If you get this error, add "datetimeformat=CurrentCulture", without double quotes, to the "Additional Connection String Parameters:" field on the Origin tab.
| Parameter | Description |
| Data Source |