11

I'm publishing Component Presentations to the Content Delivery DB and it shows up as a different encoding only in the content field of the COMPONENT_PRESENTATIONS table.

The content field in the PAGE_CONTENT table shows up correctly. Has anyone seen this before and/or have a fix?

I'm running Tridion 2013 on Windows Server 2012 with SQL Server 2012 SP1.

  • Did you look at Elena's encoding hints? elenaserghie.blogspot.com/2012/01/… #6 looks like it might be relevant. – Frank van Puffelen Sep 29 '13 at 12:40
  • Yes I have looked at the encoding. It doesn't matter what CT I use. Even the default ones will publish as the wrong encoding. What is strange is the pages publish fine but the CPs do not. I can even publish to the file system and the encoding is fine. Its just when it gets published to the DB that the CPs are incorrect. – Piti Itharat Sep 29 '13 at 13:01
  • There's not enough information in your question. Firstly - are you saying that it used to work on another version and it stopped working when you upgraded - or are you mentioning 2013 in the question just for information? What technology does your web application use? Is it ASP.NET or Java? What have you checked already? What are the symptoms? Have you checked your publication target configuration? – Dominic Cronin Sep 29 '13 at 17:26
  • Can you edit your question and add what encoding your Publication Target is using and what encoding is used by your Deployer and Application Server? – Bart Koopman Sep 29 '13 at 18:09
13

As @Likhan is mentioning in his answer, it seems that the information is saved in Unicode/UTF16 so it needs to be decoded using other Encoding like iso-8859-1.

I developed a workaround for it developing a SQL CLR Stored Procedure

SQL CLR STORED PROCEDURE

public partial class StoredProcedures {
[SqlProcedure]
public static void GetComponentPresentationContent(SqlInt32 publicationId, SqlInt32 componentId, SqlInt32 templateId) {
    using (SqlConnection connection = new SqlConnection("context connection=true")) {
        try {
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText =
                string.Format("SELECT CONTENT FROM COMPONENT_PRESENTATIONS WHERE PUBLICATION_ID = {0} AND COMPONENT_ID = {1} AND TEMPLATE_ID = {2}",
                    publicationId, componentId, templateId);

            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows) {
                SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("CONTENT", SqlDbType.VarChar, -1) });
                SqlContext.Pipe.SendResultsStart(record);

                while (reader.Read()) {
                    string content = reader.GetString(0);

                    using (MemoryStream ms = new MemoryStream()) {
                        byte[] encoded = Encoding.Unicode.GetBytes(content);
                        ms.Write(encoded, 0, encoded.Length);

                        ms.Seek(0, SeekOrigin.Begin);
                        StreamReader tr = new StreamReader(ms, true);
                        Encoding targetEncoding = Encoding.GetEncoding("iso-8859-1");

                        content = targetEncoding.GetString(ms.ToArray());
                        record.SetSqlString(0, content);
                        SqlContext.Pipe.SendResultsRow(record);
                    }
                }

                SqlContext.Pipe.SendResultsEnd();
            }
        }
        catch (Exception ex) {
            throw ex;
        }
        finally {
            if (connection.State != ConnectionState.Closed) {
                connection.Close();
            }
        }
    }
}
}

SQL ASSEMBLY CREATION

CREATE ASSEMBLY [TridionBroker]
FROM '[YOUR ASSEMBLY PATH]'

SQL STORED PROCEDURE CREATION

CREATE PROCEDURE GetComponentPresentationContent
    @PUBLICATIONID AS INT, 
    @COMPONENTID AS INT, 
    @TEMPLATEID AS INT
AS
EXTERNAL NAME [TridionBroker].StoredProcedures.GetComponentPresentationContent

TEST

EXEC GetComponentPresentationContent 11, 190, 200

It will return the component presentation in a readable format from your database. I hope this helps.

13

As far as I know since Tridion 2013 the encoding in database for content field has changed for mssql. You no longer will be able to do select on database to see the content anymore. I have seen around floating somewhere a query which can decode it for human readable view. Will try to look it up. However, even if the content cannot be seen properly in database it should be proper using broker api and/or odata webservices, so, end result should be fine.

UPDATE

To get content from COMPONENT_PRESENTATION table you can use following C# Expression in LINQPad

from c in COMPONENT_PRESENTATIONS
where c.COMPONENT_ID == 11567 
      let buf = Encoding.Unicode.GetBytes(c.CONTENT)
      select Encoding.UTF8.GetString(buf)

Or if the Default Code Page of the Publication Target is set to Unicode then in database it should also be human readable, as Content Delivery DB is using UTF16.

  • I agree to this, but in general (similar as it was in previous versions) encoding issues are usually indicating a problem with settings somewhere along the line. – Bart Koopman Sep 29 '13 at 18:07
  • 1
    If it works with the API (which it does) then I don't think there is an encoding issue... – Nuno Linhares Sep 29 '13 at 21:28
13

You can get readable content from the COMPONENT_PRESENTATION table directly in sql by double converting the content field like this:

SELECT 
  CONVERT(varchar(max),CONVERT(varbinary(max),content)) 
  FROM COMPONENT_PRESENTATIONS
  WHERE <where_clause>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.