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

SQLite Connection Edit Screen

• Related Articles

• Salesforce Connector

  Additional Documentation This Connector is based on the Salesforce ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/RFG/ado/ Salesforce Origin Case as Origin Table You ...

• Microsoft SQL Server Connector

  Overview The "SQL Server" Connector pulls data from Microsoft SQL Server. Using rowversion fields Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with ...

• Stripe Connector

  Additional Documentation This Connector is based on the Stripe ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/BOG/ado/

• Snowflake Connector

  Additional Documentation This Connector is based on the Snowflake ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/OWG/ado/

• Redshift Connector

  Additional Documentation This Connector is based on the Redshift ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/FRG/ado/