Showing posts with label Parameter. Show all posts
Showing posts with label Parameter. Show all posts
Table Name and Column Name as Parameters in MS Access
No, you can’t use table name and column name as parameters in MS Access, unfortunately.
Here is a solution: Use a uniformed names and structures in your automations, and copy your table into a temp table with a uniformed name. After process, you copy back and overwrite the temp table to its original table name. Reasonable labour work load.
Object Oriented Programming in JavaScript
There are several ways to do this, such as using prototype and JavaScript object. However, simplest may be the best. Following method matches the orthodox structure of OO, which is put everything inside of a class file:
; var ObjectClass = function(inputPara)
{
; var privateAttribute_1 = inputPara
; this.publicAttribute_Temp = 200
; function privateMethod_SetInternally()
{ ; privateAttribute_1 = 100
}
; this.publicMethod_1 = function()
{ ; privateMethod_SetInternally()
; return 50*privateAttribute_1
}
; this.publicMethod_Get = function()
{ ; return privateAttribute_1
}
; this.publicMethod_SetExternally = function(valueSetLater)
{ ; privateAttribute_1 = this.publicMethod_1*valueSetLater*123456789
}
}
Please note, for private method, do not use Varibalized Function, which is also called Function Literal, Function Reference or Function Pointer, like this:
; var privateMethod_SetInternally = function () {…}
Indeed, private method privateMethod_SetInternally is useless here, because you can always set internally directly:
; privateAttribute_1 = newValue
The purpose of privateMethod_SetInternally here is to show how to call this method, as stated in publistMethod_1. There is no way for outsider to use this private method, but through publistMethod_1.
All attributes/method started with this. is the public accessible by outsider caller, or the object based on this Class.
To use this Class, an object needs to be created:
; var obj = New ObjectClass(300)
; var att1_inThere = obj.publicMethod_Get()
; obj.publicMethod_SetExternally(400)
Please note, internally, calling all public accessible attributes/methods would not need with (), while private methods need to include (). However, for outsider object, public methods need to include () as well, like above.
There is a problem. Since internally calling public methods could not be with (), there is no way to pass the parameter while calling. In here, publicMethod_SetExternally can be called and assigned with parameters externally with no problem, but can only be called without assigning parameter internally. In this case, parameter is essential, so it would generate “not defined” error.
To overcome this, publicMethod_SetExternally needs to be rewritten as follows:
; this.publicMethod_SetExternally = function()
{ ; privateAttribute_1 = this.publicMethod_1*this.publicAttribute_Temp*123456789
}
Internally, it can now be called directly:
; this.publicMethod_SetExternally
Externally, it would be called like this:
; obj.publicAttribute_Temp = 400
; obj.publicMethod_SetExternally()
; var ObjectClass = function(inputPara)
{
; var privateAttribute_1 = inputPara
; this.publicAttribute_Temp = 200
; function privateMethod_SetInternally()
{ ; privateAttribute_1 = 100
}
; this.publicMethod_1 = function()
{ ; privateMethod_SetInternally()
; return 50*privateAttribute_1
}
; this.publicMethod_Get = function()
{ ; return privateAttribute_1
}
; this.publicMethod_SetExternally = function(valueSetLater)
{ ; privateAttribute_1 = this.publicMethod_1*valueSetLater*123456789
}
}
Please note, for private method, do not use Varibalized Function, which is also called Function Literal, Function Reference or Function Pointer, like this:
; var privateMethod_SetInternally = function () {…}
Indeed, private method privateMethod_SetInternally is useless here, because you can always set internally directly:
; privateAttribute_1 = newValue
The purpose of privateMethod_SetInternally here is to show how to call this method, as stated in publistMethod_1. There is no way for outsider to use this private method, but through publistMethod_1.
All attributes/method started with this. is the public accessible by outsider caller, or the object based on this Class.
To use this Class, an object needs to be created:
; var obj = New ObjectClass(300)
; var att1_inThere = obj.publicMethod_Get()
; obj.publicMethod_SetExternally(400)
Please note, internally, calling all public accessible attributes/methods would not need with (), while private methods need to include (). However, for outsider object, public methods need to include () as well, like above.
There is a problem. Since internally calling public methods could not be with (), there is no way to pass the parameter while calling. In here, publicMethod_SetExternally can be called and assigned with parameters externally with no problem, but can only be called without assigning parameter internally. In this case, parameter is essential, so it would generate “not defined” error.
To overcome this, publicMethod_SetExternally needs to be rewritten as follows:
; this.publicMethod_SetExternally = function()
{ ; privateAttribute_1 = this.publicMethod_1*this.publicAttribute_Temp*123456789
}
Internally, it can now be called directly:
; this.publicMethod_SetExternally
Externally, it would be called like this:
; obj.publicAttribute_Temp = 400
; obj.publicMethod_SetExternally()
Dynamic SQL in T-SQL
First of all, the dynamic SQL is not part of the stored procedure, but constitutes its own scope. So, it is a independent thread. Your task indeed is to pass the bind variable and/or parameter into that thread and retrieve the result back to your stored procedure.
There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.
In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.
If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.
To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:
SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'
In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.
After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.
Okey, here is an example:
DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs
In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see
, @EachColumn = @EachColumn
, @result = @result
The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.
http://www.sommarskog.se/dynamic_sql.html
There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.
In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.
If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.
To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:
SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'
In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.
After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.
Okey, here is an example:
DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs
In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see
, @EachColumn = @EachColumn
, @result = @result
The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.
http://www.sommarskog.se/dynamic_sql.html
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/
Date Format of Parameters for SSRS
While SQL Server does accept the date format as string, it is suggested to convert it to datetime data type. This will allow you to use a calendar control as well as avoiding any date format errors that may occur when running your query. Indeed, it does cause minor errors internally. The period, it can be:
t.add_datetime >= @StartDate AND t.add_datetime < DATEADD(D, 1, @EndDate)
The trick here is for general public both dates should be inclusive. Above code indeed includes the time from 0:00:00 of the StartDate to 23:59:59 of the EndDate.
http://www.sqlusa.com/bestpractices/datetimeconversion/
t.add_datetime >= @StartDate AND t.add_datetime < DATEADD(D, 1, @EndDate)
The trick here is for general public both dates should be inclusive. Above code indeed includes the time from 0:00:00 of the StartDate to 23:59:59 of the EndDate.
http://www.sqlusa.com/bestpractices/datetimeconversion/
Trouble with Internet Explorer
The purpose of this article is to establish an uniform convention to deal with a very special issue of IE. It is for sure without this convention, websites would still run with or without problem. The issue is when IE passing null value from JavaScript to PHP, null value becomes string "null". If there is a possibility the user input could be "null", such as using "null" as login name or password, there is no way for PHP to detect which "null" is null and which "null" is input "null".
So, the accurate solution is never let JavaScript passing null to PHP, but using '' instead. It sounds easy; but JavaScript can generate null value without your knowledge. That is, if a parameter passing through a function to feed a form, when original parameter is undefined, eventually that undefined would be become string "null" or string "undefined" at the end of PHP. So, you would need to deal with every form input by special arrangement as follows:
; document.theForm.theInputItem.value = (parameter) ? parameter : ''
Then, to test if it is valid in JavaScript would be changed to:
; if (''!=parameter) {...}
At PHP end, it is relatively easy to detect if it is a valid input:
if (0!=strlen(trim(@$_POST["theInputItem"]))) {...};
However, for numeric data, it is not suitable to use '' instead null. It is still suggested to code as usual. The only difference is at PHP end:
if (0!=strlen(trim(@$_POST["theInputItem"])) && is_numeric(@$_POST["theInputItem"])) {...};
http://koncordpartners.blogspot.com/2009/12/test-various-nothings-in-php.html
So, the accurate solution is never let JavaScript passing null to PHP, but using '' instead. It sounds easy; but JavaScript can generate null value without your knowledge. That is, if a parameter passing through a function to feed a form, when original parameter is undefined, eventually that undefined would be become string "null" or string "undefined" at the end of PHP. So, you would need to deal with every form input by special arrangement as follows:
; document.theForm.theInputItem.value = (parameter) ? parameter : ''
Then, to test if it is valid in JavaScript would be changed to:
; if (''!=parameter) {...}
At PHP end, it is relatively easy to detect if it is a valid input:
if (0!=strlen(trim(@$_POST["theInputItem"]))) {...};
However, for numeric data, it is not suitable to use '' instead null. It is still suggested to code as usual. The only difference is at PHP end:
if (0!=strlen(trim(@$_POST["theInputItem"])) && is_numeric(@$_POST["theInputItem"])) {...};
http://koncordpartners.blogspot.com/2009/12/test-various-nothings-in-php.html
Set Language in Private Search Engine
Our previous posting Create Your Own Search Engine (http://koncordpartners.blogspot.com/2009/07/create-your-own-search-engine.html) could only display search results in English. Actually, the codes in that posting only have 4 parameters, you can add much more to suit your own needs. These 4 Google WebSearch Query Parameters are:
cx - Required. The cx parameter specifies a unique code that identifies a custom search engine. You must specify a Custom Search Engine using the cx parameter to retrieve search results from that CSE.
ie - Optional. The ie parameter sets the character encoding scheme that should be used to interpret the query string. The default ie value is latin1.
q - Optional. The q parameter specifies the search query entered by the user. Even though this parameter is optional, you must specify a value for at least one of the query parameters (as_epq, as_lq, as_oq, as_q, as_rq) to get search results.
sa - Required. It is indeed you submit the search query to Google search engine.
Today, we add three more parameters to show how the Simplified Chinese and Traditional Chinese can be interchangeable as search result.
hl - Optional. The hl parameter specifies the interface language (host language) of your user interface. To improve the performance and the quality of your search results, you are strongly encouraged to set this parameter explicitly. However, a successful search engine does not rely only on the parameters, but also settings of search engine itself. That is why you may also need your own account. “Site language”, is not only the site language, but also the search engine host language (the display language other than search result). Naturally, it should be set as English. “Your site encoding”, which is the searching language, should be GB18030 in this example.
lr - Optional. The lr (language restrict) parameter restricts search results to documents written in a particular language. Google WebSearch determines the language of a document by analyzing:
- the top-level domain (TLD) of the document's URL
- language meta tags within the document
- the primary language used in the body text of the document
c2coff - Optional. The c2coff parameter enables or disables the Simplified and Traditional Chinese Search feature. Please note, this parameter is only related to Chinese. For other language, no need to use this. The default value for this parameter is "0" (zero), meaning that the feature is enabled. Values for the c2coff parameter are: 1 is disabled, and 0 is enabled.
Following is codes with these two parameters. It means the search will be restricted to only websites wrote in three languages: US English, Simplified Chinese and Traditional Chinese.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="GB18030" name="ie">
<input type="hidden" value="lang_en|lang_zh-Hans|lang_zh-Hant" name="lr">
<input type="hidden" value="0" name="c2coff">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
http://www.google.com/coop/docs/cse/resultsxml.html
cx - Required. The cx parameter specifies a unique code that identifies a custom search engine. You must specify a Custom Search Engine using the cx parameter to retrieve search results from that CSE.
ie - Optional. The ie parameter sets the character encoding scheme that should be used to interpret the query string. The default ie value is latin1.
q - Optional. The q parameter specifies the search query entered by the user. Even though this parameter is optional, you must specify a value for at least one of the query parameters (as_epq, as_lq, as_oq, as_q, as_rq) to get search results.
sa - Required. It is indeed you submit the search query to Google search engine.
Today, we add three more parameters to show how the Simplified Chinese and Traditional Chinese can be interchangeable as search result.
hl - Optional. The hl parameter specifies the interface language (host language) of your user interface. To improve the performance and the quality of your search results, you are strongly encouraged to set this parameter explicitly. However, a successful search engine does not rely only on the parameters, but also settings of search engine itself. That is why you may also need your own account. “Site language”, is not only the site language, but also the search engine host language (the display language other than search result). Naturally, it should be set as English. “Your site encoding”, which is the searching language, should be GB18030 in this example.
lr - Optional. The lr (language restrict) parameter restricts search results to documents written in a particular language. Google WebSearch determines the language of a document by analyzing:
- the top-level domain (TLD) of the document's URL
- language meta tags within the document
- the primary language used in the body text of the document
c2coff - Optional. The c2coff parameter enables or disables the Simplified and Traditional Chinese Search feature. Please note, this parameter is only related to Chinese. For other language, no need to use this. The default value for this parameter is "0" (zero), meaning that the feature is enabled. Values for the c2coff parameter are: 1 is disabled, and 0 is enabled.
Following is codes with these two parameters. It means the search will be restricted to only websites wrote in three languages: US English, Simplified Chinese and Traditional Chinese.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
This is private search engine using Google facility
</title>
</head>
<body>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
</br>
<form id="cse-search-box" action="http://www.google.com/cse">
<input type="hidden" value="partner-pub-3597878264183301:140hrt-vklq" name="cx">
<input type="hidden" value="GB18030" name="ie">
<input type="hidden" value="lang_en|lang_zh-Hans|lang_zh-Hant" name="lr">
<input type="hidden" value="0" name="c2coff">
<p align="center">
<img alt="Google" src="http://www.google.com/intl/en_us/images/logo.gif" />
</p>
<p align="center">
<input size="50" name="q">
<input type="submit" value="Search" name="sa">
</p>
</form>
<p align="center">
Koncord Private Search Engine Based on New Google Technology
</p>
</body>
</html>
http://www.google.com/coop/docs/cse/resultsxml.html
Subscribe to:
Posts (Atom)
Posts
