Why makes this return at the end of a PowerShell script a difference?

Question

3rd Edit:

The only difference between the following two scripts (besides the name of the function) is the fact, that the second doesn't return the result via explicit return. But they behave different. The first shows the two rows

abc
efg

in the grid, while the second shows to empty rows in the grid.

Skript 1:

ipmo WPK

$ConnectionString = $ConnectionString = "Server=localhost;Integrated Security=True"
$conn = new-object System.Data.SQLClient.SQLConnection
$conn.ConnectionString = $ConnectionString 
$conn.Open() 

function Invoke-sql1
{
    param( [string]$sql,
           [System.Data.SQLClient.SQLConnection]$connection
           )
    $cmd = new-object System.Data.SQLClient.SQLCommand($sql,$connection)
    $ds = New-Object system.Data.DataSet
    $da = New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
    $da.fill($ds) | Out-Null
    return $ds.tables[0]
}

function Show-Bockmarks ($conn) {
        New-ListView -Name ListView -View {
           New-GridView -AllowsColumnReorder -Columns {
               New-GridViewColumn "title" 
           }
    } -show -On_Loaded {
            $ff_sql = @"
SELECT 'abc' title
UNION
SELECT 'efg' title
"@
            $TableView = $window | Get-ChildControl ListView
            $TableView.ItemsSource = @(Invoke-sql1 -sql $ff_sql -connection $conn)
             } 
}

Show-Bockmarks $conn

Script 2:

ipmo WPK

$ConnectionString = $ConnectionString = "Server=localhost;Integrated Security=True"
$conn = new-object System.Data.SQLClient.SQLConnection
$conn.ConnectionString = $ConnectionString 
$conn.Open() 

function Invoke-sql2
{
    param( [string]$sql,
           [System.Data.SQLClient.SQLConnection]$connection
           )
    $cmd = new-object System.Data.SQLClient.SQLCommand($sql,$connection)
    $ds = New-Object system.Data.DataSet
    $da = New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
    $da.fill($ds) | Out-Null
    $ds.tables[0]
}

function Show-Bockmarks ($conn) {
        New-ListView -Name ListView -View {
           New-GridView -AllowsColumnReorder -Columns {
               New-GridViewColumn "title" 
           }
    } -show -On_Loaded {
            $ff_sql = @"
SELECT 'abc' title
UNION
SELECT 'efg' title
"@
            $TableView = $window | Get-ChildControl ListView
            $TableView.ItemsSource = @(Invoke-sql2 -sql $ff_sql -connection $conn)
             } 
}

Show-Bockmarks $conn

The first script shows 2 rows in a grid, while the 2nd show to empty rows in the grid. The second seems to behave similar to this

3rd script not using function:

ipmo WPK

$ConnectionString = $ConnectionString = "Server=localhost;Integrated Security=True"
$conn = new-object System.Data.SQLClient.SQLConnection
$conn.ConnectionString = $ConnectionString 
$conn.Open() 

            $ff_sql = @"
SELECT 'abc' title
UNION
SELECT 'efg' title
"@

function Show-Bockmarks ($conn) {
        New-ListView -Name ListView -View {
           New-GridView -AllowsColumnReorder -Columns {
               New-GridViewColumn "title" 
           }
    } -show -On_Loaded {
        $TableView = $window | Get-ChildControl ListView
        $cmd = new-object System.Data.SQLClient.SQLCommand($ff_sql,$conn)
        $ds = New-Object system.Data.DataSet
        $da = New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
        $da.fill($ds) | Out-Null
        $TableView.ItemsSource = @($ds.tables[0].rows)
    } 
}

Show-Bockmarks $conn

Note here without use of a function, I have to explicitly use $ds.tables[0].rows. Otherwise I get the error

Exception setting "ItemsSource": "Cannot convert the "Table" 
value of type "System.Data.DataTable" 
to type "System.Collections.IEnumerable"."

PowerShell function won't return object might explain, why this behaves similar to the function without return. But how does the return make the 2 rows show in the grid?

Original post:

The functions Invoke-sqlite and Invoke-sqlite1 are nearly the same.

The only difference is that Invoke-sqlite uses explicit return. The difference is very subtile, when I execute

$o1   =  Invoke-sqlite $sql $conn
$o2   =  Invoke-sqlite2 $sql $conn

I can't see any difference. But in the full context of the script below, with Invoke-sqlite the grid is filled with data and with Invoke-sqlite the grid is filled with empty rows.

BTW: The purpose of the script is to search a copy of a firefox places.sqlite history database for bookmarks with combinations of 1 to 3 keywords. You have to modify the path for the dll line 5 and the path for the sqlite database line 8.

If you have problems with System.Data.SQLite.dll see this

ipmo WPK

if (! $sqlitedll)
{
    $sqlitedll = [System.Reflection.Assembly]::LoadFrom("C:\Program Files\System.Data.SQLite\bin\System.Data.SQLite.dll") 
}

$ConnectionString = "Data Source=C:\Var\sqlite_ff4\places.sqlite"

$conn = new-object System.Data.SQLite.SQLiteConnection 
$conn.ConnectionString = $ConnectionString 
$conn.Open() 

# $sql = "SELECT * from moz_bookmarks t1 where parent = 4 and t1.title = 'sqlite' or t1.title = 'sql'"

function Invoke-sqlite
{
    param( [string]$sql,
           [System.Data.SQLite.SQLiteConnection]$connection
           )
    $cmd = new-object System.Data.SQLite.SQLiteCommand($sql,$connection)
    $ds = New-Object system.Data.DataSet
    $da = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)
    $da.fill($ds) | Out-Null
    return $ds.tables[0]
}

function Invoke-sqlite2
{
    param( [string]$sql,
           [System.Data.SQLite.SQLiteConnection]$connection
           )
    $cmd = new-object System.Data.SQLite.SQLiteCommand($sql,$connection)
    $ds = New-Object system.Data.DataSet
    $da = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)
    $da.fill($ds) | Out-Null
    $ds.tables[0]
}

# $o1   =  Invoke-sqlite $sql $conn
# $o2   =  Invoke-sqlite2 $sql $conn


function Show-Bockmarks ($resource) {
    #New-StackPanel -Orientation vertical {
    New-Grid -Rows 2 -Columns 1 -width 1400 -hight 1000 {

        New-StackPanel -Orientation horizontal -column 0 -row 0 -Children {
             New-Label    '1. Keyword'
             New-TextBox  -Name tag1 -width 200
             New-Label    '2. Keyword'
             New-TextBox  -Name tag2 -width 200
             New-Label    '3. Keyword'
             New-TextBox  -Name tag3 -width 200
             New-Button -Name Search "search" -On_Click {
            $text1 = $window | Get-ChildControl Tag1
            $tag1 = $text1.Text
            $text2 = $window | Get-ChildControl Tag2
            $tag2 = $text2.Text
            $text3 = $window | Get-ChildControl Tag3
            $tag3 = $text3.Text
            if ( $tag2 -ne '') {
$clause2 = @"            
    join moz_bookmarks l2 on b.fk = l2.fk and b.id <> l2.id
    join moz_bookmarks t2 on l2.parent = t2.id and  t2.parent = 4 and upper(t2.title) = upper('$tag2')
"@                        
            } else { $clause2 = '' }        

            if ( $tag3 -ne '') {
$clause3 = @"            
    join moz_bookmarks l3 on b.fk = l3.fk and b.id <> l3.id
    join moz_bookmarks t3 on l3.parent = t3.id and  t3.parent = 4 and upper(t3.title) = upper('$tag3')
"@                        
            } else { $clause3 = '' }        

$ff_sql = @"
SELECT b.title, datetime (b.dateAdded / 1000000, 'unixepoch', 'localtime') dateAdded , p.url
    from moz_bookmarks b
    join moz_bookmarks l1 on b.fk = l1.fk and b.id <> l1.id
    join moz_bookmarks t1 on l1.parent = t1.id and  t1.parent = 4 and upper(t1.title) = upper('$tag1')
    join moz_places p  on b.fk = p.id $clause2 $clause3
where b.title is not null and b.type = 1
"@
#             $query = $window | Get-ChildControl query
#             $query.text = $ff_sql
            $conn = $resource.conn
            $window.Title = "$($conn.database) Database Browser"
            $TableView = $window | Get-ChildControl TableView
            $TableView.ItemsSource = Invoke-sqlite -sql $ff_sql -connection $conn
             } 
#             New-textbox -Name query 
             New-Button -Name Cancel "Close" -On_Click {$window.Close()} 
        }
        # -VerticalScrollBar $True 
        # New-ScrollViewer {
        New-ListView -Column 0 -Row 1 -Name TableView -View {
           New-GridView -AllowsColumnReorder -Columns {
               New-GridViewColumn "title" 
               New-GridViewColumn "dateAdded" 
               New-GridViewColumn "url" 
           }
        }   -On_SelectionChanged {
             start $this.selecteditem.url
        }
        #}

    } -asjob -Resource $resource
}

Show-Bockmarks -resource @{conn = $conn}

score 0 · Answer 1 · answered Apr 27 '11 at 03:43

In order to help, I wrote the same thing on the top of SQLClient and it seems to work fine.

function func1
{
  param( [string]$sql,
         [System.Data.SQLClient.SQLConnection]$connection
     )


  $cmd = new-object System.Data.SQLClient.SQLCommand($sql,$conn)
  $ds = New-Object system.Data.DataSet
  $da = New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
  $da.fill($ds) | Out-Null

  $ds.Tables[0]
}

function func2
{
  param( [string]$sql,
       [System.Data.SQLClient.SQLConnection]$connection
     )


  $cmd = new-object System.Data.SQLClient.SQLCommand($sql,$conn)
  $ds = New-Object system.Data.DataSet
  $da = New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
  $da.fill($ds) | Out-Null

  return $ds.Tables[0]
}

Clear-Host

$sqldll = [reflection.assembly]::loadwithpartialname("System.Data.SqlClient")
$connectionString="Data Source=JPBHPP2\SQLEXPRESS;Initial Catalog=Ardeche-Earth;Integrated Security=True"

$conn = new-object System.Data.SQLClient.SQLConnection 
$sql = "SELECT * FROM communes WHERE COM_NCC LIKE 'AURI%'"
$conn.ConnectionString = $ConnectionString 
$conn.Open() 

$a = func1 -sql $sql -connection $conn
$b = func2 -sql $sql -connection $conn

Write-Host "`$a : $($a.count)"
Write-Host "`$b : $($b.count)"

$conn.Close()

$a and $b holds the same array of System.Data.DataRow at the end

I didn't see a difference between the two results using SQLite either. Only in the context of WPK the difference shoes. I rewrote my script using System.Data.SQLClient. The difference in context with WPK is still there. — bernd_k, Apr 27 '11 at 07:12

Why makes this return at the end of a PowerShell script a difference?

1 Answers1