Month: October 2008

JavaScript XML IE and FireFox

Once again I’m doing a lot of JavaScript, but with XML.  I have also been tasked with getting this JS to work in FireFox and IE. 
 
Some of the thing I’m having to do are the following:
Load XML
Create new Node/Elements
Edit Attribute
Find Node/Element (based on XPath)
 
//This works with IE and FireFox
function LoadXml(xml) {
 var xmlDoc = null;
 if (window.ActiveXObject) {
  xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
  xmlDoc.async = "false";
  xmlDoc.loadXML(xml);   
 } else if (document.implementation && document.implementation.createDocument) {
  parser = new DOMParser();
  xmlDoc = parser.parseFromString(xml, "text/xml");
 }
 return xmlDoc;
}
 
function FindNodeOnElementAndAttribute(xmlDoc, elementName, attributeName, attributeValue) {
 var node
 var xPath = "//" + elementName + "[@" + attributeName + "=’" + attributeValue + "’]";
 
 if (window.ActiveXObject) {
   node = xmlDoc.selectSingleNode(xPath);
  } else if (XPathEvaluator) {
  var xpe = new XPathEvaluator();
  var nsResolver = xpe.createNSResolver(xmlDoc.ownerDocument == null ? xmlDoc.documentElement : xmlDoc.ownerDocument.documentElement);
  var results = xpe.evaluate(xPath, xmlDoc, nsResolver, XPathResult.FIRST_ORDERED_NODE_TYPE, null);
  node = results.singleNodeValue;
 }
 return node;
}
 
Resources:
XML DOM Create Nodes (W3schools.com)
 
XPath Axes(W3schools.com)
 
XML DOM Examples
 
Parsing the XML DOM
 
XML DOM Load Function
 
DevGuru – XMLDom Documentation
 
Using JavaScript to retrive XML in IE and FireFox
 
FireFox – Converting XMLObject to String
 
selectSingleNode and FireFox
 
Parsing XML in JavaScript
 
How do I load an XML formatted string into the DOM?
 

Problem retrieving string value of XML document in Firefox

 

SQL Function for Parsing or Spliting String

I have a table that stores a delimited string in a column.  I need this string to be in a table format.  So instead of trying to create this myself, I decided to Google and see if I could find any SQL Server function that could parse or split a delimited string into a table.   I found many good example and below in resource are links to the better results that I found.
 
This is probably the best one I found.  Since it’s the most complex, it has to be the best. Right?
 
—Clayton Groom
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create   function fn_ParseText2Table
(
@p_SourceText  varchar(8000)
,@p_Delimeter varchar(100) = ‘,’ —default to comma delimited.
 )
RETURNS @retTable TABLE
(
  Position  int identity(1,1)
,Int_Value int
,Num_value Numeric(18,3)
,txt_value varchar(2000)
)
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (Clayton_Groom@hotmail.com)
Posted to the public domain Aug, 2004
06-17-03 Rewritten as SQL 2000 function.
Reworked to allow for delimiters > 1 character in length
and to convert Text values to numbers
********************************************************************************
*/
BEGIN
DECLARE @w_Continue  int
  ,@w_StartPos  int
  ,@w_Length  int
  ,@w_Delimeter_pos int
  ,@w_tmp_int  int
  ,@w_tmp_num  numeric(18,3)
  ,@w_tmp_txt   varchar(2000)
  ,@w_Delimeter_Len tinyint
if len(@p_SourceText) = 0
begin
  SET  @w_Continue = 0 — force early exit
 end
else
begin
— parse the original @p_SourceText array into a temp table
  SET  @w_Continue = 1
  SET @w_StartPos = 1
  SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
  SET @w_Length   = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
  SET @w_Delimeter_Len = len(@p_Delimeter)
end
WHILE @w_Continue = 1
BEGIN
  SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
      ,(SUBSTRING( @p_SourceText, @w_StartPos
      ,((@w_Length – @w_StartPos) + @w_Delimeter_Len)))
      )

  IF @w_Delimeter_pos > 0  — delimeter(s) found, get the value
  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
        ,(@w_Delimeter_pos – 1)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
  END
  ELSE — No more delimeters, get last value
  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
      ,((@w_Length – @w_StartPos) + @w_Delimeter_Len)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SELECT @w_Continue = 0
  END
  INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 
  
Resource:
SQL User Defined Function to Parse a Delimited String
 
Parse String into Table
 
Function to Split a Delimited String into a Table
 
Parse delimited string in a Stored Procedure
 
 

Custom Server Controls

I’ve been fight with creating Custom Server Controls (Custom Controls).  I have create one Custom Control that inherits from CompositeControl.  And create another control that inherits from the previous created custom control.  Let’s just say the process of create a custom control is not very intuitive.  Let’s not even talk about creating a customer control that inherits from another customer control.  Holly !@#$.
 
I will probably create custom controls again and when I do hopefully the following resources will be helpful.
 
 
 
 
Composite controls that do their own databinding
http://weblogs.asp.net/asmith/archive/2004/08/29/222442.aspx
 
Control Building and ViewState Lesson for the Day
http://scottonwriting.net/sowBlog/archive/10062004.aspx
 
A Crash Course on ASP.NET Control Development: Building Composite Controls
http://msdn.microsoft.com/en-us/library/aa479016.aspx

Building Proof of Concepts with XML test Data

 
There are many times where I want to create a Proof of Concept (POC) that uses a data table.  There are situations when I build these POC that I don’t want to connect to to Database,but still need a datatable.  There are times where I don’t want to create the plumb, or I want to create example that are not specific to the project I’m working, just something more generic. So to get around this problem of connecting to DB, I decided to use XML for the source data.
 
Today I had some problems with a 3rd party component. I didn’t want to post my companies code on the web, so I create a POC and used XML as the data.  I loaded the XML in a Dataset, which allowed me to the data through a DataTable.
 
I couldn’t get the POC working without the first line of the xml that follows
 
xmlData.AppendLine(""<?xml version=’1.0′ standalone=’yes’?>");
xmlData.AppendLine("<Validation>");
xmlData.AppendLine(" <ValidationExpression>");
xmlData.AppendLine("  <ID=’101′ Checked=’true’ Name=’Email’ ValidationSeverityID=’201′  />");
xmlData.AppendLine("  <ID=’102′ Checked=’false’ Name=’Phone Number’ />");
xmlData.AppendLine("  <ID=’103′ Checked=’false’ Name=’Social Security’ />");
xmlData.AppendLine("  <ID=’104′ Checked=’false’ Name=’Date’ />");
xmlData.AppendLine("  <ID=’105′ Checked=’false’ Name=’US Zip + 4′ />");
xmlData.AppendLine("  <ID=’106′ Checked=’false’ Name=’US Zip (5 Only)’ />");
xmlData.AppendLine("  <ID=’107′ Checked=’true’ Name=’Date’  ValidationSeverityID=’203’/>");
xmlData.AppendLine(" </ValidationExpression>");
xmlData.AppendLine(" <ValidationSeverity>");
xmlData.AppendLine("  <ID=’201′ Name=’Error’ />");
xmlData.AppendLine("  <ID=’202′ Name=’Warning’ />");
xmlData.AppendLine("  <ID=’203′ Name=’Info’ />");
xmlData.AppendLine(" </ValidationSeverity>");
xmlData.AppendLine("</Validation>");
 
System.IO.StringReader xmlSR = new System.IO.StringReader(xmlData);
dataSet.ReadXml(xmlSR);
 
This create 4 tables in the Data Set.
 
Resources
MSDN – Loading a DataSet from XML (ADO.NET)
http://msdn.microsoft.com/en-us/library/fx29c3yd.aspx
 

SQL – How to use (not use) GO within Batches

“GO” is not a T-SQL statement, it’s a command specific to utilities such as SQLCmd, OSQL, Query Analyzer and SQL Server Management Studio.  Go is not part of ANSI-SQL or Transaction-SQL (TSQL).  The GO command is interpreted by these tools to trigger a batch of Transaction-SQL.

 

In the past I’ve ran into problems using GO when creating scripts that are used for moving data or structure to another database.  Usually these scripts include multiple Creates, Alters, Inserts DROP and etc.  A bad habit I had was to add the GO command after every statement, which is not needed.  A caveat pertaining to Stored Procedures, in a script that contains multiple statements, a GO command must be the last line of the SP.  If a GO command is not include as the last line of the SP then all then the code following the SP will be compiled and save in the SP.  I’ve seen this problem many times when a person creates a Stored Procedure and in the same script add permissions to that SP without a GO command separating the two statements. Example:

 

 

CREATE PROCEDURE DoSomeThing

  @Var1 VARCHAR(50)

AS

  SELECT TOP 10 Col1, Col2

  FROM Table

  WHERE Col2 = @Car1

–GO –Go is needed hter

GRANT EXECUTE ON DoSomeThing TO User

 

Now every time the DoSomeThing SP is executed the “Grant Excute…” statement will be ran also.

 

Also the scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

 

 

 

Resources:

SQL Server Books -ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b2ca6791-3a07-4209-ba8e-2248a92dd738.htm

http://msdn.microsoft.com/en-us/library/ms188037.aspx

 

SQL Server batch rules

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1161826,00.html

 

Errors in T-SQL Batches and the GO Statement in ETL process

http://www.siusic.com/wphchen/errors-in-t-sql-batches-and-the-go-statement-in-etl-process-63.html

 

IF and TRANSACTION problem

http://www.eggheadcafe.com/software/aspnet/32705839/if-and-transaction-proble.aspx