asked on

Converting procedure from MS SQL to MySQL

hi,

currently we are migrating our database to MS SQL Server to MYSQL. I am facing some difficulty while migrating procedures having xml functionality.

Please check the sample MS SQL procedure below.

Can help me out in migrating this to MYSQL.

Thanks in advance.

CREATE PROCEDURE [dbo].[AddOrUpdateActivity] 
@activityXML XML,@ActivityId BIGINT OUTPUT,@errorStatus INT OUTPUT
AS
BEGIN
	
	DECLARE @academicSessionId BIGINT
	DECLARE @activityName NVARCHAR(50)
    DECLARE @newActivityId BIGINT
	
	SET NOCOUNT ON;
	SET @academicSessionId=(SELECT newActivity.value('@AcademicSessionId','BIGINT') FROM @activityXML.nodes('/Activity-Info') node(newActivity))
	print @academicSessionId
	SET @newActivityId=(SELECT newActivity.value('@ActivityId','BIGINT') FROM @activityXML.nodes('/Activity-Info') node(newActivity))   
	print @newActivityId
	SET @activityName=(SELECT newActivity.value('@ActivityName','NVARCHAR(50)') FROM @activityXML.nodes('/Activity-Info') node(newActivity))
	print @activityName
	
IF @newActivityId = 0
	BEGIN
		BEGIN TRY --begin the TRY block
			BEGIN TRANSACTION ADD_ACTIVITY
			-- Insert statements for procedure here
			INSERT INTO tbl_activity_master(academic_session_id,activity_name) values(@academicSessionId,@activityName)
			COMMIT TRANSACTION ADD_ACTIVITY
			SET @activityId=@@IDENTITY	
		END TRY -- end of TRY block
		BEGIN CATCH -- control would come here only in case of an exception
			SET @errorStatus =  @@ERROR
						
				  ROLLBACK TRANSACTION ADD_ACTIVITY
		
		END CATCH -- end of CATCH block	
	END
ELSE
	BEGIN
		BEGIN TRY --begin the TRY block
			BEGIN TRANSACTION UPDATE_ACTIVITY
			-- Update statements for procedure here
			UPDATE tbl_activity_master set  activity_name =@activityName where activity_id=@newActivityId
			COMMIT TRANSACTION UPDATE_ACTIVITY
				
		END TRY -- end of TRY block
		BEGIN CATCH -- control would come here only in case of an exception
			SET @errorStatus =  @@ERROR
						
				  ROLLBACK TRANSACTION UPDATE_ACTIVITY
		
		END CATCH -- end of CATCH block	
	END
	PRINT @activityId
 
END

Open in new window

snoyes_jw

If you're using MySQL 5.1, you can use the ExtractValue function.

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

In 5.0 and earlier, MySQL had no XML extraction abilities, beyond what you could roll yourself with LOCATE and SUBSTRING and things like that.

Jagdish Devaku

ASKER

sorry for delay in response...

Thanks for above info which i tried with no success..

how can i extract data from the below xml file. I need all the three columns with their values.

<Activity-Info AcademicSessionId="109" ActivityId="0" ActivityName="Test"/>

Open in new window

snoyes_jw

You have not shown what you tried. You have not reported what version of MySQL you are running.

Jagdish Devaku

ASKER

below is the code we tried...

We are upgraded MySQL to 5.4.1 version to check the XML compatibility. We even tried the below code in 5.1 version.

 create PROCEDURE AddOrUpdateActivity 
(    
IN activityXML longtext,
IN ActivityId BIGINT ,
IN errorStatus INT 
)
BEGIN
    DECLARE academicSessionId BIGINT ;
    DECLARE activityName VARCHAR(50);
    DECLARE newActivityId BIGINT ;
      
    SELECT * FROM  activityXML LIMIT 1 INTO @xml ;
    
    SET academicSessionId=(select EXTRACTVALUE(@xml,'/Activity/Info/AcademicSessionId')) ;
 
    SET newActivityId=(select EXTRACTVALUE(@xml,'/Activity/Info/ActivityId') );   
 
    SET activityName=(select EXTRACTVALUE(@xml,'/Activity/Info/ActivityName'));
 
    
IF newActivityId = 0 
   THEN
        INSERT INTO tbl_activity_master(academic_session_id,activity_name) values(academicSessionId,activityName) ;
        SET activityId=IDENTITY  ;  
        
ELSE
            UPDATE tbl_activity_master
            SET  activity_name =activityName where activity_id=newActivityId;
    
        END IF;
    
    END

Open in new window

Kevin Cross

I don't have MySQL 5.1 on this machine to test, but try like this:

 create PROCEDURE AddOrUpdateActivity 
(    
IN activityXML longtext,
OUT ActivityId BIGINT ,
OUT errorStatus INT 
)
BEGIN
    DECLARE academicSessionId BIGINT ;
    DECLARE activityName VARCHAR(50);
    DECLARE newActivityId BIGINT ;
      
    SET academicSessionId=(select EXTRACTVALUE(activityXML,'/Activity/Info/AcademicSessionId')) ;
    SET newActivityId=(select EXTRACTVALUE(activityXML,'/Activity/Info/ActivityId') );   
    SET activityName=(select EXTRACTVALUE(activityXML,'/Activity/Info/ActivityName'));
  
IF newActivityId = 0 
   THEN
      INSERT INTO tbl_activity_master(academic_session_id,activity_name) 
      VALUES(academicSessionId,activityName) ;
      
      SET ActivityId=IDENTITY  ;    
   ELSE
      UPDATE tbl_activity_master
      SET  activity_name=activityName
      WHERE activity_id=newActivityId;
END IF;
 
END

Open in new window

ASKER CERTIFIED SOLUTION

snoyes_jw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagdish Devaku

ASKER

There is some issue with MySQL 5.1 on my system... will check it and let you know if the above works or not.

Jagdish Devaku

ASKER

hey sorry for the delay uys... mysql on my system got corrupted...

mwvisa1>> when i execute your code above, i got the following error...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

I checked the code regarding this error, but could not find anything...

can you suggest me anything regarding this issue...

Kevin Cross

I had to double check the IF syntax for MySQL as I am in my MS SQL Server more often than MySQL for stored procedures, so that is why this link is for IF; however, it reminded me about the delimiters. Since ';' is the delimiter for MySQL, when building a multi-line statement, the code may try to execute too early unless you set the delimiter to something else like the exampled '//':
http://dev.mysql.com/doc/refman/5.1/en/if-statement.html

Just double checking that you changed delimiter.

Jagdish Devaku

ASKER

i think everything is fine with the syntax... but still why is this error occur...

i am using mysql 5.1....

Jagdish Devaku

ASKER

Though I am still facing issues... I would like to appreciate your efforts for trying...