U bevindt zich nu op een technische pagina over de software Mercator. Deze pagina bevat specifieke informatie die bestemd is voor professionals van de software Mercator. Wenst u naar algemenere informatie over Mercator door te gaan?


   Deze vraag niet meer stellen

xLookup, de wederkerende fout in uw code!

0000002702     -      24-04-2017

De functie xLookup is een handige functie, maar ons support-team merkt dat ze vaak verkeerd gebruikt wordt tijdens het programmeren in Mercator. Hou in gedachten dat het doel van deze methode is om een veld terug te krijgen van een record uit een tabel met behulp van een zoekindex.

Deze functie dateert uit de DBF-versie van Mercator, dit wil zeggen "uit de tijd dat Mercator nog geen client-server was", met alle bijhorende risico's (schade aan gegevens in geval van een crash, integriteitscontroles,...)

In de DBF-versie wordt een aanroep van het type xlookup('CLI_X','C_ID','ABC','C_NOM') omgezet naar volgende FoxPro code:

select cli_x
set order to c_id
seek('ABC')
return cli_x.c_nom

Omdat de gegevens toen nog lokaal op de pc bewaard werden door Mercator, was het zoekresultaat er bijna onmiddelijk.

Nu is Mercator een programma dat zich in een client-server omgeving bevindt, de voordelen van deze configuratie moeten we u niet vertellen. In de huidige Mercator client is de SQL-server verantwoordelijk voor het hosten van de database en het waarborgen van de integriteit. Het essentiele verschil is dat de gegevens niet, lokaal maar op afstand bewaard worden. Dus zal elke xLookup volgende commando's uitvoeren (voor het hierboven gegeven voorbeeld):

  • Connectie maken met de SQL-server
  • Onderstaande query uitvoeren
    exec sp_executesql N'select C_NOM as ret from CLI (NOLOCK) where (C_ID=@valeur_lookup)',N'@valeur_lookup char(3)',@valeur_lookup='ABC'
  • Het resultaat opvangen in Mercator
  • De verbinding met de SQL-server verbreken

Dus kunnen we concluderen dat de xLookUp niet meer werkt in functie van het client-server model.

Wanneer men praat over de functie xLookup, praten we ook over alle onderliggende functies die op hetzelfde principe hanteren:

  • xCondit
  • xCours
  • xDepot
  • xDesign
  • xDispo
  • xExercice
  • xFamille
  • xGamEnum
  • xGamTyp
  • xLiees
  • xNom
  • xPaiem
  • xPeriode
  • xRayon
  • xRep
  • xTiers
  • xTypePaiem
  • xUser
  • xVatRateIsStandard
  • ...

Concreet

""De validatie van een leverancierslevering van 800 lijnen is abnormaal traag.""

Diagnostisch rapport van Mercator:In de customizer bevat de BeforeSave een loop die alle lijnen uit het document overloopt. In deze loop, heeft de programmeer 2 xLookUps geplaatst, deze zullen dus 1600 connecties en deconnecties maken bij de SQL-server.

"Het rooster van de historieken is extreem traag, vooral de eerste weergave en het scrollen"

Diagnostisch rapport van Mercator: Er is een customizer geplaatst op de waarden in het rooster en deze waarden komen voort uit xLookUps. Elke refresh van een cel produceert een connectie met de SQL-server. Wat dus resulteert in meerdere connecties, gewoon opdat men in het raster(grid) zou kunnen scrollen.

Moet men het gebruik van xLookup in de programmatie van Mercator verbieden?

Het antwoord van ons programmeerteam is zonder twijfel:JA
Men zal het gebruik ervan enkel tolereren indien men overtuigd is dat er geen performance verlies zal zijn.

Hoe kan je een situatie detecteren waarin xLookup verkeerd gebruikt wordt?

Hiervoor moet men SQL-server Profiler gebruiken. Deze tool laat ons toe om de queries die de SQL-server ontvangt te visualiseren. Een situatie van performance-verlies kan gekenmerkt worden door een lange reeks identieke queries, waarvan de structuur steeds dezelfde is:

select COLONNE as ret from TABLE (NOLOCK) where (INDEX=@valeur_lookup)

Hoe kan je deze mankementen corrigeren?

Geval 1: In de LinesEditor worden cellen vervolledigd met het resultaat van een xLookup

Om dit te wijzigen, volstaat het om de read-only kolommen uit de LinesEditor te gebruiken. Deze werden aangemaakt om een antwoord te bieden op deze situatie. Indien de gewenste waarde niet direct beschikbaar is, moet een een Scalar SQL-function gebruiken. (Zie het voorbeeld met XDISPO_PERSO)

Geval 2: In een andere grid worden cellen aangevuld met het resultaat van een xLookup

Om dit te corrigeren moet men in 2 stappen werken:

  1. Via een StringUpdate, wijzig de SQL-query om de gewenste gegevens in de DataTable op te vangen.
  2. Via de customizer voeg je de kolommen toe in het rooster.

Zie deze voorbeelden:

Geval 3: In een loop de DataRows van een DataTable overlopen waarbij één of meerdere waarden worden uitgelezen met een xLookup Om dit te corrigeren, moet men als volgt te werk gaan:

  1. Upload met een SQL-bulk een tijdelijke tabel met de minimale informatie van alle records waarvoor je informatie wilt bekomen.
  2. Voor een enkele Zselect uit, waarvan de query een join bevat op de tijdelijke tabel alvorens je alle informatie die nodig is in de loop terugkrijgt. Dit door middel van één enkele SQL-verbinding.
  3. In de lus moet je een DataTable.Select gaan gebruiken die ons toelaat om een lokale opzoeking te doen in de DataTable om de gewenste informatie te verkrijgen.

Hieronder is een heel eenvoudig voorbeeld die ons toelaat, om in een BeforeSave-event van de BillingEngine, het veld LIGNES_V.XXX met de waarde van STOCK.S_XXX te overschrijven en LIGNE_V.YYY met de waarde van STOCK.S_YYY.

Zoom
private void BillingEngine_BeforeSave(object sender, MercatorUi.Engine.Gescom.BillingEngine.BeforeSaveEventArgs e)
{
    MercatorUi.Engine.Gescom.BillingEngine billingEngine = (MercatorUi.Engine.Gescom.BillingEngine)sender;
    DataTable dtArticles = new DataTable();
    dtArticles.Columns.Add("s_id", typeof(string));
    foreach(DataRow dr in billingEngine.LIGNES.Select("id_article<>''"))
        dtArticles.Rows.Add(new object[1] { dr["id_article"] });
    using (MercatorSqlConnection conn = new MercatorSqlConnection(MercatorUi.Globals.RepData, true))
    {
        if (conn.Connection == null)
        {
            e.CancelSave = true;  // connectie geweigerd door de SQL-server -> we stoppen het bewaren
            return;
        }
        if (!Api.BulkDataTable(dtArticles, "#art_tmp", conn.Connection, null, "STOCK"))
        {
            e.CancelSave = true; //error tijdens de bulk van de DataTable -> we stoppen het bewaren
            return;
        }

        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("", conn.Connection))
        {
            cmd.CommandText = "select s_id,s_xxx,s_yyy from STOCK inner join #art_tmp on (stock.s_id=#art_tmp.s_id) \r\n"
                            + "drop table #art_tmp";
            DataSet ds = Api.Zselect(conn.Connection, cmd);
            if (ds == null)
            {
                e.CancelSave = true;  // de query hierboven geeft een fout -> we stoppen het bewaren
                return;
            }
            Api.TrimEndDataTable(ds.Tables[0]);

            // einde van de voorafgaande behandeling voor code in de loop


            // begin van de behandeling in de lus van de lijnen
            foreach (DataRow dr in billingEngine.LIGNES.Select("id_article<>''"))
            {
                DataRow[] foundRows = ds.Tables[0].Select(string.Format("s_id='{0}'", Api.UnquoteSql(dr["id_article")))):
                if (foundRows.Length > 0) // zal altijd het geval zijn, wanneer alle artikelen bestaan    
                 {
                    dr["xxx"] = foundRows[0]["s_xxx"];
                    dr["yyy"] = foundRows[0]["s_yyy"];
                }
            }
        }
    }
}

 

Geval 4: In de lijnen van de BillingEngine beschikken over informatie uit de tabel STOCK wanneer deze velden niet beschikbaar zijn in  LIGNES_V

Het voorbeeld hieronder toont hoe je meerdere xLookups kan vervangen:

  • In de methodes van de BillingEngine
  • In de afdrukmodellen van de sequentie
  • In de formules van de kolom
  • ...

De mechaniek is gebaseerd op verschillende fasen:

  1. Lees de benodigde kolommen uit de tabel STOCK voor de artikelen van het document dat geïnitialiseerd wordt (niet-lege lijst als bestaand document)
  2. Voeg in de DataTable BillingEngine.LINES de gewenste kolommen toe
  3. Vervolledig deze kolommen met de overeenkomstige waarden. 
    Hier beëindigt de code die wordt uitgevoerd het initialiseren van het document.
  4. Tijdens het toevoegen van elk artikel, de kolommen vervolledigen met de informatie van het artikel dat ingevoerd wordt (AfterInsertItem)

Hieronder een voorbeeld van een customizer die dit proces implementeert.

Zoom
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using MercatorApi;
using MercatorUi;
using MercatorExtensions;
using System.Xml;

namespace Billing
{
    public class Customizer : MercatorUi.ICustomizers.IBillingEngineCreated, MercatorUi.ICustomizers.IBillingEngineClosed
    {
        public void BillingEngineCreated(MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            billingEngine.AfterInsertItem += BillingEngine_AfterInsertItem;

            if (!billingEngine.LIGNES.Columns.Contains("stock_xxx"))
            {
                billingEngine.LIGNES.Columns.Add("stock_xxx", typeof(string));
                billingEngine.LIGNES.Columns.Add("stock_yyy", typeof(string));

                bool billingEngineWasModified = billingEngine.IsModified;

                //Uitlezen van de STOCK-velden die zich niet in de LIGNES_V bevinden
                DataSet ds = Api.Zselect(Globals.RepData, "select s_id, s_xxx, s_yyy from STOCK (NOLOCK) "
                                                        + "where exists(select * from LIGNES_V (NOLOCK) where (id=@id) and (journal=@journal) and (piece=@piece) and (id_article=s_id))",
                                new MercatorSqlParam("@id", billingEngine.Id, SqlDbType.Char),
                                new MercatorSqlParam("@journal", billingEngine.Journal, SqlDbType.Char),
                                new MercatorSqlParam("@piece", billingEngine.Piece));
                if (ds != null)
                {
                    Api.TrimEndDataTable(ds.Tables[0]);
                    foreach (DataRow billingLine in billingEngine.LIGNES.Rows)
                    {
                        DataRow[] stock = ds.Tables[0].Select(string.Format("s_id='{0}'", Api.UnquoteSql(billingLine["id_article"].ToString())));
                        InsertStockInBillingLine((stock.Length > 0 ? stock[0] : null), billingLine, billingEngine);
                    }
                }
                if (!billingEngineWasModified)
                    billingEngine.LIGNES.AcceptChanges();
            }
        }

        public void BillingEngineClosed(MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            billingEngine.AfterInsertItem -= BillingEngine_AfterInsertItem;
        }

        void BillingEngine_AfterInsertItem(object sender, MercatorUi.Engine.Gescom.BillingEngine.AfterInsertItemEventArgs e)
        {
            MercatorUi.Engine.Gescom.BillingEngine billingEngine = (MercatorUi.Engine.Gescom.BillingEngine)sender;
            InsertStockInBillingLine(e.DataRowStock, e.DataRowLignes, billingEngine);
        }

        void InsertStockInBillingLine(DataRow stock, DataRow billingLine, MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            if (stock == null)
            {
                billingLine["stock_xxx"] = "";
                billingLine["stock_yyy"] = "";
            }
            else
            {
                billingLine["stock_xxx"] = stock["s_xxx"];
                billingLine["stock_yyy"] = stock["s_yyy"];
            }
        }
    }
}