Mytechtoday.com

Get the the difference between two dates in MS SQL

2011-11-18T12:21:00.001-08:00

The DATEDIFF() function returns the difference between two dates
Syntax: DATEDIFF ( datepart , startdate , enddate )

Following is an example for the DateDiff()

DECLARE @startdate datetime ='2011-11-14 02:00:06.957';
 DECLARE @enddate datetime = '2011-11-16 05:00:15.490';
SELECT DATEDIFF(day, @startdate, @enddate) as dayDifference,DATEDIFF(HOUR, @startdate, @enddate)as hourDifference 
Output

dayDifference   hourDifference  
  2                    51

Similarly the following are the available datepart Options on the DateDiff() Function.


year(or yy or yyyy)
quarter(or qq or q)
month (or mm or m )
dayofyear(or dy or y )
day (or dd, d )
week(or wk or ww)

hour(or hh)
minute(or mi or n)
second(or ss or s )
millisecond(or ms)
microsecond(or mcs) 
nanosecond(or ns) 
TZoffset(or tz)
ISO_WEEK(or isowk or isoww)

Get the Row Numbers as a separate column on MS SQL Query

2011-11-18T11:27:00.000-08:00

I was trying to execute a new query where I need the Row number as a new column on the result itself. I used the SQL Function ROW_NUMBER() to get the row number.
Following query gives the Report Path with the HitCount along with the added Row Number.


select ROW_NUMBER() OVER(ORDER BY count(ReportPath) DESC) AS 'Row Number',  ReportPath,count(ReportPath) as HitCount
FROM [ReportServer].[dbo].[ExecutionLog2]
Group by ReportPath
Order by HitCount desc

Cheers !

Get the Disk Usage Statistics from the WinDirStat !

2011-07-08T12:48:00.000-07:00

WinDirStat is an opensource tool to identify howmuch of disk space you are using on your wondow box. It shows disk, file and directory sizes in a treelist as well as graphically in a treemap, much like KDirStat or SequoiaView.

Once i started using it, i feel like life would have been very hard without it.
Why don't you try it yourself.

You can download the tool from http://windirstat.info/

How to parse multi value parameter from SSRS Report !

2011-06-29T13:18:00.000-07:00

While writing an SSRS Report, I had to parse a multivalued parameter and stored it on a temp table. After some googling i came up with the following solution. @id is the multi valued parameter that need to be parsed. Following is the sample code that you can used to parse and put the values on a temp table.



-- @uid is the parameter. Lets define the parameter for testing.

declare @uid as nvarchar(200)
set @uid='u01052901,u01052789,u01052897,u0105345,u08023432,u0234324,u23479879'

create table #TempIDTable (
    slice varchar(50))
  declare @index1 int
  declare @u_id nvarchar(4000)
    set @index1 = 1
  if @uid is null
    set @index1 = 0
  while @index1 !=0
    begin
      set @index1 = charindex(',',@uid)
      if @index1 !=0
        set @u_id = left(@uid,@index1 - 1)
      else
        set @u_id = @uid
      insert into #TempIDTable select @u_id
      set @uid= right(@uid,len(@uid) - @index1)
      if len(@uid) = 0
        break
      end
 
Select * from #TempIDTable
--drop table #TempIDTable



Date Conversion from YYYY-MM-DD HH:MM:SS:MMM to YYYY-MM-DD 00:00:00:000   !
2011-06-17T09:30:00.000-07:00
Just keeping it handy !

declare @StartTime as DateTime
set @StartTime= dateadd(dd,0, datediff(dd,0,'2011-06-08 11:18:26.000'))
select @StartTime as StartTime

 


Schema Spy, A Graphical Database Schema Metadata Browser.
2010-03-23T12:38:00.000-07:00
Schemaspy is a java based tool that analyzes the metadata of a schema in a database and generated the visual representation of it. The output is highly user friendly. You can browse through table via child and parent table relationship.

Schema Spy uses the dot executable from graphviz  to generate the graphical representation of the schema and its relationship. Graphviz is the graphics visualization software that takes descriptions of graphs in a simple text language, and make diagrams in several useful formats such as images and SVG for web pages, Postscript for inclusion in PDF or other documents; or display in an interactive graph browser.

Following is the steps to execute the SchemaSpy tool (for a sample MYSQL database.)
1. Download and install Graphviz from http://www.graphviz.org/Download..php
2. Download the Schema spay jar file, Schemaspy_x_y_z.jar from http://sourceforge.net/projects/schemaspy/files/
3. Download the corresponding JDBC Connector and have it on the same source folder where the schemaspy_x_y_z.jar resides. For example for a mysql database, I would simply download mysql-connector-java-5.1.5.jar or any other latest version of mysql connector. 
4. Now from the source location of your Schemaspy_x_y_z.jar file, execute schemaspy to generate the graphical representation of the schema of your database, as shown on the following syntax

java -jar schemaSpy_x.y.z.jar -t database_type -dp mysql jdbcdatabase_connector -hq -o output_Directory -db databaseName -u username -p password
for example : java -jar schemaSpy_4.1.1.jar -t mysql -dp mysql-connector-java-5.1.5.jar -hq -o out -db bisudatabae -u bishow -p north




Converting Clob Datatype to String and ViceVersa .
2010-01-22T13:23:00.000-08:00
If you have been using Hibernate 3.x.x or greater you can easily convert clob type to string type and vice versa, i.e String data type to Clob type.

Following piece of codes demonstrate the process of doing the conversion.


.........
.........

Clob textClobValue;
String textValue;
String newTextValue;
Clob newTextClobValue;
InputStream textStream;
Paragraph paragraph  // my domain Class that contains a clob datatypes and used to update the database connection with hibernate connection (Hibernate version >=3.0)
...........
...........

textClobValue=paragraph.getText();
textStream = textClobValue.getAsciiStream(); //return inputstream
textValue=convertStreamToString(textStream);
newTextValue=textValue.replaceAll("Some text","new Text"); //Replacing some text from old text. 
   
newTextClobValue=Hibernate.createClob(newTextValue);
//System.out.println("Updated="+newParaContentValue);
paragraph.setText(newTextClobValue);
..........
..........
..........



public static String convertStreamToString(InputStream is) throws Exception {
     BufferedReader reader = new BufferedReader(new InputStreamReader(is));
     StringBuilder sb = new StringBuilder();
     String line = null;
 
     while ((line = reader.readLine()) != null) {
       sb.append(line + "\n");
     }
     is.close();
     return sb.toString();
   }






Converting Groovy Array type to Java Array Type
2009-11-03T09:33:00.000-08:00
Following is the code snipped in groovy that demonstrate the conversion Groovy Array type to Java Array Type.

Code for Groovy 



def param=[]

param+="paramater 1"

param+="paramater 2"

param+="paramater 3"

param+="paramater 4"

def stringArgForJava = (String[])params 





Test.callJavaClass(stringArgForJava) //static method





The sample java class is as follow;



class Test

 {

  public static void callJavaClass(String[] arg)

   {

       for(int i=0;i        {

            System.out.println("params "+i ":" +params[i]);

        }



    }

}









 








Read a variable defined on the config file from a controller in a grail application
2009-10-13T12:02:00.000-07:00


Let's say, you have the config.groovy file as follow.


 ...........
 ..........

  environments {
     production {
           myVariable1="I am variable1 on RPOD"
           myVariable1="I am variable1 on RPOD"
    }



          development {
               myVariable1="I am variable1 on DEV"
               myVariable1="I am variable1 on RPOD"
      }

  log4j {
    appender.stdout = "org.apache.log4j.ConsoleAppender"
    .........
    ........

 Now the above varaible can be easily access through any of the controller as follow. 

 
    def config = ConfigurationHolder.config
    def myVaraible1 = config.myVaraible1
    def myVaraible2 = config.myVaraible2
 




Reading the status code of  HTTP Request !
2009-07-08T11:12:00.001-07:00
When you make an HTTP request to any server url throuh your client software, lets say IE, HTTP Status codes are returned by the server to the client software to determine the outcome of your request. There are five different classes of status code range. They are 
Status codes 100-101 
Status codes 200-206  
Status codes 300-307 
Status codes 400-417 
Status codes 500-505 

Following is  a simple program written in groovy that checks and returns the status code of any url.

import java.net.URL;
    def url
    def message =""
    boolean fail=true
    def urlString="http://192.168.0.1/test/test.html"
    println checkURL(urlString)

def checkURL(urlString){
          int status_code
        def tempmessage=""
  URL URLserver = new URL("urlString");
 try{
  URLConnection connection = (HttpURLConnection)URLserver.openConnection();

  status_code = connection.getResponseCode();


 switch (status_code)
 {
      case  200:
           tempmessage=" Connection Successful"
           break;
     case  401:
           tempmessage="UnAuthorised"
           break;
     case  405 :
           tempmessage= " Resource Not Found"
           break;
     case  408:
           tempmessage="Request Time-out"
           break;
     case  500:
           tempmessage="Internal Server Error"
           break;
     case  502:
           tempmessage="Bad Gateway"
           break;
      case  503:
           tempmessage="Service Temporarily Unavailable"
           break;
      default :
            tempmessage= connection.getResponseMessage()
        break;

 }
}
catch (Exception exc){
     tempmessage=exc

}
     
return "\n "+urlString+" :"+status_code+":"+tempmessage;

  }




Visit http://www.w3.org/Protocols/HTTP/HTRESP.html for detail about the status codes.





Problems with Hotmail login
2009-06-12T08:43:00.000-07:00
Hotmail has been going under the planned maintenance on its servers, So you might face some problem accessing your hotmail.  Some of the problems that I am facing are like
1. Asking to enter password for multiple times
2. When i type in the address http:// hotmail.com in the address bar and enter than I get a message “The requested URL could not be retrieved”. Sometimes it loads the page but once I enter the password and username it throws the message “The requested URL could not be retrieved”, i.e. the connection timed out. 
3.  Some time I am able to log in but I get the same problem while sending or opening a message. 
Solution: 
Following are some of the solutions recommended by Microsoft,. 
1. http://windowslivehelp.com/solutions/servicemgt/archive/2008/12/26/what-to-do-if-you-can-t-sign-into-windows-live-hotmail.aspx
2. http://windowslivehelp.com/solutions/accounts/archive/2009/03/03/How-to-optimize-Internet-Explorer-browser.aspx
3. http://windowslivehelp.com/solutions/servicemgt/archive/2008/08/22/how-to-determine-which-server-you-are-on.aspx


Microsoft to replace its live search engine with a new decision based engine “ Bing”
2009-05-28T14:45:00.000-07:00

In order to compete with the giant search engine, Google, Microsoft has unveiled its new search engine, Bing. On May 28, 2009, Microsoft CEO Steve Ballmer has announced the new incarnation of live search as Bing, formerly code-named "Kumo". Bing is fully available from June 3, 09. 
Microsoft has claimed that Bing is not only the search engine but a Decision Engine, designed to empower people to gain insight and knowledge from the Web, moving more quickly to important decisions.
“Today, search engines do a decent job of helping people navigate the Web and find information, but they don’t do a very good job of enabling people to use the information they find,” said Steve Ballmer, Microsoft CEO. “When we set out to build Bing, we grounded ourselves in a deep understanding of how people really want to use the Web. Bing is an important first step forward in our long-term effort to deliver innovations in search that enable people to find information quickly and use the information they’ve found to accomplish tasks and make smart decisions.”

 Microsoft has aimed to gun down Google with Bing. Microsoft has already announced an $80 to $100 Million ad campaign for Bing.  Google has spent about $25 million on all its advertising last year with $11.6 million for recruiting. 
As show in the graph Microsoft search market share has been lowering down with just an 8.2% share of the market for core search compared to 64.2% for Google and 20.4% for Yahoo.  Microsoft has been struggling to for improving its search advertisement revenue. Last year only Microsoft has offered more than $47 billion dollar bid.  They might come up with another Microsoft-yahoo search deal by mid July. 
Good luck to the Microsoft for the new product!  



 




Read Environment Variables with Groovy
2009-01-30T21:20:00.000-08:00
Environment variables can be easily accessed with Groovy with getenv() method as shown below.


def env = System.getenv()
//Print all the environment variables.

 env.each{
      println it
  }  
// You can also access the specific variable, say 'username', as show below 
 String user= env['USERNAME']




Spring’s JDBC Template Set Up for a Grail Application
2008-11-04T13:33:00.000-08:00
To take an advantage of the Spring JDBC Template for any Grail application, the first thing you need to do, is to define the DataSource bean in  resources.groovy , Don’t forget to have the jdbc driver(.jar) corresponding to your backend database server under the lib folder of your grail application.

For example ,resources.groovy is shown as below,

import org.springframework.jdbc.core.JdbcTemplate
import org.apache.commons.dbcp.BasicDataSource




beans = {
myDataSource(BasicDataSource) {
driverClassName = "oracle.jdbc.OracleDriver"
url ="jdbc:oracle:thin:@ . . . . . “
username = "myUser"
password = "myPass"
}
jdbcTemplate(JdbcTemplate)
{
dataSource=myDataSource
}

}



If you have used MS SQL server as your back end database, the driver class name and the url format is as follow.

myDataSource(BasicDataSource) {
driverClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver"
url = "jdbc:microsoft:sqlserver://myDataBaseServerURL"
}

Similarly for MYSQL database server, the driver class name and the url format could be as follows :

dataSource {
driverClassName = "com.mysql.jdbc.Driver"
url = "jdbc:mysql:// myDataBaseServerURL "
username = "myUser"
password = "myPass"
}

Once its set up you can simply make a query as follows
myResult = jdbcTemplate.queryForList (myQuery)

where myQuery is the query that needs to be execute on the remoteDatabase. 


Generate Random Alphanumeric Random String of N Characters with Groovy or Java
2008-07-16T16:08:00.000-07:00
I was googling for a Alpha-numeric Random String generation. I come up with two solution.  One option is to generate Random Alphanumeric Strings with  RandomStringUtils available on   Apache Common . The following section of code is borrowed from java2s.com


import org.apache.commons.lang.RandomStringUtils;
public class RandomStringUtilsTrial {
    public static void main(String[] args) {

      //Random 8 chars string where letters are enabled while numbers are not.
      System.out.print("8 char string using letters but no numbers >>>");
      System.out.println(RandomStringUtils.random(8, true, false));

    }
}


Other Option can be using the Java Uitil class Random as shown below. You don’t have to worry about Apache Common API. The following code section (written for Groovy) generates 100 '32 characters' long Alphanumeric Random string.



private static String validChars ="ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_"
    private int IDlength=32
      int maxIndex = validChars.length()
      for(i in 0..99)
      {   String resultID = ""
        java.util.Random rnd = new java.util.Random(System.currentTimeMillis()*(new java.util.Random().nextInt()))
        for ( i in 0..IDlength ) {
            int rndPos = Math.abs(rnd.nextInt() % maxIndex);
            resultID += validChars.charAt(rndPos)
            number=resultID
        }

   println  resultID
      }



Good luck !


cURL , A command line tool with URL Support !
2008-07-10T14:42:00.000-07:00
One of my co-worker introduced a tool named cURL to me today. It a quite handy command line tool for transferring files with URL Syntax supporting most of the interner protocols like FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP, TELNET, DICT, LDAP, LDAPS and FILE. curl supports SSL certificates, HTTP POST, HTTP PUT, proxy tunneling , HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, Kerberos and many more)

You can download cURL from  http://curl.haxx.se/ . It’s a free and open software tools.

For example curl http://nepalnews.com   : return a html version of your web page in your console window. Its detail tutorial and example can be found at http://curl.haxx.se/docs/httpscripting.html

I have use curl on the following
curl -X POST -H 'Content-type: text/xml' -d @ http://myserver.com/process.gsp < firstfocus.xml

Here I am sending an xml file, firstfocus.xml,  to the server myserver.com . process.gsp do calls the controller which helps in the  business logic at the back end and return the result on my console window. It’s a pretty good tool for testing and development environment.
Thanks to Joshua for letting me know about this tool and its usage.


Free Java Technology Cources!
2008-06-07T09:13:00.000-07:00
Dear Readers,
If you are interested with free online courses on java technologies, http://www.javapassion.com/ could be a good platform for you. Shan Shin teaches the courses. Sang Shin is presently working for Sun Microsystems as a Technology architect, consultant, and evangelist. After a successful completion of any courses you are awarded with the Certificate from java passion. I suggest you all to register for at least one source at a time. Detail about the schedule and courses can be seen at the course home page. Currently he is offering the following courses and all are totally free.

Java Programming (with Passion!) 
Performance, Debugging, Testing, Monitoring, and Management (with Passion!) 
Java EE Programming (with Passion!) 
Ajax Programming (with Passion!) 
Web Services Programming (with Passion!) 
Sun Java System Identity Manager (with Passion!) 
Java FX Programming (with Passion!) 
JRuby on Rails (with Passion!) 
Groovy and GRails (with Passion!) 
Java ME Mobility Programming (with Passion!)



How to read a Java Session Back bean of a JSF page from JSP pages
2008-05-09T14:34:00.000-07:00
In this section I am describing about accessing a JSF back bean from a JSP page.
Lets us take an example of a java back bean ServerInfo for a JSF page

// File Name : ServerInfo.java
package thomson.com;
public class ServerInfo {
String server;
String environment;

public String getServer() {
return server;
}

public void setServer(String server) {
this.server = server;
}
}

Bean is defined on faces-config.xml as following

<navigation-rule>
    <from-view-id>/index.jsp</from-view-id>
    <navigation-case>
        <to-view-id>/process.jsp</to-view-id>
    </navigation-case>
</navigation-rule>
<managed-bean>
    <managed-bean-name>ServerInfoBean</managed-bean-name>
    <managed-bean-class>thomson.com.ServerInfo</managed-bean-class>
    <managed-bean-scope>session</managed-bean-scope>
</managed-bean>

Now lets take an example of JSF page section that sets up the java bean ServerInfoBean from the input text of the form as shown below

//File name: index.jsp
<f:view>
    <h:form id="serverForm">
        <h:outputLabel for="serverName">
            <h:outputText value="Enter the Server Name"/>
        </h:outputLabel>
        <h:inputText id="serverName" value="#{ServerInfoBean.server}" required="true"/>
        <h:message for="serverName"/>
        <br>
        <h:outputLabel for="environment">
            <h:outputText value="Select Environment"/>
        </h:outputLabel>
    </h:form>
</f:view>

Now  in process.jsp the back bean can be accessed as shown below

File name : process.jsp
..
<%
ServerInfo mbean = (ServerInfo) request.getSession().getAttribute("ServerInfoBean");
String server = mbean.getServer();
Out.print(“server name read from the jsf back bean =” +server)

%>



Read custom created XML file from a Groovy/Java class in a J2EE Web Application
2008-05-07T08:43:00.000-07:00
I have a following xml file that I created to store the application name and Application Owner email address

//File name: EmailInfo.xml

< emailInfo>
< application> < name> Saleways < /name> < email> david@david.com< /email> < /application>
< application> < name> Mercantile House< /name> < email> binod@hotmail.com< /email> < /application>
< application> < name> BishowShop< /name> < email> bishow@hotmail.com< /email> < /application>
< /emailInfo>


Now I have a J2EE application that contains a Groovy or a Java class that access the above XML file. The method to access above html file using Groovy is presented below. Note that the xml file resides on the same location where the class exists.


def appName=[]
def appEmail=[]
java.net.URL url= this.getClass().getClassLoader().getResource("EmailInfo.xml");
java.net.URI uri = new URI(url.toString())
File f=new File(uri)
def emailInfo = new XmlSlurper().parse(f) //Specific to groovy
emailInfo.application.name.each {appName += it} //specific to groovy
emailInfo.application.email.each {appEmail += it} // specific to groovy

You simply can modify the above code for java classes. The final result for the above code is that appName contains the list of the Name and appEmail contains the list of the Email address. You can put it into hash map as below.

def emailMap=[:] //define an empty map
for(i in 0 .. appName.size()-1)
emailMap.put(appName[i].toString(),appEmail[i].toString())

Now you simply can access the email address of a person as shown below

println emailMap[“Mercantile House”] //prints binod@hotmail.com



Welcome to my Technical Blog!
2008-05-07T08:31:00.000-07:00
I create this blog so that i can share all the technical stuffs that i came across as a software engineer in J2EE Technologies. I will be glad to receive your feedback for every post and share your idea on the postings.
Welcome you all.
~Bishow