Hey, Scripting Guy!
Werken met Access-databases in Windows PowerShell D e M I C R O S O F T S C R I P T I N G G U Y S b e a nt woord e n :
Hoe kan ik naar een Access-database schrijven? Er kleeft iets mysterieus aan databases. Hoewel een database in principe alleen maar een soort archiefkast is waarin informatie wordt opgeslagen, kunnen er wonderlijke dingen gebeuren zodra die informatie ergens voor wordt toegepast. Want het draait uiteindelijk om die informatie. Aan een lege database heeft natuurlijk niemand iets, ook al is het ontwerp nog zo schitterend. Een database zonder gegevens bestaat in feite niet. Als iemand het over een grote database heeft, wordt er doorgaans met ontzag gereageerd. Niet vanwege de database, maar vanwege de inhoud. Hoe komen al deze gegevens nu eigenlijk in een database terecht? De handmatige invoer van gegevens in een
database dateert nog van voor de tijd van de ponskaarten. Die tijd is echter voorgoed voorbij, want een volwassen database waarmee je indruk maakt bij je vrienden, en die alle mysteries van het netwerk weet te ontrafelen, kan het tegenwoordig niet meer zonder automatisering stellen. Vandaag de dag praat je dan al snel over Windows PowerShell. Dat gaan we in dit artikel dan ook doen. We gaan PowerShell gebruiken om gegevens over de lokale computer te verzamelen en weg te schrijven naar een Office Access-database met de naam ComputerData.mdb. U kunt deze database handmatig maken of met behulp van het script uit het artikel ‘How Can I Create a Database with More Than
Figuur 1 Scriptomatic helpt bij het aanmaken van een aanroep van een WMI-class 54
juli 2009
One Table?’ op microsoft.com/technet/ scriptcenter/resources/qanda/feb09/ hey0217.mspx. Het nieuwe script zullen we WriteToAccessDatabase.ps1 noemen, zodat we weten wat het doet. Om te zorgen dat het pad naar de database klopt, hebben we allereerst de functie Check-Path nodig. We kunnen deze functie maken door het trefwoord Function te gebruiken, de functie een naam te geven en de m ogelijke invoervariabelen te definiëren. CheckPath gebruikt de cmdlet Test-Path om te controleren of de directory in het databasepad bestaat. Hiertoe wordt de cmdlet Split-Path gebruikt om het pad op te splitsen in een hoofd- en sub gedeelte. Om het bestaan van de directory te controleren hebben we alleen het hoofdgedeelte van het pad nodig. We gebruiken de volgende syntaxis voor Split-Path om het hoofdpad op te halen: TechNet Magazine
PS C:\> Split-Path C:\fso\ComputerData.mdb -Parent C:\fso
We kunnen controleren of het pad bestaat, maar ook of het niet bestaat. Dit doen we met de operator Not (!). Als de map niet bestaat, wordt een fout weergegeven via het trefwoord Throw:
Figuur 2 Kies het juiste type vergrendeling om records bij te werken Constante
Waarde
Beschrijving
adOpenDynamic
2
Dynamische cursor. Veranderingen door andere gebruikers zichtbaar. Alle bewegingen door de Recordset toegestaan, behalve bookmarks, zonder toestemming van provider.
adOpenForwardOnly
0
Default. Forward-only cursor: u kunt alleen vooruit scrollen. Kan performance verhogen.
adOpenKeyset
1
Keyset-cursor. Zelfde als dynamische cursor, maar geen zicht op documenten van anderen. Hun veranderingen wel te zien.
adOpenStatic
3
Statische cursor: statische kopie van set bestanden om in te werken. Veranderingen van anderen onzichtbaar.
adOpenUnspecified
-1
Een type cursor wordt niet gespecificeerd.
Function Check-Path($Db) {
If(!(Test-Path -path (Split-Path -path $Db
-parent))) {
Throw “$(Split-Path -path $Db -parent) Does not Exist” }
Function Get-Bios
Maar zelfs als de map bestaat, kan het databasebestand nog steeds ontbreken. Om dat te ondervangen gebruiken we het trefwoord ELSE. Vervolgens gebruiken we opnieuw de instructie IF om te controleren of het database bestand bestaat en het trefwoord Throw om een fout terug te geven als dat niet het geval is: ELSE {
If(!(Test-Path -Path $Db)) {
Throw “$db does not exist”
}
}
} #End Check-Path
Eigenlijk kunnen we het bestaan van de database ook controleren zonder de constructie IF…ELSE. Het gebruik van de cmdlet Test-Path in combinatie met de parameter –path zou hetzelfde effect opleveren. Maar met IF…ELSE krijgen we meer feedback. We willen namelijk zowel het bestaan van de directory als het hierin aanwezige bestand vaststellen. Het is immers niet ondenkbaar dat de database zich niet in de map bevindt, maar het kan ook zo zijn dat de complete map ontbreekt. Op deze manier krijgen we een meer gedetailleerde foutmelding die van pas komt bij een eventuele probleemoplossing. Als we zeker weten dat de database bestaat, zetten we de functie Get-Bios aan het werk om de BIOS-gegevens van de WMI-class Win32_Bios op te halen. De functie Get-Bios ziet er als volgt uit: TechNet Magazine
{
Get-WmiObject -Class Win32_Bios
} #End Get-Bios
Door de WMI-aanroep in een functie onder te brengen kan deze achteraf nog eenvoudig worden aangepast om bijvoorbeeld beheer op afstand of invoer van referenties toe te voegen. Een dergelijke aanpassing kan worden verricht zonder verdere gevolgen voor het script. Als tijdens het testen blijkt dat het niet werkt, kunt u de functiecode eenvoudig als commentaar opnemen en de rest van het script vervolgens normaal uitvoeren. Als u hulp wilt bij het zoeken naar informatie over WMI-classes, gebruikt u de Scriptomatic van Windows PowerShell (zie Figuur 1). Met deze tool kunt u eenvoudig informatie opzoeken over WMI-namespaces en –classes en het daarvoor benodigde Windows PowerShell-script automatisch maken. De tool is te downloaden van micro-
soft.com/technet/scriptcenter/tools/ psomatic.mspx. De volgende stap betreft de functie GetVideo, waarmee de videogegevens van de WMI-class Win32_VideoController worden opgehaald. Zoals u ziet, verschilt deze functie niet wezenlijk van de functie Get-Bios: Function Get-Video {
Get-WmiObject -Class Win32_VideoController
} #End Get-Video
Nu zijn we zover dat we verbinding kunnen gaan maken met de database. Hiertoe gebruiken we de functie Connect-Database. De functie ConnectDatabase krijgt twee invoerparameters (–DB en –Tables) waarvan de waarden worden opgeslagen in de variabelen $Db en $Tables binnen de functie. Allereerst zorgen we ervoor dat binnen de functie Connect-Database waarden worden toegewezen aan de variabelen die bepalen hoe de RecordSet wordt
Figuur 3 Toegestane waarden voor de parameter LockType Constante
Waarde
Beschrijving
AdLockBatchOptimistic
4
Duidt optimistische batch-updates aan. Vereist voor de modus batch-updates.
AdLockOptimistic
3
Er is sprake van een optimistische vergrendeling, record voor record. De provider maakt gebruik van een optimistische vergrendeling, waarbij records alleen worden vergrendeld wanneer sprake is van een update.
AdLockPessimistic
2
Duidt op pessimistische vergrendeling, record voor record. De provider voert de noodzakelijke handelingen uit om bewerking van de records mogelijk te maken. Dit gebeurt doorgaans door de records in de gegevensbron direct bij aanvang te vergrendelen.
adLockReadOnly
1
De records zijn read-only. U kunt de gegevens niet wijzigen.
adLockUnspecified
-1
Er is geen vergrendelingstype. In het geval van klonen, krijgt de kloon hetzelfde vergrendelingstype als het origineel. juli 2009
55
Hey, Scripting Guy!
Figuur 4 Het databaseschema voor de database ComputerData g eopend. De vijf verschillende parameters voor de methode Open van het object RecordSet worden als volgt opgenomen: RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options
De eerste parameter is de bron parameter, die resulteert in een geldig opdrachtobject, een SQL-instructie, een tabelnaam, een opgeslagen-procedureaanroep, een URL of de naam van een bestand of stream-object met een persistent opgeslagen Recordset. De tweede parameter is ActiveConnection, een string die resulteert in een geldig verbindingsobject of een string met connectionstring-parameters. De parameter CursorType wordt gebruikt om te bepalen met welk type cursor de RecordSet wordt geopend. De toegestane waarden voor het type cursor worden weergegeven in Figuur 2. De parameter LockType wordt gebruikt om te bepalen welk type vergrendeling van toepassing is voor het bijwerken van records, terwijl de parameter Options aangeeft hoe de bronparameter moet worden uitgevoerd. De toegestane waarden voor de parameter LockType worden weergegeven in Figuur 3. Alle vijf parameters voor de methode Open van het object RecordSet zijn optioneel, en doorgaans worden alleen de eerste vier gebruikt. Nadat de waar56
juli 2009
den voor het type cursor en vergrendeling zijn toegewezen, gebruikt u de cmdlet New-Object om een nieuw ADODB.Connection-object te maken dat wordt opgeslagen in de variabele $connection. Vervolgens gebruikt u de methode Open van het Connectionobject, waarbij de naam van de p rovider en de gegevensbron moeten worden opgegeven. Daarna wordt de functie Update-Records aangeroepen en de variabele $Tables doorgegeven. De functie Connect-DataBase ziet er zo uit: Function Connect-Database($Db, $Tables) {
$OpenStatic = 3
$LockOptimistic = 3 $connection = New-Object -ComObject ADODB. Connection
$connection.Open(“Provider = Microsoft.Jet. OLEDB.4.0;Data Source=$Db” ) Update-Records($Tables)
} #End Connect-DataBase
Binnen de functie Update-Records gebruiken we allereerst de cmdlet NewObject om een exemplaar te m aken van het object ADODB.RecordSet. Het nieuwe object RecordSet wordt vervolgens opgeslagen bij de variabele $RecordSet. De hele reeks tabellen wordt daarna doorlopen met de instructie For Each. De tabelnamen worden opgeslagen bij de variabele $Tables en toegewezen zodra het script wordt uitgevoerd. Als
nderdeel van de ForEach-lus wordt o een query uitgevoerd op basis van de a lgemene instructie Select * from $Table. Het gebruik van een variabele voor de tabelnaam heeft als v oordeel dat de code maar eenmaal hoeft te w orden geschreven. Telkens wanneer de array met tabelnamen wordt doorlopen, wordt een andere tabelnaam gebruikt in de query. Hiermee zijn we aangekomen bij de methode Open van het object RecordSet. Hoe de RecordSet wordt geopend, wordt bepaald door de query voor de variabele $Query, het verbindingsobject in de variabele $Connection, de waarde voor $OpenStatic en de waarde voor $LockOptimistic. Aansluitend gebruiken we de cmdlet Invoke-Expression om de waarde van een string uit te voeren. Dit is nodig omdat we twee functies hebben gemaakt voor het bijwerken van de verschillende databasetabellen. De functies zijn vernoemd naar de tabellen die ermee worden bijgewerkt. Een functie met een naam die deels uit een variabele bestaat, kan niet worden aangeroepen. Daarom moeten we eerst de variabele oplossen voordat we de functie kunnen aanroepen. Maar dat kan hier alleen via een omweg. We moeten er namelijk voor zorgen dat de functienaam als een string wordt behandeld en niet als een opdracht. Toch willen we de functie als een opdracht uitvoeren. Daarom gebruiken we Invoke-Expression. Deze cmdlet zorgt ervoor dat de verschillende updatefuncties afzonderlijk worden aangeroepen. Tijdens het updaten wordt elk RecordSet-object gesloten waarna het volgende item in de lus van doorzochte tabellen wordt opgehaald, de volgende query wordt uitgevoerd, het volgende RecordSet-object wordt geopend en de volgende functie wordt aangeroepen. Dit wordt als volgt herhaald voor elke tabel in de reeks tabellen: Function Update-Records($Tables) {
$RecordSet = new-object -ComObject ADODB. Recordset
ForEach($Table in $Tables) {
$Query = “Select * from $Table” TechNet Magazine
$RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
Invoke-Expression “Update-$Table” $RecordSet.Close()
}
Zodra de records zijn bijgewerkt, kan de verbinding worden gesloten. Hiervoor gebruiken we de methode Close van het Connection-object: $connection.Close()
} #End Update-Records
Met de functie Update-Records worden de twee aanvullende functies UpdateBios en Update-Video aangeroepen. Deze functies zorgen ervoor dat de velden van de tabel in kwestie worden bij-
gewerkt. Als u het aantal tabellen van de database in een later stadium uitbreidt, moet u een extra Update*-functie toevoegen om deze nieuwe tabellen bij te werken. Aanbevolen wordt om de databasevelden dezelfde naam te geven als die van de WMI-eigenschap. Zo kunt u alles overzichtelijk houden. Als u een script schrijft om een bestaande database bij te werken, wilt u wellicht het databaseschema bekijken om meer te weten te komen over de aanwezige tabellen, kolommen en gegevenstypen van de velden. Het databaseschema van de database ComputerData is te zien in Figuur 4. Deze weergave werd gemaakt met het script uit het artikel ‘How Can I Tell Which Tables and Columns Are
Function Check-Path($Db) {
If(!(Test-Path -path (Split-Path -path $Db -parent))) { }
Throw “$(Split-Path -path $Db -parent) Does not Exist”
$connection.Close()
} #End Update-Records Function Update-Bios {
“Updating Bios”
$BiosInfo = Get-Bios
ELSE {
$RecordSet.AddNew()
If(!(Test-Path -Path $Db))
$RecordSet.Fields.Item(“DateRun”) = Get-Date
{
$RecordSet.Fields.Item(“Manufacturer”) = $BiosInfo.Manufacturer
Throw “$db does not exist”
$RecordSet.Fields.Item(“SerialNumber”) = $BiosInfo.SerialNumber
}
}
$RecordSet.Fields.Item(“SMBIOSBIOSVersion”) = $BiosInfo.SMBIOSBIOSVersion $RecordSet.Fields.Item(“Version”) = $BiosInfo.Version
} #End Check-Path Function Get-Bios {
Get-WmiObject -Class Win32_Bios
} #End Get-Bios
Function Get-Video {
Get-WmiObject -Class Win32_VideoController
} #End Get-Video
Function Connect-Database($Db, $Tables) {
$OpenStatic = 3
$RecordSet.Update()
} #End Update-Bios
Function Update-Video {
“Updating video”
$VideoInformation = Get-Video
Foreach($VideoInfo in $VideoInformation) {
$RecordSet.AddNew()
$RecordSet.Fields.Item(“DateRun”) = Get-Date
$RecordSet.Fields.Item(“AdapterCompatibility”) = $VideoInfo.
AdapterCompatibility
$RecordSet.Fields.Item(“AdapterDACType”) = $VideoInfo.AdapterDACType $RecordSet.Fields.Item(“AdapterRAM”) = $VideoInfo.AdapterRAM
$LockOptimistic = 3
$RecordSet.Fields.Item(“Description”) = $VideoInfo.Description
$connection = New-Object -ComObject ADODB.Connection
$RecordSet.Fields.Item(“DriverDate”) = $VideoInfo.DriverDate
$connection.Open(“Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$Db” )
$RecordSet.Fields.Item(“DriverVersion”) = $VideoInfo.DriverVersion
Update-Records($Tables)
} #End Connect-DataBase
Function Update-Records($Tables) {
in a Database without Opening It?’ op icrosoft.com/technet/scriptcenter/ m resources/qanda/feb09/hey0218.mspx. Aan het begin van de functie UpdateBios plaatsen we de opmerking dat het hier gaat om een update van de BIOSgegevens. De functie Get-Bios wordt vervolgens aangeroepen en het resulterende WMI-object Win32_Bios wordt opgeslagen in de variabele $BiosInfo. Vervolgens gaan we een record toevoegen aan de databasetabel. Dit doen we met de methode AddNew van het RecordSet-object. Als het nieuwe record is gemaakt, vullen we de velden in de tabel met gegevens. Wanneer alle velden zijn bijgewerkt, roepen we de methode Update aan om het record in de tabel
$RecordSet = new-object -ComObject ADODB.Recordset ForEach($Table in $Tables) {
$Query = “Select * from $Table”
$RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic) Invoke-Expression ìUpdate-$Tableî $RecordSet.Close()
$RecordSet.Update()
}
} #End Update-Video # *** Entry Point to Script *** $Db = ìC:\FSO\ComputerData.mdbî $Tables = “Biosî,îVideo” Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables
}
Figuur 5 WriteToAccessDataBase.ps1 TechNet Magazine
juli 2009
57
Hey, Scripting Guy!
door te voeren. De functie Update-Bios ziet er in zijn geheel zo uit: Function Update-Bios {
“Updating Bios”
$BiosInfo = Get-Bios $RecordSet.AddNew()
$RecordSet.Fields.Item(“DateRun”) = Get-Date $RecordSet.Fields.Item(“Manufacturer”) = $BiosInfo.Manufacturer
$RecordSet.Fields.Item(“SerialNumber”) = $BiosInfo.SerialNumber
$RecordSet.Fields.Item(“SMBIOSBIOSVersion”) = $BiosInfo.SMBIOSBIOSVersion
$RecordSet.Fields.Item(“Version”) = $BiosInfo. Version
$RecordSet.Update()
} #End Update-Bios
Nadat de BIOS-tabel is bijgewerkt, is het de beurt aan de videotabel. Deze tabel gaan we bijwerken met de functie Update-Video, die in wezen niet verschilt van de functie Update-Bios. Allereerst laten we aan de hand van een bericht weten dat het om een update van de videotabel gaat. Vervolgens roepen we de functie Get-Video aan om de videogegevens op te halen. Daarna wordt de methode AddNew aangeroepen om een nieuw record toe te voegen aan de videotabel, waarna alle gegevens
naar de desbetreffende velden worden weggeschreven. Wanneer dit gebeurd is, roepen we de methode Update aan. Het aantal videocontrollers op de computer kan een knelpunt vormen bij het vergaren van de videogegevens. Op mijn computer is een dochterkaart geïnstalleerd die meerdere videocontrollers als resultaat kan geven. Daarom gebruiken we de instructie ForEach om de verzameling Win32_VideoControllers te doorzoeken. Als u geen interesse hebt in de configuratie met een dochterkaart of als u een dual-channel videokaart hebt die tweemaal dezelfde gegevens als resultaat geeft, kunt u de ForEach-lus weglaten en $VideoInfo[0] selecteren om het eerste record dat wordt opgehaald direct als index te gebruiken. De keerzijde van deze aanpak is dat er een foutmelding wordt gegenereerd wanneer de query in een singleton resulteert, omdat een index van één record niet mogelijk is: Function Update-Video {
“Updating video”
$VideoInformation = Get-Video
Foreach($VideoInfo in $VideoInformation) {
$RecordSet.AddNew()
$RecordSet.Fields.Item(“AdapterDACType”) = $VideoInfo.AdapterDACType
$RecordSet.Fields.Item(“AdapterRAM”) = $VideoInfo.AdapterRAM
$RecordSet.Fields.Item(“Description”) = $VideoInfo.Description
$RecordSet.Fields.Item(“DriverDate”) = $VideoInfo.DriverDate
$RecordSet.Fields.Item(“DriverVersion”) = $VideoInfo.DriverVersion $RecordSet.Update()
}
} #End Update-Video
Hieronder ziet u dat het script begint met een verwijzing naar de database en een vermelding van de tabellen en dat vervolgens de functie connect-DataBase wordt aangeroepen: $Db = “C:\FSO\ComputerData.mdb”+ $Tables = “Bios”,”Video” Check-Path -db $Db
Connect-DataBase -db $Db -tables $Tables
Als u het script hebt uitgevoerd, worden de nieuwe records weggeschreven naar de database ComputerData.mdb (zie Figuur 6). In Figuur 5 ziet u het volledige script WriteToAccessDatabase.ps1. }
$RecordSet.Fields.Item(“DateRun”) = Get-Date $RecordSet.Fields.Item(“AdapterCompatibility”) = $VideoInfo.AdapterCompatibility
Ed Wilson, een bekende scriptexpert, is de auteur van acht boeken, waaronder Windows PowerShell Scripting Guide (2008) en Microsoft Windows PowerShell Step by Step (2007). Ed bezit meer dan twintig branchecertificeringen, waaronder Microsoft Certified Systems Engineer (MCSE) en Certified Information Systems Security Professional (CISSP). In zijn vrije tijd houdt hij zich bezig met houtbewerking, onderwaterfotografie en scubaduiken. Daarnaast is hij dol op thee.
Figuur 6 Nieuwe records toegevoegd aan de database ComputerData.mdb 58
juli 2009
Craig Liebendorfer is een woordensmid en sinds lange tijd Microsoft web-editor. Craig kan het nog steeds niet geloven dat hij wordt betaald om elke dag met woorden bezig te zijn. Eén van zijn favoriete dingen is respectloze humor, dus hij zit hiermee precies op zijn plaats. Hij vindt zijn fantastische dochter de grootste prestatie in zijn leven. TechNet Magazine
