Sitecore 9 login: An Error Occurred, String or binary data would be truncated

This is caused by Sitecore generating a login ticket too long to be stored in the database, hence the data would be truncated. I'm not sure why this happens, but it looks like the URL (or part of the URL) is included in the ticket, causing it to be longer than the datatype in the database allows.

Here's the stacktrace from the log:

Nested Exception
Exception: System.Data.DataException
Message: Error executing SQL command: INSERT INTO [Properties] ( [Key], [Value] ) VALUES ( @name, @value )
Nested Exception
Exception: System.Data.SqlClient.SqlException
Message: String or binary data would be truncated.
The statement has been terminated.
Source: .Net SqlClient Data Provider

 

Solution

The solution is to delete the ticket directly in the core database, consequently forcing Sitecore to generate a new ticket upon next login. Run the following SQL query and make sure to target the impacted user by replacing "Username" with something uniquely identifying the user.

DELETE FROM [Project_Core].[dbo].[Properties]
WHERE [Project_Core].dbo.Properties.[Key] LIKE 'CORE_SC_TICKET%'
AND [Project_Core].dbo.Properties.[Value] LIKE '%Username%'

 

Select multiple files in Sitecore file upload dialog

In order to be able to select multiple files in Sitecore's media file uploader, you need to modify the UploadMedia.xml file (located in sitecore\shell\Applications\Media\Upload Media). Specifically, you need to add the multiple="multiple" attribute to the input tag as shown below:

<input id="File" name="File" type="file" style="width:100%" multiple="multiple" />

Here's the full file:

<?xml version="1.0" encoding="utf-8" ?>
<control xmlns:def="Definition" xmlns="http://schemas.sitecore.net/Visual-Studio-Intellisense">
  <UploadMedia>
    <FormDialog Icon="Business/32x32/Paperclip.png" FormTarget="sitecoreattach" Enctype="multipart/form-data" Header="Upload File"
                Text="Select a file. When done click the Upload button." OKButton="Upload">

      <Script Key="Upload">

        function submit() {
        try {
        document.forms[0].submit();
        }
        catch(e) {
        scForm.invoke("ShowError");
        }
        }
      </Script>

      <CodeBeside Type="Sitecore.Shell.Applications.Media.UploadMedia.UploadMediaForm,Sitecore.Client"/>

      <input id="ItemUri" name="ItemUri" type="hidden" value=""/>

      <Border Padding="0px 0px 4px 0px">
        <Literal Text="File name:" />
      </Border>

      <input id="File" name="File" type="file" style="width:100%" multiple="multiple" />

    </FormDialog>
  </UploadMedia>
</control>


Result:

There is a dependency loop in your Unicorn configuration dependencies

Today I came across this exception: There is a dependency loop in your Unicorn configuration dependencies. Unresolved configurations Project.XXX, Feature.ZZZ. At first I didn't realize the cause for this even though the reason is quite simple.

Short answer 

Don't violate the Helix architecture dependency direction. In this case I had created a dependency from the feature layer to the project layer, which is not allowed according to the Helix architecture. Since the dependency direction goes from Project -> Feature -> Foundation, a dependency from feature to project results in a dependency loop.

Long answer

Scenario: I wanted to configure the serialization of a root item for some datasources (only the root) in one configuration file, and then configure the serialization of the children in other configuration files.

 

So I added this configuration to my serialization config file in the project layer:

...
<predicate>
  <include name="CustomSources" database="master" path="/sitecore/system/Custom sources">
    <exclude children="true" />
  </include>
</predicate>
...

And in my feature layer I added this configuration for the child datasource folder.

...
<predicate>
  <include name="ProductCategories" database="master" path="/sitecore/system/Custom sources/ProductCategories" />
</predicate>
...

 

In this case a folder for some product categories:

 

The problem is that I have created a dependency from the feature layer to the project layer. The ProductCategories item (feature layer) depends on the parent item (project layer), which violates the Helix dependency direction.

Solution: I moved the serialization of the root item to the foundation layer, which solved the issue.

Generic base class for MySQL in python using PyMySQL

Whenever I write some code in python that involves a MySQL database, I use this base class that contains all the generic code for the basic CRUD operations. This allows me to focus on writing the queries instead of writing the same boilerplate code every time. It uses the client library PyMySQL and will take care of opening and disposing the connections to the database as well as preventing SQL injection by using parameterized queries. Just wanted to share it so that others can use it too.

Methods

__execute and __openConnection are private methods used internally in the class.

execute is intended for insert, update and delete statements and returns the number of affected rows in the table.

select_all is for select statements and returns a list of the rows that satisfy the query condition(s).

select_one is also for select statements and returns the first row that satisfies the query condition(s).

import pymysql.cursors

class SqlBase(object):
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database

    def execute(self, query:str, parameters:list=[]):
        return self.__execute(query, parameters).rowcount

    def select_all(self, query:str, parameters:list=[]):
        cursor = self.__execute(query, parameters)
        return cursor.fetchall()

    def select_one(self, query:str, parameters:list=[]):
        cursor = self.__execute(query, parameters)
        return cursor.fetchone()

    def __execute(self, query:str, parameters:list=[]):
        try:
            with self.__openConnection() as connection:
                with connection.cursor() as cursor:
                    cursor.execute(query, parameters)
                    connection.commit()
                    return cursor
        except pymysql.Error as e:
           print(f"Error while executing query [{query}]: {str(e)}")

    def __openConnection(self):
        try:
            return pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database, cursorclass=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
           print(f"Error while opening sql connection: {str(e)}")

Example

Create a class that inherits the SqlBase class and call the base constructor with the database credentials. In this example I have made a user repository with examples of the CRUD operations by using execute, select_one and select_all. Notice how the values are parsed as a separate list of parameters rather than being inserted directly into the query itself. This is very important in order to avoid SQL injection attacks in case the values comes from user input like query parameters.

from sql_base import SqlBase
  
class UserRepository(SqlBase):
    def __init__(self):
        SqlBase.__init__(self, "host", "user", "password", "database")
  
    def insert(self, username, email):
        query = "INSERT INTO users(username, email) VALUES (%s,%s)"
        return self.execute(query, [username, email])
          
    def get(self, username):
        query = "SELECT id, username, email FROM users WHERE username=%s"
        return self.select_one(query, [username])
      
    def get_all(self):
        query = "SELECT id, username, email FROM users"
        return self.select_all(query)
 
    def update(self, username, email):
        query = "UPDATE users SET email=%s WHERE username=%s"
        return self.execute(query, [email, username])
          
    def delete(self, username):
        query = "DELETE FROM users WHERE username=%s"
        return self.execute(query, [username])

 

Display flag icons in language dropdown in Sitecore 9 Forms Builder

This blog post is an extension of an earlier post I wrote on how to sort the languages in Sitecore 9 Forms Builder. You can read it here https://blog.fyhn.io/post/2019/02/24/sort-languages-in-sitecore-9-forms-builder-language-dropdown. To make it even easier to locate a specific language, this post focuses on displaying the flag icon for each language in the dropdown. The below image is the result we are going to achieve.

We are going to extend the forms.getLanguages pipeline (located in App_Config\Sitecore\ExperienceForms\Sitecore.ExperienceForms.Pipelines.Client.config) with a processor responsible for resolving the path to the language icon for each language and adding it to the data model.

1. Extend the data model

First, we have to extend the LanguageListItem class with a property containing the path to the icon. Create an ExtendedLanguageListItem class that inherits LanguageListItem and add the icon path property.

public class ExtendedLanguageListItem : LanguageListItem
{
    public string IconPath { get; }

    public ExtendedLanguageListItem(LanguageListItem languageListItem, string iconPath)
    {
        Name = languageListItem.Name;
        DisplayName = languageListItem.DisplayName;
        Text = languageListItem.Text;
        Description = languageListItem.Description;
        HasVersions = languageListItem.HasVersions;
        IconPath = iconPath;
    }
}

2. Create a pipeline processor responsible for resolving the icon path for each language

The processor iterates the languages and uses the GetIconPath method to resolve the path. Next, it replaces the LanguageListItem objects in the pipeline arguments with our ExtendedLanguageListItem containing the path.

public class GetLanguageIconsProcessor : MvcPipelineProcessor<GetLanguagesEventArgs>
{
    public override void Process(GetLanguagesEventArgs args)
    {
        var iconPaths = new Dictionary<string, string>();
        foreach (var itemLanguage in args.ItemLanguages)
            iconPaths.Add(itemLanguage.Name, $"/-/icon/{GetIconPath(itemLanguage, args.FormBuilderContext.Database)}");
        
        args.Languages = args.Languages.Select(lang => new ExtendedLanguageListItem(lang, iconPaths[lang.Name]));
    }

    private string GetIconPath(Language language, Database database)
    {
        string iconPath = language.GetIcon(database);

        if (string.IsNullOrEmpty(iconPath))
        {
            LanguageDefinition languageDefinition = LanguageDefinitions.GetLanguageDefinition(language);
            if (languageDefinition != null)
                iconPath = languageDefinition.Icon;
        }

        if (string.IsNullOrEmpty(iconPath))
            iconPath = "Office/32x32/flag_generic.png";

        return iconPath;
    }
}

3. Add img tag to the .cshtml file containing the markup

Finally, we have to add a img tag in the markup file in order to display the icon. The file is called FormsListControl.cshtml and located in the sitecore\shell\client\Applications\FormsBuilder\Layouts\Renderings\FormsListControl folder. Navigate to line 194 and add the img tag as shown below.

...
<div class="sc-listcontrol-tile-content">
    <!-- Add the below line -->
    <img style="margin-right:10px;float:left" src="{{ iconPath }}" border="0" alt="" width="32px" height="32px">
    {{{ListControl:GetTileTemplate @("@root.Settings.Id") this}}}
</div>
...

Done! Don't forget to add your pipeline processor to the config file.

If you want to add the language icons in the content editor, use this post: https://xtremdev.com/2017/10/20/sort-language-list-and-show-language-flags/

And for the experience editor, use this post: http://www.xcentium.com/blog/2016/08/05/language-flags-not-displayed

Sort languages in Sitecore 9 Forms Builder language dropdown

The sort order in the language dropdown in Sitecore 9 Forms Builder seems pretty random, as seen in the below screenshot. This is rather confusing when having a lot of languages (in this case the customer has 95 languages). Therefore, I will show you how to make a custom sort order of the list.

The data in the list is fetched from a pipeline called forms.getLanguages (located in App_Config\Sitecore\ExperienceForms\Sitecore.ExperienceForms.Pipelines.Client.config). We can sort the list by extending the pipeline with our own processor handling the sorting. Below is an example that sorts the list using Sitecores own LanguageComparer class. This class sorts the languages so that it matches the sort order of the language definition items located under /sitecore/system/Languages, but you can make your own order, e.g. by display name or item name.

public class SortLanguagesProcessor : MvcPipelineProcessor<GetLanguagesEventArgs>
{
    public override void Process(GetLanguagesEventArgs args)
    {
        var sortedLanguageNames = args.ItemLanguages
            .OrderBy(item => item, new LanguageComparer(args.FormBuilderContext.Database))
            .Select(item => item.Name)
            .ToList();
        args.Languages = args.Languages.OrderBy(lang => sortedLanguageNames.IndexOf(lang.Name));
    }
}

Note that the pipeline arguments contains two lists of languages: a list of Language objects and another list of LanguageListItem objects. The latter is the one you want to sort, as this is the data sent to the view. Last but not least, don't forget to add your processor to the config file.

This is the result of my sorting implementation, where the order follows the same order I have sorted the language definition items.

If you want to make a custom sorting of the list in the content editor, use this post: https://xtremdev.com/2017/10/20/sort-language-list-and-show-language-flags/

And for the experience editor, use this post and implement the sorting in the ExtendedLanguageGallery.cs class: http://www.xcentium.com/blog/2016/08/05/language-flags-not-displayed

Add rendering to an item in Sitecore programmatically

If you need to add a rendering to an item in Sitecore programmatically, you can use the following method which takes five parameters:

1. The item on which the rendering should be added
2. The ID of the rendering
3. The position in the presentation details, where 0 is the first (top one)
4. The placeholder in which the rendering is inserted
5. If the rendering requires a datasource this goes here (absolute path or ID)

public void AddRendering(Item item, ID renderingId, int renderingPosition, string placeholder, string datasource = "")
{
    string renderingsXml = LayoutField.GetFieldValue(item.Fields[FieldIDs.LayoutField]);
    var layoutDefinition = LayoutDefinition.Parse(renderingsXml);

    if (layoutDefinition.Devices == null || layoutDefinition.Devices.Count == 0)
        throw new Exception("No available devices");

    var renderingDefinition = new RenderingDefinition
    {
        ItemID = renderingId.ToString(),
        Placeholder = placeholder,
        Datasource = datasource
    };

    foreach (DeviceDefinition device in layoutDefinition.Devices)
    {
        device.Insert(renderingPosition, renderingDefinition);
    }

    LayoutField.SetFieldValue(item.Fields[FieldIDs.LayoutField], layoutDefinition.ToXml());
}

Note that when dealing with layouts it is important to get and set the field value with LayoutField.Get/SetFieldValue(). If you fail to do this you will not process layout deltas correctly and may instead override all fields (breaking full inheritance), or attempt to get the layout definition for a delta value, which will result in you wiping the layout details when they get saved.