Best Length in Database to Store URL

The length you need in database to store URL should be 2083. This is because popular browser Internet Explorer only accepts 2083 characters. The question is, if you do not want this length or you want a shortened for some reason however, what is the best length?

The answer might be 255. OpenID indeed is a URL. According to OpenId 1.1 Appendix D: Limits, the maximum limit for Identifier Urls is 255 bytes.


http://support.microsoft.com/kb/208427
http://openid.net/specs/openid-authentication-1_1.html#limits

Equivalent of window.innerWidth/Height in IE

IE 8 continues to defy pupolar JavaScript functions in Firefox and Chrome, for window/document measurements.

The equivalent of window.innerWidth/Height in IE is document.documentElement.clientWidth/Height.

Following codes can be used:

; var horizonPosi = (window.innerWidth) ? window.innerWidth : document.documentElement.clientWidth
; var verticalPosi = (window.innerHeight) ? window.innerHeight : document.documentElement.clientHeight

There are other functions such as document.body.clientWidth/Height, document.body.offsetWidth/Height, but none of them are the equavalent. There is also a special case for document.body.clientHeight: it does not work while document.body.clientWidth works fine.

Invalid Argument Error in IE

Sometime you will get Error: Invalid argument in IE. No more other information. And this script runs well in Firefox and Chrome. Indeed, this error message tells true the argument is invalid. However, the problem is not when argument being used, but lies on when you get this argument. In one case:

1 ; var horizonPosi = (window.innerWidth/2 - 50) + 'px'
2 ; headerPainting.style.left = horizonPosi

the problem actually is caused by window.innerWidth. When print out, the argument as horizonPosi is indeed "NaNpx".

‹div› and ‹span› in JavaScript

In JavaScript, neither dynamic HTML nor document.write() does work with string of entire ‹div› section. However, it is essential part of Dynamic HTML.

How to dynamically create a Div section in JavaScript? Follow is the codes from Toolbox for IT, edited by Smifis:

var newdiv = document.createElement('div');
newdiv.setAttribute('id', id);
if (width) {
newdiv.style.width = 300;
}
if (height) {
newdiv.style.height = 300;
}
if ((left || top) || (left && top)) {
newdiv.style.position = "absolute";
if (left) {
newdiv.style.left = left;
}
if (top) {
newdiv.style.top = top;
}
}
newdiv.style.background = "#00C";
newdiv.style.border = "4px solid #000";
if (html) {
newdiv.innerHTML = html;
} else {
newdiv.innerHTML = "nothing";
}
document.body.appendChild(newdiv);

Actually, there are two parts of it. The first part as listed above, which more or less like an defination. And the second part is to present the result. Esential scripts in 2nd part are:

; var painting = document.getElementById(id)
; painting.style.visibility = 'visible'
; if (content) painting.innerHTML = content

In addtion, you can move these arguable variable from first part to second part for dynamic purpose, such like x, y, width, height, background etc. What is suggested is to separate them into two functions:

; function drawFrame(frameId, posiType, borderPro, rightPro, bottomPro, bgc, classNam)
{ ; var newdiv = document.createElement('div')
; newdiv.setAttribute('id', frameId)
; newdiv.style.position = posiType
; if (bgc) newdiv.style.background = bgc
; if (borderPro) newdiv.style.border = borderPro
else
{ ; if (rightPro) newdiv.style.borderRight = rightPro
; if (bottomPro) newdiv.style.borderBottom = bottomPro
}
; newdiv.style.visibility = 'hidden'
; if (classNam) newdiv.setAttribute("class", classNam)
; document.body.appendChild(newdiv)
}

; function showFrame(frameId, wid, hei, xPos, yPos, content)
{ ; var painting = document.getElementById(frameId)
; if (0!=wid) painting.style.width = wid + 'px'
; if (0!=hei) painting.style.height = hei + 'px'
; painting.style.left = xPos + 'px'
; painting.style.top = yPos + 'px'
; painting.style.visibility = 'visible'
; if (content) painting.innerHTML = content
; return painting
}

Beware, you will need a "canvas" to draw the div section, such as body of HTML, or wondow.onload in JavaScript file.


http://it.toolbox.com/wiki/index.php/Dynamically_Creating_a_Div_in_Javascript

Stored Procedure: Pivot With Dynamic Columns

Following is a stored procedure in aims to improve Microsoft built-in function of PIVOT in SQL Server. To use PIVOT, one must know exact number and name of columns after pivot, which does not meet the real world’s need. What we need is the pivot function works with dynamic pivot columns. To use this stored procedure, you would need to create a temporary table first with name of “Temp_Transform_Input”. Following is the structure of the this table:

Column 1, can be any name and data type. It would remain intact as it was after pivot.
Column 2, must be named as “Pivot_Column” and should be VARCHAR data type. Its contents after automatically distinct would be pivoted as column titles after execute this procedure.
Column 3, must be named as “Content_Column” and should be INT data type. It would be summed up after pivot. If the purpose is to count, put 1 here for every record.

To use it, just type EXECUTE P_Transform GO. The result will be automatically saved into 7 temporary tables. You do not need to manage the garbage collection for these two temporary tables, this procedure will manage them for you.

Why use temporary table instead of use Multi-statement Table-Valued Function as output method? Since you are dealing with the dynamic columns, it is impossible to declare the return table in Multi-statement Table-Valued Function.

CREATE PROCEDURE P_Transform
AS
BEGIN
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Largest'
)
DROP TABLE dbo.Temp_Transform_Row_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Sum'
)
DROP TABLE dbo.Temp_Transform_Row_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Largest'
)
DROP TABLE dbo.Temp_Transform_Column_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Sum'
)
DROP TABLE dbo.Temp_Transform_Column_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Largest'
)
DROP TABLE dbo.Temp_Transform_Both_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Sum'
)
DROP TABLE dbo.Temp_Transform_Both_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Result'
)
DROP TABLE dbo.Temp_Transform_Result
DECLARE @FirstColumn VARCHAR(128)
DECLARE @Summary VARCHAR(512)
DECLARE @Columns VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
SELECT @FirstColumn = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Input'
AND ORDINAL_POSITION = 1
SET @Summary =
'
SELECT ' + @FirstColumn + '
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Row_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY MAX(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, SUM(Content_Column) AS Total
INTO Temp_Transform_Row_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Column_Largest
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Column_Sum
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY SUM(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Both_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Both_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
, Pivot_Column
'
EXECUTE(@Summary)
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
(
SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query =
'
SELECT *
INTO Temp_Transform_Result
FROM
(
SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
(
SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Input'
)
DROP TABLE dbo.Temp_Transform_Input
END
GO

It is very important the length of each contents in Pivot_Column must be less 30 characters, because the contents will become the column name after transpose. Else, it won't work properly.


http://www.paladn.com/component/content/article/40-sql-server/122-udf-forms.html

Pivot Large Table With Dynamic Columns

In general, it is recommended to use SUM(CASE) GROUP BY method to pivot rows into columns. For details why recommended such, please refer to http://koncordpartners.blogspot.com/2010/03/pivot-turning-rows-into-columns.html. However, in two circumstances SUM(CASE) GROUP BY method does not work:

1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.

2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.

In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.

The PIVOT function is easy:

SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q

The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.

Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.

Step 1

CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO

Step 2

Prepare dynamic queries:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q

Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:

DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'

What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q

Then, execute one by one before union together.

Step 3

To execute the queries:

EXECUTE(@query)


http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html

sign() in PL/SQL

Nothing special. This function is too easy to be forgeten, for so many times. Just record here:

This function returns either -1, 0 or 1.


http://www.techonthenet.com/oracle/functions/sign.php