Monday, August 23, 2010

Ajax XMLHttpRequest object in IE

It is quite hateful using XMLHttpRequest across browsers. This is especially severe in the case of IE.

In IE7 and above or other browsers you have to use the standard

obj = new XMLHttpRequest();

In IE6 or below, depending on versions of MSXML installed, you will have to use

obj = new ActiveXObject("Msxml2.XMLHTTP");


obj = new ActiveXObject("Microsoft.XMLHTTP");

Dynamic add option to Select Element

//first clear the content
for (x=mysize-1;x>-1;x--){
myobj.selected =true
if (navigator.appName=="Microsoft Internet Explorer")

Monday, August 16, 2010

Funny behavior of my IE

When I run a php script that contains a javascript script that sorts a table, it worked fine for quite sometime till I added a `part description` field. Now it seems like certain data cannot be shown without the `IE timeout due to script take longer than usual` error. After I accepted the termination of the script, the sorting script no longer runs.

Funny thing is that this issue only appears on my IE. I tried on at leat 3 other IE on other machines and they all shows the table without error. What in the world happened?

Since it is system generated error and that I know it was caused by the data in `part description` field, I decided not to `repair` my IE but concentrate on making the field workable with the javascript.

Well, it is quite simple. I simply remove any character that is not allowed by me using preg_replace command. It cut short all the necessity to replace any character that caused the javascript error. The regexp code is [^a-zA-Z0-9\\-\\.,\\/_ &:;] with a `si` parameter.

Obviously, I did not solve my IE problem. But who cares when it does not complain anymore.

Join table from two source but same server

Previously I discussed about using OPENROWSET. If the database is from the server, there is an easier way of joining the tables. Below is the example.

select top 1 case_id,cso_no from otherdb.dbo.tbl_case

inner join

delivery.dbo.cdrecord on case_id =cso_no

Notice that the database name was included with the owner `dbo` indicated also. However, use this method sparingly. It is not efficient to perform queries over different databases. Moreover, you have to make sure that the user rights are exactly the same unless you are, of course, dbo.

Self Join

Self join is an interesting subject. Why join to the same table? There are situations where you need to get some information from the same table but not from the same row of record. The following may help you to understand.

In a company, I have a employee table listing the employee information. In the employee table I list the employee information plus the manager\'s id ( to save space). Now I want to list the employee information but want to know the manager\'s name instead of id.

Since the manager is also an employee listed in the table, the only way to get the information is via self joins. The following is the sql.

select a.employee_name as employee,b.employee_name as manager from tbl_employee as a inner join tbl_employee as b on a.manager_id = b.employee_id

Here you can see that it is no differen from any other joins except that the same table is used.

Wildcards in SQL Server

Wildcards are useful when searching for items using LIKE clause. MSSQL allows a few wildcard specifier. The most commonly used is the % character. There are some other wildcards available in MSSQL. The underscore character `_` is used for a single character wildcard. the `[]` wildcard indicates that any single character set or range specified in it will be a wild card. If the first character is ^ in the `[]` set or range, it is equivalent to single character `not`matching.

Now we do have literal characters that uses the wildchard character. How do we defile the literal characters in the search? it is quite simple. Simply enclose the character between `[]`. That will include the `[` and `]` also. Another character is the single quote. Well, simply add another single quote to it. Finally there is an ESCAPE clause where you could define a escape character. Normally the escape character is a backslash. just add ESCAPE `\' to the end of the where clause.

In Oracle, it has quite similar syntax except that it does not have `[]`. It does uses ESCAPE clause exactly like MSSQL. I think we should use the ESCAPE clause as standard except under special circumstances then we use the `[]` wildcard.

Recursive search

In MSSQL 2005 there is a recursive search facility. The clause used is [with]. Example is below.

with PartSearch (Part_number,Replacement_part, level) as {

select Part_number,Replacement_part, 0 as level from wfm_parts p

union all

select p.Part_number, p.Replacement_part, ph.level+1 from wfm_parts p

inner join PartSearch ph on ph.part_number = p.Replacement_part }

select Part_number,Replacement_part, level from PartSearch where level > 0

Obviously, you have to use stored_procedures to do it.

PartSearch is a pseudo name created. It can be any name. The above is not tested as I do not have a good database sample to run the example. The syntax should be correct.

Output Clause in MSSQL 2005

In MSSQL when you insert or delete a record, you don`t get a returned recordset. It is always a headache to first determine what is the record that was deleted or inserted when there is a auto increment index present.

Starting fom MSSQL 2005 there is an interesting command which resembles MYSQL mysql_insert_id() function. It has more functionality than MYSQL. It can be used in INSERT, DELETE, UPDATE clause to get the id involved.

For example

insert into tbl_test_insert (case_id) output INSERTED.ID, INSERTED.case_id values(`123456789`)

The query is the same as any INSERT query except there is some extra clause inserted in between. The clause is OUTPUT INSERTED.ID, INSERTED.case_id. This clause tells the query to output values defined by INSERTED. The value is actually the record or the data that was inserted/deleted into the table.

It comes very handy if you have to insert/delete a main table and then insert into a sub table that has the same id as the main record. The id in the main record is auto increment identity column so that it could be an index. You will not know what is the value till the record is inserted. By inserting/deleting a record in the main table you will need to get the id value so that you could perform insert/delete on the sub table.

I used to insert a UUID when inserting the main table then query the main table to get the id by finding the UUID. Now I can just use the insert statement then simply do a odbc_fetch_row() to get the id with just one query. Neat.

Just to let you know this- use DELETED.xx if you are doing delete query. For UPDATE you use INSERTED for those fields you updated and use DELETED for those fields you replaced.

SQL FIRST aggregate function

As you all knows very well that the FIRST aggregate function does not exists in sql server.

There are actually tweaks to make it work. Heres how.

select case_id, RowNumber from (select case_id, ROW_NUMBER() over (partition by case_id order by subcase_id) as RowNumber from tbl_case) as tblcase where RowNumber=1

The above sql actually returns the first subcase information since i use ORDER BY SUBCASE_ID in PARTITION BY.

This is one of those odd cases where you do a select from a derived table.

This query is particularly useful when you happen to have duplicate subcase_id. It will return unique rows.

Obviously, if you just do like the example above, it is kind of going the long way. A simple DISTINCT and a WHERE clause can do much better job than that. But if you have duplicate subcase_id then your DISTINCT,WHERE method may not work as per expected. Moreover, you could make use of the derived table to do MAX and MIN aggregate function on other fields across all subcases.

PARTITION BY works similar to BRIOQUERY SUM BY method. It does not require a GROUP BY which is somtimes very obstructing to the AGGREGATE function. For example you want to do an aggregate but also want to include the case_title field. That is impossible for normal query with GROUP BY since case_title is almost unique to each case. After getting the aggregated results then you use the above SELECT FROM DERIVED TABLE method to do further filtering to get only the results you want.


Just discovered a new way of doing max and min on different date like brioquery (sum by) syntax.

select distinct case_id, max(close_date) over (partition by case_id) as maxdate, min(open_date) over (partition by case_id) as mindate from wfm_subcase

Previously I had to use a derived table to get each of the two dates. Now it can be done in a single clean sql statement.

There is a catch if you uses sql server 2005. You will get error message if you use query designer to edit the query. Since the query designer mode is always the default mode when you create a view, just create a normal query without the over clause then save the view. Next, open the view in edit mode (not design mode) and change the query to a over clause query.

Find duplicate cases in SQL sever

Many a times we want to check if there are duplicates in a table.

select case_id, count(case_id) as casecnt from wfm_case group by case_id having (count(case_id) >1)

Derived Table

`Derived table` in mssql is equivalent to `inline view` in Oracle or `subselect` in Postgres. Its purpose is to let us use a `select` query as a source with an alias. Example

Select * from (select case_id,open_date from tbl_data) AS S

It looked stupid to have such a query which does not help in anything. However, its very much more useful than just that.

For example. You wish to get the last close date from a subcase while retrieving a main case data. This could only be achieved if you create a view to get the last close date or create a temporary table to get the last close date. Once you can get the data from the view or temp table, you join it to main case to get the final result.

Creating temp table means you have to do a number of steps to achieve it and have to remember to remove the temp table. Creating a view is easier as it will not need maintenance after that. However, you have to do one query in two places and you cannot create the query on the run. Is it at all possible to use just one query to get the desired results?

`Derived table` is the answer. The following is a working example from wfm (our server) database.

SELECT M.Case_id, S.close_date
(SELECT case_acc_id, MAX(close_date) AS close_date
GROUP BY case_acc_id) AS S ON M.Case_id = S.case_acc_id

Join tables from two source

Generally it is not possible to join databases using SQL. However, MSSQL provides a functionality called OPENROWSET that can actually get data from another source.

Some experts even do a inner join with it. However, when I tried using inner join, our sql server keeps giving me timeout error. Creating a view using the functionality is a success though.

I guess we can then join it to other tables or views to get the final result.

The following is how to get it working.

First we must configure the sql server to allow "AD Hoc Distributed Queries". This need to be done only once per server.

Go to sql management studio and navigate to the database in where you have your original data. Go to programmability, stored procedures. Create a new stored procedure but delete everything from the template. Copy and past the following in it.

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

Click on Execute from the window\'s menu. You should see some message appearing. Unless it is an error message, you have just finished doing the configuration. You don\'t have to save the stored procedure as you do not need to run it more than once.

Next go to views in the database and create a new view. Type your "select" statement as normal but change the table name after the "From" to something like below

OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=sgsvcit01;UID=sa;PWD=MyPass', wfm.dbo.wfm_case) AS wfm

Save the query and it will be done.wfm.dbo.wfm_case is the sample database. It means we are using wfm database and gets the wfm_case table.

You have to provide an alias otherwise sql server will make noise then provide one for you instead.For obvious reasons the UID and PWD is a dummy value.

You should use the actual UID and PWD.If you are geting data from msAccess table then the openrowset setting will be different. See below example.

OPENROWSET('Microsoft.Jet.OLEDB.4.0', c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o

All these information were gathered from internet.

XML parsing issue

I tried to run a web page that gets xml data as source. It failed with 0 row. I checked the xml file and it is well formed. I tried to load another known-to-be-working xml file and it parsed ok. Seems like it just cannot work.

I then tried a very crude way. Copy the contents from another xml file and paste it into the xml file I used. The result is that there is again a 0 row. That xml is a known working file.

Finally I get it. The file name of the xml is the same as the web directory name. You will never guess it that IE rejects such xml from being loaded.

Failed to open or save XLS from outlook

If you happened to get this error "Can't open this item. Can't create file: Right-click the folder you want to create the file in, and then click Properties on the shortcut menu to check your permissions to that folder" while trying to open or save XLS, that means you have some issue with how Outlook stores the temporary file.

Use regedit to search for "OutlookSecureTempFolder". Go to that file directory and delete all the files inside. After that, the file can be opened.

The reason for doing is that outlook saves the attachment to the temporary directory first before opening it. Each time you open the attachment the filename stored will be "filename" +"(X)". X will be incremented each time the same file name was saved. The problem begins if you have previously opened the same file name 99 times. The auto filename cannot go beyond the number 99. By deleting all files in the TempFolder, you will reset the number to 1 again. This will allow you to open the attachment without problem.

MSSQL delete and truncate table

It is interesting to note that delete all from table is different to truncate the table. You will notice that the table size (view table properties) does not change and it takes longer to do the process. Truncate table is very much quicker and always reduce the size of the table to 0MB.


It is quite interesting to look at the SQLITE database. There is no server/client architechure. It strikes me that it was something like MSAccess that we use on some of our web services. However, it even gets better than that, it does not require ODBC setting.

For those data that we do not require security, for example bft results, it will be good idea to store the data in such database. The syntext of accessing is very simple. The example is below.

$db = new SQLiteDatabase("db.sqlite");

$result = $db->query("SELECT * FROM foo");

while ($result->valid()) {

$row = $result->current();





Compared with those that use ODBC, it is a breeze. There is no authentication whatsoever.

The next problem is of course obvious. How do I get the data? There is no import facility built in. Will think of some way to do it.

Remove duplicate rows

Many a times when we update database, there are chances that we may insert duplicates. It is a headache to maintain such database if the source is not within our control.

Below is a way to solve the headache.

with Uniqrow as (

select *,ROW_NUMBER() over (partition by Case_ID order by case_id,x_Cust_Track_No DESC) as rownumb FROM [wfm_ods].[dbo].[TABLE_CASE]


delete from Uniqrow where rownumb > 1

The above is based on table_case in WFMODS database. It somehow retrieved duplicate records and there is no way to find out why. Its function is to simply based on the grouping of case_id and rank it. Since we can't just do a delete directly, we use a CTE "with clause" to encase the ranking statement.

The result is very fast. removing 2700 case in less than 1 sec from a total of 11000 cases.

PIVOT in SQL Server

Prior to SQL 2005 I have to explicitly define a query to do a pivot like below

select work_group, sum(case when days <= 10 then 1 else 0 end) as LT3,sum(case when days > 10 and days <15> 15 then 1 else 0 end) as [GT 15] from view_1 group by work_group

In SQL 2005 there is another way to do it.

select work_group,LT3,[3 to 15],[GT 15] from (

SELECT case_id,work_group, CASE WHEN days <= 10 THEN 'LT3' WHEN days >10 and days < 15 THEN '3 to 15' when days >15 then 'GT 15' END AS days

FROM view_1 ) as rawdata

pivot (

count(case_id) for days in (LT3,[3 to 15],[GT 15])) as pvt

The latter looked more complicated but both worked exactly the same. However, the latter is sorted on work_group automatically.

The example is more interesting when "days" is already parsed according to its grouping. You just need to define "select case_id, work_group, days from view_1". Coupled with the fact that "select work_group,LT3,[3 to 15],[GT 15]" can be shortened to just "select *". You get the following

select * from ( SELECT case_id,work_group, days FROM view_1 ) as rawdata pivot ( count(case_id) for days in (LT3,[3 to 15],[GT 15])) as pvt

Don't you think that the above code is much simpler to write than prior SQL 2005 codes? You just can't make it simpler with the prior SQL2005 series.

Echo with variables in PHP

Standard PHP allows you to embed variables like $myvar right into the echo. But when you have array variables like $_SERVER['PHP_AUTH_USER'], you will have error "Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in ...".

Actually there is a very simple way to embed such a variable. What you need to do is to simply enclose the variable within the echo with curley braces like example below.

echo "This is the Auth user {$_SERVER['PHP_AUTH_USER']} that was logged in";

compare with the string concate format (dot format) below

echo "This is the Auth user ". $_SERVER['PHP_AUTH_USER'] . " that was logged in";

Neat? It save the trouble from the dot format especially if you have a lot of these variables in the line.

Now if you really have a lot variable in multiple lines to echo, you can use the following instead.

echo <<<>

This is the Auth user {$_SERVER['PHP_AUTH_USER']} that was logged in


Notice that there is absolutely no quotation marks any where.

Beware that white spaces is not removed if you use this method.

Funny behavior of IE6

If you happened to user innerHTML with IE6, you will be scratching with your head as to why the innerHTML can never match your string despite that the string you defined in javascript and html are exactly the same.

It turned out that IE6 (for no apparent reason) added one extra space behind the string when you use javascript innerHTML property to get the text from the html tag.

Triggers in SQL Server

Regardless of whether you use "For Insert" or "After Insert", the trigger runs synchronously. Thus when your trigger gets bogged down, you will not get the data inserted.

M$ assume your triggers work in an instant action way. It does not expect delays. Thus, It waits faitfully till your trigger terminates. This has an impact as the record inserted is locked till the trigger ends. If you try to list the record when it hangs, it will take forever without coming back to you.

Insert record where not exist

If we want to insert record but do not want to make duplicates there are a number of ways to do it. The following scenerio is that user want to insert a record from a form and do not want to insert duplicates base on case id.

Insert into tbl_case (caseid, createdatetime) select '123456', '4/3/2010 12:00:00' from tbl_case where not exists (select caseid from tbl_case where caseid = '123456')

The trick part is the first select. It is actually not getting anything from the database. It won't work if we do not refer to the table this way.

The second select is just standard syntax using the "where not exists" clause.

PHP page as sort of IFrame

Sometimes when your page needs a menu across the top of the page and a number of web pages that perform different function, a standard way is to set the main page as frames or use iframes.

I have a different way of doing the same thing using PHP. The main page itself contains the menu. The menu should be at the top of the page. When you click on it then you submit a dummy form with a variable defining the action. The php code then uses a "Switch" function to determine which web page to "include". When the page loads your web page will appear inside the main page as if it is iframe yet remains part of the main page.

Doing this has advantages.

1. Session information always stays with the main page.

2. Javascript does not need the parent child relationship. It can call functions both ways.

3. You need not define a target frame when submitting the form.

4. The sub pages need not have the full web page header. Just what ever that is need to show the content.

5. You can have the footer residing in the main page. That is somthing frames cannot do.

6. You are not bothered by the iframe size issue and there will not be any multiple scroll bars to uglify your page.


1. The main page keeps reloading itself everytime you submits a form.

2. You must always have an input that defines the menu action in your form so that the same page will load when submitted. A way out is to define a session variable that defines the menu action.

Database in PHP

I am always troubled by the code needed to connect to database and send a query. Previously I created a class to return the whole result set of odbc_exec so that it could accommodate the "update" and "select" query. But it still bothers me a lot to do a loop (odbc_fetch_row) for "select" query. I have discovered that using a callback function, I could actually avoid defining the loop itself. Although this will limit the code to just "select" query, it does make the whole database access code become just two line (actually three if you put the query statement as a variable).

The following is the class.

class myclass {
Use extract($db_j); in $callback to export the keys and values of the array as variable
public function run_sql_cb($sql,$db_con,$db_usr,$db_pwd,$callback){
if ($db_obj)
$conn = odbc_connect($db_con,$db_usr,$db_pwd);
if (!$conn){
exit("Connection failed: " . $conn);
if (!$db_obj){
exit("Sql failed");
while ($db_j = odbc_fetch_array($db_obj)) {

The sample callback routine is as below

function caller($myobj){
echo $Mycount." ".$CNT."

and the calling code

$mc= new myclass;
$sql="select Mycount,CNT from sample.csv";

The class is a simple routine to connect to a database using the ODBC connect code. I added the callback code by adding a callback on the dataset loop. The callback is done by just adding the callback function name to the class function. The class function will in turn just add a () to the variable passed and it becomes a callback function.

In the dataset loop, I use odbc_fetch_array so that I could pull a recordset as an associative array. I passed this array to the callback function.

In the callback function, I used a extract() function. Extract() function will take the associative array and make the key as variable name and the key value as the variable value. Since it is in the function, the scope of the variable is limited to the function thus does not affect the main routine variables.

The rest of the callback function is then up to you to add whatever code to manipulate the recordset.

Wednesday, August 11, 2010

Javascript Pivot

Creating pivots in HTML like that of Excel is very much harder. It take a bit of understanding of Javascript to do it. The following is a lazy way of showing a pivot

Save the following as pivottable. js file.

pivotTable =function (){
//first include this class
// create a new class var xx=new pivotTable()
//pass a tbody id to tblID as xx.tbID="idtext"
//add rows by calling xx.insertRaw tblindex is row value, thlPivot is pivot col and tblPivotValue is the numeric value of the pivot col
//finally call showTable as xx.showTable()
this.pivotArray=new Array();
this.indexArray=new Array();
this.mainArray=new Array();
this.totalArray=new Array();
pivotTable.prototype = {
insertRaw : function(tblIndex,tblPivot,tblPivotValue){
if (typeof this.indexArray[tblIndex] =="undefined")
if (typeof this.pivotArray[tblPivot] =="undefined")
if (typeof this.mainArray[tblIndex] == "undefined")
this.mainArray[tblIndex]=new Array();
if (typeof this.mainArray[tblIndex][tblPivot] =="undefined")
if (typeof this.totalArray[tblPivot] =="undefined")
showTable : function(){
for(myindex in this.pivotArray){
for (myx in this.indexArray){
for (myy in this.pivotArray){
if (typeof this.mainArray[myx][myy] == "undefined")
for(myp in this.pivotArray){

Include the pivottable.js and the following in your web page.

mypivot=new pivotTable();

Create a table with a tbody. The tbody id is "mydiv". You will see a 4x4 table displayed.

Tuesday, August 10, 2010

Javascript Class

I have been writing Javascript codes for a long time. Although there is this "class" thing in Javascript, I have never come close to need to use it. However, recent coding requirements forced me to look at it closer.

There is a web page that needs to mark a check box list upon selecting a product from a drop down list. Each product has a specific list of items that need to be checked. Normally I would retrieve all the products and their items and make them into an associative array. When user selects a product the items tagged with the product will then populate the checkbox accordingly.

However, the products are duplicated with different plans. This means that I have to have a multi-dimension arrays. Now, if the data is huge, then my web page loading will be slow. So I though why not use "Ajax" technology to retrieve only the particular checkbox list. As it goes, everything goes smoothly with a slight delay as the data need to be retrieved across the network.

When the next request to amend the web page with the ability to check for duplicate product entry, I was thinking of not submitting the form then check for duplicates as I need to fill the form with user's submitted content should duplicates occur. Thus, the best choice is to just check for duplicates using "Ajax". I am faced with having to double coding of the same "Ajax" script. The script is tailored to a single process and is not adaptive to another process.

Since the basic codes are generic and only certain parameters like URL and Callback functions need to be specific, I though why not create an "Ajax" class to create instances of the Ajax codes so that I could use a single code with different callbacks to do the job. After all, only the URL and callbacks are process specific.

Thus, I began the learning of the "class" in Javascript. As it turn out, Javascript don't actually have "class". It only have "objects". However, the object behaves quite similar to "class". The "class" constructor is actually an "object" constructor. Basically the "class" is a "function" object.

function mySpecie(){


You create and instance of the function by

myInstane = new mySpecie();

Well, normally you will never need to create instances of the function since I could call the function and just pass parameters to it. However, there is always a need to set certain parameters that does not change. For example, I need to store particulars of a group of species. Instead of having to define that they are humans one by one, I could have set globally that the species is "human" then I can concentrate on the particulars that are specific to the species. Now simple functions can't store parameters. It has to be passed as parameters every time.

There is a way to set certain paramaters as fixed values in the function. This is done by the keyword "this".

function mySpecie(){

In this way, you don't have to set the name and sex every time you call the function. Nice huh! What if I need to add/change ad hoc information during the running of the program? A "Class" has this ability to do it right? Javascript has this thing called "prototype" in functions. You could add or change any values to the function at anytime and it will remember the values when you call the function. You can set the values by

mySpecie.prototype.specieType = "notHuman"

So whenever you call the instance of mySpecie, you get to have the setting of specieType as "notHuman" instead.

In other words, you could set and change the parameters as and when you like.

As it goes, "class" can have functions. Well, Javascript can also have function in functions.

function mySpecie(){"Jon"
this.hello = function(){
echo (
myInstanc=new mySpecie();

When you set "myInstance.hello()", you get the reply as "Jon".

I also learn that you can set multiple parameter values without having to keep defining "prototype".

mySpecie.prototype = {
name : "Jon",
hello : function (){
echo (

One other thing I learn is that I could create a function and pass this function as part of the class.

function hello{
echo "Hi";
myInstace.prototype.hello = hello

Actually I could easily pass the function as a function parameter into the instance on the run. All I need to do is to set the instance's value "var hello=hello".


With the above methods, I could pass the function into the instance and use it as part of the class.

It is certainly easy now to use multiple instances of "Ajax". I could create the class and then set the URL and callback accordingly and still use one set of code. All I need to do is pass the parameters to the instances of "Ajax" class.