Nieuws
Foto's
Artikelen
Componenten
Applicaties
Kleinkunst

Delphi - SQL Server fouten afhandelen met ADO

Het goed opvangen van database fouten in je applicatie is meestal geen eenvoudige opdracht. Zeker niet als je duidelijke niet-technische meldingen in de taal van de gebruiker wil tonen. Anderzijds is het in grote applicaties ook belangrijk om alle nodige technische informatie te verzamelen zodat mensen van supportdiensten of ontwikkelaars het probleem kunnen oplossen.

In dit artikel bekijken we welke soort fouten/errors er bestaan in MS SQL Server, hoe je deze kan opvangen in stored procedures en hoe je deze via ADO in Delphi kan afhandelen. Bovendien zal ik enkele mogelijkheden beschrijven om een eigen foutmeldingsscherm te ontwikkelen met eigen meldingen in de taal van de gebruiker en toch met zoveel mogelijk technische informatie.

SysMessages

Master database

Alle foutmeldingen die zich kunnen voordoen in een MS SQL Server database zijn opgenomen in een speciale systeemtabel. Deze systeemtabellen zitten in een aparte database met de naam MASTER.

Alle meldingen zitten in de SYSMESSAGES tabel. Deze tabel bevat als sleutel de unieke ERROR code. Verder zit er in de tabel het veld SEVERITY, het veld DESCRIPTION met het bericht en MSGLANGID, de taalcode die het mogelijk maakt om meldingen in meerdere talen weer te geven. Deze systeemtabel wordt opgevuld bij de installatie van SQL server.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-m_1yk9.asp

 

Severity

Fouten worden onderverdeeld in categorieën. Dit kan je afleiden uit het veld Severity. Bij het ontwikkelen van software zullen we ons vooral op de gewone gebruikersmeldingen met Severity tussen 11 en 16 concentreren. Dit gaat dan over veel voorkomende fouten zoals inloggen met foutieve gebruikers of wachtwoorden, dubbele sleutelwaarden, verplichte velden, verwijderen van data die nog een constraint heeft naar een andere tabel, fouten in stored procedures, …

  • 10 : Status Information
  • 11..16 : User message
  • 17 : Insufficient Resources
  • 18 : Nonfatal Internal Error Detected
  • 19 : SQL Server Error in Resource
  • 20 : SQL Server Fatal Error in Current Process
  • 21 : SQL Server Fatal Error in Database (dbid) Processes
  • 22 : SQL Server Fatal Error Table Integrity Suspect
  • 23 : SQL Server Fatal Error: Database Integrity Suspect
  • 24 : Hardware Error

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_errorformats_0tpv.asp

 

Taal

Server taal

De systeemtabel SYSMESSAGES wordt opgevuld bij de installatie van SQL server en daarbij is de taal 1033 US-English altijd aanwezig. Eventueel kunnen andere talen zoals bv. Nederlands 1043 geïnstalleerd worden. Fouten worden default getoond in de taal van de server. Deze kan je instellen op het tabblad Server Settings in het eigenschappenvenster in de Enterprise Manager.

Taal opvragen (@@LangID, @@Language)

De actieve taal kan je ook opvragen via een eenvoudig SQL statement. Er zijn namelijk 2 globale variabelen met de ID (@@LangID) en de omschrijving (@@Language) van de taal.

SELECT @@LANGID, @@LANGUAGE

Sessie taal (Set Language)

Per sessie kan je ook een andere actieve taal instellen. Een anderstalige gebruiker kan dus in zijn eigen sessie in een andere taal werken. Deze taal instellen kan met SET LANGUAGE. Na het inloggen van je gebruiker kan je dus eventueel een stored procedure uitvoeren die de actieve taal zal instellen.

SET LANGUAGE Dutch
SET LANGUAGE French
SET LANGUAGE Spanish

Samenstelling bericht

Als je de omschrijvingen van de foutmeldingen in de tabel SYSMESSAGES bekijkt, zal je meteen opvallen dat daarin ook tags gebruikt worden. Deze tags worden uiteindelijk opgevuld met specifieke systeeminformatie.

  • %d, %D : Decimal number
  • %x,%X,%.*x,%lx, %04x, %08lx : Hexadecimal number
  • %s : Null-terminated string
  • %.*s, %*s, %*.s, %ls, %.*ls : String, usually the name of a particular database object
  • %S_type : Adaptive Server-defined structure
  • %c : Single character
  • %f : Floating-point number
  • %ld : Long decimal
  • %lf : Double floating-point number

 

Eigen fouten genereren in stored procedures

RaiseError

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_5ooi.asp

Om eigen user-defined meldingen te genereren moet je het RAISEERROR commando oproepen. Hiermee kan je eigen dynamische foutmeldingen meegeven of een nieuwe ErrorCode gebruiken die in de SYSMESSAGES tabel gedefinieerd zal worden.

Een vaste foutboodschap teruggeven kan vrij eenvoudig door de foutmelding samen met de Severity en State op te geven. De ErrorCode zal dan altijd 50000 zijn.

CREATE PROCEDURE RaiseError1
AS
BEGIN
  RAISERROR('Error in stored procedure', 16, 1);
END

Je kan ook nog een stapje verder gaan en tags/parameters in je vaste foutmelding opgeven. Achteraan moet je dan de waardes van deze tags opgeven.

CREATE PROCEDURE RaiseError2
AS
BEGIN
  DECLARE @intValue INTEGER;
  SET @intValue = 10;
  RAISERROR('Error in stored procedure %s. Value should be larger then %d',16, 1, 'RaiseError2', @intValue);
END

Wil je de foutmelding in meerder talen kunnen tonen, dan moet je een nieuwe ErrorCode aanmaken. In de stored procedure geef je dan de nieuwe ErrorCode, Severity, State en de waardes voor de tags op.

CREATE PROCEDURE RaiseError3
AS
BEGIN
  RAISERROR(50001,16, 1, 'RaiseError3',123);
END

 

SP_AddMessage

Nieuwe ErrorCodes moet je zelf toevoegen in de SYSMESSAGES tabel in de MASTER database. Dit kan je niet manueel ! Daarvoor moet je een speciale stored procedure SP_AddMessage uitvoeren.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8zdx.asp

SP_ADDMESSAGE 50001, 16, 'Error in stored procedure %s. Value should be larger then %d' 1033
SP_ADDMESSAGE 50001, 16, 'Fout in stored procedure %s. Waarde moet groter zijn dan %d' 1043
SP_ADDMESSAGE 50001, 16, 'Erreur en stored procedure %s. Valeur doit être plus grande que %d' 1036
SP_ADDMESSAGE 50001, 16, 'Error en stored procedure %s. Valor debe ser más grande que %d' 3082

 

SysLanguages en SP_HelpLanguage

Informatie opvragen over de beschikbare talen en hun ID’s kan vrij eenvoudig met de stored procedure SP_HelpLanguage.

SP_HELPENGLISH English
SP_HELPENGLISH Dutch
SP_HELPENGLISH French
SP_HELPENGLISH Spanish

De SP_HelpLanguage gaat eigenlijk een query uitvoeren op de systeemtabel SYSLANGUAGE die je ook in de MASTER database kan terugvinden.

 

Fouten opvangen

SQL Server (@@Error)

Met de globale variable @@ERROR kan je de laatste error code opvragen. Als je in stored procedures INSERT, UPDATE, DELETE, … statements uitvoert, kan je meteen daarna controleren of deze gelukt zijn.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_9ghe.asp

INSERT INTO TESTTABLE (TESTID) VALUES (1)
IF @@ERROR = 515
  PRINT "Key violation"

 

Delphi ADO (EOleException, Connection.Errors)

Fouten opvangen in Delphi kan eenvoudig via de exceptions. De exceptie die een ADO component (query, tabel, stored procedure, connection, ...) zal geven, is echter altijd een EOleException. Wil je meer informatie over de fout, dan moet je de Errors property van het ADOConnection component raadplegen.

Try-except

procedure TForm1.ButtonInsertRecordClick(Sender: TObject);
begin
  ADOConnection1.BeginTrans;
  try
    ADOCommand1.CommandText := 'INSERT INTO TESTTABLE (TESTID) VALUES (1)';
    ADOCommand1.Execute;
    ADOConnection1.CommitTrans;
  except
    ADOConnection1.RollbackTrans;
    
    ShowMessage(
      'Number ='+IntToStr(ADOConnection1.Errors[0].Number)+CRLF
      +'NativeError ='+IntToStr(ADOConnection1.Errors[0].NativeError)+CRLF
      +'Source ='+ADOConnection1.Errors[0].Source+CRLF
      +'Description ='+ADOConnection1.Errors[0].Description+CRLF
      +'Helpfile ='+ADOConnection1.Errors[0].HelpFile+CRLF
      +'SQLState ='+ADOConnection1.Errors[0].SQLState);
  end;
end;

Als je overal in je applicatie fouten wil opvangen, kan je dit beter op een centrale plaats doen. Het TApplicationEvents component heeft een OnException method waarmee je alle excepties in je applicatie kan opvangen. Dit is dan ook meest geschikte plaats om foutmeldingen te tonen, informatie te loggen, … Bij het uitvoeren van een ADO-actie moet je meestal echter wel transacties gebruiken. Om in de except waar je een RollBack zal moeten uitvoeren toch de exceptie door te geven aan het ApplicationEvents component, moet je het raise commando uitvoeren (re-raise exception). Dit zal er voor zorgen dat de exceptie opnieuw zal uitgevoerd worden.

ApplicationEvents.OnException

procedure TForm1.ButtonRequiredClick(Sender: TObject);
begin
  if ClientDataSet1.ChangeCount > 0 then
  begin
    ADOConnection1.BeginTrans;
    try
      ClientDataSet1.ApplyUpdates(-1);
      ADOConnection1.CommitTrans;
    except
      ADOConnection1.RollbackTrans;
      raise;
    end;
  end;
end;

procedure TForm1.ButtonStoredProcedureClick(Sender: TObject);
begin
  ADOConnection1.BeginTrans;
  try
    ADOStoredProc1.ExecProc;
    ADOConnection1.CommitTrans;
  except
    ADOConnection1.RollbackTrans;
    raise;
  end;
end;

procedure TForm1.ApplicationEvents1Exception(Sender: TObject; E: Exception);
begin
  if (E.ClassType = EOleException) and (ADOConnection1.Errors.Count > 0) then
  begin
    ShowMessage(
      'Number ='+IntToStr(ADOConnection1.Errors[0].Number)+CRLF
      +'NativeError ='+IntToStr(ADOConnection1.Errors[0].NativeError)+CRLF
      +'Source ='+ADOConnection1.Errors[0].Source+CRLF
      +'Description ='+ADOConnection1.Errors[0].Description+CRLF
      +'Helpfile ='+ADOConnection1.Errors[0].HelpFile+CRLF
      +'SQLState ='+ADOConnection1.Errors[0].SQLState);
  end;
end;

 

Eigen foutmeldingsscherm

Nadelen standaard SQL Server mogelijkheden

Taal van gebruiker en ontwikkelaar

MS SQL Server biedt de mogelijkheid om per sessie een taal te gebruiken. De eindgebruiker zal dus alle foutmeldingen in zijn eigen taal te zien krijgen. Dit is op zich een goede oplossing ware het niet dat alle technische info ook vertaald zal zijn. Mensen van support-diensten of software ontwikkelaars kunnen via de ErrorCode alle info wel opzoeken in hun eigen taal, maar dit zorgt voor extra werk.

Daarom zou het veel beter zijn dat de eindgebruiker een melding krijgt in zijn eigen taal en dat de technische informatie bv. altijd in de Engelse taal wordt weergegeven.

Technische details

De meeste meldingen in de SYSMESSAGES tabel zijn vrij technisch. Een gewone eindgebruiker heeft meestal geen boodschap aan technische informatie zoals tabelnamen, constraintnamen, veldnamen, …

Daarom is het interessanter om duidelijke niet-technische foutmeldingen te tonen maar daarbij de technische informatie toch niet verloren te laten gaan.

 

Nieuwe mechanisme

Vele MS SQL foutmeldingen bevatten tags. Van zodra een exceptie in Delphi wordt opgevangen krijg je enkel een foutmelding met ingevulde waardes. Door echter deze foutmelding te vergelijken met de foutmelding met tags kan je de namen en waardes van deze tags achterhalen.

Eigen gebruikserfoutmeldingen kunnen ondergebracht worden in een nieuwe tabel, bijvoorbeeld met de naam usermessages. In deze eigen foutmeldingen kan je de tags van de MS SQL foutmeldingen gebruiken en eventueel kan je nog eigen tags voorzien voor gebruikersnaam, PC-naam, datum en tijd, …

Hieronder een eenvoudige beschrijving van een mechanisme waarmee je een nieuw foutmeldingsscherm kan ontwikkelen.

  • 1) Exceptie geeft errorcode en foutmelding met ingevulde tags terug in de taal van de server (of sessie).
  • 2) Query wordt uitgevoerd om in de sysmessages tabel de foutmelding met tags op te zoeken in taal van de server (of sessie).
  • 3) Door de foutmelding met tags te vergelijken met de foutmelding met de ingevulde tags, kunnen de namen en waardes van de tags geparst worden.
  • 4) Query wordt uitgevoerd om in de sysmessages tabel de foutmelding (met tags) in de taal van de gebruiker op te halen.
  • 5) Query wordt uitgevoerd om in een eigen usermessages tabel naar een eenvoudige foutmelding (eventueel met tags) voor de gebruiker te zoeken.
  • 6) De tags-namen in de foutmeldingen van stap 4 en 5 worden vervangen duur hun waardes.
  • 7) Nieuw foutmeldingsscherm wordt getoond.

Opgelet

  • Je kan enkel queries uitvoeren als er toegang is tot de database.
  • Dus dit mechanisme kan enkel gebruikt worden voor fouten met severity tussen 11 en 16.
  • Eventueel moet je een 2e connectie gebruiken om deze queries uit te voeren want misschien kan de gebruiker zelf niet inloggen.
  • De tags in SQL Server foutmeldingen blijken niet altijd in alle talen overeen te komen.

 

Enkele voorbeelden