Results 1 to 1 of 1

Thread: Convert SQL Server to Mysql 5.0 PROCEDURES

  1. #1
    Join Date
    Apr 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Convert SQL Server to Mysql 5.0 PROCEDURES

    Hello, I'm trying to migrate a database from sql 2005 to mysql 5.0, but I can not in any way to convert strings, could someone help me? the code is below thanks

    ------------------------------------

    Code:
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[CreateCharacter]') AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROC [CreateCharacter]
    	@AID		int,
    	@CharIndex	smallint,
    	@Name		varchar(24),
    	@Sex		tinyint,
    	@Hair		int,  
    	@Face		int,
    	@Costume	int
    AS
    SET NOCOUNT ON
    BEGIN TRAN
    IF EXISTS (SELECT CID FROM Characters where (AID = @AID AND CharIndex = 
    			@CharIndex) OR (Name = @Name))
    BEGIN	
    	ROLLBACK TRAN
    	return(-1)
    END
    
    DECLARE @CharIdent 		int
    DECLARE @ChestCIID		int
    DECLARE @LegsCIID		int
    DECLARE @MeleeCIID		int
    DECLARE @PrimaryCIID	int
    DECLARE @SecondaryCIID  int
    DECLARE @Custom1CIID	int
    DECLARE @Custom2CIID	int
    
    DECLARE @ChestItemID		int
    DECLARE @LegsItemID			int
    DECLARE @MeleeItemID		int
    DECLARE @PrimaryItemID		int
    DECLARE @SecondaryItemID	int
    DECLARE @Custom1ItemID		int
    DECLARE @Custom2ItemID		int
    
    SET @SecondaryCIID = NULL
    SET @SecondaryItemID = NULL
    
    SET @Custom1CIID = NULL
    SET @Custom1ItemID = NULL
    
    SET @Custom2CIID = NULL
    SET @Custom2ItemID = NULL
    
    INSERT INTO Characters (AID, Name, CharIndex, [Level], Sex, Hair, Face, XP, BP, 
             		           GameCount, KillCount, DeathCount)
    Values (@AID, @Name, @CharIndex, 255, @Sex, @Hair, @Face, 0, 999999999, 0, 0, 0)
    
    IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
    END
    
    SET @CharIdent = @@IDENTITY
    
      /* Melee */
      SET @MeleeItemID = 
        CASE @Costume
        WHEN 0 THEN 1
        WHEN 1 THEN 2
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        WHEN 4 THEN 2
        WHEN 5 THEN 1
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @MeleeItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
    
      SET @MeleeCIID = @@IDENTITY
    
      /* Primary */
      SET @PrimaryItemID = 
        CASE @Costume
        WHEN 0 THEN 5001
        WHEN 1 THEN 5002
        WHEN 2 THEN 4005
        WHEN 3 THEN 4001
        WHEN 4 THEN 4002
        WHEN 5 THEN 4006
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @PrimaryItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
    
      SET @PrimaryCIID = @@IDENTITY
    
      /* Secondary */
    IF @Costume = 0 OR @Costume = 2 BEGIN
      SET @SecondaryItemID =
        CASE @Costume
        WHEN 0 THEN 4001
        WHEN 1 THEN 0
        WHEN 2 THEN 5001
        WHEN 3 THEN 4006
        WHEN 4 THEN 0
        WHEN 5 THEN 4006
        END
    
      IF @SecondaryItemID <> 0 BEGIN
        INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @SecondaryItemID)
        IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
        END
    
        SET @SecondaryCIID = @@IDENTITY
      END
    END
      SET @Custom1ItemID = 
        CASE @Costume
        WHEN 0 THEN 30301
        WHEN 1 THEN 30301
        WHEN 2 THEN 30401
        WHEN 3 THEN 30401
        WHEN 4 THEN 30401
        WHEN 5 THEN 30101
        END
    
      /* Custom1 */
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @Custom1ItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
    
      SET @Custom1CIID = @@IDENTITY
    
      /* Custom2 */
    IF @Costume = 4 OR @Costume = 5
    BEGIN
      SET @Custom2ItemID =
        CASE @Costume
        WHEN 0 THEN 0
        WHEN 1 THEN 0
        WHEN 2 THEN 0
        WHEN 3 THEN 0
        WHEN 4 THEN 30001
        WHEN 5 THEN 30001
        END
    
      IF @Custom2ItemID <> 0
      BEGIN
        INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @Custom2ItemID)
        IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
        END
    
        SET @Custom2CIID = @@IDENTITY
      END
    END
    
    IF @Sex = 0
    BEGIN
    
      /* Chest */
      SET @ChestItemID =
        CASE @Costume
        WHEN 0 THEN 21001
        WHEN 1 THEN 21001
        WHEN 2 THEN 21001
        WHEN 3 THEN 21001
        WHEN 4 THEN 21001
        WHEN 5 THEN 21001
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @ChestItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
    
      SET @ChestCIID = @@IDENTITY
    
      /* Legs */
      SET @LegsItemID =
        CASE @Costume
        WHEN 0 THEN 23001
        WHEN 1 THEN 23001
        WHEN 2 THEN 23001
        WHEN 3 THEN 23001
        WHEN 4 THEN 23001
        WHEN 5 THEN 23001
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @LegsItemID)
      IF 0 <> @@ERROR BEGIN 
    	ROLLBACK TRAN
    	RETURN (-1)
      END
    
      SET @LegsCIID = @@IDENTITY
    
    END
    ELSE
    BEGIN
    
      /* Chest */
      SET @ChestItemID =
        CASE @Costume
        WHEN 0 THEN 21501
        WHEN 1 THEN 21501
        WHEN 2 THEN 21501
        WHEN 3 THEN 21501
        WHEN 4 THEN 21501
        WHEN 5 THEN 21501
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @ChestItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
      SET @ChestCIID = @@IDENTITY
    
      /* Legs */
      SET @LegsItemID =
        CASE @Costume
        WHEN 0 THEN 23501
        WHEN 1 THEN 23501
        WHEN 2 THEN 23501
        WHEN 3 THEN 23501
        WHEN 4 THEN 23501
        WHEN 5 THEN 23501
        END
    
      INSERT INTO Inventories (CID, ItemID) Values (@CharIdent, @LegsItemID)
      IF 0 <> @@ERROR BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
      END
      SET @LegsCIID = @@IDENTITY
    
    END  
    
    UPDATE Characters
    SET ChestCIID = @ChestCIID, LegsCIID = @LegsCIID, MeleeCIID = @MeleeCIID,
        PrimaryCIID = @PrimaryCIID, SecondaryCIID = @SecondaryCIID, Custom1CIID = @Custom1CIID,
        Custom2CIID = @Custom2CIID, ChestItemID = @ChestItemID, LegsItemID = @LegsItemID, 
    	MeleeItemID = @MeleeItemID, PrimaryItemID = @PrimaryItemID, SecondaryItemID = @SecondaryItemID, 
    	Custom1ItemID = @Custom1ItemID, Custom2ItemID = @Custom2ItemID
    WHERE CID = @CharIdent
    
    IF 0 = @@ROWCOUNT BEGIN
    	ROLLBACK TRAN
    	RETURN (-1)
    END
    
    COMMIT TRAN
    ' 
    END
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DeleteCharacter]') AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROC [DeleteCharacter]
    	@AID		int,
    	@CharIndex	smallint,
    	@Name		varchar(24)
    AS
    SET NOCOUNT ON
    DECLARE @CID		int
    
    SELECT @CID = CID FROM Characters WHERE AID = @AID AND CharIndex = @CharIndex AND Name = @Name
    IF (@CID IS NULL)
    BEGIN
    	return (-1)
    END
    
    DELETE FROM Characters WHERE CID = @CID
    DELETE FROM Inventories WHERE CID = @CID
    UPDATE Characters SET CharIndex = CharIndex - 1 WHERE AID = @AID AND CharIndex > @CharIndex
    
    SELECT 1 AS Ret
    ' 
    END
    ---------------------------------------------------------
    I thank anyone who can convert
    Last edited by ddadmin; 04-10-2011 at 08:21 PM.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •