Consolit SQL Connector Handleiding

Consolit SQL Connector Handleiding

Bijgewerkt tot SQL Connector 1.8.1

Consolit Business Solutions – Scheepmakerij 330 – 3331 MC Zwijndrecht T: 078 – 620 25 30 | F: 078 – 631 43 84 | E: [email protected] | W: www.consolit.nl | KvK.nr: 24432595 | ING Bank: 66.05.12.378

SQL Connector

2

Alle rechten met betrekking tot de documentatie en de daarin beschreven software berusten bij Consolit Business Solutions. Dit geld ook voor eventuele aanvullingen of wijzigingen. Het gebruik van de in deze documentatie beschreven software is gebonden aan regels die worden beschreven in de licentiebepalingen. Reproductie van het materiaal, op welke wijze dan ook, is zonder voorafgaande schriftelijke toestemming uitdrukkelijk verboden. De informatie in deze documentatie kan zonder voorafgaande mededeling worden gewijzigd en impliceert geen enkele verplichting voor Consolit Business Solutions. Consolit Business Solutions, Consolit Business Modules, Consolit modules en Consolit klantspecifieke aanpassingen en het logo van Consolit Business Solutions zijn handelsmerken van Consolit Business Solutions. AccountView BV, AccountView, Audit Views, Business Views, BusinessModeller en het logo van AccountView BV zijn handelsmerken van AccountView BV. Microsoft, Visual FoxPro, Windows, Word, Excel en Access zijn geregistreerde handelsmerken van Microsoft Corporation. Alle andere genoemde handelsmerken zijn eigendom van hun respectievelijke eigenaren. Consolit Business Modules (CBM), Consolit Modules (C4) en Consolit klantspecifieke aanpassingen (C1) voor AccountView versie 9.0x. Copyright © Consolit Business Solutions, 2011


SQL Connector

3

Inhoudsopgave 1 2 3

4

5

6

Functionaliteit ...........................................................................................................................................4 Installatie ...................................................................................................................................................5 2.1 Controles vooraf ..............................................................................................................................5 2.2 Installatiestappen ..........................................................................................................................5 Instellingen ................................................................................................................................................9 3.1 Tabblad [Settings] .........................................................................................................................9 3.2 Tabblad [Tables] ...........................................................................................................................10 3.3 Tabblad [XML] ................................................................................................................................11 3.4 Tabblad [License] .........................................................................................................................12 3.5 Tabblad [FTP] .................................................................................................................................13 3.6 XML Templates ...............................................................................................................................14 Werkwijze .................................................................................................................................................15 4.1 Dagelijks gebruik ..........................................................................................................................15 4.1.1 Real-time synchronisatie .............................................................................. 15 4.1.2 AccountView organiseren .............................................................................. 15 4.1.3 Verschil in gegevens tussen AccountView en SQL database ............................... 15 4.2 Synchronisatie ...............................................................................................................................15 4.2.1 SQL synchronisatie ...................................................................................... 15 4.2.2 FTP synchronisatie ....................................................................................... 15 Frequently asked questions (FAQ) .................................................................................................16 5.1 Back-up maken ..............................................................................................................................16 5.2 Nieuw boekjaar AccountView ..................................................................................................16 5.3 Verbinding is weggevallen ........................................................................................................16 5.4 Foutmelding: Beperkingen konden niet worden ingeschakeld ..................................16 5.5 Foutmelding: De VFPOLEDB.1-provider is niet geregistreerd ....................................16 5.6 Foutmelding: Cannot open file ................................................................................................16 5.7 Foutmelding: Service is gestart maar meteen gestopt .................................................16 Bijlagen ......................................................................................................................................................17 6.1 XML Transport – Header ............................................................................................................17 6.2 XML Transport – Header & Detail ...........................................................................................19 6.3 XML Message ..................................................................................................................................20


SQL Connector 1

4

Functionaliteit

Met de SQL Connector wordt een koppeling gelegd tussen een AccountView administratie en een SQL database (MS-SQL of MySQL). Deze SQL database behoort over het algemeen tot een webshop, maar dit is niet verplicht. De SQL Connector synchroniseert beide kanten op: Wijzigingen in AccountView worden real-time doorgestuurd naar de SQL database. Mutaties van de webshop worden verwerkt in XML-bestanden. Deze bestanden kunnen met de AccountView.NET BackOffice Server (AVBOS) real-time in AccountView ingelezen worden. Daarnaast kunnen afbeeldingen en (kopie-)documenten gesynchroniseerd worden naar de webshop.


SQL Connector 2

Installatie

2.1 Controles vooraf De volgende punten moeten gecontroleerd worden voordat aan de installatie begonnen wordt. 1. De AccountView.NET BackOffice Server (AVBOS) moet in de licentie van AccountView aanwezig zijn. 2. Voor de installatie is volledige toegang tot Windows (administrator-niveau) nodig. 3. Microsoft.NET Framework 2.0 moet geïnstalleerd zijn op de server. 4. De AccountView runtime moet geïnstalleerd zijn op de server. De AccountView runtime kan geïnstalleerd worden door een werkstationinstallatie van AccountView uit te voeren. De AccountView VFP DLL-bestanden moeten minimaal de volgende versienummers hebben. 1. vfp9r.dll / versie 9.0.0.3504 / 04-11-2005 2. vfp9renu.dll / versie 9.0.0.3504 / 04-11-2005 3. vfp9t.dll / versie 9.0.0.3504 / 04-11-2005 De AccountView VFP DLL-bestanden worden normaal gesproken op de volgende locatie gevonden: C:\Program Files\Common Files\Microsoft Shared\VFP De AccountView VFP DLL-bestanden kunnen eventueel handmatig geregistreerd worden met het Windows-programma regsvr32.exe.

2.2 Installatiestappen Voer de volgende stappen uit om de SQL Connector te installeren. Stap 1: Ga naar [C:\Program Files\]. Stap 2: Maak een submap aan met de naam [Consolit SQL Connector]. Stap 3: Kopieer alle bestanden van de SQL Connector naar deze submap. Stap 4: Installeer de Visual Foxpro OLE DB provider 9.0 (VFPOLEDB 9.0) via vfpoledb.exe. Kies tijdens de installatie voor minimal install. Stap 5: Start de SQL Connector via SQLConnector.exe.


5

SQL Connector

Stap 6: Klik op [Install].

Stap 7: Klik op [Yes] (Ja). De Windows NT service SQLConService zal worden geïnstalleerd. Stap 8: Controleer of de service correct geïnstalleerd is via [My Computer  Manage  Services].


6

SQL Connector

Stap 9: De SQL Connector is nu gereed voor inrichting.

Stap 10: Voer de gewenste instellingen in (zie hoofdstuk 4 Inrichting SQL Connector). Stap 11: Ga naar tabblad [Tables] en klik op de knop [Install sync].


7

SQL Connector

Stap 12: Ga naar tabblad [General] en klik op de knop [Run one full sync now].

Stap 13: Zodra het vlak groen is, is de SQL Connector actief.


8

SQL Connector 3

9

Instellingen

De instellingen van de SQL Connector zijn verdeeld over meerdere tabbladen.

3.1

Tabblad [Settings]

Op dit tabblad bevinden zich de instellingen voor de bron- en doellocatie van de koppeling.

Veldnaam DataDir DataFile Server Type Database Username Password Host PASSV User Password Start running at Repeat every Timer interval Minimize memory usage Knop Test connection

Toelichting Administratiemap van AccountView Naam van de databasecontainer Locatie van de SQL server Type van de SQL server Naam van de SQL database Gebruikersnaam van de SQL database Wachtwoord van de SQL database Locatie van de FTP server Geeft aan of de FTP verbinding actief of passief is Gebruikersnaam van de FTP server Wachtwoord van de FTP server Starttijd van de FTP synchronisatie Interval van de FTP synchronisatie Interval van de SQL synchronisatie Limiteert het gebruik van het interne servergeheugen Toelichting Controleert de bijbehorende verbinding

Het veld [DataDir] moet worden afgesloten met een ‘\’. Het veld [DataFile] moet ingevoerd worden zonder de extensie [.dbc].


SQL Connector 3.2

10

Tabblad [Tables]

Op dit tabblad bevinden zich de instellingen voor de synchronisatie vanuit AccountView. Hier worden de tabellen aangegeven, die gesynchroniseerd moeten worden.

Veldnaam Tablename Unique key Custom select Knop Remove sync Install sync

Toelichting Tabelnaam (AccountView) Veldnaam van de primaire sleutel (AccountView) Optioneel filter op tabelsynchronisatie Toelichting Verwijdert alle triggers en deïnstalleert de SQLConService Installeert de triggers op de tabellen in AccountView

Het veld [Custom select] moet ingevoerd worden in overleg met de consultant van Consolit. De ‘triggers’ zorgen ervoor dat wijzigingen in AccountView worden doorgestuurd naar de SQL database. Voor het installeren van de ‘triggers’ mogen er geen gebruikers in de AccountView administratie ingelogd zijn. Door het organiseren van de AccountView administratie worden de ‘triggers’ verwijderd. Na elke organisatie moet er dus een install sync uitgevoerd worden. Als er tussen het verwijderen en weer installeren van de ‘triggers’ in AccountView wijzigingen aangebracht worden, moet er een full sync uitgevoerd worden om het verschil met de SQL database weer recht te trekken. De full sync zal precies de volgorde aanhouden zoals op dit tabblad aangegeven.


SQL Connector 3.3

11

Tabblad [XML]

Op dit tabblad bevinden zich de instellingen met betrekking tot de XML berichten.

Veldnaam XML Output folder XML Filename Prefix Enable XmlTransport Knop Regenerate last order

Toelichting Geef hier de \avs-map van de AVBOS op Willekeurige waarde om de gegenereerde orders te herkennen Verplicht voor synchronisatie met XML berichten Toelichting Genereert de laatste order opnieuw

De instellingen onder Source settings moeten ingevoerd worden in overleg met de consultant van Consolit.


SQL Connector 3.4

12

Tabblad [License]

Op dit tabblad bevinden zich de licentiegegevens.

Veldnaam Registration name Registration key Knop Check this code

Toelichting Gebruikersnaam op de licentievoucher Licentiesleutel Toelichting Controleert de licentiegegevens


SQL Connector 3.5

13

Tabblad [FTP]

Op dit tabblad bevinden zich de instellingen voor de bron- en doellocatie van de FTP synchronisatie.

Veldnaam SourceDir DestinationDir Knop Run one FTP sync now

Toelichting Lokale bronmap van de te synchroniseren bestanden Doelmap op de FTP server Toelichting Voert een eenmalige, handmatige FTP synchronisatie uit

De lokatie in het veld SourceDir wordt gescheiden door ‘\’ en afgesloten met ‘\’. De lokatie in het veld DestinationDir wordt gescheiden door ‘/’ en afgesloten met ‘/’. Deze locatie begint ook verplicht met ‘/’.


SQL Connector 3.6

14

XML Templates

Als mutaties op de webshop gesynchroniseerd gaan worden naar AccountView, moeten er XML templates (sjablonen) aangemaakt worden. In de installatiemap van de SQL Connector bevindt zich de map [\Templates\]. Voor ieder te synchroniseren object bestaan er drie XML templates: <prefix>_header.txt <prefix>_item.txt <prefix>_footer.txt Het eerste deel van het XML bericht wordt in het header-bestand opgenomen. Het item-bestand wordt gebruikt om de eventuele regels toe te voegen. Het laatste deel van het XML bericht wordt in het footer-bestand opgenomen. De templates zorgen voor een vertaling van de SQL veldnamen naar de gewenste AccountView veldnamen. De SQL veldnamen dienen in hoofdletters en tussen accolades (‘{…}’) te worden opgenomen in de XML templates. <SIGNONMSGSRQ> <SONRQ> {ORDERDATE} MWT2007 <APPID>ACCVW <APPVER>0701A <EBUSMSGSRQ> <EBUSTRNRQ> A1ZS0Y8M8N SO1 <WEB_NR>{ID} {SUB_NR} {SUB_NR} xml_header.txt <SO_LINE> {ART_CODE} {QTY} xml_item.txt xml_footer.txt De XML templates moeten aangemaakt worden in overleg met de consultant van Consolit. De SQL veldnamen zijn hoofdlettergevoelig.


SQL Connector 4

15

Werkwijze

4.1

Dagelijks gebruik

4.1.1 Real-time synchronisatie In het dagelijks gebruik is de SQLConService actief en worden wijzigingen en mutaties real-time in beide richtingen gesynchroniseerd tussen de webshop en AccountView. Deze situatie is visueel te controleren op tabblad [General] in het gekleurde vlak (groen is actief) of bij het Windowsonderdeel Services (SQLConService heeft status ‘started’). Eventueel kan met de knoppen [Start] en [Stop sync] de SQLConService gestart en gestopt worden. 4.1.2 AccountView organiseren Na het organiseren van de administratie in AccountView zijn de ‘triggers’ verwijderd van de tabellen. Wijzigingen worden dan niet meer doorgegeven aan de webshop. De triggers moeten dus elke keer direct na het organiseren van de administratie weer geïnstalleerd worden. Dit wordt gedaan met de knop [Install sync] op tabblad [Tables]. De functie install sync is ook uit te voeren door de waarde [/installsync] als opstartparameter mee te geven aan het programma SQLConnector.exe. Hierdoor kan het installeren van de triggers eventueel als geplande taak in Windows uitgevoerd worden. Voorbeeld: "\SQLConnector.exe" /installsync 4.1.3 Verschil in gegevens tussen AccountView en SQL database In sommige gevallen kan het voorkomen, dat de gegevens in de SQL database verschillen van de gegevens in AccountView, terwijl er geen wijzigingen meer klaarstaan voor de synchronisatie. Dit is bijvoorbeeld het geval als er wijzigingen doorgevoerd zijn in AccountView tussen het organiseren van de administratie en het installeren van de ‘triggers’. Deze situatie is op te lossen door een full sync te doen. Dit wordt gedaan met de knop [Run one full sync now] op tabblad [General]. Hiervoor moet de synchronisatie eerst gestopt worden. Bij een full sync zal de SQL Connector de precieze inhoud van de te synchroniseren tabellen kopiëren naar de SQL database. De functie full sync is ook uit te voeren door de waarde [/fullsync] als opstartparameter mee te geven aan het programma SQLConnector.exe. Hierdoor kan de full sync eventueel als geplande taak in Windows uitgevoerd worden. Voorbeeld: "\SQLConnector.exe" /fullsync

4.2

Synchronisatie

4.2.1 SQL synchronisatie De install sync installeert drie ‘triggers’ (stored procedures) per tabel; voor Insert, Update en Delete. Met de full sync worden ontbrekende tabellen of velden in de SQL database automatisch aangemaakt. Zodra de SQLConService is gestart, worden de volgende subprocessen uitgevoerd. 1. Weborders die nog niet zijn verstuurd, worden in de XML Output folder geplaatst op basis van de templates. Als de SQL tabel WebOrders niet bestaat, dan wordt dit onderdeel overgeslagen. 2. Als XmlTransport geselecteerd is, dan worden –indien nog niet aanwezig- de SQL tabellen [tblXmlTransport] en [tblXmlMessage] aangemaakt. 3. Als XmlTransport geselecteerd is, dan worden de transportverzoeken uit tblXmlTransport behandeld (zie par. x.x). 4. FTP synchronisatie wordt gestart. 5. SmartCopy wordt gestart. Hiertoe wordt de AccountView tabel C4_CHG.dbf uitgelezen en verwerkt. In deze tabel worden de wijzigingen in AccountView bijgehouden. 6. Het proces wacht vervolgens (timer interval) en vangt opnieuw aan bij punt 1. 4.2.2 FTP synchronisatie Bij de FTP synchronisatie worden de aangegeven mappen middels het FTP protocol gekopieerd naar de server van de webshop, waarbij gelijke bestandsnamen overschreven worden. Bij het begin van de ingestelde interval voor de SQL synchronisatie wordt gekeken of in het huidige uur een FTP cyclus gepland staat. Als dat zo is dan wordt de FTP synchronisatie in een separaat proces gestart.


SQL Connector 5

16

Frequently asked questions (FAQ)

5.1 Back-up maken Q: Ik wil een back-up maken van de AccountView administratie. Moet de SQL Connector dan uit? A: Nee, de SQLConService kan gewoon actief blijven tijdens het maken van een back-up in AccountView.

5.2 Nieuw boekjaar AccountView Q: Ik heb een nieuw boekjaar aangemaakt. Moet ik nu ook iets veranderen in de SQL Connector? A: Jazeker. Allereerst moet op tabblad [Settings] de juiste administratie gekoppeld worden onder [AccountView database]. Op tabblad [Tables] moet het veld [XML Filename Prefix] aangepast worden, als deze administratie-specifiek is ingesteld. Op tabblad [FTP] moet eventueel de map van de kopiedocumenten omgezet worden naar de nieuwe administratie. Ten slotte moet in alle templates van de SQL Connector het veld [ADMIN] aangepast worden naar de nieuwe administratiecode.

5.3 Verbinding is weggevallen Q: Wat gebeurt er als een verbinding wegvalt; door handmatig stoppen, netwerkproblemen, etcetera? Raak ik dan gegevens kwijt? A: Nee, er zullen geen gegevens kwijt raken. Bij elke verbinding (tussen AccountView en de SQL Connector en tussen de SQL Connector en de SQL database) worden de gegevens automatisch gebufferd. Wijzigingen en mutaties blijven dus gewoon wachten totdat de verbinding weer hersteld is.

5.4 Foutmelding: Beperkingen konden niet worden ingeschakeld Q: Bij het opstarten van het programma SQLConnector.exe krijg ik de volgende foutmelding: System.Data.ConstraintException: Beperkingen konden niet worden ingeschakeld. Een of meer rijen bevatten waarden die in strijd zijn met de beperkingen met betrekking tot niet-null waarden, unieke waarden of afwijkende sleutels. A: Het bestand SyncList.xml is beschadigd geraakt of incompleet ingevuld. In dit bestand worden de instellingen van de SQL Connector opgeslagen.

5.5 Foutmelding: De VFPOLEDB.1-provider is niet geregistreerd Q: De synchronisatie werkt niet en in het logbestand zie ik de volgende foutmelding staan: Fout bij verbinden met accview: De VFPOLEDB.1-provider is niet geregistreerd op de lokale computer. A: Installeer de VFP OLE DB Provider 9.0. Als de melding dan nog altijd verschijnt, registreer het bestand VFPOLEDB.DLL dan handmatig met het Windows programma regsvr32.exe. Biedt dat nog steeds geen oplossing, plaats dan het bestand VPFOLEDB.DLL in de installatiemap van de SQL Connector.

5.6 Foutmelding: Cannot open file Q: Ik ben als gebruiker ingelogd in AccountView en nu werkt de synchronisatie niet meer. In het logbestand zie ik de volgende foutmelding staan: Fout bij verbinden met accview: Cannot open file […].dbc. A: Controleer of het veld [Netwerkgebruik] in de stamgegevens van de administratie geselecteerd is. Als dat veld inderdaad aan staat, controleer dan of AccountView met de opstartparameter /single is opgestart.

5.7 Foutmelding: Service is gestart maar meteen gestopt Q: Als ik op de knop [Start] klik, wordt het vlak niet groen. Als ik in het Windows-onderdeel Services de SQLConService start, krijg ik de volgende melding: De service SQLConService is gestart en meteen weer gestopt. A: Het systeem- of toepassingenlogboek van Windows is vol. Neem contact op met uw systeembeheerder.


SQL Connector 6

17

Bijlagen

6.1 XML Transport – Header Met XmlTransport worden gegevens vanuit de SQL database naar de XML Output folder verplaatst. In de SQL tabel [tblXmlTransport] dient een record te worden aangemaakt volgens de volgende definitie. Veldnaam TransportID MasterTableName MasterPrimaryKeyName MasterPrimaryKeyValue DetailTableName DetailForeignkeyName XmlMergePrefix

Automatisch Verplicht Verplicht Verplicht Optioneel Optioneel Verplicht

Voorbeeld 8080 People REC_ID 1366785522 Null Null EMPLOYEEADD

In dit voorbeeld bestaat er een SQL tabel [People] van waaruit het record met REC_ID [1366785522] getransporteerd moet worden naar de XML Output folder. De XML templates die hierbij worden gebruikt zijn dus employeeadd_header.txt, employeeadd_item.txt en employeeadd_footer.txt. Een voorbeeld van deze templates is te vinden op de volgende pagina. Dit voorbeeld maakt gebruik van de volgende gegevens. SQL tabel [People]: Veldnaam REC_ID Sirname Firstnames DateOfBirth PlaceOfBirth City ..

Voorbeeld 1366785522 AERTS PETER 25-10-1970 EINDHOVEN AMSTERDAM ..

AccountView tabel [EMPLOYEE]: Veldnaam ID ACHTERNAAM VOORNAMEN GEBOORTEDATUM GEBOORTEPLAATS WOONPLAATS ..


SQL Connector

18

<SIGNONMSGSRQ> <SONRQ> SQL MWT2007 <APPID>ACCVW <APPVER>0701A <EBUSMSGSRQ> <EBUSTRNRQ> A1ZS0Y8M8N EM1 {REC_ID} {SIRNAME} {FIRSTNAMES} {PLACEOFBIRTH} { DATEOFBIRTH} <WOONPLAATS>{CITY}<WOONPLAATS> employeeadd_header.txt (leeg) employeeadd_item.txt employeeadd_footer.txt


SQL Connector

19

6.2 XML Transport – Header & Detail In onderstaand voorbeeld is het transport van een header-detail object (inkooporder) uitgewerkt. Tabel XmlTransport TransportID MasterTableName MasterPrimaryKeyName MasterPrimaryKeyValue DetailTableName DetailForeignkeyName XmlMergePrefix

Voorbeeld 8090 PurchaseOrders REC_ID 20092020 PurchaseOrderLines LINE_ID InkooporderAdd

Automatisch Verplicht Verplicht Verplicht Optioneel Optioneel Verplicht

In dit voorbeeld zijn er in de SQL database de tabellen [PurchaseOrders] en [PurchaseOrderLines] aanwezig, van waaruit inkooporder [20092020] moet worden getransporteerd naar de XML Output folder. De XML templates die hierbij worden gebruikt zijn dus inkooporderadd_header.txt, inkooporderadd_item.txt en inkooporderadd_footer.txt. SQL tabel [PurchaseOrders]: Veldnaam REC_ID Creditor Purchasedate Descripton ..

Voorbeeld 20092021 85074 20090203 Order XX ..

SQL tabel [PurchaseOrderLines]: Veldnaam LINE_ID ORD_NR ARTICLE QUANTITY ..

Vb 1 1 ART001 10 ..

Vb 2 1 ART002 12 ..

Vb 3 2 ART001 8 ..

Vb 4 3 ART005 5 ..

Vb 5 .. .. .. ..

AccountView tabel [PO_HDR]: Veldnaam ORD_NR RPL_INV ORD_DATE COMMENT1 .. AccountView tabel [PO_LINE]: Veldnaam REC_ID ORD_NR ART_CODE ART_QTY ..


SQL Connector

20

6.3 XML Message Indien er een ‘compleet’ XML bericht getransporteerd moet worden zonder directe beschikbaarheid van een SQL tabel kan de SQL tabel [tblXmlMessage] worden gebruikt. In deze tabel kan een record aangemaakt worden door de kolom [Message] te vullen. Vervolgens kan men dit bericht laten samenvoegen met een template door een record in de tabel [tblXmlTransport] aan te maken. In het template moet dan de volgende HTML tag aanwezig zijn: {MESSAGE}. SQL tabel [XmlMessage]: Veldnaam MessageID Message

Automatisch Verplicht

SQL tabel [XmlTransport]: Veldnaam TransportID MasterTableName MasterPrimaryKeyName MasterPrimaryKeyValue DetailTableName DetailForeignkeyName XmlMergePrefix

Voorbeeld 8085 tblXmlMessage ID 717 Null Null BLOB

Voorbeeld 717 <XML>...


Consolit SQL Connector Handleiding

Recommend Documents