Balayage de tables avec le même code SQL

[Just-Soft]

Salut !

pour compter les lignes d'une tables avec un critère donné comme la date p.e, c'est facile de le faire :

Code :

1
2
SELECT count(*) nb_ligne FROM tb_energie
WHERE tb_energie.date_mois =:date_bilan

mon problème est que je dois vérifier une dizaine de table avec le même critère pour savoir si la MAJ a été faite. Il me parait que cela soit possible dans une PS mais je ne sais par où commencer. Je dois en 1er sélectionner mes tables en mettant leurs noms dans un vecteur p.e (est-ce possible en SQL ?) puis faire une boucle pour avoir en sortie le couple (nom_table, nbr_ligne) à la date d'entrée saisie ?

quelqu'un peut-il me guider et m'orienter ?

Merci par avance.

[Ph. B.]

Bonjour,
A votre place, je procèderais comme suit :
Je créerai une table contenant 3 colonnes (nom de table, nom de colonne, valeur du paramètre):

Code :

1
2
3
4
5
CREATE TABLE CHECK_TABLE (
    TABLE_NAME   VARCHAR(31),
    COLUMN_NAME  VARCHAR(31),
    PARAM_VALUE  VARCHAR(31)
);

La table est alimentée par les éléments que l'on veut contrôler.
Ensuite, je créerai la procédure stockée suivante qui parcours la table et exécute une instruction SQL construite dynamiquement :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR ALTER PROCEDURE SP_CHECK_TABLES 
RETURNS (TableName VARCHAR(31), NbRows INTEGER)
AS
  DECLARE VARIABLE SqlStmt VARCHAR(128);
  DECLARE VARIABLE ColumnName VARCHAR(31);
  DECLARE VARIABLE ParamValue VARCHAR(31);
BEGIN
  FOR SELECT TABLE_NAME, COLUMN_NAME, PARAM_VALUE FROM CHECK_TABLE
      INTO :TableName, :ColumnName, :ParamValue DO
  BEGIN
    SQL = 'SELECT COUNT(*) FROM ' || TableName ||
          ' WHERE ' || ColumnName || ' = ' || ParamValue;
    EXECUTE STATEMENT SqlStmt INTO :NbRows;
    SUSPEND;
  END
END

A adapter...

[Just-Soft]

Salut Ph. B. et merci

mais reste quand même assez lourd comme process puisqu'on doit à chaque fois lui renseigner le nom de la table ! aurait-il un moyen de parcourir toutes les tables de la bdd et d'exécuter la ps pour chacune des tables puis avoir le résultat dans un fichier à la sortie ?!

je sais que j'exagère mais c'est ce que je veux avoir en finalité ....

merci encore et bienvenue à toute autre idée

[SergioMaster]

un début de réponse :
FAQ : Comment obtenir une liste des tables, vues et colonnes d'une base de données Firebird ?

donc avec une la procédure modifiée de ph.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE OR ALTER PROCEDURE SP_CHECK_TABLES 
(COLONNE VARCHAR(31), Paramvalue  VARCHAR(31))
RETURNS (TableName VARCHAR(31), NbRows INTEGER)
AS
  DECLARE VARIABLE SqlStmt VARCHAR(128);
  DECLARE VARIABLE ColumnName VARCHAR(31);
  DECLARE VARIABLE ParamValue VARCHAR(31);
BEGIN
  FOR SELECT f.RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS f
JOIN RDB$RELATIONS r ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
AND f.RDB$FIELD_NAME=:COLONNE
AND r.RDB$VIEW_BLR IS NULL
AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0);
INTO :TableName 
DO
  BEGIN
    SQL = 'SELECT COUNT(*) FROM ' || TableName ||
          ' WHERE ' || COLONNE || ' = ' || ParamValue;
    EXECUTE STATEMENT SqlStmt INTO :NbRows;
    SUSPEND;
  END
END

PS . Finalement j'aurais pas du supprimer mon premier POST j'y proposais déjà le EXECUTE STATEMENT

[Ph. B.]

Bonjour,

Envoyé par Just-Soft

mais reste quand même assez lourd comme process puisqu'on doit à chaque fois lui renseigner le nom de la table ! aurait-il un moyen de parcourir toutes les tables de la bdd et d'exécuter la ps pour chacune des tables puis avoir le résultat dans un fichier à la sortie ?!

Le problème lui même n'est pas aussi simple qu'il n'y parait...
Sinon, parcourir toutes les tables pourquoi pas ?
Mais comment définir la colonne qui va servir de critère de filtre ? en fonction de son type ? et si il y en a plusieurs, laquelle prendre ?
La valeur du filtre est-elle unique pour toutes les tables ?

[Just-Soft]

Envoyé par Ph. B.

Bonjour,
Le problème lui même n'est pas aussi simple qu'il n'y parait...
Sinon, parcourir toutes les tables pourquoi pas ?
Mais comment définir la colonne qui va servir de critère de filtre ? en fonction de son type ? et si il y en a plusieurs, laquelle prendre ?
La valeur du filtre est-elle unique pour toutes les tables ?

Merci ....
le chemin est assez encourageant

Envoyé par Ph. B.

Bonjour,
Parcourir toutes les tables pourquoi pas ? mais comment ?

Envoyé par Ph. B.

Bonjour,
Mais comment définir la colonne qui va servir de critère de filtre ? en fonction de son type ? et si il y en a plusieurs, laquelle prendre ?

La colonne en question est de type date, et fort heureusement que je n'ai qu'un seul champ date dans chacune des tables

Envoyé par Ph. B.

Bonjour,
La valeur du filtre est-elle unique pour toutes les tables ?

oui elle l'est

espérant que je suis assez clair sur les points soulevés

[SergioMaster]

une jonction supplémentaire dans ce cas là avec RDB$FIELDS permettra d'obtenir (et donc de sélectionner le nom) le type de champ

RDB$FIELD_TYPE
12 ->DATE
35 ->TIMESTAMP

Donc , toutes les tables qui ont une colonne date

Code :

1
2
3
4
5
6
7
 
SELECT f.RDB$RELATION_NAME, f.RDB$FIELD_NAME,T.RDB$FIELD_TYPE
FROM RDB$RELATION_FIELDS f 
JOIN RDB$FIELDS t ON f.RDB$FIELD_SOURCE=t.RDB$FIELD_NAME AND t.RDB$FIELD_TYPE IN (12,35)
JOIN RDB$RELATIONS r ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
AND r.RDB$VIEW_BLR IS NULL
AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0)

[Just-Soft]

Envoyé par SergioMaster

une jonction supplémentaire dans ce cas là avec RDB$FIELDS permettra d'obtenir (et donc de sélectionner le nom) le type de champ

RDB$FIELD_TYPE
12 ->DATE
35 ->TIMESTAMP

Donc , toutes les tables qui ont une colonne date

Code :

Salut
j'ai loupé ton poste d'avant celui-là
je cours vite voir et l'adapter à ma situation

merciiiiiiiiiiiiiii

[Just-Soft]

Re,

je viens de faire quelques modif sur le code; très peu en réalité

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE PROCEDURE SP_CHECK_TABLES (
    date_bilan date,
    nom_table varchar(31))
returns (
    n_table varchar(31),
    c_date varchar(12),
    nbrows integer)
AS
declare variable sqlstmt varchar(128);
BEGIN
    FOR SELECT f.RDB$RELATION_NAME, f.RDB$FIELD_NAME
 
    FROM RDB$RELATION_FIELDS f
        JOIN RDB$RELATIONS r ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
        AND f.RDB$FIELD_NAME =: date_bilan
        AND r.RDB$VIEW_BLR IS NULL
        AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0)
 
    INTO
        :n_table,
        :c_date
DO
  BEGIN
    SqlStmt = 'SELECT COUNT(*) FROM ' || upper(n_table) ||
          ' WHERE ' || :date_bilan || ' = ' || c_date;
    EXECUTE STATEMENT SqlStmt
    INTO
        :nbRows;
    SUSPEND;
  END
END

mais à l'exécution j'ai cette erreur :

overflow occured during data type conversion.
conversion error from string "RDB$PAGE_NUMBERe

1. dans la PS on n'a jamais utilisé implicitement le RDB$PAGE_NUMBER ?
2. le message parle de problème de conversion, j'ai suivis toutes les déclarations avec leur type et ça m'a l'air correct à moins que j'ai encore loupé quelque chose ou que j'ai mal compris le message d'erreur !!

[SergioMaster]

étonnant RDB$PAGE_NUMBER ne peut venir que d'un table système !
il faudrait d'abord faire un essai de la SELECT pour voir les Colonnes et Tables retournées avant de mettre le Execute Statement .

Les essais (post 4 et 7 ) que j'ai fait ne portait, bien sur , que sur la partie SELECT , sur une BDD perso, et ce avec Firebird 2.1 . Pour le peu que j'ai pu en juger elles étaient , si ce n'est optimum , correctes

[Ph. B.]

Bonjour,

Envoyé par Just-Soft

je viens de faire quelques modif sur le code; très peu en réalité

Euh, vous êtes sur ?
Je vois un nom de paramètre entrant inutilisé (et inutile) : nom_table
Firebird n'est pas sensible à la casse upper est inutile pour les noms de table ou colonne par contre trim, bien que facultatif, évite de trimbaler les espaces inutiles...
Mais revenons à nos moutons, je repars de la requete SQL de @SergioMaster pour trouver pour chaque table son nom et la colonne qui nous intéresse et cela donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE OR ALTER PROCEDURE NEW_PROCEDURE(Date_Bilan DATE)
RETURNS (
    Nom_Table VARCHAR(31),
    Nom_Colonne VARCHAR(31),
    Nb_Lignes INTEGER)
AS
DECLARE VARIABLE SqlStmt VARCHAR(128);
BEGIN
  FOR SELECT f.RDB$RELATION_NAME, f.RDB$FIELD_NAME
      FROM RDB$RELATION_FIELDS f
      INNER JOIN RDB$FIELDS t
        ON f.RDB$FIELD_SOURCE = t.RDB$FIELD_NAME
        AND t.RDB$FIELD_TYPE = 12 -- 12 => DATE, 35 => TIMESTAMP
      INNER JOIN RDB$RELATIONS r
        ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
        AND r.RDB$VIEW_BLR IS NULL
        AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0)
  INTO
    :Nom_Table,
    :Nom_Colonne
  DO
  BEGIN
    -- TRIM() reste facultatif
    -- Des quotes sont ajoutées pour la valeur Date_Bilan
    -- pour éviter une erreur à l'exécution
    SqlStmt = 'SELECT COUNT(*) FROM ' || TRIM(Nom_Table) ||
              ' WHERE ' || TRIM(Nom_Colonne) || ' = ''' || Date_Bilan || '''';
    EXECUTE STATEMENT SqlStmt INTO :nb_lignes;
    SUSPEND;
  END
END

Maintenant, vous avez tout, bonne continuation...

[Just-Soft]

Envoyé par Ph. B.

Bonjour,
Euh, vous êtes sur ?
Je vois un nom de paramètre entrant inutilisé (et inutile) : nom_table

ouiiiiiiiiiiiii ce paramètre

bref, un vif remerciement à vous deux car je viens de tester et c'est impeccable.

reste une chose si possible, afin d'améliorer la lisibilité des résultats j'ai pensé à mettre des slash à la place des "0" est-ce faisable avec un case when end ....?

[Ph. B.]

Envoyé par Just-Soft

reste une chose si possible, afin d'améliorer la lisibilité des résultats j'ai pensé à mettre des slash à la place des "0" est-ce faisable avec un case when end ....?

La fonction REPLACE(Chaine, Cherche, Remplace) doit convenir, non ?
REPLACE( '1209080420', '0', '/') => '12/9/8/42/'
l'aurait d'ailleurs indiqué...

Et n'oubliez pas de cliquer sur

[SergioMaster]

le seul zéro que je vois serait le 0 résultat du count , à moins qu'il y en ait dans les noms de colonnes ou de table ?
cependant j'éviterais de remplacer le 0 par un / (trop de confusion possible avec les dates) , plutôt par un '*' ou '***'

De toutes façon avec le replace pour les noms et un case pour les quantités cela devrait le faire

Code :

1
2
3
4
5
6
7
8
SELECT 
 REPLACE(Nom_Table,'0','/') AS NOMTABLE ,
 REPLACE(Nom_colonne,'0','\') as NOMCOLONNE,
 CASE nb_lignes 
   WHEN 0 THEN '*'
   else nb_lignes
end  AS NBLIGNE
FROM  NEW_PROCEDURE('01.01.2012')

[Just-Soft]

Envoyé par Ph. B.

La fonction REPLACE(Chaine, Cherche, Remplace) doit convenir, non ?
REPLACE( '1209080420', '0', '/') => '12/9/8/42/'
l'aurait d'ailleurs indiqué...

Et n'oubliez pas de cliquer sur

c'est pas ce que je voulais dire (mal exprimé de ma part)
mais quelque chose du genre :

Code :

1
2
3
case
      when (lbr_lignes = 0)  then '/'
end nbr_lignes

j'ai essayé de l'intégré dans le code mais j'ai pas réussis à avoir le bon résultat.

[SergioMaster]

Dans le code de la procédure stockée
je rappelle que en sortie : Nb_Lignes INTEGER

il serait totalement improductif de changer nb_lignes en Varchar

je suggère plutôt la syntaxe de mon dernier post pour selectionner les données de la procédure :

Code :

1
2
3
4
5
6
7
SELECT 
 NOM_TABLE ,NOM_COLONNE,
 CASE nb_lignes 
   WHEN 0 THEN '*'
   else nb_lignes
end  AS NBLIGNE
FROM  NEW_PROCEDURE(<date>)

[Ph. B.]

Envoyé par Just-Soft

Code :

j'ai essayé de l'intégré dans le code mais j'ai pas réussis à avoir le bon résultat.

Effectivement, j'avais mal compris.
Si vous voulez avoir '/' plutôt que 0 dans le paramètre Nb_Lignes, il faut déjà changer son type INTEGER vers VARCHAR(10) par exemple puis rajouter dans le code avant l'instruction SUSPEND;:

Code :

1
2
IF (nb_lignes = '0') THEN
  nbr_lignes = '/';

[Just-Soft]

Bonjour !

en fin de compte j'ai opté pour un having (count(*) > 0) pour avoir les bon résultat sans me soucier du traitement du 0.

seule chose qui me manque, c'est tester sur la colonne date, voici le code modifié (réellement ) mais qui me donne pas le résultat attendu.

Pour être plus clair, j'ai repassé toutes les tables, et j'ai vu qu'il y'avait d'autre champs type date, alors j'ai pensé à mettre un filtre avec les noms de champs qui m'intéressent.

voici le dernier code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE PROCEDURE SP_CHECK_TABLES (
    date_bilan date)
returns (
    nom_table varchar(31),
    nom_colonne varchar(31),
    nb_lignes varchar(10))
AS
declare variable sqlstmt varchar(128);
BEGIN
  FOR SELECT f.RDB$RELATION_NAME, f.RDB$FIELD_NAME
 
      FROM RDB$RELATION_FIELDS f
 
      INNER JOIN RDB$FIELDS t
        ON f.RDB$FIELD_SOURCE = t.RDB$FIELD_NAME
        AND t.RDB$FIELD_TYPE = 12 -- 12 => DATE, 35 => TIMESTAMP
      INNER JOIN RDB$RELATIONS r
        ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
        AND r.RDB$VIEW_BLR IS NULL
        AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0)
/* pour ne filtrer que sur les champs date dont le nom dans la base est */
/* date_bilan ou date_mois */
     WHERE
        (
            ( t.RDB$FIELD_NAME = 'Date_Bilan') OR (t.RDB$FIELD_NAME = 'Date_Mois')
        )
 
  INTO
    :Nom_Table,
    :Nom_Colonne
  DO
  BEGIN
    SqlStmt = 'SELECT DISTINCT COUNT(*) FROM ' || TRIM(Nom_Table) ||
              ' WHERE (' || TRIM(Nom_Colonne) || ' = ''' || :Date_Bilan || ''')' ||
              ' HAVING(count(*) > 0) ';
    EXECUTE STATEMENT SqlStmt INTO :nb_lignes;
    SUSPEND;
  END
END

[SergioMaster]

Envoyé par Just-Soft

La colonne en question est de type date, et fort heureusement que je n'ai qu'un seul champ date dans chacune des tables

Sergio@Just-Soft

Trêve de plaisanterie
- En quoi le résultat est-il incorrect ?
- As-tu songé faire une trace des SQLStmt pour vérifier ?

pour faire une Trace ?
créer une table , basique
CREATE TABLE TRACK_SQLSTMT (TEXTE VARCHAR(128))
nota , il serait bien d'y ajouter un TimeStamp

Ajouter
INSERT INTO TRACK_SQLSTMT VALUES (:SQLSTMT);
dans la PS, après son 'initialisation'

[Just-Soft]

Envoyé par SergioMaster

Sergio@Just-Soft

Trêve de plaisanterie
- En quoi le résultat est-il incorrect ?

le résultat est devenu incorrecte suite à l'ajout du where !

je ne vois pas en quoi le trace pourrait m'aider dans ce cas ?