d.Construct is an affordable, one-day conference aimed at those building the latest generation of web-based applications. The event discusses how new technology is transforming the web from a document delivery system into an application platform.
9-5:30pm, Sept 8th 2006
Brighton Corn Exchange
For a list of all the speakers,
http://2006.dconstruct.org/speakers/
Comments off
With the release of SQL Server 2005, a feature has been added that makes paging data much easier. This feature is the ROW_NUMBER function. This function assigns consecutive row numbers for data returned from a query. One requirement of this function is you need to specify a column for ordering. Take a look at the table and query below,
[ tbl_Chair ]
ChairID ChairName
——- ———-
2 Aeron
65 Designer
14 Staples
23 Ergonomic
39 Blue
select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair
Row ChairName
—- ———-
1 Aeron
2 Staples
3 Ergonomic
4 Blue
5 Designer
Now if I only wanted the 3rd thrugh 5th row, I could say,
select row, name
from (
select row_number() over (order by ChairID) as Row, ChairName from tbl_Chair
)
where row >=3 and row <=5
Now add a temp table to store the number of Chairs in your table to count the number of pages for paging, you have a stored proc like,
CREATE PROCEDURE GetChairs
(
@PageNumber int,
@ProductsPerPage int
)
…
And you have nice paging functionality.
I’ve come across a few times on IIS running on Windows 2003 Server, fortunately the problem is easy to fix. If you are developing ASP .NET 2.0 there’s a chance you are missing the ASP.NET 2.0.XXXXX Web Service Extension.First thing you want to do is open Windows Explorer and navigate to,
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files
Right-click on the folder, click on the permissions tab and give “NETWORK SERVICE” permissions to write to the folder.
Secondly open IIS Manager and click on the Web Service Exension folder. In the right window click “Add a new web service extension…” and enter the following,
Extension Name: ASP.NET v2.0.XXXXX
Required Files: C:\WINDOWS\Microsoft.NET\Framework\v2.0.XXXXX\aspnet_isapi.dll
When completed, IIS should look like below,
When Oracle performance becomes a mystery and you need to get under the hood of the car, falling upon raw oracle tracing is usuablly a good start. From SQLPLUS or your favorite Oracle Client tool, run through the following steps. What you end up doing is to dump a raw trace of what Oracle is doing. I find this to be very useful information in addition to using an Explain Plan.
alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited; alter session set events '10046 trace name context forever, level 12';
SQL STATEMENT
alter session set events '10046 trace name context off';
From the raw trace file is generated in your oracle instance path to UDUMP. “C:\oracle\product\10.1.0\admin\[INSTANCE NAME]\udump” From there open up DOS PROMPT and run,
tkprof [dump file name]
Open command line and tkprof.
With collections, it is possible to return a table from a pl/sql function. First, we need to create a new object type that contains the fields that are going to be returned:
create or replace type t_col as object ( i number, n varchar2(30) );
Then, out of this new type, a nested table type must be created.
create or replace type t_nested_table as table of t_col;
Now, we’re ready to actually create the function:
create or replace function return_table return t_nested_table as v_ret t_nested_table; begin v_ret := t_nested_table(); v_ret.extend; v_ret(v_ret.count) := t_col(1, 'one'); v_ret.extend; v_ret(v_ret.count) := t_col(2, 'two'); v_ret.extend; v_ret(v_ret.count) := t_col(3, 'three'); return v_ret; end;
Here’s how the function is used:
select * from table(return_table);
First time I ran across a query like the one below,
SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = N'CAMBR'
I asked myself, why is there an ‘N’ before ‘CAMBR’. Well, the ‘N’ actually designates unicode. If your query contains a field that can only be represented by 2 byte characters (i.e. Asian or accented characters), you need to use the N before the value or SQL Server will replace the characters with garbage (usually question marks). Below is another example, USE Northwind
SELECT CompanyName, ContactName, Phone, Fax FROM Customers WHERE CompanyName LIKE N'%snabbk'p' ORDER BY CompanyName ASC, ContactName ASC
For most cases, you should avoid using unicode. Unicode makes your database larger and slower. Also in a nvarchar field you only can store 4000 chars instead of 8000 in a “normal” varchar field. If you have in index on a nchar or nvarchar field it will take twice of the size therefore reducing the performance. I spend a good amount of time working with the Japanese character set and have run across some of these issues. If you do use Unicode, all Unicode is not the same. The Win32 API, Java, ODBC/OLEDB, etc all use UCS-2/UTF-16 as their representation of Unicode (UCS-2 encodes Unicode using 16 bits per character instead of the variable 8-24 bits used by UTF-8). But, Oracle, among others, use UTF-8, which is a multi-byte representation of Unicode. So, in UTF-8, typical ASCII codes are single byte, and double-byte is only used if a Unicode character is interjected.
This script returns the column and table names for a value input by a user. For example, if we know that there is a character value of ‘US-15017094′ with no other details available, this routine will search the user schema and list both table and column name for that value.
Declare searchtxt VARCHAR2(30):='US-15017094';
sqltxt VARCHAR2(255);
resultcount pls_integer:=0;
Begin
for c in (select table_name, column_name from USER_tab_columns
where table_name in ('ES_ALERT','AM_REGION','ES_ALERT_CACHE_PO')
and data_type in ('CHAR','VARCHAR2'))
loop
resultcount:=0;
sqltxt := 'select count(1) from '|| c.table_name||' where '|| c.column_name||' ='||chr(39)||searchtxt||chr(39);
execute immediate sqltxt into resultcount;
IF resultcount=1 THEN
dbms_output.put_line(c.table_name || '.' || c.column_name);
END IF;
end loop;
End;
While building multi-language content delivery and e-commerce sites, you may run across the need to only allow Roman characters. By using the javascript function below, you can verify the unicode value for each character from some text passed to the function. If the unicode value is greater than 256, then throw an error or deny a form submission.
function isUnicode() {
isTrue=false;
someText = document.form.fieldname.value;
textSize = someText.length;
for (i = 0; i < textSize; i++) {
if (someText.charCodeAt(i) > 256) {
alert(”This is not a Roman Character!”);
isTrue=true; break;
}
}
return isTrue;
}
The most common way to install a DLL on windows is to use the MSDOS command prompt using the program regsvr32.exe. * Install DLL. From the CMD line type,
regsvr32.exe e.g. regsvr32.exe tools.dll
* Uninstall DLL.
regsvr32.exe -u regsvr32.exe -u tools.dll
Regsvr32.exe can be found in the system directory of Windows. c:\windows\system or c:\windows\system32 or c:\winnt\system32 Depending on the Windows OS you are running. To get help about the parameters, type, regsvr32.exe NO DLL name specified.
usage: regsvr32 [/u] [/s] [/n] [/i[:cmdline]] dllname where /u means 'Unregister server' /s means 'Silent (=display no message boxes)' /i means 'Call Dllinstall passing an optional [cmdline]; when used with /u calls dll uninstall' /n means 'do not call DllRegisterServer; this option must be used with /i'
Despite the hundreds of stored procedures I have written that contain heterogeneous queries I always forget to set ANSI_NULLS and ANSI_WARNINGS. If you see the following error,
Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warnings options to be set for the connection. This ensures consisten query semantics. Enable these options and then reisse your query.
Just insert the following commands before creating/updating your STORED PROCEDURE.
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE PROCEDURE spStoredProdc AS ... go SET ANSI_NULLS OFF SET ANSI_WARNINGS OFF
In the case of CUSTOM FUNCTIONS you can do the same,
SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE FUNCTION dbo.functionName ... END
Comments off