SQLite Connector

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. Starfish'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

    • Hubspot Connector

      Hubspot Connector Note: As of September 8, 2020 the Hubspot API does not allow external applications to write into their Quoting module.  See below. Overview The StarfishETL Hubspot Connector enables you to easily connect to Hubspot for integrations ...
    • Salesforce Connector

      Salesforce Destination Be aware that if you are importing into a Salesforce Sandbox, the size is limited. If you import somewhere around 150,000 records, you will run out of space. You should only import what is necessary for testing. Standard Input ...
    • 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 ...
    • Act! Connector

      ACT! Premium Cloud: StarfishETL supports two-way integration. ACT! On-Prem: Starfish does not have a native Act! connector for versions less than Act v18 so we read directly from the database. Configuration Options Connect Act Premium (v18+) using ...
    • Sharepoint Connector

      If connecting to Sharepoint using Single Sign-on (SSO) You might see this error if not connected appropriately - "[500] Could not execute the specified command: unrecognized response when getting SharePoint online security cookies, please check your ...