Archive for the 'Technical' Category

DIV vs. TABLES

Published
by
rs
on December 12, 2006
in Technical
. 1 Comment

Tables were not created for web layout or positioning. Tables were created to provide structure to data. They were created to contain tabular data. Though the early html standard uses table for layout purposes, yet it is not the best tool for it. The main disadvantage of using tables to layout a page is its complex code(specially if nested tables are present) and limited attributes.

Div on the other hand are very much flexible. You can position the div anywhere on your page and you can position anything and anywhere on it. Unlike tables which can only be positioned on LEFT, CENTER, or RIGHT side of your page. In short, Div has no limitations when it comes to positioning, resizing, and designing.

I found a site that teaches CSS layouting (page layout using div) that is very easy to understand and easy to implement. Very helpful for both the novice and the css veteran. Check it at glish.com.

Passing URL variables with non-URL-friendly characters

Published
by
rs
on October 25, 2006
in Technical
. 0 Comments

When passing information via URL variables, we have to be careful that those variables do not contain non-URL-friendly characters. This includes spaces and non-alpha-numeric characters…
to be able to pass this characters, you have to first convert it to URL-safe hexadecimal escape characters…

And to do that, just use the URL encode function..

In classic Asp and Asp.Net:
Server.UrlEncode()

In classic PHP:
urlencode()

In classic Coldfusion:
URLEncodedFormat()

Sql Date Format - Month and Year Only

Published
by
rs
on August 30, 2006
in SQL and Technical
. 3 Comments

i remember there is one time where i have to edit an old module where in the date field in sql is not a date data type but instead, it is a char type… they did that because they wanted to store only the month and year of the date in sql… at first it wasn’t a big deal after all until i have to return the data with a given date range… now that’s a problem… my problem…

since the date is in char type, it won’t be easy to select the data with a given range..
and so i came up with this solution… this is how i make a date range with char data type..

say we have to select all datas with these given date range: from 04/2003 to 03/2006

my query would be:

select field1, field2, dateField
from   table1
where  (cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003) or
       (cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006) or
       (cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4)
       as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006

now lets disect the code…
for the first where condition:
(cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003)
this will return all the data with the date range from 04/2003 to 12/2003…

for the second where condition:
(cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006)
this will return all the data with the date range from 01/2006 to 03/2006..

for the third and last where condition:
(cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4) as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006
this will return all the data with the date from 01/2004 to 12/2004 and from 01/2005 to 12/2005

if you don’t know how to use the substring function in sql… just google it… there are lots of threads and forums out there…

a smarter way to do this is convert the varchar type date into a real date format… in that way, you can easily make a date range because it is now in date type…
say you have “08/2006″ as august 2006, all you have to do is insert a “01/” on the left side to make it “01/08/2006″.. after that you can now convert it to date format… since you only need the month and year, the value of the day does not matter…

to insert the “01/” in the left side of your varchar date and convert it to date format:

convert(datetime, ‘01/’ + dateField,103)
*101 means the date format will be dd/mm/yy

now every time you need to the month and year.. use the code month() to return only the value of month and year() for the value of year…

sample:
say we have “30/03/1984″ in dateField

select month(dateField)
from sampleTbl

this will return “03″… and if you use the year() function.. it will return “1984″

another way is to use a contiguous string format… just convert your varchar date into this format ‘200608′ (means agust 2006).. then use <,>,= operators to create a range… just use substring() function to arrange your data into this format…

SQL isZero() Solution For Divide By Zero Error

Published
by
rs
on August 4, 2006
in SQL and Technical
. 22 Comments

I had a query once that contains a divide operation….

SELECT   x, y, ROUND(x/y) as quotient
FROM     dataTbl

It always returns a correct value…
but divide by zero error occurs whenever
there is a zero(0) not NULL value in y column…
now, what i want is instead of having
an error, i want it to return a zero(0) value also…
so I wonder if there is a function isZero(), I can simply
change the y value from 0 to NULL to avoid such error…
I was thinking of a code like this: isZero(y, NULL)…
unfortunately, there is no such functions…
I googled for the correct syntax and I came up with
the: NULLIF(fieldName, condition) function…

SELECT   x, y, ROUND(x/NULLIF(y, 0)) as quotient
FROM     dataTbl

by using the query above, divide by zero error is impossible…
what happen is SQL returns a NULL in y column if its value is zero(0)…
though the error was avoided… the result still does not satisfy me…
it only returns a NULL value in quotient field instead of zero(0)…
well, the solution is simple:

SELECT   x, y, ISNULL(ROUND(x/NULLIF(y, 0)), 0) as quotient
FROM     dataTbl

by using the ISNULL(fieldName, replacementValue) function,
It sort of like reversed the effect of NULLIF function…
what happen in this function is, if the value IS NULL
then replace it with what ever the replacementValue is…
in my query, it replaces the NULL value by zero(0)…

now that’s satisfying…. divide by zero error was avoided
and quotient field is never NULL….

SQL Server Joins And Group By (Performance Tuning)

Published
by
rs
on August 2, 2006
in SQL and Technical
. 1 Comment

I was given a certain module where in it performs a simple computation (the SUM function). What I’m tryin’ to do is add all the data from a specific fields and group by a certain field from another table. but the problem is the table containing the data to be calculated is three tables apart from the table containing the field needed for the group by function and four fields are needed for groupings.

to make it clear, here’s the query sample:


SELECT  a.field1, a.field2, a.field3, SUM(d.field2+d.field3) as totalFld
FROM    table1 a
        LEFT OUTER JOIN table2 b
        ON a.field4= b.field1
        LEFT OUTER JOIN table3 c
        ON b.field2= c.field1
        LEFT OUTER JOIN table4 d
        ON c.field2= d.field1
GROUP BY a.field1, a.field2, a.field3

By running the query above, it took me more than five minutes before it returns the data.

And so i started investigating and found out that the reason for slow performance is because I am joining the tables on the field that is not an index. So what I did is I added an indexes to the columns in each table used to join the tables. I’ve learned that for maximum performance, the indexes on the columns to be joined should have the same data type and it should be a clustered key for optimum performance.

Another thing is the group by function. I’ve found out that this also affects the performance. As the number of groupings increases the performance decreases.

to create an index using t-sql:

CREATE CLUSTERED INDEX indexName on tableName(fieldName1, fieldName2,….)

to delete an index using t-sql:

DROP INDEX tableName.indexName

String Concatenation (Memory Buffer)

Published
by
rs
on July 20, 2006
in Technical
. 0 Comments

While working with a certain web application, I just found my self trapped on a string concatination problem. What I’m tryin’ to do is concatenate 2 strings and assigning the output to the first string. My code goes something like this:

PSEUDO CODE

var myString=”";

for(i=1;myQuery.recordCount;i++){
     myString = myString & myQuery.record[i];
}

note:
Let say myQuery is a SQL query which returns
all the fields and its value from a certain table

Looks simple huh? but the problem is, as the record count increases the performance decreases.
say myQuery returns 20 columns and 60,000 rows, using the code above will makes you wait forever. This kind of problem had never been totally solved until the .Net stringbuilder exist.
Since I am not using .Net, I have to find my own way to fix this problem.

What happen behind the code is that, the code creates a new myString object containing the result of the concatenation and discards the old myString. The value of myString is now then temporarily saved in a single memory buffer. So if the loop is repeated several times, the value of myString goes larger and for every 1 loop, the code retrieves that large data from a memory and then saves it again and again and again. And as the data inside the myString goes larger, the performance slows down.

I came with this idea wherein instead of saving the data in a single buffer, why not divide the data and saves it into different memory buffer, in that case, the size of the data in a single buffer will never be too large for it to slow down the performance of the system.

here’s the new code:


PSEUDO CODE

var myString=”";
var x=0;
var j=1;
var myString[1]=”",….myString[20];

for(i=1;i=20;i++){
     x=myQuery.recordCount*i/20;
     for(j;myQuery.recordCount;j++){
          myString[i]=myString[i] & myQuery.record[j];
          if(j==x){
               break;
          }
     }
}

myString = myString[1] & myStrinf[2]……..myString[20]

This code will divide the data into 20 parts and assign each data to 20 different variables.
Once the looping is done, all the data will be set into a single variable called myString.

Using these codes, it changed my waiting time from 15mins to less than a minute.

SQL Union All

Published
by
rs
on July 20, 2006
in SQL and Technical
. 0 Comments

I was working in this certain module last week wherein I will create a web app page that will generate monthly reports. Everything was fine and I manage to do it with an ease until the boss checks my work, On the report generated, there was a certain field column wherein NULL values are present, though some has a value, still it is impossible because that field is required in the form, and so the 1st problem occurs

In that module, what I did is I created a views in sql wherein it contains the computations of the data selected from a certain table, and I also added a field named “officerFld” which will contain an officer’s name selected from a certain field from another table.

I started to investigate the problem and found out that the officer’s name is stored in two different tables wherein I only selected from one of those tables. So I started searching for a way on how to select those officer’s name from the second table and insert it on the “officerFld” on the views that i’ve created, in that case, all the NULL values will be replaced by the officer’s name from the second table and all the officer’s name from the first table will still remain in the “officerFld” found on the views that i created.

Thanks to kiko(my older brother), who gave me the idea to use the UNION ALL command in sql. Well, I’m just starting to learn sql so don’t expect me to know all the commands. But now I’m starting to acquire knowledge so that’s what i did, i used the UNION ALL command in sql to combine the results of my two queries together and I used LEFT OUTER JOIN to combine it to my views.

onStart()

Published
by
rs
on July 10, 2006
in Technical
. 0 Comments

Finally, I got my own technical blog. Well I’m a novice in programming, I just recently got my first job as a software developer. I really want to be focus on .Net programming but unfortunately the company that hired me are using the old VB6 and coldfusion. But it’s ok, For now I’ll just let the current lead me to where it want me to be. I’ll just keep my self updated with the .Net technology.