Elton's Ark

work-around on PLS-00103 error

Elton — Mon, 31 Jul 2006 04:02:00 GMT

If a stored procedure has a default value parameter defined in Oracle (8i) backend and you want to call this stored procedure using ADO with MSDAORA provider, it may fail and return a error message for some scenarios, for example:

Stored Procedure:

##########################

CREATE OR REPLACE PROCEDURE SP001( InParam IN NUMBER DEFAULT 25, OutParam OUT NUMBER) AS

BEGIN

OutParam := InParam;

END SP001;

##########################

Client side program:

##########################

Sub Main()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim vExpected As Variant
Dim vActual As Variant
Dim iInParamVal As Integer
Dim iOutParamVal As Integer

cn.Open "Provider=MSDAORA.1;Password=******;User ID=******;Data Source=ORA81EN"

On Error GoTo ERR_HANDLER

cmd.ActiveConnection = cn
cmd.CommandText = "SP001"
cmd.CommandType = adCmdStoredProc

' Setup input param
Set param = cmd.CreateParameter(stInParamName, adInteger, adParamInput, 4, Empty)
cmd.Parameters.Append param

' Setup output param
Set param = cmd.CreateParameter(stOutParamName, adInteger, adParamOutput, 4)
cmd.Parameters.Append param

cmd.Execute

vExpected = 25
vActual = cmd(0).Value

If vExpected <> vActual Then MsgBox "compare failed"

cn.Close
Exit Sub

ERR_HANDLER:
MsgBox Err.Number & " <-> " & Err.Description
Resume Next
End Sub

##########################

After execute the client program, an error message returned:

-2147217900, ORA-06550: line **, column **:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

. ( ) , * @ % & = - + < / > at in mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling

This case works fine with SQL Server backend or use MSDASQL-ORA driver with Oracle backend, but it fails with MSDAORA provider. Then how could we do if want to use the default value of the stored procedure in this scenario?

The solution (or work-around) is to re-define the stored procedure and transpose the input parameter and output parameter, just like this:

##########################

CREATE OR REPLACE PROCEDURE SP001(OutParam OUT NUMBER, InParam IN NUMBER DEFAULT 25) AS

BEGIN

OutParam := InParam;

END SP001;

##########################

In client program, only append the output parameter and execute the COMMAND to call the stored procedure. Then the case will run successfully.

The reason I guess may be this:

In PL/SQL, if you want to call this stored procedure, the code should be like "SP001(OutParam => variable)", variable is used to store the output value. After we transpose the input and output parameter, the calling code could be like "SP001(variable)". I guess MSDAORA provider compose an invalid SQL clause. (maybe use "=" instead of "=>")

Got an error today: EventType clr20r3, P1...

Elton — Mon, 06 Mar 2006 11:24:00 GMT

Today after I compiled my winform program in Windows XP with .NET Framework 2.0, I copied the assembly to another Windows 2003 ENT Server. When I run this program in W2k3 server, nothing responded, and following message is created in Application Events...(read more)

Sort the objects in ArrayList

Elton — Tue, 21 Feb 2006 21:51:00 GMT

It should be a frequent scenario to sort the order of objects in a collection, such as ArrayList. ArrayList in c# provide a method named Sort(). But we need to let ArrayList know the detailed sorting algorithm.

There are two means to implement this:

1. Make the target class inheritted from IComparable interface, and provide CompareTo() method. IComparable interface comes from System namespace.

For example:

    class UITestCase: IComparable
    {
        private int _sequenceno = 0;
        public int SequenceNo
        {
            get
            {
                return _sequenceno;
            }
            set
            {
                _sequenceno = value;
            }
        }
......

        // implement the IComparable interface, define the algorithm here
        public Int32 CompareTo(object next)
        {
            UITestCase nextCase = (UITestCase)next;

            return (this.SequenceNo.CompareTo(nextCase.SequenceNo));
        }
    } // class end.

When using ArrayList to store a group of UITestCase objects, we could simply call ArrayList.Sort() to sort all objects in it.

                // get all test case entities, then make sort order
                ArrayList caseList = new ArrayList();
                foreach (DirectoryInfo dir in nodeDirInfo.GetDirectories("*.tc"))
                {
                    UITestCase testcase = new UITestCase(dir.FullName);

caseList.Add(testcase);
}

                if (caseList.Count > 0)
                {
                    caseList.Sort();
                }

2. Create a class inherited from IComparer interface, then implement Compare(object x, object y) method just like IComparable's CompareTo(object next) method.

3. Write a special method to sort the order of a given ArrayList like what we will do in C language, I prefer this one because I think the codes writing in above two methods are not straightforward enough.

the LabelEdit property in TreeView class

Elton — Fri, 20 Jan 2006 19:35:00 GMT

Today I want to add the RENAME feature to maintain the Test Case Tree in StepRecorder, which exactly like the operation in Windows Explorer. After user right click a node, the selected node turns to Edit Mode, and user could type the new name in the edit box. System will ask user to reinput the new name if some invalid characters are entered such as "!"/","/"@"... and the selected node will remain in its Edit Mode for user's re-input.

To carry out this function, TreeView.LabelEdit property, TreeNode.BeginEdit() and TreeNode.EndEdit(bool) will be used as well as the TreeView.AfterLabelEdit event.

Here is a demo code from MSDN and I think it greatly show how these property/method/event work together.

/* Get the tree node under the mouse pointer and
   save it in the mySelectedNode variable. */
private void treeView1_MouseDown(object sender,
System.Windows.Forms.MouseEventArgs e)
{
   mySelectedNode = treeView1.GetNodeAt(e.X, e.Y);
}

private void menuItem1_Click(object sender, System.EventArgs e)
{
   if (mySelectedNode != null && mySelectedNode.Parent != null)
   {
      treeView1.SelectedNode = mySelectedNode;
      treeView1.LabelEdit = true;
      if(!mySelectedNode.IsEditing)
      {
         mySelectedNode.BeginEdit();
      }
   }
   else
   {
      MessageBox.Show("No tree node selected or selected node is a root node.\n" +
         "Editing of root nodes is not allowed.", "Invalid selection");
   }
}

private void treeView1_AfterLabelEdit(object sender,
         System.Windows.Forms.NodeLabelEditEventArgs e)
{
   if (e.Label != null)
   {
     if(e.Label.Length > 0)
     {
        if (e.Label.IndexOfAny(new char[]{'@', '.', ',', '!'}) == -1)
        {
           // Stop editing without canceling the label change.
           e.Node.EndEdit(false);
        }
        else
        {
           /* Cancel the label edit action, inform the user, and
              place the node in edit mode again. */
           e.CancelEdit = true;
           MessageBox.Show("Invalid tree node label.\n" +
              "The invalid characters are: '@','.', ',', '!'",
              "Node Label Edit");
           e.Node.BeginEdit();
        }
     }
     else
     {
        /* Cancel the label edit action, inform the user, and
           place the node in edit mode again. */
        e.CancelEdit = true;
        MessageBox.Show("Invalid tree node label.\nThe label cannot be blank",
           "Node Label Edit");
        e.Node.BeginEdit();
     }
     this.treeView1.LabelEdit = false;
   }
}

How to drag and drop the treenode in TreeView Controls

Elton — Fri, 16 Dec 2005 05:57:00 GMT

Found a great article on this, and I will investigate it latter.

http://www.codeproject.com/cs/miscctrl/TreeViewDragDrop.asp

How to solve the issue of "The server instance specified was not found. Please specify the server's address and port."?

Elton — Tue, 22 Nov 2005 17:52:00 GMT

Today, when I want to use "stsadm -o addwppack..." command to add a web part into the team website, an error message returned

"The server instance specified was not found. Please specify the server's address and port"

Fortunatelly, I seeked for the help from Mohammed's blog:

Top 3 Reasons for the "The server instance specified was not found. Please specify the server's address and port." Error

My issue is the 1st reason that we have installed sharepoint SP1 build, but didn't upgrade all the virtual server, after I run following command, the web part could be added successfully.

stsadm -o upgrade -forceupgrade

A good article on how to create the Area Templates in SPS2003

Elton — Thu, 17 Nov 2005 13:43:00 GMT

Today, Jeffrey asked me to find how to create Area Template and it will be used in his new team site. Fortunately, I find an article with detail information.

Working with Sharepoint Portal Server 2003 Area Templates

How to display Visio file in SharePoint web part

Elton — Fri, 21 Oct 2005 17:15:00 GMT

I have an idea recently that I want our project web site could display Office Visio file and Office Project .mpp file.

Visio attract me very much for its powerful diagram elements, it could centralize my thinking in a diagram and it is so easy understanding and abstractive in every phase of project life cycle.

For .mpp file I want to replace normal list web part with gantt diagram. Normally we maintain a task list in SharePoint account for the project schedule, but I'd rather to use project file to plan and monitor the project status, because task list is not straitforward for task dependency and task hierarchy, but gantt diagram is a good choice to me.

I found a method to display visio file in SharePoint web part and I don't know if it is the only solution or not now.

Visio 2003 Viwer active-x control must be installed in client machine even if it has office 2003 installed, before client user browse the sharepoint web page containing visio web part (I don't like this, but in vain). This active-x control is an add-in of IE to display visio diagram. It could be found at http://www.microsoft.com/downloads/details.aspx?FamilyId=3FB3BD5C-FED1-46CF-BD53-DA23635AB2DF&displaylang=en.

In SharePoint you could drag & drop a Content Editor web part onto a web part page, then click "Source Editor" button to imput following:

Red color string indicates the visio file's location in SharePoint. Other parameters could be set according to its effect in IE visio viewer.

The result doesn't meet my idea completely, anyway it is a solution for displaying the visio file in SharePoint.

Useful link: http://www.wssdemo.com/Pages/visio.aspx?menu=Articles.

Learning Notes - Extreme Programming (1)

Elton — Sat, 17 Sep 2005 15:56:00 GMT

XP is a new development model which I first heard at last year. Recently I had a book named "Extreme Programming Adventures in C#" and I am a bit interested in it and decide to write down what I learned and what I thought.

Today, suddenly when I am smoking a question jump out of my brain: most of the human being inventions include computer science are based on the purpose of decreasing the heavy workload and making our life more free and happy, but why most people still work so hard and with heavy pressure[:)]?

XP team is composed with two roles which are Customers and Programmers. The first role provides the software requirements or features and the second role is accountable for making how to realise all the features. A person may belong a Customer or Programmer or both.

XP development process is a series of iteration in which only simple function is implemented and tested. This character is something similar with RUP model. The key point is how to break down the features into "stories" which is the objective of one iteration. (Story is a new item for me in here)

Often I have some idea to develop something by myself but I can't get whole picture of what I will make. XP make me have another idea that don't hesitate to go ahead before everything is planned, just do what I think at that point.

How to take advantage of office components in sharepoint portal server - spreadsheet

Elton — Mon, 30 May 2005 19:23:00 GMT

My boss confirmed it is acceptable that our website's users need MS Office 2003 installed in their local machine to interact with the Office Components in the web page.

I found the solution to avoid users modify spreadsheet data directly.

First, let's see where is the data source of the web part. After I drag spreadsheet web part to one of web part zones, we can set data source from "Data Connect..." button to select SQL Server or other type. In my scenario, I use Spreadsheet Add-in tool (http://www.microsoft.com/downloads/details.aspx?FamilyID=dc3d8474-d960-4d14-a9df-9024e39f5463&DisplayLang=en) to create data files which is required by the web part. This add-in tool generate three files which is the spreadsheet's data source files and I put them into one of the document library in SPS (add-in tool can help you finish this operation).

Second, what these three files are and how they control the web part?

1. Spreadsheet file in XML format, spreadsheet's data contents are saved here, included data format, formula etc.

2. Solution specification file, discribes where is the spreadsheet file listed above saved, connection interfaces definition and other many settings.

3. .dwp file, just like normal web part, it specifies the assembly and properties.

To create the relationship between the web part and these three files, we just click the right arrowhead of the spreadsheet web part, and select "Modify Shared Web Part", set "Solution Specification File" property with the second file's hyperlink in SPS.

To avoid users from directly modifiing the contents of the spreadsheet web part, we need add a line in Solution Specification File to lock the page, just like follows:

<WebPartSettings>
<XMLSSFileLocation>/myDocLib/.....</XMLSSFileLocation>

......

Want to know other detail settings about Solution Specification File, please came http://www.microsoft.com/downloads/details.aspx?FamilyId=29E1FC0C-7627-45ED-AB75-1C5080F6AC1D&displaylang=en.

After refresh the page, we will find that the spreadsheet web part is basicly same with what you see in Excel tool. Although we can change the data in our personal view, the data changed really is NOT committed to the data source file.

When we want to modify the web part's contents, just edit the first file.

It is cool, yes?