Tuesday, November 9, 2010

How To Use Order By in Subquery in Oracle

In T-SQL, Order By is supported in subquery. However, in oracle, things are not very straigt foward.

For example, we want to write a sql statement that will delete the most recent entry in Table1.

In T-SQL, we will write:

delete from table1
where key_column = (select top 1 key_column from table1 order by time_column)

This statement will not work in oracle's PL-SQL. You will get an error that looks like following:

ORA-00907: missing right parenthesis

To run the same query in oracle using order by in subquery, you need to put the order by into a view select:

select columnA from (select * from table1 order by columnB) where rownum =1

Of course, without the rownum = 1 limitation, the query above become nonsense. But it is necessary to understand the limitation in oracle.

Finally, our revised query in oracle is like this:

delete from table1
where key_column =
(select key_column from (select * from table1 order by time_column) where rownum = 1)


Tuesday, October 12, 2010

ExtJS 3.0 nonsecure content warning for IE

In Ext-JS 3.0 release, there is a piece of code that will set the default blank image to be http://extjs.com/s.gif if browser is IE6,7 or Adobe Air enabled. The s.gif file is contained in the release package (images/default/s.gif), so I don't know why sencha team still hard coding the URL.


BLANK_IMAGE_URL : Ext.isIE6 || Ext.isIE7 || Ext.isAir ? 'http:/' + '/www.extjs.com/s.gif' : 'data:image/gif;base64,R0lGODlhAQABAID/AMDAwAAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==',


This will cause some problems. For example, if user using IE and is disconnected or working in LAN who has no access to extjs.com (which will be sencha.com now), UI component such as Combo Box will be rendered incorrectly.
Also, if the website is secured website which uses https connection, visitors will get 'This Page Contains Both Secure and Non-Secure Items' Error Message.
To solve this, code must be changed on the server side because it is caused by URL hard-coded in ext-js library.
What you need to do is at the beginning of your javascript code, reassign the BLANK_IMAGE_URL variable.
Add the following lines:


if(Ext.isIE6 || Ext.isIE7 || Ext.isAir){ Ext.BLANK_IMAGE_URL = "YOUR-CONTEXT-ROOT/images/default/s.gif"; }


That's it.

Sunday, August 8, 2010

rake aborted! uninitialized constant MysqlCompat::MysqlRes

After installing mysql server and mysql gem on snow leopard, I got this annoying error when doing "rake db:migrate":
rake aborted!
uninitialized constant MysqlCompat::MysqlRes


After reading this article, I fixed the issue. This problem is caused by mysql gem installation.
To correctly install the mysql gem:
1. Determine you operation system: Leopard, Snow Leopard, etc
2. Download the correct mysql server according to your OS (10.5_32, 10.5_64, 10.6_32 or 10.6_64)
3. Use the correct gem install command:
Leopard on an Intel machine:
sudo env ARCHFLAGS="-arch i386" gem install mysql
-- --with-mysql-config=/usr/local/mysql/bin/mysql_config
Snow Leopard (only on Intel):
sudo env ARCHFLAGS="-arch x86_64" gem install mysql
-- --with-mysql-config=/usr/local/mysql/bin/mysql_config
Database version must match the gem version. E.g. on snow leopard, gem must use "-arch x86_64" and mysql server must be OSX 10.6 64 bit.
If their version does not match, you will receive another annoying error when you do "rake db:migrate":
dyld: lazy symbol binding failed: Symbol not found: _mysql_init
Referenced from: /Library/Ruby/Gems/1.8/gems/mysql-2.8.1/lib/mysql_api.bundle
Expected in: flat namespace

dyld: Symbol not found: _mysql_init
Referenced from: /Library/Ruby/Gems/1.8/gems/mysql-2.8.1/lib/mysql_api.bundle
Expected in: flat namespace

Saturday, July 24, 2010

jQuery Ajax Programming Trap

The following code looks logical if run in sequencial order but will fail in Ajax because its asynchronous nature:
$(document).ready(function(){
  $("input.myButton").click(function(event){
               var ret = handleAjaxCall($('#dataForm'));
               if(ret){              
                   alert('ajax call is successful');
               }else{
                   alert('ajax call failed');
               }
             $("#renewDetail").hide();                                     
               return false;
});}

function handleAjaxCall(form){
         var result = false; // default
         $.post("/myAction", form.serialize(), function(data){
             result = true; // ajax is successful
         });    
 
        return result;
}


You may think this code will work but it doesn't. The code will always prompt 'ajax call failed'.

The reason is that $.post() is asynchronous, so handleAjaxCall() will return before $.post() is completed.

Thus, the result value from handleAjaxCall is always false even though the $.post() is successful.

In order for Ajax to work, we need to handle all the successful logic inside $.post() callback function.
$(document).ready(function(){       
  $("input.myButton").click(function(event){
            $.post("/myAction", form.serialize(), function(data){
               alert('ajax is successful');
            });
});}

Thursday, May 6, 2010

Find Computer Name From IP

To find out the computer name associated with a specifi IP address, use the follow NSLOOKUP or NBTSTAT command.

e.g. NSLOOKUP

e.g. NBTSTAT -a ipaddress

Friday, February 19, 2010

Get Business Day in JavaScript

Often required in web application is to obtain the next business day or a business day in future.
Normally, a business day excludes national holiday and business defined non-business day of week (usually Saturday and Sunday).

National Holidays are defined in a comma delimited string such as
D01012010,D02152010,D12252010
where D12252010 refer to Dec 25th, 2010, the Christmas Day.

Non-business day of week are defined also in a comma delimited string such as
D1,D7
where D1 is to Sunday, D7 is Saturday.

Holiday and Non-business days can be defined either in properties or database, here just simply the problem by handwriting them in the strings.

Also, assuming user pass in the date as string in mm/dd/yyyy format.

The following JavaScript code can calculate a business day that is N business days from a specific date:

parameters:

fromWhen - the start date
bizDays - N business days from the start date (+ value for future date, - value for previous date)


function getBusinessDate(fromWhen, bizDays) {  
     var maxWindow = 365 ; // Business day cannot exceed this window
     var fdate;
     var forward = true;
     // list of days that are holidays
     var holidays = "D01012010,D02152010,D12252010";
     // list of days of week that's non-business day
     var nonBizDays = "D1,D7";

     if(isValiddate(fromWhen)){
         fdate = new Date(parseInt(fromWhen.split('/')[2],10), parseInt(fromWhen.split('/')[0],10) - 1, parseInt(fromWhen.split('/')[1],10), 0,0,0,0);
     }else{
         fdate = new Date();
     }
     
     if(bizDays < bizdays =" -bizDays;" forward =" false;" daysskipped =" 0;" i="0;i maxWindow){
                 alert("Cannot find any business day within " + maxWindow + " days !");
                 return false;
             }
             if(holidays.indexOf(getDateStr(fdate))<0 dayfound =" true;" day =" date.getDate();" day = "0" month =" date.getMonth()+1;" month = "0" year =" date.getFullYear();" datestr = "D" indate =" inDateObj;" slashcnt =" 0;" validdate =" true;" indate ="="" i =" 0;" onechar =" inDate.charAt(i);" onechar ="="" slashcnt ="="" slash1index =" i" slashcnt ="="" slash2index =" i"> '9')
       {
         validdate = false;
       }
 }
 if (!validdate) {
   return false;
 }

 if (slashcnt != 2) {
   return false;
 }

 monthStr = inDate.substring(0,slash1Index);
 if (monthStr.length == 0) monthStr = "0";
 dayStr = inDate.substring(slash1Index+1,slash2Index);
 if (dayStr.length == 0) dayStr = "0";
 yearStr = inDate.substring(slash2Index+1,inDate.length);
 month = parseInt(monthStr, 10);
 day = parseInt(dayStr, 10);
 year = parseInt(yearStr, 10);
 if ((yearStr.length != 2) && (yearStr.length != 4)) {
   return false;
 }

 if (month > 12 || month < daysinmonth =" getDaysInMonth(month,"> daysInMonth)) {
   return false;
 }
 return true;
}


function getDaysInMonth(month,year)  {
   var days = 0;
   if (month==1 || month==3 || month==5 || month==7 || month==8 ||
       month==10 || month==12)  days=31;
   else if (month==4 || month==6 || month==9 || month==11) days=30;
   else if (month==2)  {
       if (isLeapYear(year)) {
           days=29;
       } else {
           days=28;
       }
   }
   return (days);
}


function isLeapYear (Year) {
   if (((Year % 4)==0) && ((Year % 100)!=0) || ((Year % 400)==0)) {
       return (true);
   } else {
       return (false);
   }
}


Examples:

To get the next business day: getBusinessDate(null,1);

To get the date that's 10 business day from today: getBusinessDate(null,10);

To get the date that's 10 business day from March 1st, 2010: getBusinessDate('03/01/2010',10);

Wednesday, February 3, 2010

Get Only Date from DateTime in T-SQL

I need to sort the records based entrydate, transferType, then amount.
So I wrote something like
SELECT entrydate
FROM mytable
ORDER BY groupvalue, transfertype, amout
The result is not correct because the entrydate column is date time column so when doing the sort, records on the same date but different time are considered different group, so the later transfertype and amount sort is not grouped correctly.
I need to sort the entrydate only by the date part. I google around and find a solution at Anatoly Lubarsky's blog on T-SQL: Get only Date from DateTime.

To get the date part, we can use:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
So my previous t-sql statement becomes:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, entrydate )) 'groupvalue'
FROM mytable
ORDER BY groupvalue, transfertype, amout
Now everything is working correctly.