Nous avons vu dans un article précédent comment interroger ou charger un fichier csv dans H2. Nous allons voir dans cet article comment se connecter à un fichier Excel pour en exploiter les données, en tirant partie du langage SQL et des fonctionnalités de H2.
1 - Téléchargement et installation de la base de données H2
La base de données H2 est téléchargeable sur le site du projet H2.
Téléchargez l’installateur windows si vous êtes sur une machine windows ou l’installateur multi-plateformes dans le cas contraire.
En double cliquant sur l’installateur, vous installez à la fois le moteur de base de données, et un client léger qui permet de s’y connecter via une interface web.
Pour vous connecter à Excel (ou à Access), vous aurez besoin du driver odbc (inclus dans votre installation windows) et de la passerelle JdbcOdbc. Celle-ci peut-être passée au démarrage de la machine virtuelle en ajoutant le paramètre -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver à java
Exemple de l’exécutable bin/h2.bat avant changement :
@java -cp "h2-1.2.132.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %*
@if errorlevel 1 pauseAprès changement :
@java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver -cp "h2-1.2.132.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %*
@if errorlevel 1 pause2 - Trois bases de données en une
3 - Créer une base de données locale (ou embarquée)
4 - Se connecter à un classeur excel sans DSN
La connection à un classeur excel se fait en utilisant une table liée (LINKED TABLE) qui utilise les paramètres suivants :
nom du pilote JdbcOdbc
le driver Odbc pour Excel
l’emplacement du fichier excel à utiliser
l’utilisateur et le mot de passe s’il y a lieu
le nom de la feuille excel suivie du caractère $, le tout entre crochets
Dans l’exemple qui suit, nous nous connectons à un fichier Excel disponible sur [http://geo.michaelm.free.fr/H2/resources/<-http://geo.michaelm.free.fr/H2/resources/].
Téléchargez ce fichier et placez-le sur votre disque dur. Cet emplacement est indiqué dans votre script SQL.
CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[DEPENSES$]');Ce fichier possède deux tables : Une table DEPENSES :
| ACHETEUR | PRODUIT | NB |
|---|---|---|
| Alexandre | Voiture | 1 |
| Basile | Baguette | 2 |
| Chantal | Baguette | 1 |
| ... | ... | ... |
Une table PRIX :
| PRODUIT | PRIX |
|---|---|
| Voiture | 18000 |
| Baguette | 1 |
| CD | 15 |
| ... | ... |
Chargement des tables (notez le référencement des feuilles)
CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[DEPENSES$]');
CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[PRIX$]');5 - Exploitation
Interroger les tables en faisant une jointure :
SELECT ACHETEUR, DEPENSES.PRODUIT, NB, PRIX AS PRIX_UNITAIRE, NB*PRIX AS PRIX_TOTAL FROM DEPENSES, PRIX WHERE DEPENSES.PRODUIT = PRIX.PRODUIT;6 - Se connecter en passant par le gestionnaire de sources ODBC
L’utilisation d’un DSN (Data Source Name) facilite un peu la connexion aux sources de données. Une fois déclaré un nom pour votre source de données dans le gestionnaire de source ODBC (ex. DSN_DEPENSES pour notre exemple), la connexion se fait de la manière suivante :
CREATE LINKED TABLE DSN_DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:DEPENSES', '', '', '[DEPENSES$]');
CREATE LINKED TABLE DSN_PRIX('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:DEPENSES', '', '', '[PRIX$]');7 - Limite du driver ODBC pour excel
Le driver ODBC pour excel permet d’exploiter des données saisies dans un classeur excel mais ne permet pas d’effectuer toutes les commandes DML (Data Manipulation Language), loin de là.
Commandes autorisées :
SELECT
INSERT
Commandes non prises en compte :
DELETE
UPDATE
CREATE INDEX
DROP INDEX
ALTER TABLE
...en théorie. En pratique, je n’ai pu pas pu insérer de nouveaux enregistrements :
problème de formatage pour les colonnes numériques (définies en DOUBLE(15) par le driver lors de la définition de la table mais en DOUBLE(17) par la commande INSERT)
problème de droit d’accès en mise à jour pour une table entièrement définie en VARCHAR.
Bien entendu, il est possible de créer une table H2 normale à partir d’une source externe en dupliquant les données :
CREATE TABLE MYTABLE AS SELECT * FROM DSN_TABLE
Toutes les instructions peuvent ensuite être utilisées dans la base H2. La récupération d’un résultat pouvant se faire via l’export d’un fichier CSV (voir [l’article 12<-art12]) ou, pour un petit résultat, par copier-coller.
8 - Utilisation avancée
L’intérêt d’interroger des données Excel dans un moteur de base de données comme H2, c’est de disposer d’instructions extrêmement puissantes qui ne sont pas toujours faciles à simuler dans Excel.
Nous avons déjà vu un exemple de jointure. Voici quelques autres exemples :
Exemple 1 : suppression des doublons dans une table
SELECT DISTINCT * FROM MATABLE
Exemple 2 : recherche des enregistrements de la table 1 absents de la table 2 (les deux tables doivent avoir la même structure) :
SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2
et l’inverse :
SELECT * FROM TABLE2 EXCEPT SELECT * FROM TABLE1
RSS 2.0