PortiBlog

K2: SQL FMTONLY problemen met stored procedures

10 januari 2018

Ik hoor je denken: een blogpost over K2 en SQL? Nou, een beetje kennis van SQL, want daar gaat het deze keer vooral over, is nooit weg voor een SharePoint consultant.

Inleiding

In een K2 project, waarbij ik ook als databasebeheerder betrokken was, liepen we tegen het volgende aan. Zoals je misschien weet, is K2 Blackpearl een workflow pakket die informatie vanuit SharePoint kan gebruiken op formulieren en in workflowtaken. Echter, je kunt ook informatie ophalen uit andere systemen, SharePoint is, in tegenstelling tot bijvoorbeeld Nintex, slechts één van de informatiebronnen van het pakket. Binnen het project waar ik bij betrokken was, gebruikte de klant geen SharePoint en werd voor alle informatie een, door ons zelf ontworpen, SQL database gebruikt. K2 werkt nooit rechtstreeks op een databron, maar gebruikt een soort tussenlaag, de Smart Objects, die bestaan uit de definitie van elke methode van elke table, view en stored procedure die in de database aanwezig is. Je gebruikt bijvoorbeeld de list methode om een overzicht van klanten op een formulier te tonen en vervolgens gebruik je de update methode om de gegevens van één klant bij te werken. So far, so good. 

Probleemstelling

Het wordt lastiger als je een stored procedure gebruikt die met temporary tables werkt, of een pivot toepast. Beide zijn hele handige mogelijkheden om bijvoorbeeld meerdere resultaten samen te voegen of om een draaitabel te maken. Het nadeel is dat uit de code van je stored procedure niet duidelijk is, tenminste voor K2 niet, uit welke kolommen je result bestaat. Het gevolg is, dat het Smart Object niet weet uit welke kolommen hij moet bestaan en daarom leeg blijft. Op die manier kun je niet die handige, maar complexe, stored procedure gebruiken in je oplossing. 

Voorbeeld

Een stored procedure is niets anders dan een stukje SQL code met wat logica. Hij heeft altijd een begin en einde en daartussen worden dingen uitgerekend, items geupdated, eigenlijk van alles kan hier gebeuren. In tegenstelling tot een table of view is het niet vanzelfsprekend dat de stored procedure een resultaatset teruggeeft. We gebruiken in dit voorbeeld een tijdelijke tabel in de stored procedure.Voorbeeld simpele stored procedure

Vrij simpel toch? 

We maken eerst de tijdelijke tabel aan, met twee kolommen, Id en CustomType. Daarna selecteren we alles wat in de tijdelijke tabel zit en geven dat terug en daarna gooien we de tabel weer weg. K2 snapt hier helemaal niets van en zal aangeven dat er wel een stored procedure is gevonden, maar dat die niets teruggeeft (dus geen enkele kolom). Een error is het gevolg.

Hoe komt dat?

Eigenlijk een beetje raar dat K2 dat niet snapt, zelfs iemand met minimale SQL kennis zal het bovenstaande voorbeeld kunnen lezen en snappen dat er twee kolommen geselecteerd worden. Op het moment dat K2 verbinding met de database maakt en alle objecten gaat opzoeken om daar de definitie van het Smart Object voor te maken, zet K2 automatisch de FMTONLY parameter op ON. FMTONLY is een instelling van SQL die alle logica uit een object overslaat, geen resultaten teruggeeft, ook geen INSERT of UPDATE uitvoert, maar alleen de definitie teruggeeft van de kolommen uit de resultaatset. Ideaal voor K2, want die was nou net op zoek naar alle kolommen, zodat daarmee het Smart Object gemaakt kon worden. Helaas in ons voorbeeld in de tijdelijke tabel onderdeel van die logica en wordt daarom overgeslagen. Voor K2 lijkt dit een lege stored procedure.

Wat kun je daar officieel tegen doen?

Niks. Dit wordt niet ondersteund door K2.

En iets minder officieel?

Je kunt K2 wel een beetje voor de gek houden. En dat vereist dat je de stored procedure uitbreidt met wat code en een nep resultaatset gaan beschrijven voor het geval FMTONLY op ON wordt gezet. Het is daarbij wel van belang dat je exact dezelfde resultaatset beschrijft die je stored procedure normaal gesproken zou genereren. Kolomnamen en types moeten precies overeenkomen. K2 volgt de nep resultaatset bij het aanmaken van het Smart Object. Daarna, bij het daadwerkelijk uitvoeren van het Smart Object (lees: stored procedure) wordt de ‘echte’ code gebruikt.Voorbeeld toevoeging fmtonly redirect

Zoals je ziet, beginnen we met een statement wat onmogelijk waar kan zijn: IF 1=0. De stored procedure slaat dit daarom altijd over en komt uit bij het maken van de tijdelijke tabel. K2 ziet alleen maar het volgende als hij op zoek gaat naar de definitie:

Voorbeeld wat fmtonly ziet

Alle logica wordt overgeslagen, dus ook het onmogelijke 1=0 statement. GOTO snapt hij dan weer wel en die hebben we ná de echte code toegevoegd zonder verdere acties er achter. Kortom, bij het zoeken naar de definitie van deze stored procedure, zal K2 twee kolommen vinden, Id en CustomType. Omdat deze kolommen qua naam en datatype exact overeenkomen met het resultaat van de stored procedure, kunnen we die nu gebruiken via een Smart Object.

Submit a comment

Dit vind je vast ook interessant.