MySQL
Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:
DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)
An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey
Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.
For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey
Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?
T-SQL
Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:
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)
http://www.electrictoolbox.com/article/mysql/cross-table-delete/
Add A Linked Server In SQL Server
The system stored procedure sp_addlinkedserver can be used to created a linked server represent a remote SQL Server. This also can be used when SQL query needs to access multiple databases located in different servers.
Only following parameters are essential:
EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;
If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.
To establish the access:
EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';
This would created linked server for all database users. When use it, just like this:
SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO
http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
Only following parameters are essential:
EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;
If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.
To establish the access:
EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';
This would created linked server for all database users. When use it, just like this:
SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO
http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
Form And Table In HTML
In general, entire form must be completely included into a single table cell (single TD element). For format purpose, one might put form head/declaration outside the table and input items spread over cells. Put entire form with in a table but spread over several rows (TR elements), it will fail.
In addition, if one uses DHTML, form outsider table may not work in some browser, such as IE.
Conclusion: Put entire form completely with in a single table cell will be the best practice.
http://www.cs.tut.fi/~jkorpela/forms/tables.html
In addition, if one uses DHTML, form outsider table may not work in some browser, such as IE.
Conclusion: Put entire form completely with in a single table cell will be the best practice.
http://www.cs.tut.fi/~jkorpela/forms/tables.html
String Prefix 'N' In T-SQL
This is to enforce the string passed as Unicode NCHAR, NVARCHAR or NTEXT datatype, as opposed to CHAR, VARCHAR or TEXT. SQL Server only supports UTF-16 Unicode.
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
Code Conventions: Transact-SQL
There is a great article about code conventions of Transact-SQL:
http://msdn.microsoft.com/en-us/library/ms177563(v=SQL.105).aspx
Transact-SQL Syntax Conventions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177563(v=SQL.105).aspx
Transact-SQL Syntax Conventions (Transact-SQL)
Enforcing Width Of Cell <td> In HTML Table
It is hard to atomically break down long string such as URL to ensure the width in HTML. Here is a solution.
Within the cell or <td> section, cover your contents with a div section which shall include a width style property:
<div style="width:100px">
If that is not good enough, include this into your CSS:
word-wrap: break-word;
Within the cell or <td> section, cover your contents with a div section which shall include a width style property:
<div style="width:100px">
If that is not good enough, include this into your CSS:
word-wrap: break-word;
Parentheses () Outsider And After A Function - Nested Object Namespacing
It looks like:
; (function()
{ ...
})()
There are three purposes of it:
1. The function is anonymous so it can't be called the usual way. The outer brackets have to be there so that it can be called using brackets to enable the parameter feeding:
; (function(str){alert(str)}("test"))
2. Someone may to extend above-mentioned purpose to make the calling this anonymous function immediately after after the function definition, which is ofter called Self-ting Temporary Function. In this case, it is often with empty parameter.
3. Let us look at following example first:
; var myApp = {}
; myApp.message = 'hello'
; myApp.sayHello = function()
{ alert(this.message);
}
; myApp.sayHello() // works because "this" refers to myApp object.
; var importedfn = myApp.sayHello
; importedfn() // error because "this" refers to global object.
The lesson to be learned here is that this should never refer to an object being used as a namespace because it leads to confusion about importing identifiers from that namespace. People use nested object namespacing to avoid the identifier collisions. According to Peter Michaux, it is unnecessarily complex when the goal is simply avoiding identifier collisions.
Conclusion: Avoid this practice if you have other choice.
http://peter.michaux.ca/articles/javascript-namespacing
http://ejohn.org/apps/learn/
; (function()
{ ...
})()
There are three purposes of it:
1. The function is anonymous so it can't be called the usual way. The outer brackets have to be there so that it can be called using brackets to enable the parameter feeding:
; (function(str){alert(str)}("test"))
2. Someone may to extend above-mentioned purpose to make the calling this anonymous function immediately after after the function definition, which is ofter called Self-ting Temporary Function. In this case, it is often with empty parameter.
3. Let us look at following example first:
; var myApp = {}
; myApp.message = 'hello'
; myApp.sayHello = function()
{ alert(this.message);
}
; myApp.sayHello() // works because "this" refers to myApp object.
; var importedfn = myApp.sayHello
; importedfn() // error because "this" refers to global object.
The lesson to be learned here is that this should never refer to an object being used as a namespace because it leads to confusion about importing identifiers from that namespace. People use nested object namespacing to avoid the identifier collisions. According to Peter Michaux, it is unnecessarily complex when the goal is simply avoiding identifier collisions.
Conclusion: Avoid this practice if you have other choice.
http://peter.michaux.ca/articles/javascript-namespacing
http://ejohn.org/apps/learn/
Introduction To A New Array Type: Key-only Array
By Howard Stone (2010 October 2)
In general, there are two types of array, indexed array and associative array. The array introduced in this article does contain keys only. So far, the best use of key-only array is to represent the dataset in table data type. Let us start from a table in database which contains a dataset as a result of SELECT query.
A normalized table usually has less group relationship between records, that is more to stateless. For this nature, the applications of normalized table tend to deal with a single record once at a time. On contrast, the denormalized table usually has group relationship between records, which may generated from SELECT query that includes GROUP BY clause. This kind of dataset then would be process bulky for sake of relationship between each rows. The major usages include feet data to option group, list box, etc.
The key-only array in Javascript, for instance, for above table would be:
; var arr = new Array(13)
; arr['Language'] = new Array(1)
; arr['Language']['Order'] = 1
; arr['English'] = new Array(1)
; arr['English']['1'] = 0
; arr['Spanish'] = new Array(1)
; arr['Spanish']['2'] = 0
...
; arr['Arabic'] = new Array(1)
; arr['Arabic']['12'] = 0
Actually, this is not so-called new type, it is just a multidimensional associative array, for which some values become keys. Please note, the values of 1 or 0 here are useless. To having values is indeed a compromised setting to enable current design of languages to parse the key-only array without any change. However, the concept of key and value changed since. The container of full information is the keys. The values here thus can be used as flag at programmer's discretion.
Why such? Because it enables to access selected information simply through hierarchical traverse by using keys without complicated parse process. The key-only array does naturally contain the internal relationship between rows, which was generated by original SELECT statement in SQL query. In addition, in comparison with traditional arrays, this would use less codes.
Please note, the key-only array can include or exclude the title of the columns in original table. If any field is null, please turn it into string 'NULL' first. It would be then accessible in key-only array. Please use bracket '' for all keys because the fields in original table may contain whitespace, which would cause problems when key-only array being used in AJAX or other Dynamic HTML.
Let us see how to use this key-only array. When calling it at higher dimension, such as arr['English'] would assign a set of new sub arrays that contain all cells after dimension "English". To get list of the dimension immediately after "English", which is often used for cascade triggering, following codes can be used:
; var immediateList = new array()
; for (key in arr["English"]) immediateList.push(key)
In JSON, it would be:
; var arr =
{"Language":{"Order":1}
,"English":{"1":0}
,"Spanish":{"2":0}
...
,"Arabic":{"12":0}
}
Only at very root, there is array bracket []. All rest are in brackets {}. To access sub arrays started from "English" or "1" after "English" assume there is further sub arrays:
arr["English"]
arr["English"]["1"]
If "1" is something other than numeric, it can be called:
arr["English"].something
To access keys at next dimension after "English":
; var immediateList = new array()
; for (key in arr["English"]) immediateList.push(key)
Summary:
1. Key-only array can use all existing functionalities of indexed array and associative array.
2. Since the information is key itself, there is no need to do any value search. It thus achieves directly random access without traverse.
3. It makes contained relationship information between original rows usable.
4. It uses less code.
5. It can be in JSON format too.
Special notes:
1. Since values are not in use, it can be used to represent last column. However, it is not recommended because it would require special treatment for leave of key tree.
2. Replace null with string 'NULL" at SQL query if the data source is database.
3. Always use bracket to make sure all keys are in string datatype.
4. When calling key-only array, always use array calling method rather than identifier calling method, that is arr["English"]["something"] instead of arr.English.something. This is to avoid possible error because in some languages like JavaScript identifiers can't begin with a numeral.
In general, there are two types of array, indexed array and associative array. The array introduced in this article does contain keys only. So far, the best use of key-only array is to represent the dataset in table data type. Let us start from a table in database which contains a dataset as a result of SELECT query.
A normalized table usually has less group relationship between records, that is more to stateless. For this nature, the applications of normalized table tend to deal with a single record once at a time. On contrast, the denormalized table usually has group relationship between records, which may generated from SELECT query that includes GROUP BY clause. This kind of dataset then would be process bulky for sake of relationship between each rows. The major usages include feet data to option group, list box, etc.The key-only array in Javascript, for instance, for above table would be:
; var arr = new Array(13)
; arr['Language'] = new Array(1)
; arr['Language']['Order'] = 1
; arr['English'] = new Array(1)
; arr['English']['1'] = 0
; arr['Spanish'] = new Array(1)
; arr['Spanish']['2'] = 0
...
; arr['Arabic'] = new Array(1)
; arr['Arabic']['12'] = 0
Actually, this is not so-called new type, it is just a multidimensional associative array, for which some values become keys. Please note, the values of 1 or 0 here are useless. To having values is indeed a compromised setting to enable current design of languages to parse the key-only array without any change. However, the concept of key and value changed since. The container of full information is the keys. The values here thus can be used as flag at programmer's discretion.
Why such? Because it enables to access selected information simply through hierarchical traverse by using keys without complicated parse process. The key-only array does naturally contain the internal relationship between rows, which was generated by original SELECT statement in SQL query. In addition, in comparison with traditional arrays, this would use less codes.
Please note, the key-only array can include or exclude the title of the columns in original table. If any field is null, please turn it into string 'NULL' first. It would be then accessible in key-only array. Please use bracket '' for all keys because the fields in original table may contain whitespace, which would cause problems when key-only array being used in AJAX or other Dynamic HTML.
Let us see how to use this key-only array. When calling it at higher dimension, such as arr['English'] would assign a set of new sub arrays that contain all cells after dimension "English". To get list of the dimension immediately after "English", which is often used for cascade triggering, following codes can be used:
; var immediateList = new array()
; for (key in arr["English"]) immediateList.push(key)
In JSON, it would be:
; var arr =
{"Language":{"Order":1}
,"English":{"1":0}
,"Spanish":{"2":0}
...
,"Arabic":{"12":0}
}
Only at very root, there is array bracket []. All rest are in brackets {}. To access sub arrays started from "English" or "1" after "English" assume there is further sub arrays:
arr["English"]
arr["English"]["1"]
If "1" is something other than numeric, it can be called:
arr["English"].something
To access keys at next dimension after "English":
; var immediateList = new array()
; for (key in arr["English"]) immediateList.push(key)
Summary:
1. Key-only array can use all existing functionalities of indexed array and associative array.
2. Since the information is key itself, there is no need to do any value search. It thus achieves directly random access without traverse.
3. It makes contained relationship information between original rows usable.
4. It uses less code.
5. It can be in JSON format too.
Special notes:
1. Since values are not in use, it can be used to represent last column. However, it is not recommended because it would require special treatment for leave of key tree.
2. Replace null with string 'NULL" at SQL query if the data source is database.
3. Always use bracket to make sure all keys are in string datatype.
4. When calling key-only array, always use array calling method rather than identifier calling method, that is arr["English"]["something"] instead of arr.English.something. This is to avoid possible error because in some languages like JavaScript identifiers can't begin with a numeral.
Tutorial: JSON Format
The beauty of JSON is it can stringify a multidimensional array, either indexed or associative array, into a linear string, so it can be passed between functions as well as languages. The basic rule is as follows:
1. If value is numeric, do not include the quote. If the value is string, include the double quote. For instance: 1, 3, "ab", "5". It is not necessary to use quote for the key in associative array if no whitespace used in key, else, use double quote. However, double quote on keys is required by JSON format specification.
2. Use ":" to separate key and value if the element is of associative relationship. For instance: Category_2:3.
3, use comma to separate elements. For instance: 1, Category_2:3, "ab".
4. Use [] to signify a single dimensional indexed array. For instance [1, 3, "ab"]. When calling an element within a single indexed dimensional array, just like usual: arr[2]. In this case, it would return 3.
5. Use {} to signify a single dimensional associative array. For instance {Category_2:3}. For multi-elements associative array, one bracket is enough: {Category_2:3, Category_3:"ab"}. When calling an element within a single associative dimensional array, use following format: arr.Category_3. In this case, it would return "ab".
Here is an example:
{lastName: "Smith",
age: 25,
address:
{streetAddress: "21 2nd Street",
postalCode: "10021"
},
phoneNumber:
[{ type: "home",
},
{ type: "fax",
number: "646 555-4567"
}
]
}
To get fax number here, call: arr.phoneNumber[1].number
Another example starts with index array:
[
{A:a}
,{B:b}
]
How to stringify JSON for multidimensional array? DON't use JavaScript built-in function stringify(). It does not work for multidimensional array. Use ''+arr to make it string.
http://en.wikipedia.org/wiki/Json
1. If value is numeric, do not include the quote. If the value is string, include the double quote. For instance: 1, 3, "ab", "5". It is not necessary to use quote for the key in associative array if no whitespace used in key, else, use double quote. However, double quote on keys is required by JSON format specification.
2. Use ":" to separate key and value if the element is of associative relationship. For instance: Category_2:3.
3, use comma to separate elements. For instance: 1, Category_2:3, "ab".
4. Use [] to signify a single dimensional indexed array. For instance [1, 3, "ab"]. When calling an element within a single indexed dimensional array, just like usual: arr[2]. In this case, it would return 3.
5. Use {} to signify a single dimensional associative array. For instance {Category_2:3}. For multi-elements associative array, one bracket is enough: {Category_2:3, Category_3:"ab"}. When calling an element within a single associative dimensional array, use following format: arr.Category_3. In this case, it would return "ab".
Here is an example:
{lastName: "Smith",
age: 25,
address:
{streetAddress: "21 2nd Street",
postalCode: "10021"
},
phoneNumber:
[{ type: "home",
},
{ type: "fax",
number: "646 555-4567"
}
]
}
To get fax number here, call: arr.phoneNumber[1].number
Another example starts with index array:
[
{A:a}
,{B:b}
]
How to stringify JSON for multidimensional array? DON't use JavaScript built-in function stringify(). It does not work for multidimensional array. Use ''+arr to make it string.
http://en.wikipedia.org/wiki/Json
Issues In Multidimensional Array in JavaScript
Multidimensional array in JavaScript is treated as object, so it would be a problem when it works with dynamic HTML and Ajax. Since the reference points to the multidimensional array needs to be closely working with string, it is hard to program when should such reference being instantiated, that is presenting the whole body of the array rather than the reference itself. This issue becomes very serious when parameter management gets involved. Indeed, both bind variable and parameter management are essential for dynamic HTML.
If a string used as reference to be passed between functions, another issue may be occurred: hard to convert it back to reference. In some cases eval() function can be used to achieve this goal, however, it won't be able to work with multidimensional array.
The solution is using JSON datatype to represent the multidimensional array. In addition, it is suggested to convert it into string before being composed into bind variable or get involved with parameter management.
http://koncordpartners.blogspot.com/2010/08/parameter-management.html
If a string used as reference to be passed between functions, another issue may be occurred: hard to convert it back to reference. In some cases eval() function can be used to achieve this goal, however, it won't be able to work with multidimensional array.
The solution is using JSON datatype to represent the multidimensional array. In addition, it is suggested to convert it into string before being composed into bind variable or get involved with parameter management.
http://koncordpartners.blogspot.com/2010/08/parameter-management.html
Subscribe to:
Posts (Atom)
Posts
