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

crm8.databasemodel.ModelData as JSON?

Hi, I'm trying to troubleshoot a 8.0 database which we are having trouble upgrading to 8.1, or connect to using NetServer, and want to check the databasemodel.

In the crm8.databasemodel, the field ModelData is a encrypted text field. I was under the impression that it was supposed to just be a base64 encoded JSON string, but see now that it doesn't look like a base64 string.

How can I, via SQL Management studio, take a look at the JSON string representation of the database model?

RE: crm8.databasemodel.ModelData as JSON?

not my domain... but have you tried decompressing?
/conrad

By: Conrad Weyns 11 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

If the text is compressed, then I don't know what compression method has been used?

By: Frode Lillerud 12 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Hi Frode,

It's compressed using gzip, there is a helper in SuperOffice.CD.DSL.V1.DatabaseModel.DatabaseModelExternsions called Unzipstring you can use:

 

public static string UnzipString(this byte[] zipBytes)
{
    string str;
    using (MemoryStream stream = new MemoryStream(zipBytes))
    {
        using (MemoryStream stream2 = new MemoryStream(zipBytes.Length * 10))
        {
            using (DeflateStream stream3 = new DeflateStream(stream, CompressionMode.Decompress))
            {
                stream3.CopyTo(stream2);
            }
            str = Encoding.UTF8.GetString(stream2.ToArray());
        }
    }
    return str;
}

 
By: Matthijs Wagemakers 13 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Matthijs, do you have a recommended easy way to run that code? Having to start a full-blown C# project in Visual Studio, and remembering how System.Data.SqlClient works is a little more cumbersome than I'm up for now.

I was hoping the DECOMPRESS function in SQL 2016 would have worked, but unfortunately this complains about bad data:

select CAST(DECOMPRESS(ModelData) AS VARCHAR(MAX)) from crm8.DATABASEMODEL
By: Frode Lillerud 13 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Also Matthijs, I see you mention some exceptions here with certain CS extratables: 

https://github.com/mawax/InfoBridge.SuperLinq/blob/ada19a3a1037fc6af1499b12deb3be1d24ad9a2f/src/InfoBridge.SuperLinq.CodeGen/Generator.cs#L47

Do you know what the problem is there? I'm trying to figure out why we're having trouble upgrading an 8.0 database to 8.1, and so far we've narrowed it down to something to do with extratables.

By: Frode Lillerud 13 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Just create a new console app and paste this in, should work:

        static void Main(string[] args)
        {
            SqlConnection sql = new SqlConnection(@"Server=localhost;Database=SuperOffice81;User Id=xxx;Password=xxx;");
            sql.Open();
            var q = sql.CreateCommand();
            q.CommandText = "select [ModelData] from crm8.databasemodel";
            var result = q.ExecuteScalar();

            var resultString = UnzipString(result as byte[]);
        }

        static string UnzipString(byte[] zipBytes)
        {
            string str;
            using (MemoryStream stream = new MemoryStream(zipBytes))
            {
                using (MemoryStream stream2 = new MemoryStream(zipBytes.Length * 10))
                {
                    using (DeflateStream stream3 = new DeflateStream(stream, CompressionMode.Decompress))
                    {
                        stream3.CopyTo(stream2);
                    }
                    str = Encoding.UTF8.GetString(stream2.ToArray());
                }
            }
            return str;
        }

You should probably format it a bit, it doesn't look pretty :)

Regarding the comment on the IsEjournalExtraField: If I recall correctly I used to check if we were dealing with an extra field by looking at the property IsEjournalExtraField, however reading this property on some fields threw an exception (don't remember what exception) so therefore we now just check on the name (starts with x_). In 8.1 the property IsEjournalExtraField seems to be removed completely.

By: Matthijs Wagemakers 14 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

You could use something like LINQPad to run small scripts, so that you dont need a full blown visual studio instance.

By: David Hollegien 14 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Hi Frode,

With the DevNet Toolbox application, Ctrl+F will show you a hidden dialog that you can save the databaseModel as a JSON string to a file.

It's hardcoded to "C:\Temp\DatabaseModel.json", so just make sure the C:\temp folder already exists... I can add some functionality to it so you could chose the location and filename yourself... added to the backlog. :-) 

Hope this helps!

Best regards!

By: Tony Yates 14 Nov 2017

RE: crm8.databasemodel.ModelData as JSON?

Thanks Tony, got the JSON out using your tool.

We managed to find the problem (although not related to the JSON structure) for not being able to upgrade to 8.1.

There is a problem in 8.1 when working with CS extrafields with a name longer than 29 chars. That was reported a little while back. In this current scenario we found a related problem. If you create two CS extrafields, where the first 29 chars of the two fields are the same, then NetServer totally rolls over and dies. It kills Win, Web, Service, SoAppConfig.exe, you name it.

By: Frode Lillerud 14 Nov 2017