Parfois, on a besoin en SQL de remonter une liste de nombre pour combiner avec des tables ou pour démultiplier un résultat.
J'ai trouvé ce code de @slartidan sur service Stack : https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2
ORDER BY 1
Le code est simple et permet d'avoir une table avec une colonne contenant les entiers entre @userinput1 et @userinput2.
C'est simple clair, facile a écrire facile à adapter, élégant.
Rien à dire de plus !
01 avril 2020
28 mars 2020
Arduino : Mettre à l'heure un module RTC
Premier article sur l'Arduino !
Lorsqu'on utilise un module RTC (Real Time Clock : DS1307 ou autre) connecté à un Arduino la première difficulté que j'ai rencontré (en dehors des branchements initiaux) est d'arriver à mettre à l'heure l'horloge !
Voici 2 programmes pour y arriver aisément :
Ça fait quoi ces programmes ?
Le programme Arduino se connecte au module RTC et transmet régulièrement via le port série, l'heure du module. il est aussi capable, via ce même port série, si on transmet une chaîne représentant une date et heure, de mettre à jour le module RTC, qui grâce à sa pile pourra ainsi la conserver.
De plus, si on appuie une fois sur le bouton, il peut demander au programme PC de lui transmettre la date et heure. Un appuie long sur le bouton arrête le programme PC qui sinon tourne en boucle indéfiniment.
Le programme PC, une application console, se connecte à l'Arduino via le port série, il affiche ce qui arrive de l'Arduino, et analyse les commandes :
Comment faut faire ?
Explications et branchements : Arduino
Un module RTC DS1307 se connecte via le bus I2C de l'Arduino, Je vous fais pas de détail y a pleins de bons tutoriels pour cela. Le module est connecté comme suit :
En plus du module RTC il faut un bouton poussoir branché sur une sortie digitale de l'Arduino (PIN 2).
Le programme à téléverser dans l'Arduino est composé de 3 fichiers :
Le programme fait aussi appel à 2 librairies de communication "classique" pour l'Arduino : SPI.h et Adafruit_SSD1306.h.
On retrouve dans les déclaration (#define) successivement :
Ensuite les variables dont myhorloge qui est une instance de la classe permettant de manipuler le module RTC (Obtenir l'heure,l'ajuster et pouvoir l'afficher simplement).
Je ne détaille pas les autres variables les commentaires s'en charge pour moi.
La suite : la fonction Setup() :
La fonction loop() :
Gère quand le bouton bouton est enfoncé :
En mode INPUT_PULLUP c'est quand l'état du bouton est à LOW que le bouton est enfoncé, dans ce cas on regarde si ce n'est pas la première fois que l'on passe dans la boucle et le temps depuis le premier appui sur le bouton : s'il dépasse le temps prévu (DELAY_QUIT) on envoie la commande quit sur le port série pour forcer le programme PC à s'arrêter proprement.
Si c'est le premier appui sur le bouton, on mémorise le temps pour commencer à compter.
La suite : quand le bouton n'est pas appuyé
Si le bouton est relâche, s'il a été appuyé au moins une fois on envoie la commande adjust sur le port série pour demander au programme PC de nous transmettre l'heure (le traitement de la réception des données se fait plus bas).
Le reste du loop permet de demander l'heure au module RTC et de l'afficher à intervalle régulier. (Gère aussi le cas ou le module n'est pas correctement initialisé)
Le traitement des caractère reçus par l'Arduino se fait dans le gestionnaire d’événements du port série serialEvent().
La fonction lit les caractères reçus et les concatène jusqu'à la réception d'un retour chariot (\n).
A la réception d'un retour chariot, la fonction demande à la méthode stringValide() si la chaîne reçue est une chaîne valide représentant une date et heure au format dd/MM/yyyy HH:mm:ss. Si oui, la date reçue est utilisée pour mettre à jour le module RTC via la méthode myhorloge.adjust();
Comme avec un port série il y a souvent des parasites, une boucle est faite pour traiter toutes les chaînes reçues d'au moins 19 caractères (La longueur du template dd/MM/yyyy HH:mm:ss) en supprimant au fur et a mesure le premier caractère.
Il reste deux fonctions dans le programme : stringValide()et isValidNumber().
Ces deux fonctions ont pour but l'analyse de la chaîne reçue et renvoie True si c'est OK : Tous les contrôles possibles sont fait :
Explications : Programme PC
Le programme PC est écrit en C#,
Il nécessite le téléchargement du SDK .NET CORE (accessible à l'adresse suivante : https://dotnet.microsoft.com/download)
Il est compilé avec Visual Studio Code (Éditeur simple pour faire cela téléchargeable à l'adresse : https://code.visualstudio.com/download)
Le dossier de la solution contient un seul projet application console composé d'un seul fichier de code source Program.cs.
Le programme utilise un Package : System.IO.Ports téléchargé via la commande la la console de Visual Studio Code > donet add package System.IO.ports
Le programme déclare deux constantes
PORTNAME : Le nom du port série à utiliser pour parler avec l'Arduino.
BAUDRATE : la vitesse de dialogue.
Une amélioration certaine de l'application serait de pouvoir fournir cela sur la ligne de commande de l'exécutable (TODO !-).
La méthode Main() traite l'intégralité du programme qui consiste en 3 étapes :
Ouvrir le port série, lire les lignes de caractères recues et les traiter des trois manières suivantes :
le try... finally garantit que le port série sera toujours proprement fermé, même si l'application plantait.
le using(var myport = new SerialPort(...)) { ... } qui englobe le contenu garantit aussi que les ressources associées au port série seront nettoyées proprement par le système lors de l'arrêt du programme.
Voila voila !
Lorsqu'on utilise un module RTC (Real Time Clock : DS1307 ou autre) connecté à un Arduino la première difficulté que j'ai rencontré (en dehors des branchements initiaux) est d'arriver à mettre à l'heure l'horloge !
Voici 2 programmes pour y arriver aisément :
- Le premier à téléverser dans l'Arduino auquel est branché l'horloge
- Le second pour le PC qui fournira l'heure
Ça fait quoi ces programmes ?
Le programme Arduino se connecte au module RTC et transmet régulièrement via le port série, l'heure du module. il est aussi capable, via ce même port série, si on transmet une chaîne représentant une date et heure, de mettre à jour le module RTC, qui grâce à sa pile pourra ainsi la conserver.
De plus, si on appuie une fois sur le bouton, il peut demander au programme PC de lui transmettre la date et heure. Un appuie long sur le bouton arrête le programme PC qui sinon tourne en boucle indéfiniment.
Le programme PC, une application console, se connecte à l'Arduino via le port série, il affiche ce qui arrive de l'Arduino, et analyse les commandes :
- Adjust dans ce cas, il envoie la date et l'heure.
- Quit dans ce cas il se termine en fermant comme il faut le port série
Comment faut faire ?
- Récupérer les codes source sur Git HUB
- Brancher le module RTC et le bouton sur l'Arduino (6 fils : voir schéma ci-dessous)
- Connecter l'Arduino au PC : (Vérifier le port série utilisé et ajuster dans le code si besoin)
- Téléverser le programme dans l'Arduino
- Dans Visual Studio Code charger le programme PC (Nécessite .NET CORE 3.1 SDK) et vérifier aussi que le port série est le bon
- Exécuter le programme compilé (commande dans la console : Donet Run ou Donet Build et lancer l'exécutable généré dans le dossier Bin)
- A ce stade les informations de l'horloge doivent défiler sur la console (1 par seconde si le module RTC est déjà initialisé, sinon message d'erreur)
- Appuyer sur le bouton câblé sur l'Arduino ==> Le module RTC est à l'heure (du PC) !
- Appuyer et maintenir le bouton ==> le programme PC s'arrête proprement.
- Enjoy !!
Explications et branchements : Arduino
Un module RTC DS1307 se connecte via le bus I2C de l'Arduino, Je vous fais pas de détail y a pleins de bons tutoriels pour cela. Le module est connecté comme suit :
- SDL sur la PIN A4
- SDA sur le PIN A5
- GND sur GND
- VCC sur 5V
En plus du module RTC il faut un bouton poussoir branché sur une sortie digitale de l'Arduino (PIN 2).
Le programme à téléverser dans l'Arduino est composé de 3 fichiers :
- Horloge.h : Fichier d'entête de la librairie simplifiant les accès au module RTC DS1307
- Horloge.cpp : Le code de la librairie
- MAJ_Horloge.ino : Le programme détaillé ci-dessous.
Le programme fait aussi appel à 2 librairies de communication "classique" pour l'Arduino : SPI.h et Adafruit_SSD1306.h.
On retrouve dans les déclaration (#define) successivement :
- La déclaration de l'adresse du module RTC (ici 0x68)
- La déclaration de la pin du bouton poussoir (ici PIN 2)
- La déclaration du temps "d'appui long" sur le bouton au dela duquel on stoppe le programme PC (ici 3000 = 3 secondes)
Ensuite les variables dont myhorloge qui est une instance de la classe permettant de manipuler le module RTC (Obtenir l'heure,l'ajuster et pouvoir l'afficher simplement).
Je ne détaille pas les autres variables les commentaires s'en charge pour moi.
La suite : la fonction Setup() :
- Positionne le bouton sur la bonne pin et en mode INPUT_PULLUP,
- Initialise le port série,
- Initialise la communication avec le module RTC
- Donne quelques instructions sommaires à l'utilisateur sur le port série.
La fonction loop() :
Gère quand le bouton bouton est enfoncé :
Si c'est le premier appui sur le bouton, on mémorise le temps pour commencer à compter.
La suite : quand le bouton n'est pas appuyé
Le reste du loop permet de demander l'heure au module RTC et de l'afficher à intervalle régulier. (Gère aussi le cas ou le module n'est pas correctement initialisé)
Le traitement des caractère reçus par l'Arduino se fait dans le gestionnaire d’événements du port série serialEvent().
A la réception d'un retour chariot, la fonction demande à la méthode stringValide() si la chaîne reçue est une chaîne valide représentant une date et heure au format dd/MM/yyyy HH:mm:ss. Si oui, la date reçue est utilisée pour mettre à jour le module RTC via la méthode myhorloge.adjust();
Comme avec un port série il y a souvent des parasites, une boucle est faite pour traiter toutes les chaînes reçues d'au moins 19 caractères (La longueur du template dd/MM/yyyy HH:mm:ss) en supprimant au fur et a mesure le premier caractère.
Il reste deux fonctions dans le programme : stringValide()et isValidNumber().
Ces deux fonctions ont pour but l'analyse de la chaîne reçue et renvoie True si c'est OK : Tous les contrôles possibles sont fait :
- Les caractères doivent être des chiffres aux bons emplacements
- Les séparateurs sont aussi contrôlés
- Les valeurs des chiffres aussi (Jours de 1 à 31, mois de 1 à 12, ...)
- Les mois à 28, 29 ou 30 jours sont aussi contrôles (avec détection des années bissextiles).
Explications : Programme PC
Le programme PC est écrit en C#,
Il nécessite le téléchargement du SDK .NET CORE (accessible à l'adresse suivante : https://dotnet.microsoft.com/download)
Il est compilé avec Visual Studio Code (Éditeur simple pour faire cela téléchargeable à l'adresse : https://code.visualstudio.com/download)
Le dossier de la solution contient un seul projet application console composé d'un seul fichier de code source Program.cs.
Le programme utilise un Package : System.IO.Ports téléchargé via la commande la la console de Visual Studio Code > donet add package System.IO.ports
Le programme déclare deux constantes
BAUDRATE : la vitesse de dialogue.
Une amélioration certaine de l'application serait de pouvoir fournir cela sur la ligne de commande de l'exécutable (TODO !-).
La méthode Main() traite l'intégralité du programme qui consiste en 3 étapes :
- Soit c'est la commande quit dans ce cas la boucle While se termine, le programme aussi
- Soit c'est la commande adjust qui est reçue dans se cas le programme envoie la date et l'heure sur le port série.
- Pour toute autre chaîne reçue elle est affiché dans la Console.
le try... finally garantit que le port série sera toujours proprement fermé, même si l'application plantait.
le using(var myport = new SerialPort(...)) { ... } qui englobe le contenu garantit aussi que les ressources associées au port série seront nettoyées proprement par le système lors de l'arrêt du programme.
Voila voila !
10 décembre 2019
SQL - Surveiller un compteur d'identité
Sur les tables SQL Server mettre un compteur d'identité comme clé primaire, c'est bien. (Ça résous pas mal de problématiques).
Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.
Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.
Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.
--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND t.name = 'compteur_identite'))
BEGIN
DROP TABLE dbo.compteur_identite
END
--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT NULL
, [max_id] NUMERIC(38,0) NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)
--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name
FROM sys.schemas s
INNER JOIN sys.objects o ON o.schema_id = s.schema_id
WHERE s.name <> 'sys'
AND o.name NOT IN ('MASTERKEY', 'spt_values') --- ici exclure ce qui n'est pas nécessaire
OPEN curseur
FETCH NEXT FROM curseur INTO @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
PRINT @schema + '.' + @table;
--- insertion dans la table de sortie
INSERT INTO dbo.compteur_identite ( [schema]
, [table], [index_pk]
, [column], [type]
, [seed], [increment]
, [max_id], [current]
, [nombre de lignes restant]
, [critique])
SELECT s.name AS [schema]
, o.name AS [table], i.name AS [index_pk]
, c.name AS [column], t.name AS [type]
, IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
, CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
WHERE i.type = 1 --- pk
AND s.name = @schema
AND o.name = @table
;
FETCH NEXT FROM curseur INTO @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;
--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
;
Bien sût cette requête doit être adaptée a vos besoins.
Quelques infos clé :
Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.
Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.
Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.
--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND t.name = 'compteur_identite'))
BEGIN
DROP TABLE dbo.compteur_identite
END
--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT NULL
, [max_id] NUMERIC(38,0) NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)
--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name
FROM sys.schemas s
INNER JOIN sys.objects o ON o.schema_id = s.schema_id
WHERE s.name <> 'sys'
AND o.name NOT IN ('MASTERKEY', 'spt_values') --- ici exclure ce qui n'est pas nécessaire
OPEN curseur
FETCH NEXT FROM curseur INTO @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
PRINT @schema + '.' + @table;
--- insertion dans la table de sortie
INSERT INTO dbo.compteur_identite ( [schema]
, [table], [index_pk]
, [column], [type]
, [seed], [increment]
, [max_id], [current]
, [nombre de lignes restant]
, [critique])
SELECT s.name AS [schema]
, o.name AS [table], i.name AS [index_pk]
, c.name AS [column], t.name AS [type]
, IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
, CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
WHERE i.type = 1 --- pk
AND s.name = @schema
AND o.name = @table
;
FETCH NEXT FROM curseur INTO @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;
--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
;
Bien sût cette requête doit être adaptée a vos besoins.
Quelques infos clé :
- Le nom de la table dbo.compteur_identite est arbitraire choisissez celui qui vous fait plaisir ;-)
- Dans la déclaration du curseur sur la liste des tables a surveiller retirer les tables inutiles, moi j'ai retiré le schéma sys et les deux tables 'MASTERKEY' et 'spt_values'.
- Calcul de la colonne "Critique", j'ai posé un seuil à 1 million de lignes restant le seuil doit être ajusté en fonction de la vitesse de remplissage et de votre capacité à agir lorsqu'il n'y a plus assez de place.
- Les filtres sur les types de colonne sont assez obscur, je les ai déterminé par recherche : Tout ce qui a une collation c'est des colonnes de type texte : pas de problème ici. Tout les type avec une longeur à 0 ou négative (il y en an si si) sont filtré pour que les calculs restent cohérents, les type avec une xprec à 0 sont aussi filtrées (Exemple les type images).
- La requête finale est a ajusté en fonction de vos besoins et peut être jouée autant de fois que nécessaire.
Enjoy !
09 août 2019
SQL - Connaître le format de réponse d'une requête sans pour autant l'exécuter
On a de temps en temps besoin de connaitre les informations renvoyées par une requête avant de l'exécuter.
Par exemple pour savoir quelles vont être les colonnes.
Ou dans quand on renvoie plusieurs tables et qu'on a besoin que le Dataset reçu soit toujours identique, alors que dans certains contextes une ou plusieurs tables ne doivent pas être calculées.
Par exemple :
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';
Ce DataSet renvoie 2 tables :
On peut donc mettre en place un programme qui lise les 2 tables et remplisse comme il faut les informations dans une liste de contacts qui on un nom et une liste d'adresses le concernant.
Imaginons que dans une des interfaces, je n'ai pas besoin de calculer les adresses, mais que je veuille toujours utiliser le même programme / les mêmes objets pour charger les données.
Il me faut donc modifier le SQL pour remonter les mêmes infos, sans que le calcul de la seconde table se fasse.
1) Solution instinctive
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto'
AND 1= 0
;
On ajoute une condition qui renvoie toujours faux à la requête : Ça marche, mais Ouais bof !
2) seconde solution la plus sexy à mon gout
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SET FMTONLY ON;
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';
SET FMTONLY OFF;
On entoure ici la requête par un SET FMTONLY (ON au début et OFF pour rétablir). Cela a pour effet que SQL Server renvoie une table vide avec les bonnes colonnes.
Voila voila !
Sachez aussi qu'a partir de SQL SERVER 2012 il existe aussi une procédure stockée : sp_describe_first_result_set qui renvoie une table contenant toutes les informations sur les colonnes issues du texte de la requête passée à la procédure.
voir la documentation MSDN
Enjoy !
Par exemple pour savoir quelles vont être les colonnes.
Ou dans quand on renvoie plusieurs tables et qu'on a besoin que le Dataset reçu soit toujours identique, alors que dans certains contextes une ou plusieurs tables ne doivent pas être calculées.
Par exemple :
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';
Ce DataSet renvoie 2 tables :
- une table des id et noms de contact
- une tables avec l'id du contact et ses informations d'adresses.
On peut donc mettre en place un programme qui lise les 2 tables et remplisse comme il faut les informations dans une liste de contacts qui on un nom et une liste d'adresses le concernant.
Imaginons que dans une des interfaces, je n'ai pas besoin de calculer les adresses, mais que je veuille toujours utiliser le même programme / les mêmes objets pour charger les données.
Il me faut donc modifier le SQL pour remonter les mêmes infos, sans que le calcul de la seconde table se fasse.
1) Solution instinctive
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto'
AND 1= 0
;
On ajoute une condition qui renvoie toujours faux à la requête : Ça marche, mais Ouais bof !
2) seconde solution la plus sexy à mon gout
SELECT id, nom FROM dbo.contact WHERE nom = 'toto';
SET FMTONLY ON;
SELECT c.id, a.adresse, a.code_postal, a.ville FROM dbo.adresse a INNER JOIN dbo.contact c ON a.contact_id = c.id
WHERE c.nom = 'toto';
SET FMTONLY OFF;
On entoure ici la requête par un SET FMTONLY (ON au début et OFF pour rétablir). Cela a pour effet que SQL Server renvoie une table vide avec les bonnes colonnes.
Voila voila !
Sachez aussi qu'a partir de SQL SERVER 2012 il existe aussi une procédure stockée : sp_describe_first_result_set qui renvoie une table contenant toutes les informations sur les colonnes issues du texte de la requête passée à la procédure.
voir la documentation MSDN
Enjoy !
17 décembre 2018
C# - Avoir un fichier de app.config différent entre release et debug
C'est natif dans les projets Web mais dans les autres types de projets c'est pas si évident !
J'ai trouvé ce tuto : https://www.linkedin.com/pulse/multi-appconfig-visual-studio-2017-benjamin-davis/
Voici la traduction rapide avec mes notes.
Attention : C'est un peu long, mais ça marche tellement bien que cela vaut le coup. En plus on ne le fait qu'une seule fois par projet.
Prérequis : .NET4.5 (ou plus) et Visual studio 17
1) Charger le projet et ajouter 2 fichiers : App.Debug.config et App.Release.config sans pour l'instant les modifier.
2) Enregistrer et Déchargez le projet
3) Avec un éditeur de texte quelconque éditer le fichier projet (.csproj)
4) Après le dernier bloc XML PropertyGroup Ajouter
<PropertyGroup> <ProjectConfigFileName>App.config</ProjectConfigFileName>
</PropertyGroup>
Sans ce bloc la librairie qui fait la transformation du fichier cherche un fichier "web.config" ! (C'est normal c'est une librairie qui vient des projets Web)
5) Dans le bloc XML qui contient l'inclusion du fichier App.config, modifier comme suit (en orange) afin de lier les fichiers Debug et Release avec le fichier App.config.
<ItemGroup> <None Include="App.config" />
<!-- Ne pas toucher ce qu'il y a avant -->
<None Include="App.Debug.config"> <DependentUpon>App.config</DependentUpon> </None> <None Include="App.Release.config"> <DependentUpon>App.config</DependentUpon> </None> <!-- Ne pas toucher ce qu'il y a après --> </ItemGroup>
A noter le fichier App.config a peut être d'autres configurations en fonction du projet : par exemple dans mon projet j'ai :
<None Include="App.config">
<SubType>Designer</SubType>
</None>
Ne pas toucher : non plus !
6) Chercher si un nœud XML PropertyGroup existe et contient un nœud VisualStudioVersion si ce n'est pas le cas, il faut le créer sinon il faut y ajouter le noeud VSToolsPath. Ce nœud permet au processus de Build de connaitre le chemin d'accès de la librairie de transformation.
Donc à la fin vous devez avoir :
<PropertyGroup> <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">10.0</VisualStudioVersion> <VSToolsPath Condition="'$(VSToolsPath)' == ''">$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)</VSToolsPath>
</PropertyGroup>
Ne mettez pas d'espace dans de texte du noeud VSToolsPath (pas d'espace avant le $(MSBuild. et après le ...VisualstudioVersion)</VSToolsPath.
Bien sûr il faudra surement ajuster le numéro de version de Visual Studio en fonction de votre version (Je n'ai testé qu'avec un Visual Studio 2017)
7) En fin de fichier après le dernier Import ajouter l'import de la librairie à utiliser
<Import Project="$(VSToolsPath)\WebApplications\Microsoft.WebApplication.targets"
Condition="'$(VSToolsPath)' != ''" />
8) Ajouter le lancement de la transformation en toute fin de fichier à la racine
<!-- Tout le fichier avant -->
<Target Name="AfterBuild"> <TransformXml Source="@(AppConfigWithTargetPath)" Transform="$(ProjectConfigTransformFileName)"
Destination="@(AppConfigWithTargetPath->'$(OutDir)%(TargetPath)')" /> </Target> </Project>
Le Tutorial s'arrête la !
Moi J'ajouterais :
9) Recharger le projet dans Visual Studio. Constater que le fichier App.config peut se déplier et qu'il y a les fichiers App.Debug.config et App.Release.config en dessous.
10) Modifier les fichier Debug et Release en fonction de vos besoins.
Pour ce faire : un peu de lecture de la doc Microsoft et roule ma poule !
https://docs.microsoft.com/fr-fr/previous-versions/dd465326(v=vs.100)
11) Cas d'exemple : Changer la chaîne de connexion en fonction de DEBUG ou RELEASE
Un des choix possible est :
11.a) Dans le App.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="MonAppli" connectionString="Sera remplace dans le App.Debug.config ou le App.Release.config"/>
</connectionStrings>
<appSettings>
<!--- Suite du fichier --->
</appSettings>
</configuration>
A noter ici :
A noter ici :
J'ai trouvé ce tuto : https://www.linkedin.com/pulse/multi-appconfig-visual-studio-2017-benjamin-davis/
Voici la traduction rapide avec mes notes.
Attention : C'est un peu long, mais ça marche tellement bien que cela vaut le coup. En plus on ne le fait qu'une seule fois par projet.
Prérequis : .NET4.5 (ou plus) et Visual studio 17
1) Charger le projet et ajouter 2 fichiers : App.Debug.config et App.Release.config sans pour l'instant les modifier.
2) Enregistrer et Déchargez le projet
3) Avec un éditeur de texte quelconque éditer le fichier projet (.csproj)
4) Après le dernier bloc XML PropertyGroup Ajouter
<PropertyGroup> <ProjectConfigFileName>App.config</ProjectConfigFileName>
</PropertyGroup>
Sans ce bloc la librairie qui fait la transformation du fichier cherche un fichier "web.config" ! (C'est normal c'est une librairie qui vient des projets Web)
5) Dans le bloc XML qui contient l'inclusion du fichier App.config, modifier comme suit (en orange) afin de lier les fichiers Debug et Release avec le fichier App.config.
<ItemGroup> <None Include="App.config" />
<!-- Ne pas toucher ce qu'il y a avant -->
<None Include="App.Debug.config"> <DependentUpon>App.config</DependentUpon> </None> <None Include="App.Release.config"> <DependentUpon>App.config</DependentUpon> </None> <!-- Ne pas toucher ce qu'il y a après --> </ItemGroup>
A noter le fichier App.config a peut être d'autres configurations en fonction du projet : par exemple dans mon projet j'ai :
<None Include="App.config">
<SubType>Designer</SubType>
</None>
Ne pas toucher : non plus !
6) Chercher si un nœud XML PropertyGroup existe et contient un nœud VisualStudioVersion si ce n'est pas le cas, il faut le créer sinon il faut y ajouter le noeud VSToolsPath. Ce nœud permet au processus de Build de connaitre le chemin d'accès de la librairie de transformation.
Donc à la fin vous devez avoir :
<PropertyGroup> <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">10.0</VisualStudioVersion> <VSToolsPath Condition="'$(VSToolsPath)' == ''">$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)</VSToolsPath>
</PropertyGroup>
Ne mettez pas d'espace dans de texte du noeud VSToolsPath (pas d'espace avant le $(MSBuild. et après le ...VisualstudioVersion)</VSToolsPath.
Bien sûr il faudra surement ajuster le numéro de version de Visual Studio en fonction de votre version (Je n'ai testé qu'avec un Visual Studio 2017)
7) En fin de fichier après le dernier Import ajouter l'import de la librairie à utiliser
<Import Project="$(VSToolsPath)\WebApplications\Microsoft.WebApplication.targets"
Condition="'$(VSToolsPath)' != ''" />
8) Ajouter le lancement de la transformation en toute fin de fichier à la racine
<!-- Tout le fichier avant -->
<Target Name="AfterBuild"> <TransformXml Source="@(AppConfigWithTargetPath)" Transform="$(ProjectConfigTransformFileName)"
Destination="@(AppConfigWithTargetPath->'$(OutDir)%(TargetPath)')" /> </Target> </Project>
Le Tutorial s'arrête la !
Moi J'ajouterais :
9) Recharger le projet dans Visual Studio. Constater que le fichier App.config peut se déplier et qu'il y a les fichiers App.Debug.config et App.Release.config en dessous.
10) Modifier les fichier Debug et Release en fonction de vos besoins.
Pour ce faire : un peu de lecture de la doc Microsoft et roule ma poule !
https://docs.microsoft.com/fr-fr/previous-versions/dd465326(v=vs.100)
11) Cas d'exemple : Changer la chaîne de connexion en fonction de DEBUG ou RELEASE
Un des choix possible est :
11.a) Dans le App.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="MonAppli" connectionString="Sera remplace dans le App.Debug.config ou le App.Release.config"/>
</connectionStrings>
<appSettings>
<!--- Suite du fichier --->
</appSettings>
</configuration>
A noter ici :
- Le nom de la chaîne de connexion peut changer en fonction de vos besoins
- Le texte de la chaîne de connexion est non valide : Cela permet de valider que cela fonctionne bien dans les 2 configuration.
11.b) Dans le App.Debug.config ou App.Release.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
<connectionStrings>
<add name="MonAppli" connectionString="Data Source=ServerBDD;Initial Catalog=MaBase_test;integrated security=SSPI" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
</connectionStrings>
</configuration>
A noter ici :
- Le NameSpace ajouté au nœud configuration est obligatoire
- Le nom de la chaîne de connexion est le même que dans le fichier App.config (c'est important voir ci-dessous)
- La chaîne dans l'attribut ConnectionString (en rose) est a adapter en fonction du fichier et de la base à laquelle se connecter quand on est en Debug ou en Release.
- L'attribut xdt:Locator permet d'indiquer, à l'aide d'une requête XPath, dans le fichier App.config, sur quel ou quels nœuds XML la transformation va s'appliquer. Ici on Matche sur l'attribut name c'est à dire tous les nœuds du fichier App.config qui ont le chemin XML suivant : configuration/ConnectionStrings/add[name="MonAppli"]
- L'attribut xdt:Transform permet d'expliquer quelle transformation appliquer (Ici remplacement de tous les attributs du ou des nœuds trouvés.
12) Compilez et Enjoy !
12 décembre 2018
c# - Expression régulière comment les tester
REGExpr Testeur est bientôt disponible...
Cette application permet : de mettre au point, tester, et surtout comprendre une expression régulière
Dans cet écran : un édite et on teste l'expression régulière.
L'onglet Information décrypte la composition de l'expression.
Cette application permet : de mettre au point, tester, et surtout comprendre une expression régulière
SQL - Concaténer plusieurs lignes dans une colonne
Ça arrive de temps en en temps : j'ai besoin de remonter dans une colonne une liste de pleins d'éléments présent dans plusieurs lignes d'une sous table.
Pour être concret :
On a une table activité : dbo.activite qui contient les colonnes: actId; actNom
On a une table participant d'une activité : dbo.participant qui contient les colonnes : parId, parNom, actId
J'ai besoin de remonter sur une ligne les activités avec une colonne contenant les noms de tous les participants de chaque activité !
Ma proposition :
SELECT a.actId, a.actNom
, (SELECT p.parNom + ', ' as [text()]
FROM dbo.participant p
WHERE p.actId = a.actId
ORDER BY p.parId
FOR XML PATH('')) AS participants
FROM dbo.activite a
Cette requête remonte les infos de la table activité et une colonne participants qui contient les noms séparés par une virgule des participants de l'activité.
Les points à noter :
La colonne participants est construite par une sous requête qui remonte en XML (FOR XML PATH('')) les noms des participants en y ajoutant un séparateur (la virgule + espace ici),
Cette sous requête, remontée dans une colonne de résultat, a pour effet de concaténer les lignes de XML en une seule valeur.
La colonne remontée est renommée AS [text()], fonction de conversion XML qui permet à SQL Server de retirer les balises XML ! (Tout est la !!) . Cela nettoie la chaîne concaténée des balises xml inutiles ici (Attention text() est sensible à la casse : il doit être écrit en minuscule, n'oubliez pas non plus les parenthèses pour que la méthode soit invoquée, le tout entre crochet ou apostrophes pour respecter la syntaxe SQL ).
A noter la clause Order By qui s'assure de l'ordre de présentation des participants.
Reste un petite erreur dans ce qui est remonté : La liste des noms se termine par un séparateur (la virgule + espace ici), il faudra filtrer cela dans l'applicatif ou complexifier la requête pour retirer les 2 derniers caractères inutiles.
Maj 05/2021 : Attention aussi au contenu remonté, s'il y a des caractères spéciaux (pour l'XML) ils seront XML encodé, il faudra donc les décoder avant de les utiliser. Avec par exemple CONVERT(NVARCHAR(MAX), CONVERT(XML, participants)dans l'exemple ci dessus.
Enjoy !
Pour être concret :
On a une table activité : dbo.activite qui contient les colonnes: actId; actNom
On a une table participant d'une activité : dbo.participant qui contient les colonnes : parId, parNom, actId
J'ai besoin de remonter sur une ligne les activités avec une colonne contenant les noms de tous les participants de chaque activité !
Ma proposition :
SELECT a.actId, a.actNom
, (SELECT p.parNom + ', ' as [text()]
FROM dbo.participant p
WHERE p.actId = a.actId
ORDER BY p.parId
FOR XML PATH('')) AS participants
FROM dbo.activite a
Les points à noter :
La colonne participants est construite par une sous requête qui remonte en XML (FOR XML PATH('')) les noms des participants en y ajoutant un séparateur (la virgule + espace ici),
Cette sous requête, remontée dans une colonne de résultat, a pour effet de concaténer les lignes de XML en une seule valeur.
La colonne remontée est renommée AS [text()], fonction de conversion XML qui permet à SQL Server de retirer les balises XML ! (Tout est la !!) . Cela nettoie la chaîne concaténée des balises xml inutiles ici (Attention text() est sensible à la casse : il doit être écrit en minuscule, n'oubliez pas non plus les parenthèses pour que la méthode soit invoquée, le tout entre crochet ou apostrophes pour respecter la syntaxe SQL ).
A noter la clause Order By qui s'assure de l'ordre de présentation des participants.
Reste un petite erreur dans ce qui est remonté : La liste des noms se termine par un séparateur (la virgule + espace ici), il faudra filtrer cela dans l'applicatif ou complexifier la requête pour retirer les 2 derniers caractères inutiles.
Maj 05/2021 : Attention aussi au contenu remonté, s'il y a des caractères spéciaux (pour l'XML) ils seront XML encodé, il faudra donc les décoder avant de les utiliser. Avec par exemple CONVERT(NVARCHAR(MAX), CONVERT(XML, participants)dans l'exemple ci dessus.
Enjoy !
Inscription à :
Articles (Atom)