• Article

Hé, vous le scripteur !Le retour du script de base de données

Les scripteurs Microsoft

Télécharger le code de cet article: HeyScriptingGuy2008_04.exe (151KB)

Vous savez, si nous autres scripteurs avons une faiblesse (j'ai bien dit « si »), c'est celle-ci : nous nous inquiétons beaucoup trop des conséquences possibles des actions que nous commettons, ou ne commettons pas. Au lieu d'agir dans notre propre intérêt, nous nous retenons souvent pour plutôt faire ce que nous pensons être dans le meilleur intérêt de tous les autres. Nous le faisons en outre sans tenir compte de ce que ces actions pourraient signifier pour nous.

Prenez les articles sur les scripts de base de données, par exemple. Pour être parfaitement honnêtes, les articles sur les scripts de base de données sont difficiles à rédiger pour nous autres scripteurs. Ce n'est pas parce que les scripts de base de données mêmes sont difficiles à créer ; en fait, ils sont au contraire plutôt faciles à écrire. Le problème est plutôt que nous n'avons pas souvent l'occasion de travailler avec des bases de données. Pour cette raison, chaque fois que nous écrivons des articles sur les bases de données, nous devons nous arrêter un instant pour réfléchir à ce que nous faisons. Comme vous l'avez probablement deviné depuis longtemps, réfléchir n'est définitivement pas une condition requise pour devenir un scripteur.

À vrai dire, si l'un de nous y avait réfléchi dès le départ, les scripteurs n'existeraient probablement même pas.

Donc, si les articles portant sur l'écriture de scripts de base de données sont pour nous si difficiles à écrire, pourquoi continuons-nous à les écrire ? La réponse est simple : nous nous préoccupons de ce qui pourrait arriver au reste du monde si nous cessions d'écrire ces articles. Lorsque Sir Arthur Conan Doyle décida de tuer Sherlock Holmes (en le faisant tomber d'une falaise dans le récit « Le dernier problème »), il pensait simplement se débarrasser d'un personnage à propos duquel il ne voulait plus écrire. Au lieu de cela, une clameur énorme se fit entendre dans le monde entier. Selon certaines rumeurs, des londoniens allèrent jusqu'à placer des rubans de deuil sur leurs chapeaux et leurs manches en apprenant la nouvelle. « Le dernier final » fut publié dans The Strand Magazine en 1893. En réponse, 20 000 personnes annulèrent leur abonnement.

Aïe !

Les scripteurs craignent que l'élimination de leurs articles sur l'écriture de scripts de base de données ait un effet similaire dans le monde entier. Nous ne voulons pas être la cause de tant de chagrin et de désespoir, et nous ne voulons certainement pas que 20 000 d'entre vous annulent leur abonnement à TechNet Magazine. Par conséquent, nous souhaitons annoncer ici que les scripteurs continueront à écrire des articles sur les scripts de base de données. Quand ? Pourquoi pas maintenant ?

Ajout d'enregistrements à une base de données

Ce mois-ci, nous avons pensé vous montrer quelques petits tours habiles pour travailler avec les bases de données. Et oui, ceux-ci sont différents des astuces que nous vous avons montrées dans notre article de septembre 2007 (technetmagazine.com/issues/2007/09/HeyScriptingGuy). Même les scripteurs ne s'abaisseraient pas à publier deux fois le même article.

Enfin, sauf si nous pensions pouvoir le faire en toute impunité.

Commençons par vous montrer une manière simple d'ajouter un enregistrement à une base de données. Supposons que nous disposons d'une base de données nommée C:\Scripts\Inventory.mdb et que cette base de données inclut une table nommée Computers. La table Computers comprend les champs suivants :

  • ComputerName
  • SerialNumber
  • UserName
  • Department

Maintenant, comment ajouter un nouvel ordinateur à cette base de données ? Examinez le code de la figure 1.

Figure 1 Ajouter un enregistrement à la table Computers

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

Nous ne parlerons pas en détail des premières lignes de ce script. Si vous avez besoin de ce type d'informations générales, vous pouvez consulter les ressources mise à votre disposition dans le Centre de scripts à l'adresse microsoft.com/techNet/scriptcenter. Qu'il suffise de dire que nous utilisons les constantes adOpenStatic et adLockOptimistic pour contrôler le type de curseur et de verrouillage d'enregistrement pour notre jeu d'enregistrements. (Oui, ça semble impressionnant, mais c'est vraiment très simple) Après avoir créé les instances des objets ADODB.Connection et ADODB.Recordset, nous utilisons cette commande pour ouvrir notre base de données :

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

À propos, cette commande ouvre une base de données Microsoft® Access® 2003. Pour ouvrir une base de données Access 2007, utilisez plutôt cette commande :

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

Bien que nous commencions à nous éloigner un peu du sujet principal, vous pourriez utiliser une commande similaire à ceci pour ouvrir une base de données SQL Server® :

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

Jusqu'ici, tout ce que nous vous avons montré est du code passe-partout. Comme vous le verrez bientôt, à peu près tous vos scripts de base de données commenceront avec ces mêmes lignes de code. La partie que nous voulons souligner est en fait cette ligne :

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Nous utilisons ici une requête INSERT INTO pour insérer un nouvel enregistrement dans la table Computers. Remarquez qu'après le nom de la table, nous incluons les noms de tous les champs pour lesquels nous avons des valeurs, en séparant ces noms de champs par des virgules et en les plaçant entre parenthèses.

Vous vous demandez peut-être : qu'est-ce qu'ils veulent dire par « tous les champs pour lesquels nous avons des valeurs » ? Bonne question. Hé bien supposons que ce nouvel ordinateur n'a pas encore été affecté à un service. En partant du postulat que la base de données nous autorise à mettre une valeur Null dans le champ Department (service), nous pouvons simplement ne pas inclure Department dans la liste et ne pas lui affecter de valeur. En d'autres termes, nous pourrions écrire une requête telle que celle-ci :

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Puisque nous parlons d'affectation de valeurs, voici ce qui vient après les noms de champs : le mot clé VALUES suivi par les valeurs que nous voulons affecter à chaque champ (qui sont également entre parenthèses). Gardez à l'esprit que lorsque nous indiquons les noms de champs, ils peuvent être dans n'importe quel ordre. Par exemple, nous mettons ComputerName en premier, bien qu'il puisse très bien ne pas être le premier champ de la base de données. Cela convient tout à fait pour les noms de champs, mais pas tellement pour les valeurs. Les valeurs doivent être indiquées dans l'ordre exact des champs. Si notre premier champ est ComputerName, notre première valeur doit être le nom de l'ordinateur. Si ce n'est pas le cas, nous rencontrerons des problèmes. (Par exemple, nous finirons par affecter le nom d'utilisateur ou le numéro de série au champ ComputerName)

Comme vous pouvez le voir, l'affectation de valeur n'est pas très compliquée. Il suffit juste de s'assurer que les valeurs sont formatées correctement pour le type de données : les valeurs de dates et de chaînes doivent être placées entre des apostrophes, les valeurs numériques et booléennes ne doivent pas être placées entre apostrophes.

Au fait, si une valeur possède sa propre apostrophe (comme le nom O'Brien), vous devez y remédier en doublant l'apostrophe :

'O''Brien'

C'est fou, mais c'est comme ça.

Suppression d'enregistrements d'une base de données

Vous ne trouvez pas ça sympa ? nous pouvons ajouter un enregistrement à une base de données en exécutant simplement une seule requête SQL. Est-il possible de faire mieux ? Pas à notre connaissance.

Sauf peut-être la possibilité de supprimer des enregistrements multiples d'une base de données à l'aide d'une seule requête.

Supposons que votre entreprise décide d'éliminer son service Ressources humaines. (Non, pas de plaisanterie ; nous avons déjà assez d'ennuis avec le service RH de Microsoft) Comment pouvez-vous supprimer tous les ordinateurs des ressources humaines de votre base de données Inventory ? La figure 2 présente une façon d'y parvenir.

Figure 2 Supprimer plusieurs enregistrements

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

Comme vous pouvez le voir, ce script particulier démarre exactement de la même façon que notre script pour l'ajout d'un nouvel enregistrement : en définissant certaines constantes, en créant quelques objets et en se connectant ensuite à la base de données Inventory.mdb. Après avoir utilisé une simple requête DELETE pour supprimer tous les enregistrements (*) de la table Computers, ou tout du moins tous les enregistrements dans lesquels le champ Department est égal à Human Resources :

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

C'est aussi facile à faire que tomber d'une falaise.

Euh... désolé Sherlock.

Mise à jour d'enregistrements dans une base de données

Tout le monde est probablement d'accord pour dire qu'il serait vraiment amusant de supprimer toute trace du service des ressources humaines. (Remarque pour le service des ressources humaines de Microsoft : nous voulons dire que ce serait amusant pour d'autres personnes, et leur propre service RH. Pas pour nous. Ca ne nous amuserait pas du tout). Quoi qu'il en soit, la suppression de tous les ordinateurs des RH de la base de données Inventory ne serait pas la meilleure chose à faire. Pourquoi cela ? Eh bien, à moins que les employés des RH ne disparaissent avec tout leur matériel (ce qui leur ressemblerait bien, mais nous avons dit que nous ne ferions pas de plaisanterie), ces ordinateurs appartiennent toujours à votre entreprise, ce qui signifie que ces ordinateurs doivent toujours être répertoriés dans la base de données. En gardant ceci à l'esprit, allons-y. Au lieu de supprimer ces enregistrements de la base de données, mettons simplement à jour chaque enregistrement. Nous modifierons juste le nom de service de chacun de ces ordinateurs des ressources humaines pour le remplacer par None (Aucun). En d'autres termes, nous exécuterons le script présenté à la figure 3.

Figure 3 Mettre à jour des enregistrements

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

Ici encore, la seule différence dans ce script est la requête SQL. Dans ce cas-ci, nous utilisons une requête UPDATE pour mettre à jour des enregistrements de la table Computers. Outre le mot clé UPDATE, nous devons ajouter deux paramètres qui indiquent à la requête comment mettre à jour les enregistrements et les enregistrements à mettre à jour :

  • Set Department = 'None'. Ceci spécifie simplement la nouvelle valeur pour le champ Department. Vous pouvez en fait définir plus d'une valeur de champ à la fois en utilisant une syntaxe telle que celle-ci : Set Department = 'None', UserName = 'None'. Comme vous pouvez le voir, ceci définira les champs Department et UserName sur None.
  • Where Department = 'Human Resources'. Ceci est une clause Where standard, qui indique les enregistrements qui doivent être mis à jour. Dans ce cas, il s'agit de tous les enregistrements dont le champ Department est égal à Human Resources. Nous ajoutons ceci pour une raison simple : nous ne voulons pas modifier tous les enregistrements de la base de données, seulement les enregistrements (les ordinateurs) qui appartenaient auparavant au service Human Resources.

Voici quelque chose de sympa que vous pouvez faire avec les requêtes Update. Imaginons que votre entreprise ait décidé de donner à tout le monde une augmentation de 10 % net. (Nous autres scripteurs avons manifestement au moins un point commun avec Sir Arthur Conan Doyle : nos écrits comme les siens décrivent des mondes imaginaires). Voici un script qui ouvre une table nommée Employees et modifie le champ Salary pour chaque employé. Sur quoi définit-il le champ Salary ? La requête définit ce champ sur le salaire actuel de l'employé multiplié par 1,1. En d'autres termes, il donne une augmentation de 10 % à chaque employé. (Si seulement c'était aussi facile que ça) ! Voici la requête :

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Qu'est-ce que ça a de sympa ? Comme vous pouvez le voir, vous pouvez faire des calculs dans vos requêtes Update. Avez-vous décidé de rallonger le contrat de tous vos employés temporaires de 60 jours ? Dans ce cas, une requête similaire à la suivante peut s'avérer pratique :

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Méthodes pratiques pour extraire les données

Puisque nous parlons de requêtes qui pourraient s'avérer utiles, bouclons notre histoire en examinant les méthodes pratiques d'extraction de données d'une base de données. Par exemple, voici un script simple et pourtant très utile. Supposons que notre base de données Inventory comprenne un champ Price, un champ qui, en toute logique, représente le prix d'un ordinateur. Voulez-vous connaître les cinq ordinateurs les plus chers de votre entreprise ? L'exemple de script de la figure 4 vous l'indiquera.

Figure 4 Trier le jeu d'enregistrements

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

Comme vous pouvez le voir, dans cette requête SQL, nous avons fait deux choses : nous avons trié le jeu d'enregistrements par prix (Order By Price) et nous avons demandé uniquement les cinq ordinateurs de tête (c'est-à-dire les cinq plus chers). C'est ce que fait la partie SELECT Top 5. Si nous avions voulu une liste des 10 ordinateurs les plus chers, nous aurions utilisé cette requête :

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Remarque. Nous avons également ajouté du code pour parcourir le jeu d'enregistrements et afficher le nom de chaque ordinateur. Nous n'avions pas besoin de ce code dans nos scripts précédents parce que ces scripts ne renvoyaient et n'affichaient pas de données.

Nous pouvons également obtenir les 10 % de tête en utilisant cette requête :

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Et si nous voulions maintenant les ordinateurs les plus économiques ? Aucun problème ; nous pouvons simplement utiliser la même approche, mais trier le jeu d'enregistrements selon un ordre décroissant (c'est-à-dire du prix le plus bas vers le prix le plus élevé). En d'autres termes, nous utilisons cette requête, avec DESC indiquant que le jeu d'enregistrements doit être trié dans l'ordre décroissant :

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Vous voyez ? Nous vous avions dit qu'ils étaient utiles.

En voici deux de plus que vous pourriez trouver intéressants. Supposons que votre base de données possède un champ nommé Budgeted pour le suivi de la quantité d'argent prévue à l'origine pour l'ordinateur. Vous voulez comparer le coût actuel au montant prévu par le budget ? Cette requête renvoie une liste des ordinateurs qui ont coûté davantage que le montant prévu :

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Entretemps, cette requête calcule le prix moyen de tous les ordinateurs (SELECT AVG(Price) FROM Computers) et renvoie une liste de tous les ordinateurs qui ont coûté moins que le prix moyen :

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Eh oui, vous avez raison, nous avons utilisé une requête de sélection dans notre clause Where. Comment cela fonctionne-t-il ? Tout cela est une autre histoire, pour un autre jour. Mais, comme vous pouvez le voir, elle ouvre la porte à l'extraction de toutes sortes d'informations pratiques avec vos requêtes SQL.

La morale de l'histoire

Comme nous l'avons fait remarquer au début de l'article de ce mois, nous n'étions pas sûrs de vouloir écrire un autre article sur les scripts de base de données. Nous sommes cependant contents de l'avoir fait malgré tout, en partie parce que nous pensons que vous trouverez certaines de ces requêtes utiles, mais également pour la raison suivante : si nous n'avions pas écrit cet article maintenant, nous aurions fini par devoir l'écrire un jour ou l'autre. Après tout, même Sir Arthur Conan Doyle fut forcé de ressusciter Sherlock Holmes. Il fit les premiers pas dans cette direction en écrivant un nouveau récit de Sherlock Holmes qui se passait avant sa mort. Après cela, cependant, il céda simplement à la pression de ses lecteurs, en concoctant une fable plus ou moins douteuse expliquant que Sherlock Holmes avait dû simuler sa propre mort. En fin de compte, tout le monde était content et tout était pour le mieux.

Ce qui est assez intéressant, c'est que, lorsque ce dernier récit de Sherlock Holmes parut, 30 000 nouveaux lecteurs s'abonnèrent à The Strand Magazine. Les éditeurs reconnaissants de Sir Arthur Conan Doyle en Grande-Bretagne et aux États-Unis répondirent en faisant de lui l'écrivain le mieux payé au monde.

Tiens... Sir Arthur Conan Doyle a redonné vie à un personnage très apprécié et est devenu l'écrivain le mieux payé du monde ; les scripteurs quant à eux ont redonné vie à un sujet très apprécié, et ceci sans avoir à simuler la mort de qui que ce soit. Vous pensez que les bonnes gens de TechNet Magazine l'ont remarqué ?

Les scripts embrouillés du Dr. Scripto

Le défi mensuel qui teste non seulement vos compétences à résoudre les énigmes, mais également vos compétences à écrire des scripts.

Avril 2008 : Choisissez une Lettre

Dans cette énigme du mois, vous devez insérer les lettres A à O dans les espaces bleus pour révéler le nom d'une fonction VBScript. Chaque lettre (A à O) doit être utilisée seulement une fois, et les lettres n'apparaissent pas dans l'ordre alphabétique. La lettre insérée peut être au début ou à la fin du nom de la fonction, ou n'importe où au milieu du nom de la fonction. Par exemple, dans la ligne suivante, vous devriez insérer la lettre D pour compléter le nom de fonction IsDate :

  (Cliquer sur l'image pour l'agrandir)

Maintenant, essayez. Insérez les lettres A à O pour révéler les noms de fonctions VBScript de cette grille :

**** (Cliquer sur l'image pour l'agrandir)

ANSWER:

Les scripts embrouillés du Dr. Scripto

Réponse : Choisissez une lettre, avril 2008

  (Cliquer sur l'image pour l'agrandir)

Les scripteurs Microsoft travaillent pour (enfin, sont employés par) Microsoft. Lorsqu’ils ne sont pas en train de jouer au base-ball, d’entraîner une équipe ou de regarder un match (ou de se livrer à de multiples autres activités), ils dirigent le Script Center TechNet. Vous pouvez le vérifier vous-même sur www.scriptingguys.com.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.