Last active
July 13, 2016 11:27
-
-
Save DominicCronin/1baffc31b834a2c3d8fd0cefd506aa9c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Setup | |
$publicationId = 20 # The publication where your content is published | |
# Broker database - substitute your own settings here. | |
$connStringBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder | |
$connStringBuilder["Data Source"] = "WEB8,1433" | |
$connStringBuilder["Initial Catalog"] = "Tridion_Broker" | |
$connStringBuilder["User ID"] = "TridionBrokerUser" | |
$connStringBuilder["Password"] = "Tridion1" | |
$message = "JSON binary data for publication $publicationId" | |
$message | |
'=' * $message.Length | |
function DisplayOutput( $binaryId, $variantId, $path, $text) { | |
Write-Output "Dumping JSON content for Binary: `"$binaryId`", Variant `"$variantId`"" | |
Write-Output "`t Path: `t$path" | |
try { | |
$json = ConvertFrom-Json $text | |
ConvertTo-Json -Compress:$false $json | |
} | |
catch { | |
"Ünable to parse JSON." | |
$_.Exception.Message | |
"===================" | |
$text | |
} | |
} | |
try { | |
$conn = new-object System.Data.SqlClient.SqlConnection | |
$conn.ConnectionString = $connStringBuilder.ConnectionString | |
$conn.Open() | |
$comm = new-object System.Data.SqlClient.SqlCommand | |
$comm.CommandText = @" | |
select bv.BINARY_ID | |
,bv.VARIANT_ID | |
,bv.PATH | |
,bv.URL | |
,bc.CONTENT | |
from BINARYVARIANTS bv | |
inner join BINARY_CONTENT bc | |
on bv.PUBLICATION_ID = bc.PUBLICATION_ID | |
and bv.BINARY_ID = bc.BINARY_ID | |
and bv.VARIANT_ID = bc.VARIANT_ID | |
where bv.PUBLICATION_ID = $publicationId | |
and bv.TYPE = 'application/json' | |
"@ | |
$comm.CommandType = "Text" | |
$comm.Connection = $conn | |
try { | |
$reader = $comm.ExecuteReader() | |
$readResult = $reader.Read() | |
if (-not $readResult) { | |
"No record found" | |
} | |
while ($readResult) { | |
$binaryId = $reader.GetInt32(0) | |
$variantId = $reader.GetString(1) | |
$path = $reader.GetString(2) | |
$url = $reader.GetString(3) | |
try { | |
# It's actually Unicode, but then things get weird, so try lying.... | |
$contentStream = new-object IO.StreamReader($reader.GetStream(4), [Text.Encoding]::ASCII) | |
$stringFromUnicode = $contentStream.ReadToEnd() | |
$bytes = [Text.Encoding]::ASCII.GetBytes($stringFromUnicode) | |
$text = [Text.Encoding]::UTF8.GetString($bytes) | |
} finally {$contentStream.Dispose()} | |
DisplayOutput $binaryId $variantId $path $text | |
$readResult = $reader.Read() | |
} | |
} finally {$reader.Close() } | |
} finally {$Conn.Close() } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment