Saturday 20 July 2013

Microsoft DotNet Framework - Catch the UnHandled Exception in Application


              This article discuss about the information related to Catch the UnHandled Exception for application. Whenever a application crashes due to unhandled exception, We dont have information why it crashes, What type of exception is unhandled, Where it is raises

UnHandled Exception :
            Unhandled Exception event handles the exceptions that are unhandled or not catched from Main UI Thread

Thread Exception :
           Thread Exception event handles the exceptions that are unhandled or not catched from Threads that are Non UI.

For Windows Application , We can ignore or log the error and keep on running the application by using Thread Exception.


static void Main()
{
  Application.EnableVisualStyles();
  Application.SetCompatibleTextRenderingDefault(false);
  Application.ThreadException += new ThreadExceptionEventHandler(App_ThreadException;
  AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(Cur_UnhandledException);
  Application.Run(new Form1());
}

static void App_ThreadException(object sender, ThreadExceptionEventArgs e)
{
  MessageBox.Show(e.Exception.Message, "Unhandled Thread Exception");
  File.AppendAllText(@"D:\thread.log",e.Exception.StackTrace)                          //If you need to terminate the Application uncomment the following line              // Application.Exit()                                                              }                                                                                     
static void Cur_UnhandledException(object sender, UnhandledExceptionEventArgs e)
{
  MessageBox.Show((e.ExceptionObject as Exception).Message, "Unhandled UI Exception";
  File.AppendAllText(@"D:\unhandex.log",(e.ExceptionObject as Exception).StackTrace); )
}






For web Application Some times Application restarts and Session Expries because of UnHandled Error How to overcome this.

Change the config file from the following path

C:\>type %WINDIR%\Microsoft.NET\Framework\v2.0.50727\aspnet.config


<configuration>                                                                                 <runtime>                                                                                           <legacyUnhandledExceptionPolicy enabled="true" /> 
  </runtime>
 </configuration>
        
        
        
        
    


Suppress JIT Debugging pop up screen Window When Unhandled Exception is Caught in Various Ways


















JIT Debugging :
       JIT Debugging is launches automatically when error or crash takes place in application which is runnning outside of  Visual Studio.

JIT debugging will popup a Window to select the debugging options when application crash takesplace.
Below is the sample pop up window




Now Let we see how we can suppress in various ways ,starts with the window based  application. Now how we can avoid the pop up window to launch when application crashes or error occurs.

Windows forms Application 
         Now our Window forms application is crashes outside the visual studio environment , Due to UnHandled Exception takes in Application. How we can avoid the pop up of this window.Add the following element in the app.exe.config file.
<configuration>
 <system.windows.forms  jitDebugging ="false" />
</configuration>
For C++ Application set the DebuggableAttribute in .Config file or in Code
[assembly:System::Diagnostics::DebuggableAttribute(false, false)]; 

To Enable or Disable JIT Debugging using visual studio
1.  In Visual Studio on the Tools Menu , Click Options.
2.  In the Options select Debugger.
3.  Select Just-In-Time 
4.  Enable Just-In-Time debugging of these types of code ,Select or De-Select the programs Managed, Native Or Script

To Disable JIT Debugging using Registry
1.  Click the Start Menu , select the Run dialog
2.  In the Registry Edit Delete the following Keys.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\DbgManagedDebugger

3.  If the system is 64 bit then delete the following keys also

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\DbgManaged\DbgManagedDebugger
Some times Internet Explorer also do the pop up 
1.  Go to the Control Panel Menu , select the Internet options dialog
2.  Select the Advance 
3.  Check the "Disable the Script Debugging(Others)" and "Disable the Script Debugging(Internet Explorer)"

Oracle Stored Procedure Versus Sql server Stored Procedure - Part 1

Let we start the article with simple examples.

Oracle :
In Oracle Create or Replace is a keyword is used to create the stored procedure , Replace indicates that the procedure with that name already exists replace with this new one

CREATE OR REPLACE PROCEDURE sample_sp 
(
emp_no IN number,
emp_name OUT varchar2
)
IS
BEGIN

SELECT employee_name INTO emp_name
FROM Employee
Where employee_id = emp_no

END sample_sp;

To Execute the Above Sp We have to write a simple PL\SQL 

Declare 
name varchar2(40);
BEGIN
       sample_sp(2,name);
       dbms_output.put_line(name);
END


Sql Server 

In Sql Server Create is the Keyword used to create the Stored Procedure and if you want to overwrite the existing stored procedure used ALTER keyword instead of CREATE.

CREATE PROCEDURE sample_sp
(
@id  int,
@name varchar(30) out
)
AS
BEGIN

SELECT @name = employee_name 
FROM Employee
WHERE employee_id = @id

END

How to Execute the Stored Procedure

Declare @emp_name varchar(30)
EXEC sample_sp 2,@emp_name out
Select @emp_name

Convert Sybase Db version 9 to Sybase 12

This article discuss about Rebuild a version 9 or earlier database from Sybase Central.You can use the dbunload utility to do this.

Note : 
1. In sybase 12 version password is case sensitive
2. Default page size of 12 version DB is 4096 bytes.If older version uses un-supported page size it can be         changed using dbinit -p or dbunload -ap to specify a different page size
3. database file size is smaller in rebuilded new DB after the process of unloading and reloading the pld .
4. Collations  Older version uses SACA collations, 12 version uses 2 collations SACA and UCA


Using Sybase Central Rebuild Database 

1.   Click Start -> Programs -> Sql Anywhere 12 ->Administration tools -> Sybase central
2.   Select Tools -> Sql AnyWhere 12 -> Unload Database
3.   Read the introductory page of Wizard
4.   Click the Next in Wizard.
5.   Select Unload A Database Running  On An Earlier Version of the Server, Or a Database That is not               running.
6.   Enter the Connection Information of the Database Then Click Next .
7.   Select Unload and Reload into a New Database.Then Click Next.
8.   Specify the New Database Name ,Click Next.(Choose Page Size)
9.   Select the Unload structure and Data , Click Next
10. Specify that you want to connect database after rebuild is complete.
11. Click Finish.

Monday 15 July 2013

Solution for Check VARCHAR(n) for Well Formed XML before cast or convert in Sql Server using User Defined Function

















               Once a time i had faced a thing,Which makes me to do a User Defined function which will check xml is well-formed. Some time we are saving  the XML as VARCHAR in table. May be that time we don't knew whether it is well formed xml or not.But when select the records as xml we have to cast it to XML type that time it will raise error.

Consider there are 10000 records in table due to one record whole select process is interrupt. How to find that particular record. There is no pre-defined function which will test varchar type is in well formed xml.
Based on that we can select only well formed data as XML.


Let we take a scenario

1. Create a table 
2. Insert some values with well formed xml and not
3. then finally we want the data as xml in output.so we are going to select with cast as xml.

Step 1: 
create table samplexml
(
id int identity(1,1),
fromname varchar(40),
xmldata nvarchar(2000)
)

Step 2:
Insert into samplexml(fromname,xmldata)
select 'samsung' ,'<Products><Model><Name>AC123</Name></Model><Model><Name>WE345</Name></Model></Products>'

Insert into samplexml(fromname,xmldata)
select 'samsung' ,'<Products><Model><Name>AF234</Name></Model><Model><Name>WD329<Name></Model></Products>'

Insert into samplexml(fromname,xmldata)
select 'samsung' ,'<Products><Model><Name sub="ss">DF556</Name></Model><Model><Name>WD098</Name></Model></Products>'

Insert into samplexml(fromname,xmldata)
select 'samsung' ,'<Products><Model><Name sub="ss>DF456</Name></Model><Model><Name>gd028</Name></Model></Products>'

In the Above code second value is not well formed. Model tag is not closed and fourth insert have attribute not closed well 

Step 3: 

When selecting the records from the Table as Xml "will Throw an Error "
Msg 9436, Level 16, State 1, Line 1

XML parsing: line 1, character 75, end tag does not match start tag


Select id , fromname, cast(xmldata as xml) "ProductXml" From samplexml

Now from the above we cant able to select the single records due to error in casting.If there is 10000 Records how we can find the particular row which is not in correct format (or) we have to select the records which can be able to cast instead of interrupt.

To Overcome this, i have implemented a user defined function which will tell whether particualr varchar data is in well-formed xml or not , If it well formed then function will return 1 or if not then-1 

How to find the particular record is not a well formed xml using function ?

SELECT ID,FROMNAME, DBO.ISXMLFORMAT(XMLDATA) FROM SAMPLEXML 

ISXMLFORMAT is the user defined function which will get the data as input and return the 1 for well formed xml (syntax check), -1 for not well formed xml

















When run the above select statement it will show the result which data are well formed xml.To select the Well-formed xml and then cast as xml using following query.

SELECT ID,FROMNAME, CAST(XMLDATA AS XML) FROM SAMPLEXML 
WHERE DBO.ISXMLFORMAT(XMLDATA) =1 

Output :














CREATE FUNCTION ISXMLFORMAT(@DATA NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @S              NVARCHAR(MAX)
DECLARE @CHAR           CHAR(1)
DECLARE @opentag    char(1)
DECLARE @endtag     char(1)
DECLARE @ATT            NVARCHAR(1) = 'S'
DECLARE @WORD           NVARCHAR(MAX) = ''
DECLARE @OFLAG          VARCHAR(1) = 'N'
DECLARE @EFLAG          VARCHAR(1) = 'N'
DECLARE @TCLOSE   VARCHAR(MAX) =''
DECLARE @LENGTH   INT
DECLARE @MIN            INT = 1;
DECLARE @COM            NVARCHAR(MAX)
DECLARE @key            NVARCHAR(100)=''
DECLARE @EQUAL          NVARCHAR(1) ='N'
DECLARE @OQUOTE   NVARCHAR(1)='N'
DECLARE @EQUOTE         NVARCHAR(1)='N'
DECLARE @OSQUOTE  NVARCHAR(1)='N'
DECLARE @ESQUOTE  NVARCHAR(1)='N'
DECLARE @KEYEND         NVARCHAR(1)='N'
DECLARE @STLETTER   CHAR(1)

DECLARE @TAB            TABLE
(
ID INT IDENTITY(1,1),
COL VARCHAR(100)
)

SELECT @S = @DATA;
SELECT @LENGTH = LEN(@S);
SELECT @STLETTER = SUBSTRING(@S,1,1);

IF @STLETTER <> '<' AND  @STLETTER <> '' AND  @STLETTER <> ' '
RETURN  -1;

IF SUBSTRING(@S,1,5) = '
BEGIN
  SELECT @MIN = 40;
END

WHILE @MIN<=@LENGTH
BEGIN

SELECT @CHAR = SUBSTRING(@S,@MIN,1);
IF @char = '<' AND SUBSTRING(@S,@MIN+1,1) <> ' '
BEGIN
SELECT @MIN=@MIN+1;

WHILE SUBSTRING(@s,@min,1) <> '>'
BEGIN
   IF SUBSTRING(@S,@MIN,1) = '/'
BEGIN

IF SUBSTRING(@S,@MIN,1) = '/' AND  SUBSTRING(@S,@MIN+1,1) = '> '
BEGIN
   SELECT @MIN = @MIN +1;
   SELECT @WORD=NULL;
END


ELSE

BEGIN

  SELECT @EFLAG = 'S'

  SELECT    @COM=COL
  FROM      @TAB
  WHERE     ID = (SELECT MAX(ID) FROM @TAB)
  SELECT @MIN=@MIN+1;
END
END
ELSE
BEGIN
BEGIN
IF SUBSTRING(@S,@MIN,1) = ' ' and SUBSTRING(@S,@MIN+1,2) <> '/>'
BEGIN
IF @WORD IS not NULL
BEGIN

INSERT INTO @TAB(COL) SELECT @WORD;
SELECT @WORD= null;
END

SELECT @min = @min +1;
WHILE SUBSTRING(@s,@min,1) <> '>'
BEGIN

IF SUBSTRING(@S,@MIN,1) <> '=' AND  SUBSTRING(@S,@MIN,1) <> '/' AND SUBSTRING(@S,@MIN+1,1) <> '>'
BEGIN
IF SUBSTRING(@S,@MIN,1) = '"' AND @OQUOTE <> 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @OQUOTE = 'S'
END
ELSE IF SUBSTRING(@S,@MIN,1) = '"' AND @OQUOTE  = 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @equote = 'S'
END
ELSE IF SUBSTRING(@S,@MIN,1) = '''' AND @osquote <> 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @osquote = 'S'
END
ELSE IF SUBSTRING(@S,@MIN,1) = '''' AND @osquote  = 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @esquote = 'S'
END
ELSE IF @equal<>'S'
BEGIN
SELECT @KEY=@KEY+SUBSTRING(@S,@MIN,1);
END

IF @equal='S' AND @OQUOTE ='S' AND @equote= 'S'
BEGIN
SELECT @KEY='',@equal='N',@OQUOTE ='N',@equote= 'N',@keyend='S'
END

IF @equal='S' AND @OSQUOTE ='S' AND @ESQUOTE= 'S'
BEGIN
SELECT @KEY='',@equal='N',@OsQUOTE ='N',@esquote= 'N',@keyend='S'
END
END
ELSE
BEGIN

IF (SUBSTRING(@S,@MIN,1) = '"' OR SUBSTRING(@S,@MIN,1) = '''') AND SUBSTRING(@S,@MIN+1,1) = '>'
BEGIN
IF SUBSTRING(@S,@MIN,1) = '"' AND @OQUOTE  = 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @equote = 'S'
END

IF SUBSTRING(@S,@MIN,1) = '''' AND @osquote  = 'S' AND @equal='S' AND @KEY <> ''
BEGIN
SELECT @esquote = 'S'
END

IF @equal='S' AND @OQUOTE ='S' AND @equote= 'S'
BEGIN
SELECT @KEY='',@equal='N',@OQUOTE ='N',@equote= 'N',@keyend='S'
END

IF @equal='S' AND @OSQUOTE ='S' AND @ESQUOTE= 'S'
BEGIN
SELECT @KEY='',@equal='N',@OsQUOTE ='N',@esquote= 'N',@keyend='S'
END

END

IF (SUBSTRING(@S,@MIN,1) = ' ' OR  SUBSTRING(@S,@MIN,1) = '/') and SUBSTRING(@S,@MIN,2) = '/>' AND @keyend='S'
BEGIN
DELETE FROM @TAB WHERE ID = (SELECT MAX(ID) FROM @TAB)
SELECT @KEYEND ='N'
END
IF  @KEY <> ''
SELECT @equal='S'
END
SELECT @min = @min +1;
END

SELECT @min = @min -1;
--set the attr status.
IF @KEY<>'' OR  @equal<>'N'OR @OsQUOTE <>'N'OR @esquote<>'N'
SELECT @ATT = 'N'

END
ELSE
BEGIN
SELECT @WORD = @WORD + SUBSTRING(@S,@MIN,1);
END
END
SELECT @MIN=@MIN+1;
END
END

IF @COM = @WORD and @EFLAG = 'S'
BEGIN
DELETE FROM @TAB WHERE ID = (SELECT MAX(ID) FROM @TAB)
SELECT @EFLAG = 'N'
END
ELSE
BEGIN
IF @WORD IS not NULL
INSERT INTO @TAB(COL) SELECT @WORD;
END

SELECT @WORD='';
SELECT @TCLOSE='';
END
--  ELSE
--  BEGIN
----      Return -1
--  END
SELECT @MIN=@MIN+1;

IF SUBSTRING(@S,@MIN,1) = '"' OR SUBSTRING(@S,@MIN,1) = '>' OR SUBSTRING(@S,@MIN,1) = '&'  OR SUBSTRING(@S,@MIN,1) = ''''
BEGIN
Return -1
END
END
IF (SELECT COUNT(*) FROM @TAB) > 0 OR @ATT='N'
BEGIN
Return -1;
END
ELSE
BEGIN
Return 1;
END
Return -1;

END


From the above function we can validate the syntax of the varchar type is in xml format.