Saturday, December 21, 2013

T-SQL Data type Precedence

I recently ran across a piece of T-SQL code that looked something like this:

DECLARE @test Int = 1

SELECT 
    CASE 
        WHEN (@test = 1) THEN 5.75
        WHEN (@test = 2) THEN CAST(3.25 as DECIMAL(38,0)) 
     END as Result

At first glance you would expect this to return 5.75 as the result, but in fact it will return 6 instead. Why does this happen? A CASE statement in T-SQL is only allowed to return one data type, so each of the WHEN clauses has to return the same type. In the example the two WHENs are returning different types, so SQL uses type precedence to determine which type to use. Since 5.75 cannot fit in a DECIMAL(38,0) because decimals have a maximum precision of 38, so there is no room for the decimal portion of 5.75. To resolve this, SQL casts 5.75 to DECIMAL(38,0) which causes it to round up to 6. If we were to change the DECIMAL(38,0) to DECIMAL(10,0) in this example we would get the expected result, 5.75, because there is now room for the decimal portion.

If the data types in the WHEN clauses are totally different, SQL will use the type with the highest precedence. For example this code will return “1900-01-06 18:00:00.000”.

DECLARE @test Int = 1

SELECT 
    CASE 
        WHEN (@test = 1) THEN 5.75
        WHEN (@test = 2) THEN GETDATE() 
     END as Result

DateTime has a higher precedence then decimal, so 5.75 gets cast to a DateTime. You can the precedence order for all the T-SQL datatypes here.

To avoid any confusion it is always best to be sure you CASE expressions return the same data type in each WHEN clause.

Saturday, November 9, 2013

Salesforce Get Deleted/Get Updated

Salesforce has just started releasing the Winter ’14 update which contain a couple new features in the REST API. You can view the full release notes here:

https://help.salesforce.com/help/doc/en/salesforce_winter14_release_notes.pdf

In this post I want to talk about the new Get Deleted and Get Update resources. As the names suggest these resources are used to retrieve lists of recently deleted and update records. These are specifically designed to facilitate replication of data. For example, if you are developing a mobile app you may want to store a local copy of some of the contact fields. These resources will help you keep that local copy in sync.

Get Deleted

First, let’s look at Get Deleted. The URI for this resource looks like this:

/services/data/v29.0/sobjects/Contacts/deleted/
?start=2013-05-05T00:00:00+00:00&end=2013-05-10T00:00:00+00:00

This will return a list of all Contacts that were deleted between the specified start and end dates.
There are a couple things you need to be careful of when specifying the dates. First, the dates are UTC not local time, second they must be in ISO 8601 format and third the start date has to been within 30 days of the current date.

Let’s talk a little more about how to format the dates. If you want to send the date in the “2013-05-05T00:00:00+00:00” format you must UrlEncode it because of the plus sign. This can be done in .NET like this:

System.Web.HttpUtility.UrlEncode(“2013-05-05T00:00:00+00:00”)

Note that you may need to add a reference to the System.Web assembly if you are developing a desktop application. It turns out that there is no simple way to generate this format date in .NET short of using a custom format string. As an alternative you can use a slight variation of this format, "2013-10-08T04:00:00Z". There is a standard format that come pretty close to this but puts a space between the time and date instead of the ‘T’, but this can easily be fixed. Here is an example of how to convert a DateTime variable in local time to the properly formatted UTC time:

dtStart.ToUniversalTime().ToString("u").Replace(" ","T");

When you make the Get Deleted call you will get a JSON result that looks like this:

{
  "deletedRecords" : [ {
    "id" : "003E000000kkhvtIAA",
    "deletedDate" : "2013-10-09T12:17:12.000+0000"
  }, {
    "id" : "003E000000kle9zIAA",
    "deletedDate" : "2013-10-09T19:03:28.000+0000"
  }, {
    "id" : "003E000000kmVqiIAE",
    "deletedDate" : "2013-10-11T11:46:00.000+0000"
  } ],
  "earliestDateAvailable" : "2012-10-28T16:00:00.000+0000",
  "latestDateCovered" : "2013-10-11T12:05:00.000+0000"
}


you will see an array called deletedRecords which contains the id of the deleted record and the date it was delete, again in UTC. In a synchronization scenario you would use these id’s to delete the records from the local copy. The lastDateCovered value is the last date that the query looked at for deleted records. You would save this value and use it as the start date for you next Get Delete query. The API documentation says that earliestDateAvailable is “timestamp (Coordinated Universal Time (UTC)—not local— timezone) of the last physically deleted object.”, although I haven’t been able to figure out what that really means.


Get Updated

Get updated works like Get Deleted, but returns a list of the ID’s of objects that were either added or modified between the specified dates. The URI for this resource looks like this:

/services/data/v29.0/sobjects/Contacts/updated/?start=2013-05-05T00:00:00+00:00&end=2013-05-10T00:00:00+00:00

All the rules for dates are the same as they are for Get Deleted. When you make a Get Updated call you will get back a JSON result that looks like this:


{
  "ids" : [ "003E000000QCZ1kIAH", "003E0000001ZufeIAC", "003E000000kmVoiIAE" ],
  "latestDateCovered" : "2013-10-21T10:59:00.000+0000"
}


The ids array contains the id of each object that was either added of modified. Just like with Get Deleted, lastestDateCovered is the last date that the query looked at for updated records. You would save this value and use it as the start date for you next Get Updated query

Sunday, September 22, 2013

Salesforce REST Exceptions

I get a lot of emails from people working with the Salesforce REST who say that they are getting a 400 error (Bad Request) when trying to perform one of the API functions. In this article I will talk about this error and how to troubleshoot it as well as other API errors.
For the purpose of this article I will assume you are using an HttpWebRequest object to make your requests to Salesforce. The function on HttpWebRequest that does the actual work is  GetResponse. This function makes the call to the remote server and retrieves the response. If anything goes wrong with a REST API request on the Salesforce server it will generate an 400 HTTP error response code and then GetResponse will throw a WebException containing that status code. The 400 response code isn’t too useful since there are a lot of things that can cause it, but if you catch the exception you can still access the response stream which will contain a JSON object with more details.
As an example, if we make a call to get an Access Token and pass an incorrect client_secret you will get this web exception:
"The remote server returned an error: (400) Bad Request."
If we then get the response stream we find this:
{\"error\":\"invalid_client\",\"error_description\":\"invalid client credentials\"}
Here is another example. If we issue a query command with a syntax error in the SOQL query (SELEC id,accountNumber,name from account), you will get this:
{ \"message\" : \"unexpected token: SELEC\",\n  \"errorCode\" : \"MALFORMED_QUERY\"}
You can see that it didn’t recognize “SELEC” since it is missing the ‘T’. Note that the format of the JSON in this message is different then the first one.
So what is the best way to handle this? There are a number of options depending on you needs, but here is one suggestion. Here is the piece of code that makes the web request that would be in a library used by all Salesforce REST calls. ‘req’ is  a HttpWebRequest object.

req.Method = "GET";
System.Net.WebResponse resp = null;
try
{
    resp = req.GetResponse();
}
catch (WebException ex)
{
    string msg = "";

    if (ex.Status == WebExceptionStatus.ProtocolError)
    {
        resp = ex.Response;
        msg = new System.IO.StreamReader(resp.GetResponseStream()).ReadToEnd().Trim();
    }

    throw new SalesforceException(msg, ex);
}


In the try block we make the call to Salesforce and get the response. Next we catch only WebExceptions, other exceptions are allow to percolate up to the next level which is a good practice for library functions. Next we check that status of the WebException. There are a lot of things that can cause WebExceptions but most of them will result in a response not being returned, so we just look for a ProtocolError. If we do have a protocol error we retrieve the JSON error message from the response. Finally we create and throw a custom exception I call SalesforceException that will hold both the JSON message and the original exception in the InnerException. The code for the SalesforceExpcetion is here:


public class SalesforceException : Exception 
{
    public SalesforceException(string message, Exception innerException)
        : base(message, innerException) { }
}


One other thing to note about Salesforce REST errors. Since REST commands are based on URIs, if you have an error in the URI you will normally get back a HTTP 404 error instead of a 400 error. For example if we send a read command with an invalid object ID in the URI, we will get a 404 error instead of a 400 error.

Saturday, June 8, 2013

Windows 8 + Salesforce.com Part 5: Value Converters

One of the nice features of XAML is it’s extremely flexible data binding capabilities. In previous posts I have been showing how to build a Windows 8 application to display data from Salesforce.com and I use data binding to display the data. XAML doesn’t limit you to just binding text values, you can bind to pretty much any property on a control.
In the Salesforce example I have been developing I display the account data in a tiled gird view. Lets say we wanted to change the color of the tile based on the Priority field in the Salesforce account record. If that field contained a color value we could bind it directly to the Background property of the tile, but you normally wouldn’t have a color value in a data record. In this case the field contains the text values High, Medium or Low. Fortunately, XAML provides a solution to this, value converters.
Value converters simply take one value as an input and return a different value that may even be of different data type. Here is a the converter I created to convert the Priority field value into a color.

public class AccountPriorityConverter : IValueConverter
{

    public object Convert(object value, Type targetType, object parameter, string language)
    {
        switch ((string)value)
        {
            case "High":
                return new Windows.UI.Xaml.Media.SolidColorBrush(Windows.UI.Colors.Green);
            case "Medium":
                return new Windows.UI.Xaml.Media.SolidColorBrush(Windows.UI.Colors.Yellow);
            case "Low":
                return new Windows.UI.Xaml.Media.SolidColorBrush(Windows.UI.Colors.Red);  
            default:
                return new Windows.UI.Xaml.Media.SolidColorBrush(Windows.UI.Colors.LightGray);
        } 
    }

    public object ConvertBack(object value, Type targetType, object parameter, string language)
    {
        throw new NotImplementedException();
    }
}

Value converters are classes that implement the IValueConverter interface which has two functions, Convert and ConvertBack. Convert converts the value from your data source into a value that can be used by a XAML property. ConvertBack is needed when you are doing two way binding and need to convert a XAML property back to a value needed by your data source, you will usually not need to implement this function.

Now lets look back at Convert. The first parameter is the value that needs to be converted. Note that it is type object so you will need to cast it to the proper data type. Most of the time you will only need to use this first parameter, the remaining three are used in special situations. The targetType parameter specifies the type you need to convert to. Usually you will know ahead of time what type you are converting to, but there may be special cases when you need this. The third parameter is called parameter and allows you to pass a value from your XAML markup into the converter. For example you could use this to pass in a format string so a string value can be formatted differently in different bindings. The final parameter, language, is used when you do internationalized applications.

In the Convert function I first cast the value to a string then in  a switch statement I return a different colored Brush for each of the Priority values.

To use the value converter we must first declare it on the page were we want to use it. This is done by adding the following line to the <Page.Resources> section of the XAML markup:

<local:AccountPriorityConverter x:Key="PiorityConverter" />

AccountPriorityConverter is the name of the class we just created and PriorityConverter will be the name we use in this page to refer to it. Finally in the DataTemplate for the account items the static value for Background property is changed to bind to the CustomerPriority property using the converter:

<StackPanel Orientation="Vertical" Width="200" Height="200"  Background="{Binding CustomerPriority__c,  Converter={StaticResource PiorityConverter}}"  >

In the binding statement we start with the property we want to bind to, in the case CustomerPriority__c which will contain a string value with the priority. In the Converter attribute we refer to the PriorityConverter resource we specified in the <Page.Resources> section. This converter will take the priority string, convert it to a Brush object which is then assigned to the Background of the StackPanel.

Now when we run the program instead of every item being the same color, they are colored based on the priority.

image


You can download the complete code for this project here:


https://sites.google.com/site/danlboris/SalesforceDemo_Part5.zip

Friday, February 22, 2013

Syncfusion Succinctly series

I recently learned about a new series of free e-books from Syncfusion called the Succinctly series. If you have ever picked up a book on a technical topic you will know that they can run into the hundreds if not thousands of pages and normally cover their topic with great width and depth. This is fine as a reference book, or if you need to know everything about a technology, but what if you just need a quick introduction to something?

This is the goal of the Succinctly series. Each book is under 100 pages and provides an easy to read primer on a specific topic. If you have been reading my posts on the Salesforce REST interface, the book on HTTP would a good read. It provides a nice introduction to the technical details of the HTTP protocol. I found the level of the writing pretty good, it’s not overly technical but also isn’t dumbed down to the point where it will turn off technical readers. HTTP is topic that I was pretty knowledgeable about but I still learned a few things from this book.

Currently I am reading the one on Knockout.js and I am finding it as good a the HTTP one. I like that it didn’t spend a lot of time on introductory topics but jumped right into code samples.

I highly recommend checking out this series of book, you can find them here:

http://www.syncfusion.com/resources/techportal

Saturday, January 19, 2013

Salesforce Demo Bug

Today I went back to do some more work on the Salesforce Demo application that I have been presenting in my last few blog posts. When I ran the application and tried to login nothing would happen, the login was not completing for some reason even though the same code worked a few weeks ago. Doing some debugging I found I was getting the error “redirect_uri must match configuration” when I made the call to get the access token. The problem turned out to be with this line:

body.Append("redirect_uri = " + redirectURL);

It appears that the extra white space before and after the equals sign wasn’t a problem for Salesforce in the past, but it is now. Removing the white space fixes the problem.

body.Append("redirect_uri=" + redirectURL);

Saturday, January 12, 2013

Windows 8 Snapped View

When a Windows Store application is run, by default it is displayed without any chrome (window border, menus, etc) and fills the entire screen. You an also display two applications at once by snapping one of them to one side of the screen or the other.
 SnappedView1
It’s interesting to note that one of the two applications can actually be the desktop.
 SnappedView2
In this post I will show how to implement the snapping feature in your own apps. I will use the Salesforce app that we have been developing in the last few posts to demonstrate this, but the techniques are applicable to any Windows 8 application.
Changing an application from full screen to snapped view is handled automatically by the Windows Runtime (and cannot be disabled) but unless you specifically setup your application to handle this, the same layout will be used for both the full screen and snapped view which usually won’t look very good. Fortunately the page templates that come with Visual Studio provide most of the code you need to handle this.
First lets look at AccountsView.xaml. The default itemsView page template has two content controls in it, a GridView and a ListView. The ListView is used when the application is snapped to the left or right side of the screen and the GridView is used at other times. Currently our application only has the GridView setup, so let’s start by setting up the ListView.
By default the ListView’s ItemTemplate uses the Standard80ItemTemplate, but this doesn’t reference the fields we have in the Account object so we need to create a new template. Here is a simple template that goes in the Page.Resource section.

<DataTemplate x:Key="StandardListItemTemplate">
    <StackPanel Margin="10,0,0,0">
        <TextBlock Text="{Binding Name}" Style="{StaticResource ItemTextStyle}" MaxHeight="40"/>
        <TextBlock Text="{Binding AccountNumber}" Style="{StaticResource CaptionTextStyle}" TextWrapping="NoWrap"/>
    </StackPanel>
</DataTemplate>

This template simply displays two TextBlocks in a StackPanel, one for the account Name and the other for the AccountNumber. Next we need to change the ListView to use this template.

ItemTemplate="{StaticResource StandardListItemTemplate}"

Now that we have defined two separate views of the data, the next thing we need to do is define which views are used when. This is also done in the XAML markup using a VisualStateManager. The VisualStateManager manages groups of control states where one state in each group can be active at a time. The code that actually triggers the state switching in the LayoutAwarePage.cs class, which we will look at in a minute, and this code expects the various visual states to be named the same as the states the application can be in. There are four possible states:

FullScreenLandscape: The application is filling the screen in landscape orientation.

FullScreenPortrait: The application is filling the screen in portrait orientation.

Filled: Another application is snapped and the current application is filling the rest of the screen.

Snapped: The application is snapped to either the left or right side of the screen.

There is also one more state which I will call the base state. This is the state of the controls as they are defined in the XAML markup before any other states have been applied.

Now let’s look at the markup:

<VisualStateManager.VisualStateGroups>
         <VisualStateGroup x:Name="ApplicationViewStates">
             <VisualState x:Name="FullScreenLandscape"/>
             <VisualState x:Name="Filled"/>

The first part declares the visual state manager, and a VisualStateGroup called ApplicationViewStates which will hold the various states our application can be in. Next we have the first two states, FullScreenLandscape and Filled. You will notice that both of these states are empty which means that when the application enters either of these states the base state will be used. The base state in the XAML displays the GridView which is appropriate for both of those states.

<VisualState x:Name="FullScreenPortrait">
    <Storyboard>
        <ObjectAnimationUsingKeyFrames Storyboard.TargetName="backButton" Storyboard.TargetProperty="Style">
            <DiscreteObjectKeyFrame KeyTime="0" Value="{StaticResource PortraitBackButtonStyle}"/>
        </ObjectAnimationUsingKeyFrames>
        <ObjectAnimationUsingKeyFrames Storyboard.TargetName="itemGridView" Storyboard.TargetProperty="Padding">
            <DiscreteObjectKeyFrame KeyTime="0" Value="96,136,86,56"/>
        </ObjectAnimationUsingKeyFrames>
    </Storyboard>
</VisualState>

The next section defines the view for FullScreenPortrait. In this case the state simply adjusts some spacing around the GridView and the backButton. To do this it uses a storyboard that describes which properties of the controls need to change and how they should transition. The first part uses an animation to change the Style property on the backButton to the PortraitBackButtonStyle defined in StandardStyles.xaml. Even though this is defined as an animation the KeyTime is zero so the change happens immediately. The second part performs a similar animation to change the gridView padding.

        <VisualState x:Name="Snapped">
            <Storyboard>
                <ObjectAnimationUsingKeyFrames Storyboard.TargetName="backButton" Storyboard.TargetProperty="Style">
                    <DiscreteObjectKeyFrame KeyTime="0" Value="{StaticResource SnappedBackButtonStyle}"/>
                </ObjectAnimationUsingKeyFrames>
                <ObjectAnimationUsingKeyFrames Storyboard.TargetName="pageTitle" Storyboard.TargetProperty="Style">
                    <DiscreteObjectKeyFrame KeyTime="0" Value="{StaticResource SnappedPageHeaderTextStyle}"/>
                </ObjectAnimationUsingKeyFrames>
                <ObjectAnimationUsingKeyFrames Storyboard.TargetName="itemListView" Storyboard.TargetProperty="Visibility">
                    <DiscreteObjectKeyFrame KeyTime="0" Value="Visible"/>
                </ObjectAnimationUsingKeyFrames>
                <ObjectAnimationUsingKeyFrames Storyboard.TargetName="itemGridView" Storyboard.TargetProperty="Visibility">
                    <DiscreteObjectKeyFrame KeyTime="0" Value="Collapsed"/>
                </ObjectAnimationUsingKeyFrames>
            </Storyboard>
        </VisualState>
    </VisualStateGroup>
</VisualStateManager.VisualStateGroups>

The final section of the VisualStateManager markup is for the Snapped state. For this state we change the appearance of the back button and page title. We also hide the gridview and show the listview by setting the Visibility property on both objects.
Now that we have a VisualStateManager that describes how the page looks in each state, the final piece of the puzzle is the code that triggers the change from one state to another. The code for doing this is provided as part of the LayoutAwarePage class.
Here is the key function from LayoutAwarePage.cs

public void InvalidateVisualState()
{
    if (this._layoutAwareControls != null)
    {
        string visualState = DetermineVisualState(ApplicationView.Value);
        foreach (var layoutAwareControl in this._layoutAwareControls)
        {
            VisualStateManager.GoToState(layoutAwareControl, visualState, false);
        }
    }
}

This function is called whenever the windows size changes. First it gets the current state of the page by calling ApplicationView.Value and passing this to the DetermineVisualState function. By default DetermineVisualState just does a ToString() on the value, but I assume there are cases where you would override this default implementation. Once it has the state it iterates through a collection of controls, which by default only contains the current page, and calls the GoToState function passing in the control and the name of the state we want to put the control into. The name corresponds to the name property in VisualState elements of the VisualStateManager markup.

Now we have everything needed for our application to respond to state changes. I have shown a lot of code where but the majority of it is included in the Visual Studio templates to that actual work to implement this is pretty minimal.