Converting Database Records into PowerShell objects

When you access a database, the result is not automatically wrapped as objects so you cannot pipe the result into other cmdlets like Sort-Object or Group-Object. You can with PowerShell v.2. Here is a sample based on a previous tip (refer to the previous post regarding general database access):

$objConnection = New-Object -comobject ADODB.Connection
$objConnection.Open("myDB")
$objRS = $objConnection.Execute("SELECT * FROM TableName")
while ($objRS.EOF -ne $True) {
$hash = @{}
foreach ($field in $objRS.Fields) {
$hash.$($field.name) = $field.value
}
#$hash
New-Object PSObject -property $hash
$objRS.MoveNext()
}

The point which you should review occurs inside the while loop. For each recordset, an empty hash table is created and then filled with the fields and field values. In PowerShell v.2, New-Object can convert a hash table into a real object, which is then output. As you see, all database records surface as real objects, and each table column becomes an object property.

[via Powershell.com]

Opening Databases from PowerShell

The easiest way of accessing databases right from PowerShell is to visit control panel and open the Data Sources (ODBC) module (which resides in Administrative Tools inside control panel). Use the GUI to set up the database type by clicking the "User DN" or "System DN" tab and then click Add. Remember to write down the "Data Source name" you assign because that’s the only thing PowerShell is going to need.

You should next use this code (replace "myDataSource" with the data source name you assigned, and replace TableName with the table name inside your database you want to display):

$objConnection = New-Object -comobject ADODB.Connection
$objConnection.Open("myDataSource")
$objRS = $objConnection.Execute("SELECT * FROM TableName")
while ($objRS.EOF -ne $True) {
foreach ($field in $objRS.Fields) {
'{0,30} = {1,-30}' -f $field.name, $field.value
}
''
$objRS.MoveNext()
}

[via Powershell.com]

Running PowerShell Scripts as Scheduled Task – like Cronjobs for Windows!!

If you have jobs that need to execute regularly, you can manage them with a PowerShell script and make it a scheduled task:

schtasks /CREATE /TN CheckHealthScript /TR "powershell.exe `
-noprofile -executionpolicy Unrestricted `
-file %public%\checkhealth.ps1" /IT /RL HIGHEST /SC DAILY

To remove the scheduled task, specify the name you assigned:

schtasks /DELETE /TN CheckHealthScript

Just like a Cronjob, but in Windows!! Use this to run rebuilds and updates of software to web. Automate the build numbers etc, and never worry about manually releasing interim builds again.

[via Powershell.com]

Creating Large Dummy Files With .NET

You can always resort to the underlying .NET framework whenever the functionality you need isn’t available through a cmdlet.

The following code is a very fast way to generate really large test files:

$path = "$env:temp\testfile.txt"
$file = [io.file]::Create($path)
$file.SetLength(1gb)
$file.Close()Get-Item 
$path

[via PowerShell.com]

Powershell – Get-CountryForIPAddress function

Just a little looping script to take an IP address and return the country and country code.

function Get-CountryForIPAddress {
    param(
    [Parameter(Position=0, Mandatory=$FALSE, ValueFromPipeline=$TRUE)]
    [String] $IPAddress="10.0.0.1" )
    process {
        "Looking up: {0}" -f $IPAddress
        $s = new-webserviceproxy -uri http://www.webservicex.net/geoipservice.asmx 
        foreach ($addr in $IPAddress) {
            $result = $s.GetGeoIP($addr)
            "Country: {0} ({1})" -f $result.countryname, $result.countrycode
        }
    }
}
while ($IPAdd -ne "exit") {
    Write-Host "Enter IP Address ('exit' to finish):" -ForegroundColor Green
    $IPAdd = read-host
    if ($IPAdd -ne "exit") {
        $a = [string] $IPAdd
        $b = $a.split(",")
        foreach ($ip in $b) {
            Get-CountryForIPAddress $ip
        }
    }
}

Copy the code block, and paste into your text editor. Save as Get-CountryForIPAddress.ps1 and then run in Powershell. Done!

what I’ve been doing

I’ve been quite a bit of work in Powershell recently, just making sure I’m up to speed with it. I’ve found all my meddling in PHP quite useful and relevant actually, which is fantastic. Working with building GUIs in Powershell too, which is so much nicer than just doing plain CMD looking screens. Although, applying color to Powershell output is easily done too.

I’ve pulled/hacked together a couple of nice little scripts in the meantime, so I’ve posted them on the useful code page for your looking pleasure.

This year’s Google CodeJam is on the way too; just got my invite email to enter in a month’s time. Looking forward to it again; this year I’m going to not confuse my data type sizes like last year (such a n00b FAIL moment!!). Undecided on language as yet, tossing up between PHP and C#, but I want to do some prep and practice before making a call on that. Really, it’s going to involve having my iterable arrays pre-planned and ready to go. IEEE Xtreme 2009 t-shirts have arrived now too, so I’ll be getting mine next week!! Wahoo!!

On the job hunting front, I’ve had 2nd interview for one job which would be really cool; but another job has just appeared which looks purpose-built for me!! Even my boss commented that he thought they had written the job-spec and PD for me. Not going to say too much, ‘cause I don’t want to jinx it, but yeah.

quick update – needing to get posting again….

I haven’t posted for a while, been off-line a bit, and doing a lot of reading and preparation for job hunting. It’s interesting times at the moment; there are jobs out there, but they don’t just leap out at you and chase you down. When I find the one, I’ll definitely be letting you all know.

I’ve moved to using Windows Live Writer now, as well as a couple of plug-ins to enable easier posting of code etc. Hopefully this gets me back to blogging a lot more regularly, and also posting some more tutorials and sample scripts. I’ve been working in PHP lots, and diving back into shell scripting (mainly in PowerShell – which I’m loving!!). Most people will balk there and scream GEEK!!! But, for those who understand, they will appreciate the joy I’m feeling (yes, joy is a relative term).

I’ll be pulling some stuff together over the next few days and get a full update and some new tutorials etc up mid-week.