StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy makes this return at the end of a PowerShell script a difference?
text
Body
copied!<p><strong>3rd Edit:</strong></p> <p>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</p> <pre><code>abc efg </code></pre> <p>in the grid, while the second shows to <strong>empty</strong> rows in the grid.</p> <p><strong>Skript 1:</strong></p> <pre><code>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 </code></pre> <p><strong>Script 2:</strong></p> <pre><code>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 </code></pre> <p>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 </p> <p><strong>3rd script not using function:</strong> </p> <pre><code>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 </code></pre> <p>Note here without use of a function, I have to explicitly use $ds.tables[0]<strong>.rows</strong>. Otherwise I get the error</p> <pre><code>Exception setting "ItemsSource": "Cannot convert the "Table" value of type "System.Data.DataTable" to type "System.Collections.IEnumerable"." </code></pre> <p><a href="https://stackoverflow.com/questions/2961075/powershell-function-wont-return-object">PowerShell function won't return object</a> might explain, why this behaves similar to the function without return. But how does the return make the 2 rows show in the grid?</p> <p><strong>Original post:</strong></p> <p>The functions Invoke-sqlite and Invoke-sqlite1 are nearly the same.</p> <p>The only difference is that Invoke-sqlite uses explicit return. The difference is very subtile, when I execute</p> <pre><code>$o1 = Invoke-sqlite $sql $conn $o2 = Invoke-sqlite2 $sql $conn </code></pre> <p>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.</p> <p>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.</p> <p>If you have problems with System.Data.SQLite.dll see <a href="https://stackoverflow.com/questions/5756326/problem-opening-a-firefox-places-sqlite-with-powershell-and-system-data-sqlite">this</a></p> <pre><code>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} </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload