We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Extract the modeldata from databasemodel table

STAFF Marcus Svenningsson

Hi,

I have been trying to extract the modeldata from the databasemodel table, but don't know what format to expect.

The documentation says compressed json, which naturally makes me think of a zip-compressed blob-file.

I have tried to export the data as a file using the below BCP-command

c:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn>bcp.exe "select ModelData from superofficetest.crm7.DATABASEMODEL where databasemodel_id = 1" queryout "d:\superoffice_media\temp\so-db-model-json.zip" -S servername -T -c -C RAW -r "" -f d:\superoffice_media\temp\bcp.fmt

##################################################
bcp.fmt - Format file for BCP
##################################################
14.0
1
1 SQLBINARY 0 0 "" 1 ModelData ""

##################################################

Result:

Warning: -f overrides -c.

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (62.50 rows per sec.)
c:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn>

Result - File

Some kind of binary, but I don't know what format. It doesn't work to zip-decompress, it doesn't seem to be Base64-encoded.

The start of the file looks like this:

Comments

I know that BCP might add some extra characters in the beginning of the file, but my format settings should fix this. I have also tried another DB Tool that seem to have exported a file with the exact same content.

Question

What fileformat and compression should I expect in this blob-field?

Can anybody see any obvious errors in my extract method? Is it implicitly converted to some faulty format in the export?

Any tips on how to extract this information?


Best Regards

Marcus

PARTNER /sv/My-Page/11000/ Varm

RE: Extract the modeldata from databasemodel table

Hello Marcus,

See this thread: https://community.superoffice.com/en/technical/forum/rooms/topic/superoffice-product-group/crm-web-application/crm8databasemodelmodeldata-as-json/

You can use the C# method that Matthijs mentioned, or use the DevNet Toolbox to extract the modeldata as JSON.

 

Av: David Hollegien 26. nov 2018
STAFF /sv/My-Page/5821/

RE: Extract the modeldata from databasemodel table

Alternatively, use a tool like LINQPad and get a more visual description of the DatabaseModel table.

 

Just add CDD references, and namespace imports...

Hope this helps!

Av: Tony Yates 27. nov 2018
STAFF /sv/My-Page/10289/

RE: Extract the modeldata from databasemodel table

Hi David and Tony,

I would like to thank you both for interesting resources and solutions!

As I understand it, the content is compressed on a byte-by-byte-level, which probably is the resason for 7zip/zip not being able to decompress an exported file, just by naming it ".zip".

I have done some tests with powershell-solutions as well and might post one when I have a simple one that works as I want it to work. Haven't had the time to go through and test all details. :)

But some interesting links are:

http://sushihangover.blogspot.com/2013/03/powershell-gzip-gz-compress-and.html

https://blog.kenaro.com/2010/10/19/how-to-embedd-compressed-scripts-in-other-powershell-scripts/

https://gist.github.com/marcgeld/bfacfd8d70b34fdf1db0022508b02aca

https://stackoverflow.com/questions/39939869/data-format-for-system-io-compression-deflatestream


I tend to use PowerShell if possible, as that is a tool that is always available at customers servers. :)

/Marcus

Av: Marcus Svenningsson 27. nov 2018
PARTNER /sv/My-Page/5970/

RE: Extract the modeldata from databasemodel table

Hi

I have interest in this PowerShell export script if you got it work.
Is everything about design i superOffice in this datbase modell table.
Before when we were at 8.1/8.2 I found out that if this table is corrupted/maipulated and or deleted it will make dbsetup.exe unusefull. You cant even kill o import a backup of your database. Did report that to Margrethe.

Actually I would like a solution for just extracting the whole design from one datbase to another blank empty database.
It would be very nice to see this function in dbsetup. That would make it possible to set up test/dev environment with all fields and customization but with out data

Another solution would be function: Strip/remove all data. 
Eg in dbsetup after a transfer from prd-environment to test/dev it would be nice to be able to clean the database from PRD-data.
remove alla contact persons, documents, appointments and customers. (an HTA like Consitens-check , with checkboxes for what data to remove)

Another function in dbsetup that would help us partners to transfer a PRD database to test/dev would be:

Scramble data. Eg scramble contacpersons, email-adresses, and other sensitive GDPR data.

 

Best regards
Anders

Av: Anders Larsson 27. feb 2019
STAFF /sv/My-Page/10289/

RE: Extract the modeldata from databasemodel table

Hi Anders,

I'm afraid that I haven't had the time to further look into a Powershell-export solution, but if/when I do, I will try to remember to post it here as well. :)

/Marcus

Av: Marcus Svenningsson 28. feb 2019