Search This Blog

Thursday, August 25, 2016

R Script arguments from Command Line

Passing RScript arguments from Command Line

There may be situations where you might want to execute R scripts from command line and passing appropriate arguments as batch. So here is an example below

C:\Users\UserName\Documents\>
"C:\Program Files\R\R-3.3.0\bin\RScript.exe" args.R 2016-08-01 28 1 30 > args.t

In the above I have specified the location of the RScript.exe on my local computer and args.R is the R script that I want to run from command line and the arguments are 2016-08-01 which is a Date in the format yyyy-mm-dd, then 28 and then 1 and 30 and the result of the execution will get saved in args.txt file which is the output of the execution.

Here is the sample R script code below

#READ THE ARGUMENTS
args <- commandArgs(TRUE)

# test if there is at least one argument: if not, return an error
if (length(args)==0) {
  stop("First parameter is a required argumenst.n", call.=FALSE)

print(args)

#GETTING ARGUMENT OF THE R SCRIPT.
startDate <- as.Date(args[1])
sId <- eval(parse(text=args[2]))
min <- eval(parse(text=args[3]))
max <- eval(parse(text=args[4]))

#write the variables as observations of a dataframe
columnNames <- c('sId', 'startDate', 'min', 'max')
columnValues <- c(sId, startDate, min, max)
df = data.frame(columnNames, columnValues)
str(df)
print(df)
write.csv(df, file = "C:/args.csv", row.names=F)

The data frame created above gets saved as a csv file for review. Please note internally Date is represented as integer in R.

Wednesday, August 17, 2016

Self-Signed Certificates with Microsoft Enhanced RSA and AES Cryptographic Provider

Creating Enhanced SHA256 self-signed certificates

There are 2 options to create self-signed certificates very easily

using windows makecert

The following command can be run from the command prompt to create a self-signed certificate. Based on location of the makecert.exe on you machine, the path might differ. I am using a Windows 8.1
"C:\Program Files (x86)\Windows Kits\8.1\bin\x86\makecert.exe" -n "CN=Local" -r -pe -a sha256 -len 2048 -cy authority -e 03/03/2017 -sv Local.pvk Local.cer


"C:\Program Files (x86)\Windows Kits\8.1\bin\x86\pvk2pfx.exe" -pvk Local.pvk -spc Local.cer -pfx Local.pfx -po MyPassword -sy 24

using openSSL

you can use openSSL that comes with Apache Webserver to get the same thing done as follows

openssl.exe req -x509 -nodes -sha256 -days 3650 -subj "/CN=Local" -newkey rsa:2048 -keyout Local.key -out Local.crt

openssl.exe pkcs12 -export -in Local.crt -inkey Local.key -CSP "Microsoft Enhanced RSA and AES Cryptographic Provider" -out Local.pfx

Difference Between Above two

One major and most important difference between the 2 above is makecert is not able to create the certificate file with CSP of 24 as provided as provided as parameter so while using this *pfx file to sign any XML as SHA256 will give exception like "Invalid Algorithm Specified" because the CSP value remains 1 instead of 24.

The one created by Open SSL will come out with correct CSP value and will give any errors.

Check Keys of Generated Certificate

You can write a small test program to test the Keys generated by the certificates in the above 2 methods.

class Program
    {
        static void Main(string[] args)
        {
            var x509Certificate = new X509Certificate2(@"Local.pfx", 
                "LocalSTS", X509KeyStorageFlags.Exportable);
            Console.WriteLine(x509Certificate.ToString(true));
            Console.ReadLine();
        }
    }


Monday, May 16, 2016

Azure SQL query slow performance

Azure SQL Intermittently slow at intervals

If you do not have any maintenance job scheduled on the database, then you will need to run some maintenance queries to update the stats if your data in the database is having frequent inserts and updates. Here is  how you can do it as below

Connect to your database from SSMS (SQL Server Management Studio), or another client of your choosing.

Update all your tables data distribution statistics, with a 100% sampling rate (Fullscan). This data is used by the query optimizer to choose an execution plan for the queries, and it’s vital that they are updated to get effective execution plans.

---------------------------Update statistics of all database tables
DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'UPDATE STATISTICS ' + DB_NAME() + '.' + rtrim(sc.name) + '.' + rtrim(so.name) + ' WITH FULLSCAN, ALL; '
from sys.sysobjects so
join sys.schemas sc
on so.uid = sc.schema_id
where so.xtype = 'U'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)
---------------------------------------------------------------------
Then follow with a recompilation of all objects, by using sp_recompile.

This causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. This ensures the new data distribution statistics or indexes are used in execution plans.
----------------------------------------------------------------------
--Force recompilation of all objects

SET QUOTED_IDENTIFIER OFF
DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT "EXEC sp_recompile '" + rtrim(sc.name) + "." + rtrim(so.name) + "' "
from sys.sysobjects so
join sys.schemas sc
on so.uid = sc.schema_id
where so.xtype = "U"
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)
SET QUOTED_IDENTIFIER ON
----------------------------------------------------------------------
References

UPDATE STATISTICS (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms187348.aspx

sp_recompile (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms181647.aspx  

Wednesday, February 18, 2015

jquery ui autosuggest dropdown in ASP.NET

Jquery UI Autosugget dropdown in ASP.NET

Very recently, I came across a requirement where I have a conventional ASP.NET site which has a dropdown, I have to make
1. Enabling filtering on the dropdown by its bound text.
2. Provide another search field which will enable search by the Id and the same will get selected in the auto suggest dropdown.

Problem : The default jquery ui events in the demos of their official site will not have this functionality, since its an existing dropdown which is used in numerous places in the code behind, its not really advisable to change the code behind.

Solution : We know for sure that the dropdown is bound to values in the code behind, all we have to do is to enable filtering by its text, which is pretty straight forward with the jquery ui in play.

Now coming to 2nd part, here we have to bind the source of the search field by the values of the dropdown that is already bound in the code behind. Then we have to enable search by id not by the text and select the appropriate dropdown item on select of the search field.

The following code snippet just does that.

First bind the dropdown in the code behind, (please note that this already existed)
//bind dropdown
            ddLangs.Items.Clear();
            ddLangs.Items.Add(new ListItem("select", "0"));
            ddLangs.Items.Add(new ListItem("vb.net", "1"));
            ddLangs.Items.Add(new ListItem("c#", "2"));
            ddLangs.Items.Add(new ListItem("java", "3"));
            ddLangs.Items.Add(new ListItem("pascal", "4"));

Secondly Add the Search elements to the form and enclose the existing dropdown in a div and style class

<p>
        Search lang Id:<input type="text" id="autoLangs" style="width:500px;" /><input type="hidden" id="autoLangId" />
        <span id="autoResult"></span>
    </p>
    
        Languages : <div class="ui-widget"><asp:DropDownList ID="ddLangs" runat="server"></asp:DropDownList></div>

Thirdly add the javscript starting with the reference to the UI and Jquery libraries needed to refer and then the custom style and coding, I have highlighted the calls that initiates the combobox feature and the search by ID feature.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/themes/smoothness/jquery-ui.css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/jquery-ui.min.js"></script>


<style>
        .ui-autocomplete { 
        /* these sets the height and width */
        max-height:200px; 
        max-width: 500px; 

        /* these make it scroll for anything outside */
        overflow-x:auto;
        overflow-y:auto;
        }
      .custom-combobox {
        position: relative;
        display: inline-block;
      }
      .custom-combobox-toggle {
        position: absolute;
        top: 0;
        bottom: 0;
        margin-left: -1px;
        padding: 0;
      }
      .custom-combobox-input {
        margin: 0;
        padding: 5px 10px;
      }
    </style>
    <script type="text/javascript">
        $(document).ready(function () {
            (function ($) {
                $.widget("custom.combobox", {
                    _create: function () {
                        this.wrapper = $("<span>")
                      .addClass("custom-combobox")
                      .insertAfter(this.element);

                        this.element.hide();
                        this._createAutocomplete();
                        this._createShowAllButton();
                    },

                    _createAutocomplete: function () {
                        var selected = this.element.children(":selected"),
                      value = selected.val() ? selected.text() : "";

                        this.input = $("<input>")
                      .appendTo(this.wrapper)
                      .val(value)
                      .attr("title", "")
                      .addClass("custom-combobox-input ui-widget ui-widget-content ui-state-default ui-corner-left")
                      .css("width", "500px")
                      .autocomplete({
                          delay: 0,
                          minLength: 0,
                          source: $.proxy(this, "_source")
                      })
                      .tooltip({
                          tooltipClass: "ui-state-highlight"
                      });

                        this._on(this.input, {
                            autocompleteselect: function (event, ui) {
                                //alert('select fired');
                                //search ruleId text box value set as dropRules
                                $("#autoLangs").val('');
                                $("#autoLangId").val('0');
                                $("#autoResult").text('');

                                ui.item.option.selected = true;
                                this._trigger("select", event, {
                                    item: ui.item.option
                                });
                            },

                            autocompletechange: "_removeIfInvalid"
                        });
                    },

                    _createShowAllButton: function () {
                        var input = this.input,
                      wasOpen = false;

                        $("<a>")
                      .attr("tabIndex", -1)
                      .attr("title", "Show All Items")
                      .tooltip()
                      .appendTo(this.wrapper)
                      .button({
                          icons: {
                              primary: "ui-icon-triangle-1-s"
                          },
                          text: false
                      })
                      .removeClass("ui-corner-all")
                      .addClass("custom-combobox-toggle ui-corner-right")
                      .mousedown(function () {
                          wasOpen = input.autocomplete("widget").is(":visible");
                      })
                      .click(function () {
                          input.focus();

                          // Close if already visible
                          if (wasOpen) {
                              return;
                          }

                          // Pass empty string as value to search for, displaying all results
                          input.autocomplete("search", "");
                      });
                    },

                    _source: function (request, response) {
                        var matcher = new RegExp($.ui.autocomplete.escapeRegex(request.term), "i");
                        response(this.element.children("option").map(function () {
                            var text = $(this).text();
                            if (this.value && (!request.term || matcher.test(text)))
                                return {
                                    label: text,
                                    value: text,
                                    option: this
                                };
                        }));
                    },

                    _removeIfInvalid: function (event, ui) {
                        try {

                            // Selected an item, nothing to do
                            if (ui.item) {
                                return;
                            }

                            // Search for a match (case-insensitive)
                            var value = this.input.val(),
                          valueLowerCase = value.toLowerCase(),
                          valid = false;
                            this.element.children("option").each(function () {
                                if ($(this).text().toLowerCase() === valueLowerCase) {
                                    this.selected = valid = true;
                                    return false;
                                }
                            });

                            // Found a match, nothing to do
                            if (valid) {
                                return;
                            }

                            // Remove invalid value
                            this.input
                          .val("")
                          .attr("title", value + " didn't match any item")
                          .tooltip("open");
                            this.element.val("");
                            this._delay(function () {
                                this.input.tooltip("close").attr("title", "");
                            }, 2500);
                            this.input.autocomplete("instance").term = "";
                        }
                        catch (e) {
                        }

                    },
                    autocomplete: function (searchVal, searchTxt) {
                        this.element.val(searchVal);
                        this.input.val(searchTxt);
                        // Search for a match (case-insensitive)
                        var value = this.input.val(),
                      valueLowerCase = value.toLowerCase(),
                      valid = false;
                        this.element.children("option").each(function () {
                            if ($(this).text().toLowerCase() === valueLowerCase) {
                                this.selected = valid = true;
                                return false;
                            }
                        });
                        // Found a match, nothing to do
                        if (valid) {
                            return;
                        }
                    },

                    _destroy: function () {
                        this.wrapper.remove();
                        this.element.show();
                    }
                });
            })(jQuery);

            $(function () {
                $('#<%= ddLangs.ClientID %>').combobox();
                bindAutoSuggest();
            });

            
        });

        //bind auto suggest search
        function bindAutoSuggest() {
            var rules = [];
            var rule = { value: "", label: "", desc: "" };

            $('#<%= ddLangs.ClientID %>' + ' option').each(function () {
                var rule = { value: $(this).val(), label: $(this).val() + '-' + $(this).text(), desc: $(this).text() }
                rules.push(rule);
            });

            $("#autoLangs").autocomplete({
                minLength: 0,
                source: rules,
                focus: function (event, ui) {
                    $("#autoLangs").val(ui.item.label);
                    return false;
                },
                select: function (event, ui) {
                    $("#autoLangs").val(ui.item.label);
                    $("#autoLangId").val(ui.item.value);
                    $("#autoResult").text('You selected ruleId : ' + $("#autoLangId").val());
                    var selectedRuleId = $("#autoLangId").val();
                    var selectedText = ui.item.desc;
                    $('#<%= ddLangs.ClientID %>').combobox('autocomplete', selectedRuleId, selectedText);
                    return false;
                }
            });
        }

       
        
    </script>

Wednesday, November 19, 2014

asp.net mvc 4 json post max limit problem

Sending Long JSON complex objects content length problem

While working on a project, I was faced with a very difficult to resolve issue. At first it was very difficult to detect what was wrong and then what boiled down to was that my JSON complex object that I was POSTing back to the server on ajax call was too big and hence the server was not able to handle it.

Problem

In my scenario, the call from the client side was an ajax post call to the MVC controller and the mvc site is actually running as a subsite of the main asp.net website. An initial search on the google will give you resolutions like below

var jsonResult = Json(dataPlot, JsonRequestBehavior.AllowGet);
jsonResult.maxJsonLength = int.MaxValue;
return jsonResult;

Possible configuration change

<configuration>
    <system.web.extensions>
        <scripting>  
             <webServices>                                                   
                 <jsonSerialization maxJsonLength="1000000" />                 
             </webServices>
        </scripting>
    </system.web.extensions>
</configuration>

But believe me, none of the above will resolve the issue as the inbuilt Javascript serializer is not going to check this setting while serializing and deserializing data to the object.

Solution

I came across a blogpost from a gentleman called Robert and his explanation seemed very good. I went with the jquery plugin that he wrote and it actually resolved my issue. I am going to paste the jquery plugin that he wrote and also how I have called it in my code to resolve it is pasted below.

Call from my Code

Here you can see below I have commented the datatype and content type of Json and used the plugin to convert it to a dictionary of object.
$.ajax({
                    url: '@Url.Action("save", "CmpgnView")',
                    type: 'POST',
                    //dataType: 'json',
                    //contentType: 'application/json; charset=utf-8',
                    //data: JSON.stringify(reqObj),
                    data: $.toDictionary(reqObj),
                    async: false,
                    success: function (data) {
                      //do something
                    },
                    error: function (data, status, xhr) {
                       alert("Error: " + xhr.status + " : " + xhr.statusText);
                    }
                });

Controller Method on Server Side

[HttpPost]
        public async Task<ActionResult> save(Req req)
        {
            string userId = UserId;
            Response result = await Task.Run(() =>
            {
                return validateAndSave(req, userId);
            });
            return Json(result);
            
        }

Jquery Plugin Code from this WebSite

Please also read the explanations given by Robert, its excellent from his blog
(http://erraticdev.blogspot.com/2010/12/sending-complex-json-objects-to-aspnet.html)

/*!
 * jQuery toDictionary() plugin
 *
 * Version 1.2 (11 Apr 2011)
 *
 * Copyright (c) 2011 Robert Koritnik
 * Licensed under the terms of the MIT license
 * http://www.opensource.org/licenses/mit-license.php
 */
 
(function ($) {
 
    // #region String.prototype.format
    // add String prototype format function if it doesn't yet exist
    if ($.isFunction(String.prototype.format) === false)
    {
        String.prototype.format = function () {
            var s = this;
            var i = arguments.length;
            while (i--)
            {
                s = s.replace(new RegExp("\\{" + i + "\\}", "gim"), arguments[i]);
            }
            return s;
        };
    }
    // #endregion
 
    // #region Date.prototype.toISOString
    // add Date prototype toISOString function if it doesn't yet exist
    if ($.isFunction(Date.prototype.toISOString) === false)
    {
        Date.prototype.toISOString = function () {
            var pad = function (n, places) {
                n = n.toString();
                for (var i = n.length; i < places; i++)
                {
                    n = "0" + n;
                }
                return n;
            };
            var d = this;
            return "{0}-{1}-{2}T{3}:{4}:{5}.{6}Z".format(
                d.getUTCFullYear(),
                pad(d.getUTCMonth() + 1, 2),
                pad(d.getUTCDate(), 2),
                pad(d.getUTCHours(), 2),
                pad(d.getUTCMinutes(), 2),
                pad(d.getUTCSeconds(), 2),
                pad(d.getUTCMilliseconds(), 3)
            );
        };
    }
    // #endregion
 
    var _flatten = function (input, output, prefix, includeNulls) {
        if ($.isPlainObject(input))
        {
            for (var p in input)
            {
                if (includeNulls === true || typeof (input[p]) !== "undefined" && input[p] !== null)
                {
                    _flatten(input[p], output, prefix.length > 0 ? prefix + "." + p : p, includeNulls);
                }
            }
        }
        else
        {
            if ($.isArray(input))
            {
                $.each(input, function (index, value) {
                    _flatten(value, output, "{0}[{1}]".format(prefix, index));
                });
                return;
            }
            if (!$.isFunction(input))
            {
                if (input instanceof Date)
                {
                    output.push({ name: prefix, value: input.toISOString() });
                }
                else
                {
                    var val = typeof (input);
                    switch (val)
                    {
                        case "boolean":
                        case "number":
                            val = input;
                            break;
                        case "object":
                            // this property is null, because non-null objects are evaluated in first if branch
                            if (includeNulls !== true)
                            {
                                return;
                            }
                        default:
                            val = input || "";
                    }
                    output.push({ name: prefix, value: val });
                }
            }
        }
    };
 
    $.extend({
        toDictionary: function (data, prefix, includeNulls) {
            /// <summary>Flattens an arbitrary JSON object to a dictionary that Asp.net MVC default model binder understands.</summary>
            /// <param name="data" type="Object">Can either be a JSON object or a function that returns one.</data>
            /// <param name="prefix" type="String" Optional="true">Provide this parameter when you want the output names to be prefixed by something (ie. when flattening simple values).</param>
            /// <param name="includeNulls" type="Boolean" Optional="true">Set this to 'true' when you want null valued properties to be included in result (default is 'false').</param>
 
            // get data first if provided parameter is a function
            data = $.isFunction(data) ? data.call() : data;
 
            // is second argument "prefix" or "includeNulls"
            if (arguments.length === 2 && typeof (prefix) === "boolean")
            {
                includeNulls = prefix;
                prefix = "";
            }
 
            // set "includeNulls" default
            includeNulls = typeof (includeNulls) === "boolean" ? includeNulls : false;
 
            var result = [];
            _flatten(data, result, prefix || "", includeNulls);
 
            return result;
        }
    });
})(jQuery);

Wednesday, October 15, 2014

Reading XML in SQL Server 2005/2008

XML Processing in SQL Server

SQL Server has very good support for reading/processing XML. Here are some of the examples below with different XML structures. I found it extremely handy when using them for passing as collections to stored procedures.

Example 1: Entire data is passed as an attribute so that the XML is short formed. The below example shows how to read the element name along with the attribute value.

SQL :  DECLARE @xml XML = 
'<root>
<offer old="old1" new="new1" module="1" />
<offer old="old2" new="new2" module="2" />
</root>'

SELECT  T.c.value('local-name(.)[1]', 'varchar(100)') AS Element,
        T.c.value('@new', 'varchar(20)') as New
FROM   @xml.nodes('/root/offer') T(c);

Example 2: XML had Node value and as well as attributes
SQL : 
DECLARE @xml XML = 
'
<root>
<item>
    <node1 offer="1">2.09</node1>
    <node2 offer="2">2.97</node2>
</item>
</root>
'

SELECT  T.c.value('local-name(.)[1]', 'varchar(100)') AS Element,
        T.c.value('./text()[1]', 'decimal(17,2)') AS Value,
        T.c.value('@offer', 'varchar(20)') as Offer
FROM   @xml.nodes('//item/child::node()') T(c);

Both the above examples (xml structures), have been very effective and useful ones for me. 

Friday, April 4, 2014

Reading ASP.NET Checkboxlist selected text using Jquery

Reading Checkbox list selected items using jquery

*I have posted the same on StackOverflow.

I solved the problem in the following way. My ASP.NET code for the checkboxlist is as below
    
    <asp:CheckBoxList ID="chkHourly" runat="server" RepeatLayout="Table" 
    RepeatColumns="4"   RepeatDirection="Horizontal">
     <asp:ListItem Value="0">00:00 AM</asp:ListItem>
     <asp:ListItem Value="1">01:00 AM</asp:ListItem>
     <asp:ListItem Value="2">02:00 AM</asp:ListItem>
    </asp:CheckBoxList>  

The generated HTML will look like this below

    <table id="ctl00_chkHourly" border="0">
    <TBODY>
     <TR>
     <TD>
      <INPUT id=ctl00_chkHourly_0 name=ctl00$chkHourly$0 value="" CHECKED type=checkbox>      
      <LABEL for=ctl00_chkHourly_0>00:00 AM</LABEL></TD>
     <TD>
      <INPUT id=ctl00_chkHourly_1 name=ctl00$chkHourly$1 value="" type=checkbox>
      <LABEL for=ctl00_chkHourly_1>01:00 AM</LABEL></TD>
     <TD>
      <INPUT id=ctl00_chkHourly_2 name=ctl00$chkHourly$2 value="" type=checkbox>
      <LABEL for=ctl00_chkHourly_2>02:00 AM</LABEL>
     </TD>
      </TR>
     </TBODY>

Please notice that there is a label created besides each input in the table, and when a checkbox is checked, the input's value will be 'on' and what you see as an option is the label's text, in my case I needed the text, but to get the value also in a round about away, I would read the name of the individual input fields that are checked. Please see the code below that I have written to read the text selected and also the name of the input selected so that I can strip it and read the value if needed.

    var postData = new Array();
    $("[id*=chkHourly] input[type=checkbox]:checked").each(function () {
         alert($(this).next().text());
         alert($(this).next().html());
         alert($(this).attr("name"));
         postData.push($(this).next().text());
     });
                
     if (postData.length > 0) {
      alert("Selected Text(s): " + postData);
     } 
     else {
      alert("No item has been selected.");
     }