Modules/businessdev.ALbuild.Containers/Public/Get-BcContainerTableData.ps1
|
function Get-BcContainerTableData { <# .SYNOPSIS Reads rows of a Business Central table out of a running container. .DESCRIPTION Business Central offers no general way to read arbitrary table data from outside: OData needs an API page per table, and the management cmdlets administer the server rather than query it. What every single-container image does have is its own SQL Express instance, so this reads through SQL from INSIDE the container - nothing is exposed to the host. The SQL name of a BC table is '<Company>$<Table Name>$<app GUID>', and a field's column name is the AL field name with characters SQL cannot take replaced ('.' becomes '_'). Rather than guessing the GUID, the table is located by pattern, which also makes the failure mode useful: if two apps define a table of the same name, both are reported instead of one being picked silently. Intended for seeding a local probe with realistic data - not for bulk export, which is why -First is capped and defaulted low. Reading a production-sized table into memory to answer a question about one record helps nobody. .PARAMETER Name The container to read from. .PARAMETER Table The AL table name, e.g. 'Currency'. .PARAMETER Company Company whose data to read. Defaults to the first company in the database. .PARAMETER Filter A SQL WHERE clause without the keyword, e.g. "[Code] = 'EUR'". .PARAMETER First Maximum number of rows (default 50, maximum 1000). .OUTPUTS PSCustomObject with Table (the SQL table found), Company, RowCount and Rows (an array of ordered hashtables keyed by SQL column name). .EXAMPLE Get-BcContainerTableData -Name bld -Table 'Currency' -Filter "[Code] = 'EUR'" Reads the EUR currency record from the container's database. #> [CmdletBinding()] [OutputType([PSCustomObject])] param( [Parameter(Mandatory)] [string] $Name, [Parameter(Mandatory)] [string] $Table, [string] $Company, [string] $Filter, [ValidateRange(1, 1000)] [int] $First = 50, [string] $DockerExecutable = 'docker' ) Test-BcPlatform -Require | Out-Null # -Variables injects each entry as a variable of that name; the block takes no param() block. $script = { $ErrorActionPreference = 'Stop' $srv = '.\SQLEXPRESS' # The BC database is the only user database in a standard container image. $db = (Invoke-Sqlcmd -ServerInstance $srv -Query ` "SELECT TOP 1 name FROM sys.databases WHERE database_id > 4 ORDER BY database_id").name if (-not $db) { throw "No Business Central database found in the container." } $escaped = $tableName.Replace("'", "''") $like = "%`$$escaped`$%" $candidates = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query ` "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '$like'" | ForEach-Object { $_.TABLE_NAME }) if ($company) { # SQL cannot take a '.' in an identifier, so BC substitutes '_' - in the COMPANY part of the # name too. 'CRONUS International Ltd.' is stored as 'CRONUS International Ltd_'. Accepting # only the literal name would reject the very name the user reads in the client. $prefix = ($company -replace '\.', '_') + '$' $candidates = @($candidates | Where-Object { $_ -like "$prefix*" }) } if ($candidates.Count -eq 0) { throw "No SQL table matching '$tableName' in database '$db'. Company-specific tables are named '<Company>`$<Table>`$<app guid>'." } if ($candidates.Count -gt 1) { # Several companies, or two apps defining the same table name. Naming both beats picking one. throw ("'$tableName' is ambiguous - " + $candidates.Count + " matching SQL table(s): " + (($candidates | Select-Object -First 5) -join '; ') + ". Narrow it with -Company.") } $sqlTable = $candidates[0] $where = if ($filter) { " WHERE $filter" } else { '' } $rows = @(Invoke-Sqlcmd -ServerInstance $srv -Database $db -Query ` "SELECT TOP $first * FROM [$sqlTable]$where") $out = @() foreach ($r in $rows) { $o = [ordered]@{} foreach ($c in $r.Table.Columns) { $v = $r[$c.ColumnName] if ($v -is [System.DBNull]) { $v = $null } $o[$c.ColumnName] = $v } $out += , $o } # Invoke-BcContainerCommand returns the container's STDOUT as text, so the result has to cross # the boundary as JSON. Emitting objects here would arrive as PowerShell's formatted table. [PSCustomObject]@{ Table = $sqlTable Database = $db RowCount = $out.Count Rows = $out } | ConvertTo-Json -Depth 6 -Compress } Write-ALbuildLog "Reading '$Table' from container '$Name' (max $First row(s))..." $stdout = Invoke-BcContainerCommand -ContainerName $Name -ScriptBlock $script ` -Variables @{ tableName = $Table; company = $Company; filter = $Filter; first = $First } ` -DockerExecutable $DockerExecutable $json = ($stdout | Out-String).Trim() if (-not $json) { throw "Reading '$Table' from '$Name' produced no output." } try { $result = $json | ConvertFrom-Json } catch { throw "Could not read the container's answer as JSON: $($_.Exception.Message)`n$json" } Write-ALbuildLog -Level Success "Read $($result.RowCount) row(s) from '$($result.Table)'." return $result } |