Peupler l'instance

Créer une table

dbmcli -d mails -u dbm,passwd_dbm
dbmcli> sql_connect jm,jm_passwd
dbmcli> sql_execute CREATE TABLE courriel (
msgid VARCHAR(80) NOT NULL PRIMARY KEY CONSTRAINT ct_msgid CHECK msgid LIKE '<%>', 
fils_de varchar(80) NOT NULL DEFAULT '<head>' CONSTRAINT ct_fils_de CHECK fils_de LIKE '<%>',
maildate VARCHAR(40) NOT NULL,
mailfrom VARCHAR(60) NOT NULL,
subject VARCHAR(128) NOT NULL,
body VARCHAR(7000) NOT NULL),
FOREIGN KEY ct_fk_fils_de (fils_de) REFERENCES courriel (msgid) ON DELETE SET DEFAULT
)
dbmcli> sql_release

J'ai mis le texte sur plusieurs lignes pour faire plus propre mais dbmcli n'aime pas... Le caractère de liaison de lignes de dbmcli est le /

En fait on s'aperçoit vite de deux choses:

Que ce serait une bonne idée de mettre les commandes dans des scripts, ça permettrait de garder une trace et de s'en resservir;
Qu'à priori l'user défini comme précédemment n'a pas le droit de créer de table (c'est un user de classe STANDARD).

Donc on va tester un nouvel 'sql statement' pour donner à l'user en question la classe RESOURCE:

dbmcli -d mails -u dba,passwd_dba
dbmcli> sql_execute ALTER USER jm RESOURCE NOT EXCLUSIVE
dbmcli> sql_release

Et puis on va mettre le CREATE TABLE dans un script, mais un script loadercli qui sera de syntaxe plus facile et on l'appelera ainsi (le script s'appelle courriel.ini.loader):

loader -d mails -u user,user_passwd -b courriel.ini.loader

// rappel: c'est la marque du commmentaire loadercli
// courriel.ini.loader
// lodercli script de creation & chargement de table
CREATE TABLE courriel
(
    msgid VARCHAR(80) NOT NULL PRIMARY KEY CONSTRAINT ct_msgid CHECK msgid LIKE '<%>',
    fils_de VARCHAR(80) NOT NULL DEFAULT '<head>' CONSTRAINT ct_fils_de CHECK fils_de LIKE '<%>',
    maildate VARCHAR(40) NOT NULL,
    mailfrom VARCHAR(80) NOT NULL,
    subject VARCHAR(128) NOT NULL,
    body VARCHAR(7000) NOT NULL,
    FOREIGN KEY ct_fk_fils_de (fils_de) REFERENCES courriel (msgid) ON DELETE SET DEFAULT
)

Voir 4 pour des infos loadercli et d'autres liens sur cette doc.

Descriptions

Pour obtenir des renseignements sur la table créée (structure, colonnes, accès...) il faut faire une requête sur les tables système (System Tables). Voir la doc, il y en a pas mal. Par exemple pour connaître les noms et types de colonnes de la table courriel créée, sous une section ireport.py:

==> SELECT COLUMNNAME,DATATYPE FROM DOMAIN.COLUMNS WHERE TABLENAME='PRODUCTEUR'
|COLUMNNAME|DATATYPE|
+----------+--------+
|IDPRODUCTEUR|SMALLINT|
|NOMPRODUCTEUR|VARCHAR|
|TAILLEPRODUCTEUR|FIXED|
|IDUNITEMESURE|VARCHAR|
|DESCPRODUCTEUR|VARCHAR|

C'est quand même un mauvais exemple car ireport implémente une commande table NOMTABLE qui envoie la description (sqlcli aussi, mais ce n'est pas la même instruction)

Attention: le serveur stocke les noms en majuscules donc les clauses de recherche doivent porter sur des nom en majuscules. Par exemple:

==> SELECT TABLENAME FROM DOMAIN.TABLES WHERE OWNER = 'jm'

Ne donnera rien.

Bien entendu il y a des droits d'accès sur certaines info: user jm ne peut interroger
la table CONNECTEDUSERS par exemple.

Les tables systèmes permettent d'obtenir pas mal d'infos sur ce qui ce passe. La lecture de la doc à ce sujet est donc encouragée...

Par exemple pour les verrouillages, utile pour savoir si une transaction verrouille un objet:

==> UPDATE PRODUCTEUR SET TAILLEPRODUCTEUR=222.0 WHERE IDPRODUCTEUR=11
1
==> SELECT TRANSCOUNT,USERNAME,LOCKMODE,LOCKSTATE FROM DOMAIN.LOCKS
|TRANSCOUNT|USERNAME|LOCKMODE|LOCKSTATE|
+----------+--------+--------+---------+
|                 656|JM|row_exclusive|write|
==> commit
==> SELECT TRANSCOUNT,USERNAME,LOCKMODE,LOCKSTATE FROM DOMAIN.LOCKS
|TRANSCOUNT|USERNAME|LOCKMODE|LOCKSTATE|
+----------+--------+--------+---------+
<<<End of Cursor>>>

Modifications

Les modifications sur les tables sont effectuées à l'aide de l'instruction ALTER TABLE. Alors j'en mets quelques unes, puisque je les ai utilisées, mais le manuel est là pour y puiser les différentes syntaxes d'alter table.

ALTER TABLE COURRIEL DROP DEFAULT
ALTER TABLE COURRIEL COLUMN FILS_DE ADD DEFAULT '<parent>'
ALTER TABLE COURRIEL MODIFY MAILFROM VARCHAR(80) NOT NULL
ALTER TABLE COURRIEL COLUMN FILS_DE ALTER DEFAULT '<head>'

Pour ajouter une contrainte référentielle entre fils_de et msgid, et une contrainte d'intégrité sur les valeurs de fils_de:

ALTER TABLE COURRIEL ADD FOREIGN KEY ct_fk_fils_de (FILS_DE) REFERENCES COURRIEL (MSGID)
    ON DELETE SET DEFAULT
ALTER TABLE COURRIEL ADD CONSTRAINT ct_fils_de CHECK fils_de LIKE '<%>'

Enfin, moi je vous dis tout ça car il vaut mieux charger ses tables sans les contraintes, puis les appliquer une par une (en commençant par les tables les plus externes) pour repérer les erreurs et les archiver. On verra plus loin qu'on peut créer une table, la charger, y appliquer des contraintes référentielles en utilisant loadercli et le même scipt.

Trigger

Voilà un trigger qui sert à historiser un enregistrement en cas d'update:

// historiquegarantieintrant.trigger.loader
// Un trigger pour faire l'historisation dans
// HISTORIQUEGARANTIEINTRANT
//
CREATE TRIGGER TGU_HISTORIQUE FOR GARANTIEINTRANT AFTER UPDATE EXECUTE
(
    TRY
        INSERT INTO JM.HISTORIQUEGARANTIEINTRANT VALUES
        (
            :OLD.IDGARANTIE,
            :OLD.IDINTRANT,
            :OLD.IDETABLISSEMENT,
            DATE,
            :OLD.DEBUTGARANTIE,
            :OLD.FINGARANTIE
        );
    CATCH
        IF $RC <> 0 THEN STOP ($RC, 'Historisation de garantie impossible');
)

C'est à lancer avec loadercli.
Le trigger récupère les anciennes valeurs de l'enregistrement (c'est à dire celles juste avant la mise à jour) pour les insérer dans une autre table. Notez la syntaxe pour obtenir la date du moment à partir de la valeur DATE (On aurait pu utiliser TIMESTAMP, selon le même principe). $RC représente le code de retour du trigger, utile quand il y a un problème...
Voir plus bas, les procédures, pour quelques remarques sur la syntaxe.

Procédures

Une procédure constitue une requête précompilée dans le serveur. Par conséquent l'accès est plus rapide puisqu'une partie du travail est déjà fait à l'appel de la procédure. Voici deux exemples de procédures qui seront utilisées via odbc et qui retournent chacune un curseur.

On trouve pas mal de doc sur la création des procédures stockées dans les archives de la liste sapdb-general.

// procedure pour retourner subject et body
// a partir de msgid
// message.dbproc.loader
//
CREATE DBPROC pc_message (IN id VARCHAR(80))
RETURNS CURSOR AS
TRY
    $CURSOR = 'MESSAGE_CURS';
    DECLARE :$CURSOR CURSOR FOR SELECT mailfrom, subject, body
        FROM JM.COURRIEL
        WHERE msgid = :id;
CATCH
    IF ($rc) <> 100 THEN STOP ($rc, 'Erreur pc_message');

Cette procédure demande deux paramètres en entrée (IN), définit un curseur que je nomme MESSAGE_CURS auparavant qui va permettre l'accès au résultat de la requête.

La déclaration TRY....CATCH demande à la procédure d'exécuter ce qu'il y a après le CATCH en cas d'erreur SQL. Ici le code d'erreur 100 correspond à Row not found, on appelle donc en cas d'erreur qui serait différente d'un résultat inexistant.

Il faut remarquer quelques aspect de la syntaxe:

Il faut donner le nom complet de la table, sous la forme PROPRIÉTAIRE.NOM_TABLE;
On appelle les paramètres dans la requête avec la notation :variable

Puis on peut donner à un utilisateur le droit d'exécuter cette procédure avec un grant (voir 3.6).
GRANT EXECUTE ON pc_message TO INVITE

J'ai écrit ce script pour loadercli, qui est plus pratique à utiliser pour lancer de longues commandes sql puisqu'il peut lire ligne à ligne en concaténant (ne pas oublier le séparateur ; quand c'est nécessaire).

On envoie la commande au serveur à travers loadercli ainsi:
loadercli -d base -u user,passwd -b fichier

Une autre petite, pour la route, qui renvoie également un curseur, en exécutant ce que la doc sapdb nomme un named_select_statement: une requête nommée qui renvoie une table de résultat nommée. De plus la procédure suivante utilise un peu de dynamic SQL puisque la requête elle même change en fonction des paramètres envoyés.

// script de creation de procedure avec loadercli
// listing.dbproc.loader
//
CREATE DBPROC pc_listing (IN valeur VARCHAR(40), IN type_requete VARCHAR(10))
RETURNS CURSOR AS
VAR
    stmt char(256);
    wherestr char(100);
    orderstr char(21);
    ValeurQuotee char(48);

$CURSOR = 'LISTING_CURS';
orderstr = ' ORDER BY fils_de ASC';
SET valeurQuotee = '''' || valeur || '''';

stmt = 'SELECT LISTING_CURS(msgid, fils_de, subject) FROM JM.COURRIEL WHERE' ;
CASE
    WHEN type_requete = 'sujet' THEN wherestr = ' courriel.subject LIKE ' || valeurQuotee ;
    WHEN type_requete = 'corps' THEN wherestr =  ' body LIKE ' || valeurQuotee ;
    WHEN type_requete = 'all' THEN wherestr = ' body LIKE ' || valeurQuotee ||
                                  ' OR ' || ' subject LIKE ' || valeurQuotee ;
    ELSE STOP (-29000, 'Type de requete inconnu');
END CASE;
stmt = stmt || wherestr || orderstr ;
EXECUTE stmt;
IF ($rc <> 0) AND ($rc <> 100) THEN STOP ($rc, stmt);

Premier changement par rapport à la procédure précedente: on introduit des variables avec le mot clé VAR. $CURSOR n'est pas déclarée car c'est une variable interne, mais elle est définie plus loin, ainsi que la chaine orderstr.

La chaine valeurQuotee est définie en utilisant un opérateur de concaténation de chaines, le ||. De plus je concatène des guillemets simples à la valeur envoyée, avant et après. Ceux-ci se transforment en séquence de quatre '. En effet il en faut deux pour encadrer la chaine qui est concaténée et deux pour former la chaine ' car le guillemet simple se protège avec un autre guillemet simple.

Puis j'utilise la déclaration CASE...WHEN...THEN...ELSE...END CASE pour concaténer à la chaine stmt la chaîne de traitement désirée. Si aucun WHEN n'est possible c'est le ELSE qui est exécuté. L'instruction EXECUTE finit le travail. En cas d'erreur je demande l'arrêt de la procédure et l'affichage du code de retour de l'EXECUTE et de la requête demandée.

J'ai écrit deux fichiers pour ces deux procédures mais il aurait été possible de tout mettre en un seul, la séparation des deux commandes devant être effectuée au moyen d'une ligne commentée (//) au moins, dans le fichier appelé par le loader.

Remarque importante: A partir de la version 7.5.1 cette syntaxe utilisant les named_select_statements ne sera plus supportée.
On ne pourra plus écrire:
SELECT NOMCURSEUR(COL1, COL2....) FROM TABLE mais il faudra utiliser le DECLARE CURSOR
ce qui donnera, pour du SQL dynamique:

set $CURSOR = 'MONCURSEUR';
stmt = 'DECLARE ' || $CURSOR || ' CURSOR FOR SELECT col1, col2...,coln FROM OWNER.TABLE'

Pour détruire une procédure:
dbmcli -d base -uSQL user,passwd -c "sql_execute DROP DBPROC nom_procédure"

Pour appeler une procédure:
dbmcli -d base -uSQL user,passwd -c "sql_execute CALL nom_procédure(param1, ...)"

La seule chose c'est que si l'on appelle une procédure sous dbmcli ou loadercli, et que ça fonctionne ..., ça renvoie 'OK'...et c'est pas cool ! Voir à 8.9 pour l'utilisation de ces procédures via ODBC. En revanche sqlcli permet d'obtenir le résultat de la procédure si c'est un curseur.

On ne s'en lasse pas, en v'la une autre que j'ai commise pour mon appli en Python (un langage d'enfer d'ailleurs) qui n'utilise pas de curseur.

// pc_insert
// procedure d'insertion et verification d'enregistrement
// teste un SELECT
// si un enregistrement est trouve, mise a jour si la valeur donnee dans nom est
// differente de celle trouvee oldName par le SELECT
// sinon, insertion de nom et adresse
CREATE DBPROC pc_insert (IN nom VARCHAR(128), IN adresse VARCHAR(128)) AS
VAR oldName CHAR(128);

SELECT nom FROM JM.ADRESSES WHERE adresse = :adresse;
FETCH INTO :oldName;
CASE
    WHEN $RC = 0 THEN
        IF oldName <> nom THEN
            BEGIN
                UPDATE JM.ADRESSES SET nom = :nom WHERE adresse = :adresse;
            END;
    WHEN $RC = 100 THEN
        BEGIN
            INSERT INTO JM.ADRESSES VALUES (:nom, :adresse);
        END;
    ELSE STOP ($RC, 'Erreur procedure pc_insert');
END CASE;

Ça fait un SELECT et, si un résultat est trouvé, le met dans la variable OldName. Un test sur cette variable par rapport au nom trouvé et on fait un UPDATE, ou rien du tout. Si rien n'est trouvé c'est alors un INSERT qui est réalisé.

Fonctions utilisateurs

MaxDB 7.5 permet de créer des fonctions personnelles, dont la syntaxe est proche de celle des procédures. Voilà pour une fonction qui formate la date du format INTERNAL AAAAMMJJ au format JJ-MM-AAAA

CREATE FUNCTION DATEFR (dateus DATE) RETURNS VARCHAR AS
    VAR sep CHAR(1);
    SET sep = '-';
    TRY
        RETURN (SUBSTR(dateus,7,2) & sep & SUBSTR(dateus,5,2) & sep & SUBSTR(dateus,1,4));
    CATCH
        RETURN '';

C'est tout bête. L'esperluette (&) est un opérateur de concaténation de chaînes.

L'utilisation des fonctions est implémentée, mais n'est pas conseillée avant la version 7.5.1. Ça fonctionne, du moins les deux dont je me sers mais les erreurs ne pardonnent pas et m'ont fait crasher l'instance.

Divers

Domaine

On peut créer un domaine, par exemple sur un type de donnée souvent utilisé:
CREATE DOMAIN PRIX FIXED(9,3)
Ça se passe de commentaire...

Synonyme

Pour utiliser des noms plus évocateurs, ou bien pour simplifier la dénomination <propriétaire>.<nom_table>, il est possible d'utiliser des synonymes:

CREATE SYNONYM SOCIETE FOR ETABLISSEMENT
CREATE PUBLIC SYNONYM ES FOR JM.ENTREESORTIE

Commentaires

Il est possible d'enregistrer des commentaires sur des objets de la base, commentaires qui seront stockés dans les tables système:
COMMENT ON INTRANTPRODUIT IS 'Correspondance entre l''intrant et le _producteur_'

==> SELECT COMMENT FROM DOMAIN.TABLES WHERE TABLENAME='INTRANTPRODUIT'
|COMMENT|
+-------+
|Correspondance entre l'intrant et le _producteur_|
==>

Notez l'utilisation du ' (apostrophe) qui protège l'apostrophe véritable du mot.

jean-michel OLTRA 2004-07-06