samedi 30 novembre 2002

Trigger


TRIGGER

Un trigger est associé à une table ou vue et permet d'exécuter du code selon un évènement donné. Tout comme les procédures stockées, tout s'exécute sur le serveur. Le trigger n'est jamais appelé directement. C'est lorsque l'évènement auquel est associé le trigger survient, le code est alors exécuté. Le langage utilisé par la base de données peut être utilisé pour le trigger, point besoin d'être limité à du SQL. Il est donc possible d'effectuer des tâches très complexes très aisément en utilisant ce langage au lieu d'avoir à gérer de complexe et lourde requête SQL.

Il est donc possible de se passer des triggers, mais un plus grand effort sera nécessaire pour parvenir au même objectif. Un trigger est exécuté sur le serveur, il n'y a donc pas de trafic réseau qui est généré telle que lors de requête SQL. Cette centralisation permet que plusieurs applications puissent y avoir accès. Si un changement doit se faire, il n'est pas nécessaire de recompiler l'application. L'utilisation de cette technique peut alors s'avérer beaucoup plus rapide que son équivalent en SQL. Un trigger n'a aucun paramètre d'entré et de sortie.
Il est possible d'utiliser les triggers pour diverses raisons:
  • Gérer l'intégrité des données
  • Gérer les règles d'affaires
  • Sécurité
  • ...

Syntaxe d'un trigger

CREATE TRIGGER name FOR { table | view}
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {DELETE | INSERT | UPDATE}
[POSITION number]
AS < trigger_body >
< trigger_body > = [< variable_declaration_list >] < block >
< variable_declaration_list > =DECLARE VARIABLE variable < datatype >;
[DECLARE VARIABLE variable < datatype > ;...]
< block > =
BEGIN
< compound_statement > [< compound_statement >...]
END
< compound_statement > = {< block > | statement;}

Il existe tel qu'on peut le voir certaines ressemblances avec la syntaxe des procédures stockées

Opérateur

Les conditions et les boucles sont identiques à celles qu'on a pu voir dans les procédures stockées
Un trigger peut être exécuté pour les évènements suivants
  • BeforeInsert
  • BeforeUpdate
  • BeforeDelete
  • AfterInsert
  • AfterUpdate
  • AfterDelete
Lors du tutoriel des Générateurs, nous avons rapidement vu le code d'un trigger
SET TERM ^;
CREATE TRIGGER TRIG_CLIENT_GenId FOR CLIENT
ACTIVE BEFORE INSERT AS
BEGIN
  IF ( NEW.NOCLIENT IS NULL ) THEN
    NEW.NOCLIENT = GEN_ID(TRIG_CLIENT_GenId,1);
END

Ce trigger permet automatiquement de renseigner la clé primaire de la table. Il n'est alors plus nécessaire
d'intervenir, Interbase le fait pour nous. Une telle approche permet d'éviter les erreurs en cas d'omission.
Création d'une table

create table tclient(
 noms char(12),
 prenom char(12),
 motpasse char(12)
 );

SET TERM ^;
CREATE TRIGGER TRIG_CLIENT_GenId FOR CLIENT
ACTIVE AFTER INSERT AS
BEGIN
  IF ( NEW.motpasse IS NULL ) THEN
    NEW.motpasse = 'tmp';
END

La création de cette table ainsi que la procédure stockée permet de mettre un mot de passe si aucun n'a été inclus.Il
est possible de faire appel à une procédure stockée ou un UDF dans un trigger

UDF

UDF signifie fonction définie par l'usager (user defined function). Ces UDF sont bien entendu exécutés sur le serveur, la majorité du temps, le langage utilisé est le c ou le c++. Il est possible d'utiliser d'autre langage. Dans les répertoires d'Interbase, il y a de nombreux exemples.

Les UDF sont utilisés pour ajouter des fonctionnalités non disponibles dans SQL.
Les UDF ont 2 défauts: lorsqu'une UDF est exécuté, aucun accès à la BD ne peut survenir (affecte interbase inférieur à 7). Le second est que si le besoin de changer de machine pour une quelques raisons, on doit installer tous les UDF avant de restaurer la sauvegarde de la bd.

Lorsqu'on créer une UDF, on doit garder à l'esprit qu'elle devrait être le plus simple possible. Il est tout à faire possible dans une UDF d'exécuter un programme ou de faire des insertions, suppression... mais une telle approche peut mettre diminuer significativement les performances du serveur de plus ça pourraient occasionner une congestion des transactions.
Interbase inclus plusieurs UDF dont:
  • lower: converti une chaîne de caractère en minuscule
  • strcat: concatène deux chaînes
  • substr: retourne une portion d'une chaîne
  • trim: enlève les espaces d'une chaîne
  • trunc: trunc une chaîne
  • sysdate: retourne la date du système
  • sqrt: retourne la racine carré d'un double
  • abs : retourne la valeur absolue d'un double

Création d'une UDF avec delphi

Nous allons créer une UDF qui retournera la taxe (7%) sur un chiffre passé en paramètre
  • Choisir l'expert dll
  • Ajouter une unité au projet
  • Sauvegarder l'unité sous le nom MathFct
  • Sauvegarder le projet sous le nom brudflib
L'unité MathFct à l'allure suivante
unit MathFct;

interface

function TaxeTps(var i:Double): Double; cdecl; export;

implementation

function TaxeTps(var i:Double): Double; cdecl; export;
begin
  result := i*0.07+i;
end;

end.

Voici maintenant le code du projet
library brudflib;

uses
  SysUtils,
  Classes,
  MathFct in 'MathFct.pas';

{$R *.res}

exports
  TaxeTps;

begin
end.

Notez la présence du exports. TaxeTps fait partie de cette section, elle pourra donc être utilisé par un autre programme.
Il aurait été possible dans l'unité MathFct d'avoir plusieurs fonctions, mais que seulement quelque unes soit accessible
de l'extérieur.Ne pas oublier que tous les paramètres dans une UDF sont passés par pointeur donc il ne faut pas oublier
de mettre var devant nos paramètres.

En exécutant le projet, une dll est créé brudflib.dll. Cette dll doit être placée dans le répertoire UDF d'Interbase, habituellement c'est: disqu:\Borland\InterBase\UDF\. Il faut par la suite déclarer cette fonction pour l'utiliser dans Interbase.

Syntaxe de la déclaration de l'UDF pour Interbase

Voici la syntaxe d'une UDF:
DEFINE EXTERNAL FUNCTION name [<datatype> | CSTRING (int)
[, <datatype> | CSTRING (int) ...]]
RETURNS {<datatype&gt; [BY VALUE] | CSTRING (int)}
ENTRY_POINT "<entryname>"
MODULE_NAME "<modulename>" ;

Déclarons maintenant la fonction à Interbase
Declare external function f_TaxeTps float
  returns float by value
  entry_point 'TaxeTps' module_name 'brudflib.dll';

Il est maintenant possible d'utiliser cette fonction dans Interbase. Nous pouvons donc effectuer ce genre de
requête:
select f_TaxeTps(12.5) from rdb$database

L'exécution de cette requête retournera bien attendu 13,375. Les fonctions ainsi créées peuvent être ensuite utilisées dans n'importe quelle requête. Imaginons que nous avons une table logicielle avec un champ prix. Nous pourrions connaitre le coût du logiciel après taxe en faisant: select f_TaxeTps(prix) from logiciel.

String

Jusqu'à maintenant, nous avons utilisé que des nombres, nous allons maintenant utilisé des chaînes de caractères. Les strings utilisés par Delphi, lui sont propres. Interbase ne les utilise pas. Nous allons donc être obligés d'utiliser la fonction PChar.

function MixStr(Incoming: PChar):PChar;cdecl; export;
var OutStr, IncomingStr: string;
    i, j: Integer;
begin
  IncomingStr := StrPas(Incoming);
  OutStr := '';
  while Length(IncomingStr) > 0
  do begin
       i := Random(Length(IncomingStr) - 1) + 1;
       OutStr := OutStr + IncomingStr[i];
       Delete(IncomingStr, i, 1);
     end;
  StrPCopy(Incoming, OutStr);
  result := Incoming;
end;

Tappez ce code sous votre outil de gestion de bd:
Declare external function f_MixStr cstring(64)
  returns cstring(64)
  entry_point 'MixStr' module_name 'brudflib2.dll';
select f_MixStr('linux') from rdb$database 
Vous devriez voir que les lettres du mot Linux ont changé de position.

Suppression

Il suffit de taper drop external function nom_de_la_fonction pour la supprimer de la bd. Pour notre fonction, ça serait:

drop external function f_MixStr;

jeudi 28 novembre 2002

Requête paramétrée


REQUÊTE PARAMÉTRÉE

Les requêtes paramétrées servent à effectuer des requêtes SQL dynamiques. Le contenu de la requête peut donc varier. C'est très utile lorsque la requête dépend de la saisie d'un utilisateur venant d'un textbox, maskedit... De plus, ça permet d'économiser de la bande passante ce qui n'est pas le cas avec un dblookupbox.

Dans cet exemple nous avons une table client qui contient les champs: nom, prénom, adresse, téléphone, nopermis. Après avoir tapé un numéro de permis de conduire (nopermis) l'information du client sera affichée dans la propriété SQL d'un SQLquery, ibquery... nous entrons par exemple

select NOM, NOPERMIS, PRENOM from CLIENT where nopermis = :nopermis

Cette requêtant étant dans la propriété SQL du ibquery. Le «:» devant nopermis signifie que c'est un paramètre. Il ne reste plus qu'à mettre la valeur du paramètre comme étant notre textbox, maskedit... J'effectue souvent cette opération soit après avoir cliqué sur un bouton ou après être sortie du composant en question.

procedure TfrmLocation.MaskEditExit(Sender: TObject);
begin
  datamodule.query.Active := False;
  datamodule.query.Params[0].AsString := MaskEdit.Text;
  datamodule.query.Active := True;
end;
 

Il est possible d'utiliser la propriété ParamByName au lieu de Params. Le risque d'erreur est plus grand avec Params
lorsqu'on a quelques paramètres. Il est plus difficile de se rappeler ce qu'est le Params[0], Params[1]... que de faire
référence directement à son nom.
Le code ci-dessous permet de voir comment effectuer la même chose, mais avec un ibSQL.

ibsTmp.Close;
ibsTmp.SQL.Clear;
ibsTmp.SQL.Add('select NOM, NOPERMIS, PRENOM from CLIENT where nopermis = :nopermis');
ibsTmp.ParamByName('nopermis').AsString := MaskEdit.Text;
ibsTmp.ExecQuery;


Si la requête ci-dessus nous retournait une valeur, on pourrait affecter ces valeurs à des tlabel.
 
lblNom.Caption := ibsTmp.FieldByName('nom').AsString;
lblPrenom.Caption := ibsTmp.FieldByName('prenom').AsString;
lblNoPermis.Caption := ibsTmp.FieldByName('nopermis').AsString;

samedi 23 novembre 2002

Premier programme Interbase


PREMIER PROGRAMME INTERBASE

Nous allons créer une petite application qui utilisera Interbase, elle permettra de nous familiariser avec les composants de Delphi.

DataModule

Démarrez Interbase ensuite, démarrez Delphi, créez un nouveau projet ensuite ajoutez un datamodule




Au lieu de mettre nos composants de base de donnée sur chaque forme qu'on crée, il est possible de les mettre tous sur un DataModule. On centralise nos composants, il est ainsi beaucoup plus aisé de gérer le tout.
Je vous rappelle ici notre schéma de table



Nous déposerons donc tous nos composants liés à notre bd sur ce DataModule.
Déposons 5 DataSource, 1 ibDataBase,5 ibDataset et ibTransaction sur le DataModule.
Le DataModule devrait ressembler à ceci après avoir mis tous les composants nécessaires dessus et leur avoir donnée leur nom. J'ai mis qu'une transaction pour IbdVille et IbdProvince, puisque nous allons effectuer leur transaction sur la même fiche. Le nom du DataModule est dmGestion et sera sauvegardé sous le nom datamodule.





Lorsque nous ajouterons des formulaires au projet, pour avoir accès au composant de base de donnée, il faudra ajouter datamodule dans le unit du formulaire.




Information des composants

Composant Propriété Valeur
idbdLocation DatabaseName E:\divers\exemple\Exemple1.gdb
idbdLocation LoginPrompt False
idbdLocation Params user_name=SYSDBA password=masterkey
dsVille DataSet ibdVille
dsProvince DataSet ibdProvince
dsVehicule DataSet ibdVehicule
dsClient DataSet ibdClient
dsLocation DataSet ibdLocation

Composant Propriété Valeur
ibdVille Database idbdLocation
ibdVille SelectSQL select * from VILLE
ibdVille InsertSQL insert into VILLE (NOMVILLE) values ( :NOMVILLE)
ibdVille DeleteSQL delete from VILLE where NOMVILLE = :OLD_NOMVILLE
ibdVille Transaction ibtAutres

Composant Propriété Valeur
ibdProvince Database idbdLocation
ibdProvince SelectSQL select * from Province
ibdProvince InsertSQL insert into Province (NOMProvince) values ( :NOMProvince)
ibdProvince DeleteSQL delete from Province where NOMProvince= :OLD_NOMProvince
ibdProvince Transaction ibtAutres

Composant Propriété Valeur
ibdVEHICULE Database idbdLocation
ibdVEHICULE SelectSQL select * from VEHICULE
ibdVEHICULE InsertSQL insert into VEHICULE (NOVEHICULE, MARQUE, ANNEE, KM) values (:NOVEHICULE, :MARQUE, :ANNEE, :KM)
ibdVEHICULE DeleteSQL delete from VEHICULE where NOVEHICULE = :OLD_NOVEHICULE
ibdVEHICULE RefreshSQL Select NOVEHICULE, MARQUE, ANNEE, KM from VEHICULE where NOVEHICULE = :NOVEHICULE
ibdVEHICULE ModifySQL update VEHICULE set NOVEHICULE = :NOVEHICULE, MARQUE = :MARQUE, ANNEE = :ANNEE, KM = :KM where NOVEHICULE = :OLD_NOVEHICULE and

Composant Propriété Valeur
ibdClient Database ibdbLocation
ibdClient SelectSQL select * from CLIENT
ibdClient Transaction ibtClient
ibdClient InsertSQL insert into CLIENT (NOCLIENT, NOM, PRENOM, ADRESSE, NOVILLE, NOPROVINCE, TELEPHONE, NOPERMIS, DATEEXPERATION, CODEPOSTAL) values (:NOCLIENT, :NOM, :PRENOM, :ADRESSE, :NOVILLE, :NOPROVINCE, :TELEPHONE, :NOPERMIS, :DATEEXPERATION, :CODEPOSTAL)
ibdClient DeleteSQL delete from CLIENT where NOCLIENT = :OLD_NOCLIENT
ibdClient RefreshSQL Select NOCLIENT, NOM, PRENOM, ADRESSE, NOVILLE, NOPROVINCE, TELEPHONE, NOPERMIS, DATEEXPERATION, CODEPOSTAL from CLIENT where NOCLIENT = :NOCLIENT
ibdClient ModifySQL update CLIENT set NOCLIENT = :NOCLIENT, NOM = :NOM, PRENOM = :PRENOM, ADRESSE = :ADRESSE, NOVILLE = :NOVILLE, NOPROVINCE = :NOPROVINCE, TELEPHONE = :TELEPHONE, NOPERMIS = :NOPERMIS, DATEEXPERATION = :DATEEXPERATION, CODEPOSTAL = :CODEPOSTAL where NOCLIENT = :OLD_NOCLIENT

Composant Propriété Valeur
ibdLocation Database ibdbLocation
ibdLocation SelectSQL select * from Location
ibdLocation Transaction ibtLocation
ibdLocation InsertSQL insert into LOCATION (NOLOCATION, NOCLIENT, NOVEHICULE, PRIX, DATE_EMPRUNT, DATE_RETOUR) values (:NOLOCATION, :NOCLIENT, :NOVEHICULE, :PRIX, :DATE_EMPRUNT, :DATE_RETOUR)
ibdLocation DeleteSQL delete from LOCATION where NOLOCATION = :OLD_NOLOCATION and
ibdLocation RefreshSQL Select NOLOCATION, NOCLIENT, NOVEHICULE, PRIX, DATE_EMPRUNT, DATE_RETOUR from LOCATION where NOLOCATION = :NOLOCATION
ibdLocation ModifySQL update LOCATION set NOLOCATION = :NOLOCATION, NOCLIENT = :NOCLIENT, NOVEHICULE = :NOVEHICULE, PRIX = :PRIX, DATE_EMPRUNT = :DATE_EMPRUNT, DATE_RETOUR = :DATE_RETOUR where NOLOCATION = :OLD_NOLOCATION

Composant Propriété Valeur
ibtAutres DefaultDatabase idbdLocation
ibtVehicule DefaultDatabase idbdLocation
ibtClient DefaultDatabase idbdLocation
ibtLocation DefaultDatabase idbdLocation

Ajoutez dans la propriété params de chaque transaction le code suivant
read_committed
rec_version
nowait

Ce code permettra de voir les valeurs validées par les autres transactions

Générateur

Comme je l'ai mentionné plutôt, chaque table a un champ unique et qu'il ne peut être nul. Nous utilisons un générateur pour incrémenter cette valeurs. Puisque que nous connaissons pas la valeur des champs uniques devant être inséré, nous devons mettre la propriété required de ces champs à false. Dans l'évènement AfterOpen de chaque ibDataSet, faudra donc mettre la propriété de son cham unique à false.
Exemple
  • Sélectionner ibdVille
  • Aller dans l'inspecteur d'objet
  • Sélectionner l'onglet Évènement
  • Cliquer sur AfterOpen
  • Entrer le code suivant
DataSet.FieldByName('NOVILLE').Required := False;
L'opération prédente doit être effectué pour chaque ibDataset, il suffit de remplacer le champ NOVILLE, par le champ unique du dataset.

Formulaire

Villes et provinces

Nous allons maintenant créer les formlaires nécessaires pour l'application. La première forme sera celle qui permettra de naviguer entre les formes. Nous parlerons plus en détails du TDBLookupListBox dans le prochain chapitre.













Composant Propriété Valeur
DBLookupListBox1 Name dblcVille
dblcVille ListSource dmGestion.dsVille
dblcVille ListField NOMVILLE
dblcVille KeyField NOVILLE
DBLookupListBox2 Name dblcProvince
dblcProvince ListSource dmGestion.dsProvince
dblcProvince ListField NOMPROVINCE
dblcProvince KeyField NOPROVINCE
Edit1 Name edtVille
Edit2 Name edtProvince

Ajout et suppresion

Dans ce formulaire, nous n'avons pas utilisé de dbnavigator. De cette façon, on pourra apprendre commet on fait pour ajouter et effacé manuellement.
Avant d'ajouter, on va regarder s'il existe une donnée ayant la même valeur que l'on désire insérer. Cette opération sera effectuée grâce à la méthode locate du ibDataset. Le code suivant doit être ajouté dans l'évènement onclick du bouton ajouter (ville).

if not(dmGestion.ibdVille.Locate('NomVille',edtVille.Text,[])) then
  begin
    dmGestion.ibdVille.Append;
    dmGestion.ibdVille.FieldValues['NomVille'] := edtVille.Text;
    dmGestion.ibdVille.Post;
    edtVille.Text := '';
  end;

Append, insère un enregistrement vide. Ensuite on met la valeur du edit box dans le
champ Ville. Finalement, post envoit les données à Interbase.
Il faudra aussi ajouté ce code pour les provinces.
Maintenant pour effacer, il sélectionnant un item, ce dernier devient actif. Il reste plus qu'à faire appel à la méthode delete du ibDataset

if dblcVille.SelectedItem <> '' then
    dmGestion.ibdVille.Delete;

Transcation

Les transactions ont déjà été abordées précédemment. La transaction est déjà active. Lorsqu'on quitte la fiche, on tente de sauvegarder sinon on revient dans l'état initial. Il est préférable d'utiliser le CommitRetaining et le RollbackRetaining pour des raisons de performances puisque la transaction demeure ouverte. En employant commit et Rollback, la transaction doit se fermer et ouvrir de niveau ce qui pas nécessaire pour une simple mise à jour. Adapter ce code pour les autres transactions et ajouter le dans l'évènement OnClose de chaque form.

try
    dmGestion.ibtAutres.CommitRetaining;
  except
    dmGestion.ibtAutres.RollbackRetaining;
  end;

Véhicule

 


Composant Propriété Valeur
DBNavigator1 Name dbnVehicule
dbnVehicule DataSource dmGestion.dsVehicule
dbEdit1 Name dbeMarque
dbeMarque DataSource dmGestion.dsVehicule
dbeMarque DataField Marque
dbEdit2 Name dbeKm
dbeKm DataSource dmGestion.dsVehicule
dbeKm DataField Km
dbEdit3 Name dbeAnnee
dbeAnnee DataSource dmGestion.dsVehicule
dbeAnnee DataField Annee

Client

 


Champ persistent

On doit entrer un numéro de téléphone ainsi qu'un code postal pour un client. Ces données ont un certain format pour qu'il soit valide. C'est là que les champs persistent entre en action. Il est possible de
  • mettre des mises en forme particulière
  • d'appliquer des contraintes de saisi
  • d'effectuer des validations
En effectuant de telle opération au niveau client, on évite du traffic réseau et on personnalise d'avantage notre application.
Pour ajouter des champs persistent, il faut faire un clique droit de la souris sur notre ibdClient




Une image comme celle-ci apparaitra





Ensuite on fait un clique droit de la souris et on choisis l'option "Ajouter tous les champs"

Tous les champs sont maintenant ajoutés




Nous allons maintenant changer la mise en forme du champ téléphone.
  • Sélectionner le champ téléphone
  • Cliquer dans sa propriété EditMask
La fenêtre suivant s'ouvrira


La section du masque permet de définir les critères de saisi possible. Aller dans l'aide pour de plus ample renseignement sur les codes disponibles. Si vous ne voulez pas sauvegardé les caractère unilatéraux (dans l'exemple de tel caractère sont ()-, décochez la case "Enregistrer les caractères unilatéraux. Ensuite vous pouvez tester votre masque dans la section définie à cet effet. 

Faire de même pour le code postal.



Il est aussi possible d'effectuer des validations sur des champs, la propriété validate remplit cette fonction.

Composant Propriété Valeur
DBNavigator1 Name dbnClient
dbnClient DataSource dmGestion.dsClient
dbEdit1 Name dbeNom
dbeNom DataSource dmGestion.dsClient
dbeNom DataField NOM
dbEdit2 Name dbePrenom
dbePrenom DataSource dmGestion.dsClient
dbePrenom DataField PRENOM
dbEdit2 Name dbeAdresse
dbeAdresse DataSource dmGestion.dsClient
dbeAdresse DataField Adresse

TDBLookupComboBox

Je prends un petit moment pour vous expliquer d'avantage ce composant. Vous l'avez surement remarqué, dans la table client, il y a les champs noville et noprovince. Ces champs font naturellement références aux tables. Il faut donc trouver un moyen d'avoir le noville de la table Ville et d'envoyer cette valeur dans le noville de Client. Il est possible de le faire avec les TDBLookupComboBox.




On remplira le TDBLookupComboBox comme d'habitude.

Composant Propriété Valeur
DBLookupComboBox1 Name dblcVille
dblcVille ListSource dmGestion.dsVille
dblcVille ListField NOMVILLE
dblcVille KeyField NOVILLE

La propriété ListSource permet d'identifier la source de donnée qui seront affiché dans le contrôle. Nous utilisons le ibDataSet de Ville qui fait référence à notre table Ville.
La propriété ListField identifie le champ qui sera affiché dans le DBLookupComboBox. Nous affichons le nom de la ville. Sachez qu'il est aussi possible d'afficher plus valeur, il suffit de les séparer par un ";".

La propriété KeyField identifie le champ auquel sa valeur sera envoyée au datafield. Ce champ a la valeur NoVille car dans notre table Client nous y faisons référence grace à noville.

Composant Propriété Valeur
dblcVille DataSource dmGestion.dsClient
dblcVille DataField NoClient
DataSource représente la table qui va reçevoir la valeur du champ noville (propriété KeyField). dans notre cas c'est le dataset Client.

Finalement le champ DataField, représente le champ du dataset Client qui reçevra la valeur de la propriété KeyFiel. Cette valeur est noclient

Location



Un programme montrant les acquis est disponible ici.
Quelques éléments du programme sont manquants, vous pouvez les ajouter et compléter votre formation.