NG Yew Ban's Software Development Blog

Oracle PL/SQL Stored Procedure Looping Nested Exception Handling

2011-12-09T16:48:00.001+08:00

Today we have a requirement that we want to have a stored procedure loop an Oracle records, inside the loop, we want to insert each record into few more tables via sub stored procedure, but we want to continue the loop if the insertion fail, and just only rollback the fail insert for the particular record, let's see an example:-

We have Main table having Product ID 1 to 3, we will loop these records and insert each record into Table 1 and Table 2, i.e.
Loop for Product ID 1 - Insert Product ID 1 to table 1 success, Insert Product ID 1 to table 2 success;
Loop for Product ID 2 - Insert Product ID 2 to table 1 success, Insert Product ID 2 to table 2 fail;
Loop for Product ID 3 - Insert Product ID 3 to table 1 success, Insert Product ID 3 to table 2 success;

The tricky part is - we want to rollback the inserting Product ID 2 to table 1 and record the exception of inserting Product ID 2 to table 2 into Error Log and continue the next looping;

How we design the Exception Handling to handle this requirement?

The solution is we need to handle the exception within looping, in main loop, we should commit the transaction for each record's sub-transactions success, and ignore/rollback when any record sub-transaction is failure, and the logging of the exception should do it in sub transaction.

Here is the main stored procedure and sub stored procedures to simulate this scenario:-

PACKAGE BODY "YB_TEST" IS

PROCEDURE "PROC_MAIN" IS
BEGIN -- executable part starts here
    DECLARE CURSOR P IS
        SELECT PRODUCT_ID, PRODUCT_NAME
        FROM TABLE_LOOP
        ORDER BY PRODUCT_ID;

    BEGIN

        FOR r IN p LOOP
        BEGIN
            PROC_SUB1(r.PRODUCT_ID, r.PRODUCT_ID, 'REMARK FOR ' || r.PRODUCT_ID);
            PROC_SUB2(r.PRODUCT_ID, r.PRODUCT_ID, 'AUDIT FOR ' || r.PRODUCT_ID);
            COMMIT;
        EXCEPTION
        WHEN OTHERS
        THEN
            NULL;
--            ROLLBACK;
        END;
        END LOOP;
    END;
END "PROC_MAIN";

PROCEDURE "PROC_SUB1" (
P_ORDER_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_REMARKS IN VARCHAR2) IS
BEGIN -- executable part starts here

    INSERT INTO TABLE1 (ORDER_ID, PRODUCT_ID, REMARKS)
    VALUES (P_ORDER_ID, P_PRODUCT_ID, P_REMARKS);

    EXCEPTION
    WHEN OTHERS
    THEN
            ROLLBACK; -- Rollback the previous insert
            INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
            VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
            COMMIT; -- Commit this exception logging
            RAISE; -- Re-throw Exception to caller, i.e. Main Loop

END "PROC_SUB1";

PROCEDURE "PROC_SUB2" (
P_AUDIT_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_AUDIT_DESC IN VARCHAR2) IS
BEGIN -- executable part starts here

    INSERT INTO TABLE2 (AUDIT_ID, PRODUCT_ID, AUDIT_DESC)
    VALUES (P_AUDIT_ID, P_PRODUCT_ID, P_AUDIT_DESC);

    EXCEPTION
    WHEN OTHERS
    THEN
            ROLLBACK; -- Rollback the previous insert
            INSERT INTO TABLE_LOG (PRODUCT_ID, EXCEPTION_DESC)
            VALUES(P_PRODUCT_ID, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 100));
            COMMIT; -- Commit this exception logging

            RAISE; -- Re-throw Exception to caller, i.e. Main Loop

END "PROC_SUB2";

END "YB_TEST";

SharePoint 2010 SharePoint Designer (50354B) Lab

2011-11-11T14:05:00.000+08:00

To do the lab in SharePoint 2010 SharePoint Designer course 50354B, we can reuse other SharePoint courses Hyper-V image (e.g. SharePoint 2010 Application Development 10175) with the following configuration:-

Enabled Desktop Experience in Windows 2008 R2 to enable MS Word documents save into SharePoint directly
All http://Servername:100 change to http://SharePoint
Setup http://SharePoint:112 for Publishing Portal
SQL Server 2008 R2 need to enable SQL Server Authentication
SQL Server sa user status should be enabled and configure password
Install Northwind Database
Module 4 Lab 1 Task 6 to join 2 data sources has bug in step 16 and require to fix the XSLT by following this article
No SMTP setup is required
Module 6 Lab 1 Step 17 (pg 20 of 27) should change "Send Email" to "Log to History"
Module 6 Lab 2 Task 7 Step 3 (pg 3 of 6) should change Lookup Manager to Set Variable "manager" to SHAREPOINT\Administrator
Module 6 Lab 2 Task 10 Step 8 (pg 5 of 6) Contose\sp_member change to Home Members
Module 6 Lab 3 (pg 3 of 3) last step, the recursive workflow will stop by creating another announcement items and the exception will be log into C:\Program Files\Common Files\Microsoft Shared\Web Server Extension\14\Logs
Module 6 Lab 4 (pg 6 0f 7), before import, we need to delete the workflow Anywhere from Site Setting->Site Admin->Workflow Settings
Module 6 Lab 5 (pg 2 of 3) Step 14 is applied to Single Task not Overall Tasks

There is one mystery, the comment created from Workflow cannot be found from SharePoint.

SharePoint Designer 2010 Data View bug in Join Linked Data Source!

2011-11-09T23:10:00.000+08:00

I have 2 Data Sources - Category and Product, I created a Linked Data Source to join these 2 tables using their common key, and created a Data View to access this Linked Data Source, unfortunately the Data View display only the blank data view but with the number of records correct which I can see it from the paging info.

Finally I found out there is a XSLT bug in SharePoint Designer 2010 after spending sometime to troubleshoot this funny problem:-

The node is selecting the wrong data source value, we can erase the data source value inside this node and use XPath Expression Builder to find out the actual data source value.

After correcting the data source value by using XSLT Expression Builder, my data view work like charm!

SharePoint Wiki Link

2011-10-29T12:50:00.000+08:00

SharePoint 2010 Wiki Link is powerful! We can create SharePoint Wiki Link by using [[Square Brackets]]

To link to your Wiki Page, type [[Wiki Page Name]], e.g. [[SharePoint]], it will be linked if the Wiki Page is exists else the Wiki Page will be created.

We also can link to any documents or lists using [[List:Path to the list item]], e.g. We have a video clip abc.wmv in Shared Documents, we can link it using [[List:Shared Documents/abc.wmv]]

You also can link to View using [[View:View Path]]

This feature is enabled in Site Settings/Features/Wiki Page Home Page.

BizTalk 2010 Redistributable CAB Files

2011-10-04T16:17:00.001+08:00

Windows Server 2008 32-bit Edition
http://go.microsoft.com/fwlink/?LinkID=189407&clcid=0x409

Windows Server 2008 64-bit Edition
http://go.microsoft.com/fwlink/?LinkID=189408&clcid=0x409

Windows Server 2008 R2
http://go.microsoft.com/fwlink/?LinkID=189409&clcid=0x409

Upgrade from BizTalk 2006 to 2010

2011-09-06T10:59:00.000+08:00

Few useful articles for Upgrade from BizTalk 2006 to 2010:-

C# Batch Replace with encoding

2011-07-09T23:16:00.000+08:00

Here is the code I wrote for batch replace with GB2312 encoding:-

var dir = new DirectoryInfo(@"D:\hgb");
string strFileName;
string content;
var encoding = Encoding.GetEncoding(936);

foreach (var f in dir.GetFiles("*.htm", SearchOption.AllDirectories))
{
strFileName = f.DirectoryName + @"\" + f.Name;
using (StreamReader reader = new StreamReader(strFileName, encoding))
{
content = reader.ReadToEnd();
content = Regex.Replace(content, "font size=-1", "font size=48");
}

StreamWriter writer = new StreamWriter(strFileName, false, encoding);
writer.Write(content);
writer.Close();
}

10175 Lab Correction (SharePoint 2010 Application Development)

2011-07-07T14:23:00.015+08:00

General

Trainer MOC page number may different with student page number.

Lab 2

There are spacing issues in Exercise 3 Task 4 PowerShell Commands, All *SP Solution commands should be corrected as *SPSolution, e.g. Add-SP Solution should be corrected as Add-SPSolution.
The -GAC Deployment should be corrected as -GACDeployment

Lab 4

We should remove the where clause in unfilteredJobs LINQ query from Exercise 3 Task 5 Step 12 then only step 21 All Jobs will be displayed correctly.

Lab 5

The web.config never changed although the lab works as expected, this issue is caused by access denied, we have to fix it in Powershell:
$svc = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$svc.RemoteAdministratorAccessDenied = $False
$svc.Update()
We should reference to System.Configuration to use the ConfigurationManager in Exercise 3 Task 2 Step 2.

Lab 6

When you are instructed to input any object or variable name with spacing, please remove the space, example:- HR Training Management has to be input as HRTrainingManagement.

Lab 8

There is a list "Mashup" is hidden, we can display it from SharePoint Designer All Files section.

Lab 10

Please create a site page named Training for Exercise 2 Task 9 Step 3
You may need to click the Share Documents few times to let the video thumbnails to display accordingly.

Lab 13

The Microsoft Office is not activated in Hyper-V image and not able to perform Exercise 2 Task 1, workaround is to copy the Metro theme from C:\Program Files (x86)\Microsoft Office\Document Themes 14\Metro.thmx to Desktop

Unsubscribe Tweeter from Maxis

2011-02-28T09:52:00.001+08:00

Recently test with Tweeter to try out Mobile notification, after testing try to unsubscribe but failed few times by sms STOP to 28933 Maxis Tweet2Me service.

Finally I can unsubscribed successfully by using this command:-

STOP TWEET2ME to 28933.

Receive the response from Maxis:-
RM0. Your Twitter SMS subscription has been terminated.

Javascript getElementByTagName XML with namespace prefix

2011-01-12T16:43:00.003+08:00

How to use Javascript to retrieve XML data with namespace prefix?

Example:-

We can use getElementByTagName together with namespace prefix to access the element value:-
xmlDoc.getElementsByTagName("ns0:POID")[0].firstChild.nodeValue ;

Whole code to access the XML data with namespace prefix via getElementsByTagName:-


<html><head>

<script language="javascript">

function getXML()

{

var xmlstring = "<?xml version='1.0' ?><ns0:PO xmlns:ns0='http://www.abc.com/PO'><ns0:POID>PO123</ns0:POID><ns0:CustomerID>ABC007</ns0:CustomerID></ns0:PO>";

if (window.DOMParser)

  {

  parser=new DOMParser();

  xmlDoc=parser.parseFromString(xmlstring,"text/xml");

  }

else // Internet Explorer

  {

  xmlDoc=new ActiveXObject("Microsoft.XMLDOM");

  xmlDoc.async="false";

  xmlDoc.loadXML(xmlstring); 

  } 

var x = xmlDoc.getElementsByTagName("ns0:POID")[0].firstChild.nodeValue ;

alert(x);

}

</script>

</head><body onload="javascript:getXML()">

</body></html>

<?xml version='1.0' ?>
<ns0:PO xmlns:ns0='http://www.abc.com/PO'>
<ns0:POID>PO123</ns0:POID>
<ns0:CustomerID>ABC007</ns0:CustomerID>
</ns0:PO>

BizTalk 2009 Redistributable CAB files

2010-08-13T10:46:00.004+08:00

BizTalk 2009
What's new webcast - https://www106.livemeeting.com/cc/microsoftevents/view?id=BTS18AL&role=attend&pw=FGE399

Q&A

Windows XP

32-bit: http://go.microsoft.com/fwlink/?LinkId=137256
64-bit: http://go.microsoft.com/fwlink/?LinkId=137262

Windows Server 2003

32-bit: http://go.microsoft.com/fwlink/?LinkID=137166
64-bit: http://go.microsoft.com/fwlink/?LinkId=137260

Windows Vista

32-bit: http://go.microsoft.com/fwlink/?LinkId=137259
64-bit: http://go.microsoft.com/fwlink/?LinkId=137263

Windows Server 2008

32-bit: http://go.microsoft.com/fwlink/?LinkID=137254
64-bit: http://go.microsoft.com/fwlink/?LinkId=137261

T-SQL Split

2009-11-18T13:04:00.003+08:00

I still have seen developer asking me how to Split in SQL Server, although we can enable CLR Integration in SQL Server, here is the classic one from SQL Server Magazine that I have used since SQL Server 2000 and it is very powerful, Treat Yourself to Fn_Split() - This UDF lets you emulate VB's Split() function and parameter arrays

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can抰 find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0 
END
END

END

RETURN
END
GO

-- Sample Code for Using Fn_Split() in Pubs to Emulate a Parameter Array

Use Pubs

CREATE  PROCEDURE procPubsTitleView

@sAuthors varchar(8000)
AS
SELECT title, au_lname, price FROM Pubs.dbo.titleview
JOIN dbo.fn_Split(@sAuthors, ',') 
ON au_lname = value
RETURN
GO

Is BizTalk dead?

2009-10-31T14:15:00.004+08:00

One of the frequently asked question - Is BizTalk Dead?

The question arises with the announcement of Dublin able to makes long running workflow implemented in Windows Workflow Foundation (WF) persisted in SQL Store, sounds like Biztalk.

More details at A First Look at WF 4.0, “Dublin”, and “Oslo” http://msdn.microsoft.com/en-us/library/dd200919.aspx

I have implemented BizTalk projects in multinational organizations with industry standard like SWIFT, my personal opinion is, no doubt WF, Dublin and Oslo are evolving technologies in application development, but BizTalk still playing as a strong leader in Integration technologies for EAI and B2B, reason being are

B2B industry standard like EDI, SWIFT, RosettaNet contains hundred to thousand schemas and only available in BizTalk as Accelerators. I do not think developers can create all these schema parser easily within short time frame
BizTalk has many built in Network, Data and Application Adapters like MQ, MSMQ, SAP, Oracle, FTP, and etc and all these can be easily tested by BizTalk engineer, without BizTalk, this will need to develop by developers, I believe this is not a difficult task but also not a straight forward task for developer, and how to unit test all these protocol adpaters required sophisticated knowledge, is our developer have all these knowledge to work with different kind of network, data and application protocols?
BizTalk Business Activity Monitoring (BAM) is a good integration analytical tool can be easily implemented by Business Analysts, Administrators and Developers, without BizTalk, can developers built this tool?
Last but not least, from the business point of view, BizTalk has been sold to many multinational organizations and implemented successfully, will Microsoft stone themselves by killing BizTalk?

I foresee BizTalk will continue evolve by combining WF, Dublin and Oslo instead of deprecated, and this is clear in BizTalk roadmap:-
BizTalk Server and "Oslo" and .NET
"Oslo" is the codename for Microsoft’s forthcoming modeling platform. Modeling is used across a wide range of domains and allows more people to participate in application design and allows developers to write applications at a much higher level of abstraction. "Oslo" delivers a new integrated platform for connecting across modeling domains, including a new "Oslo" modeling tool, an "Oslo" modeling language, and an "Oslo" repository. As we gathered feedback from BizTalk customers, they indicated they would prefer to take a disciplined, evolutionary path to adopting some of these newer platform technologies. We have thousands of customers that have deployed mission-critical applications on top of our BizTalk Server architecture; they want to decide for themselves when to move to newer versions of the platform.
Therefore, it’s an important guiding principle to our planning efforts that we preserve our customers existing investments in their BizTalk Server infrastructure. In fact, you won’t need to upgrade BizTalk Server to take advantage of "Oslo" – current BizTalk Server 2006 R2 or BizTalk Server 2009 customers can benefit from "Oslo" by being able to leverage and compose existing services into new composite applications. BizTalk Server today provides the ability to service enable LOB systems or trading partners as web services (using WCF supported protocols), which can be composed with the "Oslo" modeling technologies.
This principle applies to advances in the .NET Framework as well, such as Windows Workflow Foundation (WF). In response to customer feedback, we are committed to continued support for BizTalk Server’s XLANG orchestration technology - the existing BizTalk orchestration engine. Additional support for WF will be prioritized for the coming releases based upon customer demand and scenarios.

Developing Business Process and Integration Solutions Using Microsoft BizTalk Server

2009-10-29T22:00:00.005+08:00

BizTalk Server (BTS) is a great product from Microsoft and it solves a lot of integration challenges, I have shared this technology with patterns and best practices to many experience BizTalk engineers and developers at Kuala Lumpur.
Course 2933A:
Developing Business Process and Integration Solutions Using Microsoft BizTalk Server 2006
Module 1: Introduction to BizTalk Server 2006
Module 2: Creating Schemas
Module 3: Creating Maps
Module 4: Deploying a BizTalk Project
Module 5: Routing BizTalk Messages
Module 6: Creating Pipelines
Module 7: Integrating with Adapters
Module 8: Creating a BizTalk Orchestration
Module 9: Automating Business Processes
Module 10: Creating Transactional Business Processes
Module 11: Deploying and Managing BizTalk Applications
Module 12:Integrating with Web Services
Module 13: Integrating Business Rules
Module 14: Enabling Business Activity Monitoring
Module 15: Integrating Trading Partners
http://www.metricsthatmatter.com/iversona54
https://www.metricsthatmatter.com/infotrek12

Connect WSS internal database Microsoft##SSEE using SSMS

2009-08-25T12:44:00.005+08:00

Default Windows SharePoint Services internal database can be connected successfully using this connection from SQL Server Management Studio (SSMS):-

\\.\pipe\mssql$microsoft##ssee\sql\query

1) Start SSMS and key in the connection

2) We can access the Microsoft##SSEE now!

ASP.NET populates Excel data without Office installed - DSOFile

2009-07-25T16:05:00.004+08:00

Do you have any requirements to generate online Excel form from server without Office installed?

Why not download DSOFile from Microsoft website?

The Dsofile.dll files lets you edit Office document properties when you do not have Office installed

Sample Code in C#:-

DSOFile.OleDocumentPropertiesClass d = new DSOFile.OleDocumentPropertiesClass();
string file = Server.MapPath(@"Files\I9.xls");
d.Open(file, false, DSOFile.dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);

foreach (DSOFile.CustomProperty cp in d.CustomProperties)
{
if (cp.Name == "Name")
{
object objValue = "YB test on " + DateTime.Now;
cp.set_Value(ref objValue);
}
}

d.Save();
d.Close(true);

System.Runtime.InteropServices.Marshal.ReleaseComObject(d);

Response.Write(file + " [Name] field has been updated successfully");

Offshore Development with Agile Software Process

2009-07-25T15:54:00.003+08:00

No requirements can get 100% in software development, the better Software Development Process is Agile compared to Waterfall, especially in Offshore Development Business.

This article from Martin Fowler is a good one:-

Using an Agile Software Process with Offshore Development

Documentation in Agile is much lesser compared to Waterfall, but the most important in Agile is user participation.

Community Launch for Heroes

2008-07-15T13:18:00.003+08:00

I have presented LINQ to SQL Introduction on 28 June 2008 (Saturday) at Microsoft Auditorium, details at http://www.microsoft.com/malaysia/events/communityheroes/

BizTalk WCF Oracle Dynamic Send Port

2008-07-11T13:46:00.005+08:00

How to implement WCF Oracle Dynamic Send Port? There is an example in Microsoft BizTalk Adapter 3.0 for Oracle Database Documentation:-

Request2(BTS.OutboundTransportType)="WCF-Custom";
Request2(WCF.Action)="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/ACCOUNTACTIVITY/Select";
Request2(WCF.BindingType) = "oracleDBBinding";
Request2(WCF.UserName)="SCOTT";
Request2(WCF.Password)="TIGER";
SendPort(Microsoft.XLANGs.BaseTypes.Address)="oracledb://adapdoc/";

If you deployed the code may hit the error:-
Event Type: Error
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5754
Date: 7/3/2008
Time: 11:21:31 AM
User: N/A
Computer: AMSDC1-S-7538
Description:A message sent to adapter "Oracle" on send port "OracleDynamicSendPort_1.0.0.0_OracleDynamicSendPort.OracleSend_Port_DynamicOracle_beec5c3523602cdb" with URI "oracledb://TNSNAME/?PollingId=1" is suspended. Error details: Value cannot be null.Parameter name: s MessageId: {A7F0A852-C315-4521-9B63-E0BFE221F4AE} InstanceID: {FF53A23E-3F21-4098-A02B-8381E6C3D851}

The solution is to add additional line in the code above:-

SendPort(Microsoft.XLANGs.BaseTypes.TransportType) = "WCF-Custom";

Then this exception will be resolved.

NOTE: to use the WCF Oracle Dynamic Send Port, the Adapter's default send handler, i.e. the BizTalk hosts must be running.

New Web Site created

2006-04-06T09:37:00.001+08:00

Just created a new website http://www.kuala-lumpur-guide.com/ to share what is Kuala Lumpur included dark side information

WebSphere MQ Samples

2006-02-20T18:45:00.001+08:00

Sample code for accessing Webphere MQ in Microsoft C#, VB6 and VB.NET

http://www.capitalware.biz/mq_code_csharp.html

Project Server

2006-02-14T19:44:00.002+08:00

I implemented a windows sharepoint services + SQL Server 2000 Reporting Services project in Australia last year, I used Smart Part http://www.microsoft.com/belux/nl/msdn/community/columns/u2u/smartpart.mspx developed for Sharepoint Web Parts, the result is good, when combining with SQL Reporting Services can become a impressive BI portal.

Anyway the new SQL Server 2005 has been released last year after end of my Project, everybody have to pick up the new skill asap to keep the competency :)

SQL Black Belt Advance Articles

2005-09-09T12:15:00.001+08:00

Recently just involved in SQL Server 2000 Reporting Services Business Intelligence Development, found out a must read articles for any one who would like to master in SQL Server, the web site is at http://databasejournal.com/article.php/1459531 by William E. Pearson, III

MSDAORA Oracle upgrade connectivity Problem

2005-04-13T16:58:00.001+08:00

Oracle client always have connectivity problem to the Oracle database server due to the improper configuration in C:\Oracle\Ora81\network\ADMIN\TNSNAMES.ORA (Assume you did not change the default install folder)

Sometimes you may experience all the configuration are fine, you also can use TNSPING and SQLPLUS to access the ORACLE server without problem, but your Application using MSDAORA provider is facing problem to access the Oracle Server, you may get the error like:-
Error while trying to retrieve text for error ORA-03121
This is usually happen after you upgraded your Oracle client, you have restarted your machine, all the TNSNAMES.ORA entries are correct, everything is fine except your application, either ASP, ASP.NET, VB.NET or any application that use MSDAORA provider.
Why the problem happen is, after you upgraded your Oracle client, your MSDAORA still pointing to the old Oracle version, that's why the problem occurs.
To resolve this problem, goto your machine MSDAORA home C:\Program Files\Common Files\System\Ole DB folder, you will see there are some .reg files:-
mtxoci81_winnt.reg
mtxoci81_win2k.reg
mtxoci7x_winnt.reg
mtxoci7x_win2k.reg
If your Oracle client is 8.1 with Windows 2000 and above, double click the mtxoci81_win2k.reg file to register the new registry for the newer client. Then restart your machine, the problem will be solved automatically.
The content of the mtxoci81_win2k.reg is
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
The nice thing about Oracle is, once the Oracle problem is resolved, you will enjoy with Oracle.

ADO filter with NULL value

2005-04-13T10:45:00.001+08:00

NULL value in database means no storage in the cell, if you use ADO to query the data and you would like to filter the NULL value, it will be a challenge for a lot of programmers.

Let's see the following code:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ReportsTo FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
do while not rs.eof
rs.movenext
loop
rs.close

The code above is retreiving data from MS SQL Server Northwind database, it tries to get the employeeId and superior employee Id (reportsTo column).

Let's say the requirement is those employee without superior display in one panel and those employee with superior display in another panel.

I believe no one want to query the database two times by using SQL query "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NULL" and "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NOT NULL", so we will query one time from the database by re-using the above code.

You will add another code before the while loop:-
rs.filter = "ReportsTo IS NULL"

Immediately you will get this error:-
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

The reason being is because ADO Filter does not support "IS" and "NULL" statement, even you tried to use the filter as ISNULL(ReportsTo, 'N/A') = 'N/A' also get the same problem.

The only solution to this is back to the SQL query, change the query as
SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees

Then the rs.filter use ReportsTo = 'N/A' will solve the problem, the whole code is shown below:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
rs.Filter = "ReportsTo = 'N/A'"
do while not rs.eof
' -- Display Data in Superior Panel
rs.movenext
loop
rs.Filter = "ReportsTo <> 'N/A'"
do while not rs.eof
' -- Display Data in Subordinate Panel
rs.movenext
loop
rs.close

Cheers!

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

2005-03-29T15:35:00.001+08:00

A lot of programmers coded the SQL query in dynamic SQL either in presentation layer or inside stored procedure, it has created a lot of problems in performance, security, maintenanbility, network and memory. More in The Curse and Blessings of Dynamic SQL.

From today onwards, I will compose a FAQ how to convert dynamic SQL to proper stored proc.

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

The first FAQ I always heard is "I have a query based on a parameter, if the parameter is ALL, there will be no filter, if the parameter is a comma delimeter string (CSV), the query result will be filtered by the multiple values string in CSV format.

It has 2 challenges here:-
1) How to split the comma delimeter string
2) How to filter the result when it is not 'All'

It can be achieved easily by using dynamic SQL, the sample below is using SQL Server Northwind database with querying from SQL Query Analyser:-

declare @strSQL varchar(1000), @param varchar(100)
set @param = 'All'
set @param = '1,2,3'
set @strSQL = 'select * from employees'
if @param <> 'All'
set @strSQL = @strSQL + ' where employeeid in (' + @param + ')'
exec(@strSQL)

Let's see the 1st challenge, to split the CSV string, we can use the split function provided from Microsoft SQL Server Magazine Treat Yourself to Fn_Split():
This UDF lets you emulate VB's Split() function and parameter arrays

Then you can split the string by using the query easily:-

SELECT value FROM dbo.fn_split(@s, ',')

To filter either 'All' or not 'All', we have to use the power of "OR" operator, then the above dynamic SQL can be converted to proper SQL query as below:-

declare @s varchar(100)

set @s = '1,2,3'
--set @s = 'All'

select * from employees
where (@s <> 'All' AND employeeid in (SELECT value FROM dbo.fn_split(@s, ',')))
OR (@s = 'All')

Let's give me FIVE!

Microsoft JET Database Engine error '80004005' Unspecified error

2005-03-24T13:24:00.001+08:00

When you try to access JET data source like MS Access or MS Excel from your Active Server Pages (ASP), you may get this problem

Microsoft JET Database Engine error '80004005'

Unspecified error

You have set the permission of your IUSR_ComputerName user to full control, may be you already give full control permission to everyone, but the problem still occur.

You also reinstalled your JET with latest service pack, rebooted the server, but the problem still exists.

The problem actually is you have set the environment variables TMP and TEMP to invalid temporary folder.

Please access the Microsoft Knowledge base for more details:-
PRB: "Disk or Network Error" or "Unspecified Error" Returned when Using Jet
http://support.microsoft.com/kb/251254

After set the environment variables correctly, please remember to restart your computer. If it still can not work, you may need to change the temporary folder or set the temporary folder have EVERYONE full control.

Good luck!

IIS Log Analysis with Log Parser 2.2

2005-03-22T17:35:00.001+08:00

How to analyze your website usage? How many users access your web site? Where is the top 10 URL in your web application? There are many questions you may ask after you deployed your web application into production web server.

You may think to write a small program to read from your web server log files, it will not be simple, you have to spend sometime to study web server log file, programming it open the log file by date, aggregate the data, filter the data, order the data, accept input from users and etc..

Wait a minute, there is a better way to do this: -

Thanks to Microsoft, they have provided a very powerful log analysis tool - Log Parser, now it is version 2.2, you can download it at http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en. There is also an unofficial Log Parser support website at http://www.logparser.com/

The beauty part of the Log Parser is it combines the power of SQL query to query the data like RDBMS. It can be output to chart, grid, XML, SQL and other formats.

I have execute the Log Parser in my IIS web server to trace my web pages usage on March 2005 by using the following log parser command plus SQL query:-

logparser -i:IISW3C -o:Datagrid
"SELECT
date,
cs-uri-stem,
count(*) AS ACCESS_PER_DAY
FROM
c:\winnt\system32\logfiles\w3svc1\ex0503*.log
WHERE
cs-uri-stem LIKE '/myreport/%.asp'
GROUP BY
date,
cs-uri-stem
ORDER BY 
DATE"

It gives me the output in Grid:-

date       cs-uri-stem     ACCESS_PER_DAY
2005-03-08 /myreport/A.asp              1
2005-03-08 /myreport/B.asp              2
2005-03-08 /myreport/A.asp              6

Cool!

Disable Control+C in javascript

2005-03-18T14:01:00.000+08:00

How to disable control+C in your web page to prevent user simply copy your text? Here is the code:-

<html>
<head>
<script language="javascript">
<!--
function Disable_Control_C() {
var keystroke = String.fromCharCode(event.keyCode).toLowerCase();

if (event.ctrlKey && keystroke == 'c') {
event.returnValue = false; // disable Ctrl+C
}
}
//--!>
</script>
</head>
<body onkeydown="javascript:Disable_Control_C()">
Hello World!
</body>
</html>

NOTE: You may need to disable the right click and menu bar to prevent user to copy from your text.

Convert dynamic SQL to stored procedure by using CASE

2005-03-16T18:04:00.001+08:00

I always access other programmer code, I saw the code interesting and challenging today - the code is in ASP VBScript + dynamic SQL, the purpose of the code is to generate the SQL query string based on web page querystring, the code is as below:-

StrSQL = "select * from fact_drive_failures2 where day_id = " & request("day_id")
If request("floor") <> "" Then
StrSQL = strSQL & " and floor_id = " & request(“floor_id”)
End If
I believe this is a very common practice for many programmers. With the dynamic SQL string with the ASP code, it has many pitfalls in security, performance, permission, network traffic and many more disadvantages...

I convert this code to MS SQL Server stored procedure:-

CREATE PROCEDURE dbo.GET_DATA
(
@pn_DayId int,
@pv_FloorId varchar(1000)
)
AS
Select * from fact_data
where day_id = @pn_dayid
and floor_id =
case @pv_Floor = ‘’ then floor_id
else @pv_floor end

Now the stored procedure can be called by front-end application without worrying the string concatenation, easier to maintain, better security and the performance is much better.

My blog is alive!

2005-03-16T17:44:00.000+08:00

Finally, my blog is alive!

I have been in software devlopment in Microsoft and Unix with more than 10 years experience, I have trained many software developers either during my work, speaking in technical conference and IT training center, it is glad to share my tips and tricks online here.

Let's start!