HFSQL Synchronization with Office 365, SharePoint, Dynamics and 150+

HFSQL data can be integrated and synchronized codeless with various external systems, on premises or in the cloud, using the Layer2 Cloud Connector. You will find a selection of supported systems and applications like Microsoft Office 365, SharePoint, CRM Online and 100+ more here.​​​​​

HFSQL is the database engine included with the WINDEV developement environment. It works cross-platform, with networks of any size and type. HFSQL data can be integrated with 150+ other systems and apps codeless using the Layer Cloud Connector. 

Benefits of HFSQL Synchronization and Integration

HFSQL data integration via Layer2 Cloud Connector has the following features and benefits:

• Connect and sync your HFSQL data with 150+ other data sources, e.g. native SharePoint lists in Office 365 or on-premises, keeping them always up-to-date.
• High speed of implementation: No programming or tools required. Predefined sample connection templates included. Setup in a few minutes guided by wizards. Remote setup support (30 minutes are free).
• Low risk in project: No installation or customizing for the HFSQL data source and the data destination required. Just install the connector at a Windows server or client in your network, in the cloud, or even at your laptop.
• Highest Security: Connection via secure web services (SSL). Disconnected systems: A connection is only required for data exchange – not for data usage. Users do not have direct access to external corporate data sources. You don't have to open your network for the Internet. The application runs where your data resides today - on a server or client inside your corporate network.
• Highest performance: Can manage very large databases, SharePoint lists and libraries. No SharePoint 5,000 items limit. Can scale-out to multiple servers or to the Microsoft Azure Cloud, if required. No bulk-import as with other tools, data changes are processed only (full CRUD).
• Fully compatible with 3rd-party data providers: Data providers of the application vendors (e.g. Oracle, mySQL, IBM), 3rd-party data providers, or included Layer2 data providers can be used to access 150+ data sources.

• Virtually any data sources supported: ODBC, OLEDB, OData, SOAP, Microsoft .NET based providers, Files (Excel, XML, CSV, Text), RSS / XML feeds, SQL databases like MS SQL, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix; Notes, SharePoint, Office 365 (lists and libraries), local file system, cloud-based file stores, Exchange, Active Directory, Dynamics NAV/CRM, Navision, SAP, and many more. More complex data sources, REST-based web services, custom COM, or .NET objects can be connected using 3rd-party add-ons. Cloud-to-cloud connections, e.g. to salesforce.com, Twitter, Facebook, Google, Amazon, BOX.NET and others are also supported.
• Layer2 CSOM Data Provider included: You can connect and sync your HFSQL data with SharePoint lists and libraries on-premise or in the Office 365 / OneDrive for Business cloud.

• Layer2 File System Data Provider  included: You can sync local files or file server shares with SharePoint Online or on-premises libraries, e.g. for backup, collaboration or mobile access. Optionally, you can sync metadata only - not the file itself. You can link to, for example, your own FTP/HTTP, to Azure, BOX, or SkyDrive / OneDrive to save space or use low-cost Office 365 plans. You can update file metadata based on SQL Server queries.

• Layer2 OData Data Provider included: You can connect your HFSQL data to all OData sources, e.g. Microsoft CRM/ERP or SAP via NetWeaver.

• Very flexible sync options: Uni- and bi-directional sync is supported to and from HFSQL. Execute a specific connection scheduled in background (Windows Service) or on demand (e.g. by command line). Sync all data or pre-selected subsets only. Select source and target folders, make use of additional no-delete, no-insert, no-update options. Field mapping and data type conversion included.
• Process integration: Raise change notifications and start workflows in the cloud, triggered on external HFSQL data changes. Find your data changes in Delve via Office Graph. All features of native SharePoint lists and libraries are supported, e.g. views, sorting, filtering, grouping, search, mobile and offline access, social features like tagging and enterprise metadata, RSS feeds, change notifications per email, or workflows in the cloud on external data change. You can also add your own business logic.
• Mobile and offline access: You can sync database queries with Office 365 / SharePoint Online for data access from everywhere, with any device, at any time - also offline with write-back of changes.
• Additional unmapped "SharePoint-only" columns can be created: Ranking, rating, managed metadata, comments or file attachments.
• Data aggregation: Multiple connections can combine several different data queries into one list.
• Flexible logging and alerts: You can log to files, databases, and Windows Event Log via NLOG.
• Free Shareware Edition: You can test the free Shareware Edition as long as you want or you can use in production with limited features. You can upgrade the Shareware Edition to a licensed version at any time without new installation.
• Flexible, cost-effective licensing: Personal or Professional Edition, depending on requirements. Licensing per installation (no user CALs, not bound to any SharePoint server or site, just a one-time fee - no data volume-related costs). Easy to setup and maintain.

HFSQL Specific Integration and Configuration Settings

​In the Layer2 Cloud Connector, the data source entity must be configured as follows to connect to HFSQL:

Fig. HFSQL example connection in the Layer2 Cloud Connector.

Please note the following about settings, features and possible issues.

• You need to have the ODBC driver for HFSQL Classic or Client/Server driver installed on the computer that runs the Layer2 Cloud Connector. You can find the component on the PC Soft WINDEV Download Center.
• There is also an Ole DB driver provided by WINDEV that also works with the Cloud Connector - the download location is the same as above.
• Install the appropriate bit version of the HFSQL ODBC driver. It must match the same bit architecture as the Cloud Connector version installed (32- or 64-bit) AND be the same .NET version as the Cloud Connector. 
• Configure a System DSN (not User or File DSN) according to the vendor's instructions. Both "HFSQL Classic" and "HFSQL Client/Server" configurations are supported.
• Please select the "Odbc Data Provider" from the list of installed providers. The connection string will be "DSN=mySystemDSN;"
• You can also use a DNS-less connection string - this example if for the Client/Server configuration:
  Driver=HFSQL;Server Name=myServerName; server port=4900; database=myDatabase; UID=username; pwd=xxxxxx;
• You can make use of all SQL queries your data provider supports. See the HFSQL Documentation for more information.
• Please make sure to define an appropriate primary key.
• The Layer2 Cloud Connector only supports reading from HFSQL files. Write is not supported at this time.

Do you have any issues to connect? Please contact [email protected] for next steps.

HFSQL Integration - Known Issues & Workarounds

For HFSQL Integration via Layer2 Cloud Connector we have found the following issues and workarounds:

• Both the ODBC and Ole DB providers only appear to allow for reading HFSQL files - writing results in errors that the columns are "read-only". At this time, HFSQL is supported as read-only.
• Cloud Connector does not support items with the "automatic identifier on 8 bytes" enabled. It leads to the error "Arithmetic operation resulted in an overflow." The item must be recreated to use the 4 byte version or not to use the autogenerated IDs at all.

Step-by-Step Intros For Specific HFSQL Integration Scenarios

• Connect and sync HFSQL data queries to SharePoint lists, on-prem or in Office 365.
• Connect and sync HFSQL data queries with Dynamics CRM or ERP via OData.
• Sync with 3rd party tools based on SQL databases like Oracle, mySQL, or Microsoft SQL Server.
• Push HFSQL data to Microsoft Exchange on-premises or Exchange Online for mobile and offline access on any device (BYOD).

Can't find the scenario you are looking for? Please contact [email protected] for help.

 Ready to go next steps?