Azure Search Index with 2 datasources

Recently I had the need to index documents stored in Azure Storage Blobs. Additionally I wanted to use the blob storage metadata also to add some information to those documents. Because I needed rich text information on the metadata I could use blob storage metadata directly. Se here why.

So I had to use 2 different data sources. One for the documents and another to the metadata. So I chose Azure Blob Storage and Azure Table Storage. This is the full diagram of the final solution:

The indexers are responsible for updating the index with the contents of the 2 different data sources. There is a very important field that in my case it’s called the UniqueIdentifier field because this field is marked with the key property. This is the field that uniquely identifies each document on the Azure Search Index.

And it’s this field that is responsible for correlating the items that come from one data source (documents from blob storage) and items that come from the other data source (records from table storage).

Every document inserted in blob storage has a custom metadata property named also UniqueIdentifier that will have a table storage record associated with the corresponding metadata.

Continue reading

Azure Blob Storage Metadata 400 Bad Request

I was getting a 400 Bad Request when inserting blobs in Azure Blob Storage because I was setting metadata with non-ASCII characters.


// Retrieve storage account from connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the blob client.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

// Retrieve a reference to a container.
CloudBlobContainer container = blobClient.GetContainerReference("mycontainer");

// Create the container if it doesn't already exist.
container.CreateIfNotExists();

// Add some metadata to the container.
container.Metadata.Add("docType", "textDocuments");

According to Microsfot documentation:

“You will receive a 400 Bad Request if any name/value pairs contain non-ASCII characters. Metadata name/value pairs are valid HTTP headers, and so must adhere to all restrictions governing HTTP headers. It is therefore recommended that you use URL encoding or Base64 encoding for names and values containing non-ASCII characters.”

https://docs.microsoft.com/en-us/azure/storage/blobs/storage-properties-metadata

Using KeywordQuery with CSOM SharePoint Online


string username = "***********";
string pwd = "*********";
string siteURL = "**********";

ClientContext context = new ClientContext(siteURL);
Web web = context.Web;
SecureString pass = new SecureString();
foreach (char c in pwd.ToCharArray()) pass.AppendChar(c);
context.Credentials = new SharePointOnlineCredentials(username, passWord);
try
{
    KeywordQuery query = new KeywordQuery(context);
    query.QueryText = "ContentType:Factura AND PrecoOWSCURR>10000";
    
    query.RowLimit = 10;
    query.RowsPerPage = 10;
    SearchExecutor search = new SearchExecutor(context);
    ClientResult<ResultTableCollection> results = search.ExecuteQuery(query);
    context.ExecuteQuery();

    foreach (var resultRow in results.Value[0].ResultRows)
    {
        Console.WriteLine("{0}", resultRow["Title"]);
    }
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
    Console.ReadKey();
}

Move modules – Remove IPP – OutSystems

I needed to move some modules from one infrastructure to another so first I needed to remove IPP  (Intellectual Property Protection).

1 – In Service Studio, open your module and goto Module -> Export -> Save As…

Export
Export OML

2 – Navigate to Intellectual Property Protection (IPP) Rights Validation

Here you need to enter your email, the destination activation code for your target infrastructure, and upload your OML

IPP Validation

3 – You will receive an email with the OML file without IPP protection. Download the file.

4 – In Service Studio goto Module -> Open File…

Open module

5 – Publish your module

No data type ‘TYPE’ could be found – OutSystems Combo Box

I was trying to set up a Combo Box bounded to a static entity and I got this error when I published the website:

Internal Error 
No data type 'Status' could be found. 
Exception Details:
[1] Internal Error: No data type 'Status' could be found.
at OutSystems.HubEdition.Compiler.WebWidgets.ComboBox.dumpEntityDeclaration(TextWriter res)
 at OutSystems.HubEdition.Compiler.WebWidgets.ComboBox.DumpDataBindingCode(TextWriter writer)
 at OutSystems.HubEdition.Compiler.AbstractServerWebWidget.DumpOnDataBinding(TextWriter writer, NodeViewState viewState)
 at OutSystems.HubEdition.Compiler.AbstractServerWebWidget.DumpHandler(TextWriter writer, NodeViewState viewState)
 at OutSystems.HubEdition.Compiler.WebWidgets.ComboBox.DumpHandler(TextWriter res, NodeViewState viewState)
 at OutSystems.HubEdition.Compiler.Nodes.WebScreen.Dump(TextWriter writer)
 at OutSystems.HubEdition.Compiler.Flows.WebFlow.Dump()
 at OutSystems.HubEdition.Compiler.WebFlows.WebHandler.Dump(IEnumerable`1 webFlows)
 at OutSystems.HubEdition.Compiler.ESpace.Dump()
 at OutSystems.HubEdition.Compiler.Compiler.InnerCompile(CompilationContext context)
 at OutSystems.HubEdition.Compiler.Compiler.Compile(CompilationContext context)
 at OutSystems.HubEdition.Compiler.Utils.CompilerUtils.WithOverridenSettings(IDictionary`2 settingsOverride, Action body)
 at OutSystems.HubEdition.Compiler.Compiler.<>c__DisplayClass3.<Compile>b__1()
 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
 at System.Threading.ThreadHelper.ThreadStart()

But TrueChange didn’t show me any errors or warnings. I got this error only when I tried to publish the application.

The problem was that the variable that I defined to hold the value entered by the user was of the Static Entity type instead of the Static Entity Identifier type.

That variable “Status” was defined like this:

And must be defined like this:

It must be a Status Identifier instead of the Status (static entity).

Converting a Vertical Table to an Horizontal Table in SQL Server

Today I’ve encountered a vertical table in an SQL Database and I wanted to transform it to an horizontal one. A vertical table is described as an EAV model.

Imagine you have this table


CREATE TABLE VerticalTable
(
Id int,
Att_Id varchar(50),
Att_Value varchar(50)
)

INSERT INTO VerticalTable
SELECT 1, 'FirstName', 'John' UNION ALL
SELECT 1, 'LastName', 'Smith' UNION ALL
SELECT 1, 'Email', 'john.smith@dummy.com' UNION ALL
SELECT 2, 'FirstName', 'Jack' UNION ALL
SELECT 2, 'LastName', 'Daniels' UNION ALL
SELECT 2, 'Email', 'jack.daniels@dummy.com'

If you run


SELECT * FROM VerticalTable

you get this

Id Att_Id Att_Value
1 1 FirstName John
2 1 LastName Smith
3 1 Email john.smith@dummy.com
4 2 FirstName Jack
5 2 LastName Daniels
6 2 Email jack.daniels@dummy.com

 

To convert this into an horizontal table I’m going to use PIVOT.


SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

And I will get this

Id FirstName LastName Email
1 1 John Smith john.smith@dummy.com
2 2 Jack Daniels jack.daniels@dummy.com

 

You can find the code here.

Speaker at Collab 365

Hi,

I’m proud to announce that I was selected to present a session at Collab 365 Global Conference 2016. This is a global online conference delivering sessions about SharePoint, Office 365 and Azure on the 19th-20th October.  And it’s free. Register here.

In my session I will talk about event driven applications with Azure Functions, it will cover triggers and webhooks that Azure Functions provide. It will also have a look at various integrations and at last, it will check the scaling and costs.

See you there

log4net PatternLayout and PatternConverter

I have the following scenario:

I’m using Unity dependency injection container and interception techniques to log some WCF operation calls. I need to log the input parameters and also the result of these operations so I’ve implemented a CallHandler that I can apply to whatever methods that I need to intercept.

In this CallHandler I’ve defined a class called LogEntry and I’m logging with log4net. Something like this:


LogEntry logEntry = new LogEntry();
....
// set some base properties
logEntry.MethodName = input.MethodBase.Name;
logEntry.TypeName = input.Target.GetType().FullName;
...
// set input parameters
logEntry.Input = ....;
...
// set return value
logEntry.ReturnValue = result.ReturnValue;
.....
// set total method duration
logEntry.CallTime = stopwatch.Elapsed.TotalMilliseconds;
.....

// use log4net logger to log this information
logger.Debug(logEntry);

So this LogEntry class has some properties that will hold the information that I want to log. Now I need to capture these properties and configure the log4net appenders properly.

I’m using log4net PatternLayout and PatternConverter features. Let’s start with PatternLayout


///<summary>
/// Extended version of <see cref="PatternLayout"/>
/// </summary>


public class ExtendedPatternLayout : PatternLayout
{
 ///<summary>
 /// Initializes a new instance of the <see cref="ExtendedPatternLayout"/> class.
 /// </summary>;


 public ExtendedPatternLayout()
 {
   this.AddConverter(new ConverterInfo()
   {
     Name = "logEntry",
     Type = typeof(LogEntryConverter),
   });
 }
}

This layout just adds a new Converter named “logEntry” implemented by the class LogEntryConverter. There is no reference to LogEntry class here. And the LogEntryConverter class is here:


 /// <summary>
 /// Custom <see cref="PatternConverter"/> to handle <see cref="LogEntry"/>
 /// </summary>
 public class LogEntryConverter : PatternConverter
 {
   /// <summary>
   /// Converts the specified writer.
   /// </summary>
   /// <param name="writer">The writer.</param>
   /// <param name="state">The state.</param>
   protected override void Convert(System.IO.TextWriter writer, object state)
   {
       var loggingEvent = state as LoggingEvent;
       var logEntry = loggingEvent.MessageObject as LogEntry;

       if (logEntry != null)
       {
          switch (this.Option.ToLower())
          {
             case "calltime":
               writer.Write(logEntry.CallTime.HasValue ? logEntry.CallTime.Value : 0);
               break;
             case "methodname":
               writer.Write(logEntry.MethodName);
               break;
             case "returnvalue":
               writer.Write(SerializationService.SerializeToJSON(logEntry.ReturnValue));
               break;
             ..........
             ..........
             default:
               writer.Write(string.Empty);
               break;
          }
       }
    }
 }

I’ve made the code smaller but you need to check for null references!

I’m using a JSON serialization technique to grab the representation of the method’s return value.

I need to override the Convert method and access state object that is a log4net LoggingEvent object. This object has my logged object from the CallHandler which is a LogEntry object. Based on the Option property of the converter I can choose which property to write.

And finally I can configure my log4net like this


<layout type="ExtendedPatternLayout">
  <conversionPattern value="%logEntry{CallTime}" />
</layout>

You can see the conversionPattern follows this syntax: %convertername{option}

nettiers – GetBy Index problem with StoredProcedure long name

Today I’ve discovered a problem with CodeSmith nettiers templates because it was possible to generate two SPs with the same name, for different entities.

This happens when there are two indexes for two different tables that have the same column names and the column names reach the maximum lenght (128 for SQL). In this case the generated SP name doesn’t contain the table name as a prefix.

Here is the original nettiers code for the GetProcNameForGetByIX method in CommonSqlCode.cs:

// get the key names one at a time until we run out of space
stringbuilder names = new stringbuilder(maxlen);
 
string keyname;
for(int x = 0; x < keys.length; x++)
{
  keyname = getpropertyname(keys[x]);
  if (names.length + keyname.length <= maxlen)
    names.append(keyname);
  else
    break;
}
return names.tostring();

I’ve upgraded the method to receive the index name and the new code is:

if (string.IsNullOrEmpty(indexName))
 {
   throw new Exception("Need index name");
 }
 
 string name = string.Concat(prefix, indexName);
 
 if (name.Length > maxLen)
 {
   throw new Exception("SP name continues to be greater than maxlen");
 }
 
 return name;

Then I needed to pass the index name on all the places that this method was call. Just use find in files and search for that method.