DAO ain't DOA

The latest build that I know about is DAO.DBEngine.120.

Its a bit tricky to write in powershell. Below is the code just in case anyone is looking for it:


    $iret = [System.Reflection.Assembly]::LoadWithPartialName("System.Management")
    $mc = new-object System.Management.ManagementClass
    $mc.Path.NamespacePath = "root\cimv2"
    $mc.Path.ClassName = "Win32_Process";
    $mc.Scope.Options.Authentication = [System.Management.AuthenticationLevel]::PacketPrivacy;
    $mc.Scope.Options.Impersonation = [System.Management.ImpersonationLevel]::Impersonate;
    $moc = $mc.GetInstances()
    $DBEngine = new-Object -comobject DAO.DBEngine.120
    $DBEngine.CreateDatabase("C:\Melody.mdb",  ";LANGID=0x0409;CP=1252;COUNTRY=0", 64)
    $db = $DBEngine.OpenDatabase("C:\Melody.mdb", $false, $false, "")
    $rs = $db.OpenRecordset("Properties");
    foreach($mo in $moc)
    {
         $rs.AddNew()
         $x=0
         $Fields = $rs.GetType().InvokeMember("Fields", [System.Reflection.BindingFlags]::GetProperty, $null, $rs, $null)
         foreach($Field in $Fields)
         {
                [System.String] $Name = $Field.Name
                [System.String] $Value = $mo.Properties.Item($Name).Value
                $Field.Value = $Value
         }
         $x=0
         $rs.Update()
    }

Create a SQL Database using Powershell and SQLClient

First, I am by far no expert when it comes to knowing all the ins and outs of SQL Server.  What I do know is this, you can create a database, create and populate a table and then render the information in a variety of ways using Powershell.


$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.SqlClient.SqlConnection
$con.ConnectionString="Data Source=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery

You can do the same thing with Odbc and OleDb as well.

Here's the OleDb example:

$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.OleDb.OleDbConnection
$con.ConnectionString="Provider=SQLOLEDB;Data Source=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.OleDb.OleDbCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery



And when using Odbc:

$iret = [System.Reflection.Assembly]::loadWithPartialName("System.Data")
$con = new-object System.Data.Odbc.OdbcConnection
$con.ConnectionString="Driver={SQL Server};Server=.;Integrated Security=sspi;"
$con.Open()

$cmd = new-object System.Data.Odbc.OdbcCommand()
$cmd.Connection = $con
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "CREATE Database DataOne"
$cmd.ExecuteNonQuery

Tomorrow, I'll show you how to create and populate the table.

To Text Or Not To Text -- I just want to be in control

If you are like me -- another programmer forced to learn Powershell -- you want to be in control of the output.  What output format it is in and just how easy it is two read comes quickly to mind.
With that thought in mind, I decided to see if I could get the textwriter to work for me.

I did this by using the following code:

[System.IO.TextWriter]$txtstream = new-object System.IO.Streamwriter([System.Environment]::CurrentDirectory + "\WillItWork.txt")

That worked perfectly fine. To close this object out:

$txtstream.Flush()
$txtstream.Close()
$txtstream = $null

The programer's mindset text formats include: csv, excel spreadsheets, hta, html, attribute xml, element xml, element xml for xsl, and xsl -- just to name a few. Some of these can also be put into different formats such as Single and multi line horizontal and vertical views as well as the text being formatted for a more table like view of the data.

Please use the 32 bit version of Powershell for the following code as the code will fail in the 64 bit version. Jet is not supported in the 64 bit world.

$con = new-object -comobject ADODB.Connection
$con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb")
$rs = new-object -comobject ADODB.Recordset
$rs.ActiveConnection = $con
$rs.CursorLocation = [ADODB.CursorLocationEnum]::adUseClient
$rs.LockType = [ADODB.LockTypeEnum]::adLockOptimistic
$rs.let_Source("Select * From Products")
$rs.Open() 

Now that I have an open recordset, I want to do something with the information. So, I'm going to create some xml:

[System.IO.TextWriter]$txtstream = new-object System.IO.Streamwriter([System.Environment]::CurrentDirectory + "\product.xml")
$con = new-object -comobject ADODB.Connection
$con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb")
$rs = new-object -comobject ADODB.Recordset
$rs.ActiveConnection = $con
$rs.CursorLocation = [ADODB.CursorLocationEnum]::adUseClient
$rs.LockType = [ADODB.LockTypeEnum]::adLockOptimistic
$rs.let_Source("Select * From Products")
$rs.Open()
$txtstream.WriteLine("<?xml version='1.0' encoding='iso-8859-1'?>")
$txtstream.WriteLine("<data>")
$txtstream.WriteLine("<Products>")
$rs.MoveFirst();
for($y=0;$y -lt $rs.RecordCount;$y++)
{
     $txtstream.WriteLine("<Product>")
     for($x=0;$x -lt $rs.Fields.Count;$x++)
     {
         $fld = $rs.Fields[$x]
         $Name = $fld.GetType().InvokeMember("Name", [System.Reflection.BindingFlags]::GetProperty, $null, $fld, $null)
         $Value = $fld.GetType().InvokeMember("Value", [System.Reflection.BindingFlags]::GetProperty, $null, $fld, $null)
         $txtstream.WriteLine("<" + $Name + "><![CDATA[" + $Value + "]]></" + $Name  + ">")
     }
     $txtstream.WriteLine("</Product>")
     $rs.MoveNext()
}
$txtstream.WriteLine("</Products>")
$txtstream.WriteLine("</data>")
$txtstream.Flush()
$txtstream.Close()
$txtstream = $null

Get-WMIObject To be or not to be -- a collection or an object

Consider this code:

$mystr = ""
$ws = new-object -comobject WScript.Shell
$path = $ws.CurrentDirectory + "\Win32_Process.csv"
$fso = new-object -comobject Scripting.FileSystemObject
$txtstream = $fso.OpenTextFile($path, 2, $true, -2)
$moc = Get-WMIObject -namespace root\cimv2 -class Win32_Process
$mocEnum = $moc.GetType().InvokeMember('GetEnumerator', 'InvokeMethod',$Null, $moc, $Null)
while($mocEnum.MoveNext())
{
    $obj =  $mocEnum.Current
    foreach($prop in $obj.Properties)
    {  
        if($mystr -ne "")
        {
            $mystr += ","
        }
        $mystr += $prop.Name 
    }
    $txtstream.WriteLine($mystr)
    $mystr = ""
    break     
}
$mocEnum.Reset()

while($mocEnum.MoveNext())
{
    $obj = $mocEnum.Current
    foreach($prop in $obj.Properties)
    {  
        if($mystr -ne "")
        {
            $mystr += ","
        }
        $tstr = '"'
        $tstr += $prop.value
        $tstr += '"'
        $mystr += $tstr 
    }
    $txtstream.WriteLine($mystr)
    $mystr = ""        
} 

As it stands, this code works well to create a csv file.  And, yes, you can do the same using the PSObject. The problem is, the code assumes the Get-WMIObject will return a collection of objects and in some cases it will not.

Exception calling "InvokeMember" with "5" argument(s): "Method 'System.Management.ManagementObject.GetEnumerator' not found."
At C:\Users\Administrator\Desktop\Test.ps1:2 char:39
+ $mocEnum = $moc.GetType().InvokeMember <<<< ('GetEnumerator', 'InvokeMethod',$Null, $moc, $Null)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
You cannot call a method on a null-valued expression.
At C:\Users\Administrator\Desktop\Test.ps1:3 char:24
+ while($mocEnum.MoveNext <<<< ())
+ CategoryInfo : InvalidOperation: (MoveNext:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull


And, by the way, the code works fine using foreach enumerations of the objects.

Powershell Access The Full monty

Powershell: Learning something new everyday

Learning something new everyday is what makes programming interesting. The lesson learned today is to stop thinking powershell is "just like any other language".  Because it is not.

Consider the following:

My task is to check the local registry for the version of the OS.  So, in line, I did the following to check it:

PS C:\Users\Administrator> $HKEY_LOCAL_MACHINE = 2147483650
PS C:\Users\Administrator> $Reg = [WMIClass]"ROOT\DEFAULT:StdRegProv"
PS C:\Users\Administrator> $Key = "SOFTWARE\Microsoft\Windows NT\CurrentVersion"
PS C:\Users\Administrator> $Result = $Reg.GetStringValue($HKEY_LOCAL_MACHINE, $Key, "CurrentVersion")
PS C:\Users\Administrator> write-host $Result
System.Management.ManagementBaseObject
PS C:\Users\Administrator> $Result.GetText(0)
instance of __PARAMETERS
{
        ReturnValue = 0;
        sValue = "6.1";
};

Okay, everything looks good. In line, that is.  What happens when you want to put the routine into a function and call it anywhere.

Well, it turns out, you can try, but you'll be wasting a lot of hours.

First, let's create the function wrapper:

function get_operatingsystem_version()
{

}


Now, you would think, if you've been programming for as long as I have, that you would od the following:

$HKLM = 2147483650
$Key = "SOFTWARE\Microsoft\Windows NT\CurrentVersion"

$Version = get_operatingsystem_version
if ($Version -ne  "Error")
{
      write-host $version
}
function get_operatingsystem_version()
{
      $Reg = [WMIClass]"ROOT\DEFAULT:StdRegProv"
      $Result = $Reg.GetStringValue($HKLM, $Key, "CurrentVersion")
      if($Result.ReturnValue -eq 0)
      {
            return  $Result.sValue          
      }
      else
      {
            return   "Error"    
      }
}
Should work, right?  Doesn't. In fact, it comes back with the following error:

The term 'get_operatingsystem_version' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At C:\Users\Administrator\Desktop\GetOS.ps1:5 char:39
+ $Version = get_operatingsystem_version <<<<
    + CategoryInfo          : ObjectNotFound: (get_operatingsystem_version:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
PS C:\Users\Administrator>
Why did this happen?  Because the function has not yet been seen by Powershell at the point where the call is being made.

Now, let's try it this way:

$HKLM = 2147483650
$Key = "SOFTWARE\Microsoft\Windows NT\CurrentVersion"

function get_operatingsystem_version()
{
      $Reg = [WMIClass]"ROOT\DEFAULT:StdRegProv"
      $Result = $Reg.GetStringValue($HKLM, $Key, "CurrentVersion")
      if($Result.ReturnValue -eq 0)
      {
            return  $Result.sValue          
      }
      else
      {
            return   "Error"    
      }
}

$Version = get_operatingsystem_version
if ($Version -ne "Error")
{
write-host $version
}
When I run this, I get:

PS C:\Users\Administrator> C:\Users\Administrator\Desktop\GetOS.ps1
6.1