问题描述
我希望能够阅读直接从SQL Server数据库中获取> Dynamics NAV 2013表元数据，而无需需要NAV开发环境.
I would like to be able to read the Dynamics NAV 2013 Table Metadata directly from the SQL Server database without requiring the NAV Development Environment.
我可以使用以下查询查看二进制SQL图像" BLOB列(使用WHERE子句进行适当过滤):
I can view the binary SQL "image" BLOB columns with a query like the following (filter as appropriate with WHERE clause):
SELECT
o.[Name],
m.[Object Type],
m.[Metadata], -- XML Metadata
m.[User Code], -- C# Metadata
m.[User AL Code] -- C/AL Metadata
FROM [Navision].[dbo].[Object Metadata] AS m
JOIN [Navision].[dbo].[Object] AS o
ON m.[Object ID] = o.[ID]
AND o.[Company Name] = 'YourCompanyName'
AND o.[Type] = 0 -- 0 is NAV Table Object Type
我可以使用.Net代码或带有SQL驱动程序的快速脚本，将[元数据]，[用户代码]和[用户AL代码]中的二进制数据保存到文件中.我曾尝试使用7-zip解压缩，使用十六进制编辑器进行查看以及使用Cygwin文件"命令来检测这些BLOB文件类型.
I can save the binary data from the [Metadata], [User Code], and [User AL Code] off to files using .Net code or a quick script with SQL driver. I have tried using 7-zip to decompress, a hex editor to view, and the Cygwin "file" command to detect these BLOB file types.
不幸的是，我无法弄清楚如何将二进制数据解码或解压缩为可读或可用的格式.在直接使用这些字段中的数据之前，我必须打开NAV开发环境，并使用对象设计器查看以逗号分隔的OptionString属性(用于从零开始的查找列表下拉菜单)(列表中的每个项目均以整数形式存储在后端数据库中-第一项为0，第二项为1，依此类推).字符串值不存在于SQL查找表中，但是NAV确实将它们放在表元数据blob中.
Unfortunately I can't figure out how to decode or decompress the binary data into a readable or usable format. Until I can use the data in those fields directly, I must open NAV Dev Environment and use the Object Designer to view the comma-separated OptionString properties for zero-based lookup list drop-down menu (each item in the list is stored in the back-end database as an integer - 0 for first item, 1 for second, etc). The string values are not present in a SQL lookup table, but NAV does put them in the table metadata blobs.
对于我来说，这是缺少链接来完全支持我的NAV用户作为DBA，而无需NAV开发人员为我查找这些数字到名称的NAV自定义字段映射.然后，我可以查找这些列表值，并根据需要创建匹配的SQL CASE语句或自定义查找表.
This is the missing link for me to completely support my NAV users as a DBA without needing a NAV developer to lookup these number-to-name NAV custom field mappings for me. I can then lookup these list values and create matching SQL CASE statements or custom lookup tables as needed.
一旦掌握了这一部分，就应该能够创建高级SQL视图，查询，报表和工具，而无需访问Dynamics NAV前端用户或开发人员工具.
Once I have this piece I should be able to create advanced SQL views, queries, reports, and tools with no need for access to the Dynamics NAV front-end user or developer tools.
如果您了解用于这些NAV对象元数据blob属性的二进制数据格式，请告诉我.有关如何转换为可读或可用格式的任何建议都将有所帮助.
Please let me know if you have knowledge of the binary data format used for these NAV Object Metadata blob properties. Any advice on how to convert to a readable or usable format would be helpful.
推荐答案
我能够从 deV.ch-动态与代码的对比，Dynamics NAV& C#.NET博客.基于devch的逆向工程，我们确定NAV使用这些字段的前四个字节(32位)来存储确定自定义NAV Blob类型的幻数".
I was able to get an answer for the format of these Metadata binary fields from the author of the deV.ch - man vs. code, Dynamics NAV & C# .NET blog. Based on the reverse engineering by devch, we determined that the first four bytes (32 bits) of these fields are used by NAV to store a "magic number" that determines the custom NAV Blob type.
在这些元数据字段的情况下，NAV压缩的Blob型幻数为0x02457d5b(十六进制).为了使用标准的.Net DeflateStream进行解压缩，只需丢弃前四个魔术数字字节，然后照常使用DeflateStream处理其余的流.
In the case of these metadata fields, the NAV Compressed Blob-Type magic number is 0x02457d5b (hex). In order to use the standard .Net DeflateStream to Decompress, just throw away those first four magic-number bytes and then process the rest of the stream with DeflateStream as usual.
我能够使用.Net成功地测试此过程，现在我计划使用Python或其他一些非Microsoft deflate工具进行测试，以查看deflate实现是否符合行业标准.再次感谢devch提供了导致该解决方案的文章:从外部NAV访问压缩的Blob(NAV2013)(重新访问).
I was able to successfully test this process with .Net, now I plan to test with Python or some other non-Microsoft deflate tools to see if the deflate implementation follows the industry standard. Thanks again to devch for the article that led to this solution: Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited).
更新:已通过Python zlib测试，并且可以正常运行！移除自定义NAV Blob型幻数后，将使用符合标准的Deflate算法.这是一些示例代码(Python):
Update: tested with Python zlib and it works! Standards-compliant Deflate algorithm is used once the custom NAV Blob-type magic number is removed. Here's some sample code (Python):
# Example Using Python 3.x
import zlib, sys, struct
# NAV custom Blob-Type identifier (first 4 bytes)
magic = struct.unpack('>I',sys.stdin.buffer.read(4))[0]
print('magic number = %#010x' % magic, file=sys.stderr)
# Remaining binary data is standard DEFLATE without header
input = sys.stdin.buffer.read()
output = zlib.decompress(input,-15)
sys.stdout.buffer.write(output)
使用如下所示的内容进行测试:
Use something like the following to test:
python -u test.py < Input_Meta.blob > Output_Meta.txt
.Net DeflateStream当然也可以在删除前四个字节之后工作.此示例只是为了表明您不限于使用.Net语言.
Of course the .Net DeflateStream works after removing the first four bytes as well. This example is just to show that you're not limited to using .Net languages.
这篇关于使用SQL读取Dynamics导航表元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！