Er zijn twee manieren om de gegevensstroom naar je SQL-tabellen te beheren met behulp van een van onze SQL-formulierconnectors (SQL Server, MySQL of PostgreSQL): “Auto Maintain” en “Custom SQL“.

In dit artikel kijken we naar “Custom SQL” en hoe je dit kunt gebruiken in je SQL-formulierconnectors voor nog meer controle over je SQL-gegevens.

Custom SQL Overzicht

Wanneer je je gegevens beheert via een SQL-formulierconnector, is de eerste en meest populaire optie om de Auto Maintain-optie in te schakelen op de formulierconnector. Als deze optie is ingesteld op je formulierconnector, zal ons platform (zoals de naam al aangeeft) de SQL-tabel automatisch onderhouden waarnaar het gegevens schrijft. Dit kan erg handig zijn, want als je bijvoorbeeld verschillende velden uit je formulier verwijdert en er nieuwe toevoegt, zal “Auto Maintain” ervoor zorgen dat de SQL-tabel die met je formulier is verbonden ook wordt bijgewerkt, zodat records daar kunnen worden geschreven zonder dat jij iets hoeft te doen.

Voor de meeste use cases is dit voldoende; echter hebben sommige gebruikers de optie nodig om meer geavanceerde gegevensverwerking uit te voeren door hun eigen aangepaste SQL-scripts te schrijven, wat totale controle geeft over welke gegevens naar welke tabellen in hun SQL-configuratie gaan. Voor deze gebruikers bevelen we de “Custom SQL”-optie aan op hun SQL-formulierconnector.

Custom SQL is beschikbaar op al onze SQL-formulierconnectors. Om het in te schakelen, schakel je de Auto Maintain Table-optie uit op je respectieve SQL-formulierconnector, en het tekstvak Custom SQL Template verschijnt.

Gebruiksscenario’s

Om je een idee te geven van typische use cases waarbij Custom SQL op onze SQL-formulierconnectors de beste optie zou kunnen zijn, bekijk deze scenario’s:

• Wanneer formuliergegevens worden ingediend, wil je gegevens naar verschillende tabellen schrijven voor gegevensanalyse en rapportagedoeleinden. Custom SQL stelt je in staat om alleen de gegevens die je nodig hebt naar de tabellen te schrijven waar je ze wilt schrijven, zodat je productie- en rapportagesystemen de gegevens hebben die ze nodig hebben ter ondersteuning van je bedrijfsvoering.
• Je moet een Platform Sync (Hosted GET)-tabel onderhouden met _lastupdated en/of _deleted-waarden. Custom SQL geeft je de granulaire controle die nodig is om je gegevens met de grootste precisie te onderhouden.

Custom SQL stelt je in staat om meerdere query’s te schrijven, die worden uitgevoerd wanneer het formulier wordt ingediend. Dit opent een aantal mogelijke use cases die veel te veel zijn om in dit artikel op te noemen.

Custom SQL Details

Onze SQL-formulierconnectors kunnen meerdere query’s tegelijkertijd uitvoeren in een Custom SQL-statement.

Om Custom SQL-code aan je SQL-formulierconnector toe te voegen, voer je de Custom SQL in die je wilt dat de formulierconnector uitvoert in het tekstvak “Custom SQL Template“.

Custom SQL Syntaxis

Hoewel de syntaxis die wordt gebruikt om het tekstvak “Custom SQL Template” in te vullen reguliere SQL is, kun je de data templating-syntaxis van ons platform gebruiken om formulierveldplaceholders in te voegen en herhalende secties in je Custom SQL-syntaxis te definiëren, zoals weergegeven in het onderstaande voorbeeld:

DELETE FROM [MyTableName] WHERE Id = CAST({{%ENTRYGUIDFULL}} as uniqueidentifier);
INSERT INTO [MyTableName] (
  [id],
  [itemno],
  [formversion],
  [myField1],
  [myfield2],
  [myfieldn]
)
VALUES {{!REPEATSTART}} (
  CAST({{%ENTRYGUIDFULL}} as uniqueidentifier),
  CAST({{%ITEMNO as int}}),
  CAST({{%FORMVERSION}} as int),
  CAST({{myField1}} as datetime),
  CAST({{myfield2}} as nvarchar),
  CAST({{myfield2}} as int)
), {{!REPEATEND}};

Een speciaal ingebouwd gegevenscommando is beschikbaar voor SQL-connectors, namelijk {{%UTCNOW}}, dat de datum en tijd (in UTC) bevat van wanneer de query wordt uitgevoerd. Dit kan nuttig zijn voor het bijwerken van de _lastupdated-kolom als je Platform Sync gebruikt.

Dit ingebouwde gegevenscommando, samen met ENTRYGUID, ENTRYGUIDFULL en ITEMNO, is altijd gegarandeerd een waarde te hebben, ongeacht of “Fill Repeat Rows” is uitgeschakeld (d.w.z. NULL gebruiken in Repeat Rows).

Voorbeeldcode Custom SQL

Om je een idee te geven van een typisch use case voor Custom SQL, hebben we wat voorbeeldcode toegevoegd die je als richtlijn kunt gebruiken voor het schrijven van je eigen SQL-scripts en het beheren van de gegevensstroom van je platform naar je SQL-databases en tabellen.

Voorwaardelijke SQL-instructies

Er kan een moment komen waarop je de voorwaardelijke logica van SQL wilt gebruiken om je de flexibiliteit te geven om verschillende SQL-instructies alleen uit te voeren als aan een bepaalde voorwaarde is voldaan. Daarvoor kun je gebruik maken van de native IF ELSE-logica van SQL om de uitvoering van je Custom SQL-scripts te beheren, zoals weergegeven in het onderstaande voorbeeld:

IF EXISTS (SELECT * FROM tb_project WHERE projectID = 'o}xoxprojectID}}')
BEGIN
   UPDATE tb_projects
SET project = '{{project}}',  description= '{{description}}', client = '{{client}}', projecttype = '{{projecttype}}', user = '{{user}}', foreman = '{{foreman}}'
WHERE projectID = '{{projectID}}'
END
ELSE
BEGIN
    INSERT INTO tb_projects  (projectID, project, description , client, projecttype, user, foreman)
    VALUES('{{projectID}}', '{{project}}', '{{description}}', '{{client}}', '{{projecttype}}', '{{creator}}', '{{foreman}}')
END