Showing posts with label MySQL. Show all posts

What happen if String is used for Number data type in MySQL?

String for Number datatype in MySQL

For Number data type, MySQL parse String value character-by-character until it finds character other than number.

Queries
SELECT * FROM users WHERE id = '12 or 1=1'; \\ 1 -> OK, 2 -> OK,  -> false-and-return
> SELECT * FROM users WHERE id = 12;

SELECT * FROM users WHERE id = '123XYZ'; \\ 1 -> OK, 2 -> OK, 3 -> OK, X -> false-and-return
> SELECT * FROM users WHERE id = 123;

SELECT * FROM users WHERE id = '1&23XYZ'; \\ 1 -> OK, & -> false-and-return
> SELECT * FROM users WHERE id = 1'

Lock wait timeout exceeded; try restarting transaction

Lock wait timeout exceeded; try restarting transaction


MySQL is always painful for developer when its comes to Stored Procedures, Function, PL/SQL, etc... This may be different for you and may not resolve problem however if you've situation similar to mine than it might help you.

Error
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Scenario
We've Stored Procedure that performs Insert/Update/Delete in about 45 tables. This Stored Procedure working fine in local databases with windows environment. It was not working on production server with Linux environment. I've followed below steps to find out the culprit query.

Steps
Find out the query that executing for long time. This information also contains process_id.
SHOW ENGINE INNODB STATUS

You can also execute following query to find out the process_id(id column) and query on particular database schema.
SHOW PROCESSLIST;

Now if the query is still executing you'll have to kill the process.
KILL process_id;

Culprit Query
INSERT INTO ...
SELECT 
FROM mytable
WHERE column_name
IN(SELECT mytable2_col_name 
   FROM mytable2 
   WHERE id IN(SELECT mytable3_col_name
               FROM mytable3 
               WHERE type = 'GRID' and m_id IN(SELECT DISTINCT column_name
                                               FROM mytable 
                                               WHERE nt_id = NewNtID)) 
   AND fk_col IS NOT NULL)
The query contains long inner query plus I'm using IN for column and same column I used as last inner query.

Solution
I chopped all inner queries and used temporary table to store records of each query and I used final output of all inner query in main query and it worked for me. 

How to change collation_database value in MySQL?

collation_database

A collation is a set of rules for comparing characters in a character set.
Read more about Character Sets and Collations in General.

How to check database collation character set in MySQL?
Execute following query to know the collation character set of current database.Currently its 'utf8_general_ci'.
SHOW VARIABLES LIKE '%collation_database%';
Variable_name value Value
collation_database utf8_general_ci


How to change database collation character set in MySQL?
We need to alter the database with new collation character set. We will change it to 'utf8_unicode_ci', change character set as per your requirement.
ALTER DATABASE inventory COLLATE utf8_unicode_ci;
Now lets check the collation character set again by executing same query.
SHOW VARIABLES LIKE '%collation_database%';
Variable_name value Value
collation_database utf8_unicode_ci


How to change character_set_database value in MySQL?

character_set_database + MySQL

MySQL is highly configurable database. It support different character set at Database level, Table level and Column level as well. Sometime it gets worse when you provide so many options.

We were facing an issue because of mixed character set at different level in MySQL. I've tried to change configuration [my.cnf in Linux and my.ini in Windows] file of MySQL but didn't work. I've searched to change value of 'character_set_database' but no one explained to the point. After many try and error I figured out solution.

How to check database character set in MySQL?
Execute following query to know the character set of current database. Currently its 'latin1'.
SHOW VARIABLES LIKE '%character_set_database%';
Variable_name value Value
character_set_database latin1


How to change database character set in MySQL?
We need to alter the database with new character set. We will change it to 'utf8', change character set as per your requirement.
ALTER DATABASE inventory CHARACTER SET utf8;
Now lets check the character set again by executing same query.
SHOW VARIABLES LIKE '%character_set_database%';
Variable_name value Value
character_set_database utf8

Error Code: 1093. You can't specify target table 'user_master' for update in FROM clause

mysql_logo

You can't specify target table 'user_master' for update in FROM clause error comes up when you try to perform an update or delete operation while selecting data from table it self. This is something like How to update/delete self table in MySQL? We will produce same error and then will give you solution for the same.

Problem
/* UPDATING RECORDS */
UPDATE user_master
SET first_name = 'javaQuery'
WHERE id IN (SELECT id FROM user_master WHERE last_name = 'Jobs');

/* DELETING RECORDS */
DELETE 
FROM user_master
WHERE id IN (SELECT id FROM user_master WHERE user_name = 'javaQuery');
We are fetching all users whose Lastname is Jobs and replacing its Firstname in 1st query and deleting records in second query. This won't allow you to update or delete records.


Solution
/* UPDATING RECORDS */
UPDATE user_master
SET first_name = 'javaQuery'
WHERE id IN (SELECT * FROM (SELECT id FROM user_master WHERE user_name = 'javaQuery') AS userIDs);

/* DELETING RECORDS */
DELETE
FROM user_master
WHERE id IN (SELECT * FROM (SELECT id FROM user_master WHERE user_name = 'javaQuery') AS userIDs);

What we did?
  • We are selecting data from query SELECT id FROM user_master WHERE user_name = 'javaQuery' to temporary table called userIDs.
  • Select data from temporary table and use it in WHERE condition.

Database ORDER BY on two columns

ORDER BY two column_Microsoft_SQL_Server_MySQL

ORDER BY 
ORDER BY is used in database to sort data in Ascending or Descending order. ORDER BY keyword is available in all across different database platforms. It comes with two option ASC and DESC. If you don't apply order pattern(ASC or DESC) then it'll take ASC as default pattern.

Microsoft SQL Server and MySQL (Ascending) : SELECT * FROM user_master ORDER BY user_name
Microsoft SQL Server and MySQL (Descending) : SELECT * FROM user_master ORDER BY user_name DESC

I tested ORDER BY on two columns in Microsoft SQL Server and MySQL. You can share for other database platform.

Microsoft SQL Server Start up scripts
/* MSSQL tables and Insert queries */
/****** Object:  Table [dbo].[post]    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE [dbo].[post](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [post] [varchar](max) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT post ON;
INSERT [dbo].[post] ([id], [post]) VALUES (1, N'Post 1')
INSERT [dbo].[post] ([id], [post]) VALUES (2, N'Post 2')
SET IDENTITY_INSERT post OFF;

/****** Object:  Table [dbo].[comments]    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE [dbo].[comments](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [comment] [varchar](max) NULL,
 [postID] [int] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT comments ON;
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (1, N'comment 1 of 1', 1)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (2, N'comment 2 of 1', 1)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (3, N'comment 1 of 2', 2)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (4, N'comment 2 of 2', 2)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (5, N'comment 3 of 2', 2)
SET IDENTITY_INSERT comments OFF;

MySQL Start up scripts
/* MySQL tables and Insert queries */
/****** Object:  Table post    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT post (id, post) VALUES (1, 'Post 1');
INSERT post (id, post) VALUES (2, 'Post 2');

/****** Object:  Table comments    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `postID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT comments (id, comment, postID) VALUES (1, 'comment 1 of 1', 1);
INSERT comments (id, comment, postID) VALUES (2, 'comment 2 of 1', 1);
INSERT comments (id, comment, postID) VALUES (3, 'comment 1 of 2', 2);
INSERT comments (id, comment, postID) VALUES (4, 'comment 2 of 2', 2);
INSERT comments (id, comment, postID) VALUES (5, 'comment 3 of 2', 2);

ORDER BY on Single Column
SELECT *
FROM post p
LEFT JOIN comments c
ON p.id = c.postID
ORDER BY p.id DESC;

ORDER BY on Two Columns
SELECT *
FROM post p
LEFT JOIN comments c
ON p.id = c.postID
ORDER BY p.id DESC, c.id DESC;

  • You can't apply ORDER BY on two columns within same table. If you apply ORDER BY on same table. It'll take last column name and apply ORDER BY pattern on it.

Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

To solve this error I pulled my hair and scrolled through so many blog articles and forum. Finally today I came up with different solutions either can help you.

Note in Public Interest: MySQL has strange behavior, I don't know which kinda developer working on it? So if you are facing an issue right now may not occur after day or so.

Scenario: Most of our clients using Microsoft SQL Server but two or three client want MySQL Server on their server so we've to work on it. We deployed our code on MySQL Server. Our product has so many Stored Procedures, It was working fine but two days back they were facing same error.

Solution 1: Check your query related column's collation using following query and change as per your requirement.
/* Show all columns and its collation */
SELECT * 
FROM information_schema.columns 
WHERE table_schema = DATABASE();
/* ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci; to change column collation */
ALTER TABLE address MODIFY address1 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 2: Check your table's collation using following query and change as per your requirement.
/* Show all tables and its collation */
SHOW TABLE STATUS;
/* ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE <collation>; to change table collation. */
ALTER TABLE actor CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 3: Check collation of your database and change as per your requirement.
/* To check your database collation */
SELECT * 
FROM information_schema.SCHEMATA S
WHERE schema_name = DATABASE(); 
/* ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE <collation>; To change database collation */
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I did above suggested all changes still I was facing same error. I dig down further and found that my connection's collation is utf8_general_ci and database collation is utf8_unicode_ci. To understand the my ground follow below image.

utf8_unicode_ci - utf8_general_ci


Solution 4: Use COLLATE within inline query as follow.
SELECT *
FROM actor 
WHERE name = tmp_VARIABLE
COLLATE utf8_unicode_ci; 

Solution 5: Change parameter's character set in case of Stored Procedure or functions.
CREATE PROCEDURE proc_test (param1 VARCHAR (10) charset utf8, param2 int)

Solution 6: MySQL configuration changes
Place following lines under [mysqld] tag in MySQL configuration file lying under /etc/mysq/my.cnf [Linux] and C:\ProgramData\MySQL\MySQL Server 5.6\my.ini [Windows].
character-set-server=utf8
collation-server=utf8_unicode_ci

After change it'll look like as follow...
[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci
Now restart MySQL Server.

In my case solution 4 and 5 worked like charm. I tried to change collation_connection variable using mysql configuration file but didn't get success to solve this issue. Please share your solution if you have any.

Special Thanks to:
- http://forums.mysql.com/read.php?103,265345,265579#msg-265579
- http://www.excindex.com/mysql/589131.html

MySQL: Queries to 'Show All Database Indexes' and 'Drop All Table Indexes'

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As described above database index helps only for search operation. Insert, Update and Delete will suffer. Index should be created only selected fields based on your retrieval of data (Columns).

- Source (WikiPedia: http://en.wikipedia.org/wiki/Database_index)

In my previous article we discussed about MSSQL Indexes . Now we'll see how you can achieve same in MySQL. 

SHOW ALL INDEXES 
/* It'll show all indexes except Primary Key */
SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = (SELECT DATABASE())
AND INDEX_NAME != 'PRIMARY';

DROP ALL TABLE INDEXES
Basic Syntax of dropping index is DROP INDEX INDEX_NAME ON TABLE_NAME; but to drop index in one single shot is not possible. You've to loop through all indexes of database. Thus, We've to use Stored Procedure in MySQL.
DROP PROCEDURE IF EXISTS usp_DropIndexOfTable;
DELIMITER $$
CREATE PROCEDURE usp_DropIndexOfTable(IN TableName VARCHAR(100))
BEGIN
 /**
  * @Created By: Vicky Thakor
  * @Created On: 6th March, 2014
  * This will delete all Indexes of table except Primary Key
  * 
  * @Test: CALL usp_DropIndexOfTable('user_master');
  */
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_index varchar(200) DEFAULT "";
        DEClARE index_cursor CURSOR FOR 
        /* In below query remove TABLE_NAME criteria to remove all indexes */
        SELECT INDEX_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = (SELECT DATABASE())
  AND TABLE_NAME = TableName
  AND INDEX_NAME != 'PRIMARY';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

 DECLARE exit handler FOR sqlexception
 BEGIN
 -- ERROR
 SHOW ERRORS LIMIT 1;
 ROLLBACK;
 END;

 DECLARE exit handler FOR sqlwarning
 BEGIN
 -- WARNING
 SHOW WARNINGS LIMIT 1;
 ROLLBACK;
 END;

 OPEN index_cursor; 
  get_index: LOOP
   FETCH index_cursor INTO v_index;

   IF v_finished = 1 THEN 
    LEAVE get_index;
   END IF;
  
   SET @v_query = CONCAT('DROP INDEX ',v_index,' ON ', TableName);
   PREPARE stmt FROM @v_query;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END LOOP get_index;
       CLOSE index_cursor;
END$$
DELIMITER ;
Just fire above Stored Procedure in your database and then execute this stored procedure using
CALL usp_DropIndexOfTable('user_master'). You can modify above stored procedure according to your requirement.

MSSQL to MySQL Guide to Migration Part - 2

I covered few points in my previous article MSSQL to MySQL Guide to Migration Part - 1. We'll cover few more points for migration. So far we covered important points for basic migration in this article we'll discuss some extended migration points.

1. DENSE_RANK function
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. - Microsoft (http://technet.microsoft.com/en-us/library/ms173825.aspx)

You all may not aware of this function but it exists in MSSQL. This function doesn't exists in MySQL. There are many ways available on the internet but word to wise eliminate duplicate records if possible. Don't use this function if your product support multiple dialect.

2. Database INDEX migration
CLUSTERED INDEX Primary key will do the job in both MSSQL and MySQL.
NON-CLUSTERED INDEX There ain't clause "Non-Clustered" in MySQL. Yet you can assign multiple index in MySQL. Its just matter of syntax.
/* MSSQL */
CREATE NONCLUSTERED INDEX [IndxNc_user_master__MultiCol01] ON [dbo].[user_master] 
(
 [column1] ASC,
 [column2] ASC
);
/* -------------------------------------------------- */
/* MySQL */
ALTER TABLE user_master
ADD INDEX IndxNc_user_master__MultiCol01 
(
column1 ASC,
column2 ASC
);

3. JOIN(concat) two string in MSSQL and MySQL
MSSQL is designed in a way that it will identify your input.

  • If you are using string with `+` then it'll append and prepend for you. 
  • If you are using numeric value with `+` then it'll do summation. 

MySQL you've to use different methods to perform the same.

  • If you are using string use concat('string', column_value, 'string', column_value).
  • If you are using numeric value, its same as MSSQL. Use `+` sign
/* MSSQL numeric value */
SELECT 100 + id FROM user_master;
/* MSSQL String value */
SELECT ':'+Firstname+':' FROM user_master;
/* -------------------------------------------------- */
/* MySQL numeric value */
SELECT 100 + id FROM user_master;
/* MySQL String value */
SELECT concat(':', Firstname, ':') FROM user_master;

4. PRINT message in MSSQL and MySQL
MSSQL provide clause called PRINT to print run time custom message. In MySQL SELECT clause does the job for you.

Warning: PRINT in MSSQL won't effect your output result while you are dealing with hibernate, where in MySQL SELECT clause print message in tabular format so hibernate will use this table as a result. You may get wrong result in hibernate. Read more about hibernate + stored procedure How To Call Stored Procedure In Hibernate
/* MSSQL */
PRINT 'javaQuery';
/* -------------------------------------------------- */
/* MySQL */
SELECT 'javaQuery';

5. Comments
This is not much important however for your consideration.

  • MSSQL: It supports comment block /* comment */ and hyphen --.
  • MySQL: It supports comment block /* comment */ and hyphen following one space -- .
/* MSSQL */
/* MSSQL comments */
/* --SELECT * FROM user_master */
/* -------------------------------------------------- */
/* MySQL */
/* MySQL comments */
/* -- SELECT * FROM user_master */

6. SELECT values into variables
Selecting values in multiple values differ in both the database language. Here sample code is available for both database.

  • In MSSQL you can use `=` operator.
  • In MySQL you can use `INTO` phrase.
/* MSSQL */
DECLARE @VARIABLE1 VARCHAR(100);
DECLARE @VARIABLE2 VARCHAR(100);

SELECT @VARIABLE1 = Firstname, @VARIABLE2 = Lastname
FROM user_master
WHERE id = 1;
/* -------------------------------------------------- */
/* MySQL */
DECLARE VARIABLE1 VARCHAR(100);
DECLARE VARIABLE2 VARCHAR(100);
 
SELECT Firstname, Lastname INTO VARIABLE1, VARIABLE2 
FROM user_master
WHERE id = 1;

SPLIT function (Stored Procedure) in MySQL

As name suggest SPLIT means separate out something. Here we are talking about separating character, words from given string by specifying some special character.

Note: MySQL has limitation that It can't return table from function. That's why we have to create stored procedure of SPLIT Function in order to get value in tabular format.


DROP PROCEDURE IF EXISTS SP_SPLIT;
DELIMITER $$
CREATE PROCEDURE `SP_SPLIT`(
 IN StringValue LONGTEXT,
 IN DelimiterChar  VARCHAR(1))
BEGIN
 DECLARE INDX INT;
 DECLARE SLICE nvarchar(4000); 
 SET INDX = 1;
 
 DROP TEMPORARY TABLE IF EXISTS Results;
 CREATE TEMPORARY TABLE Results(Items VARCHAR(100));

 MyLoop: WHILE INDX != 0 DO
 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
 SET INDX = LOCATE(DelimiterChar,StringValue);
 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
 IF INDX !=0 THEN
  SET SLICE = LEFT(StringValue,INDX - 1);
 ELSE
  SET SLICE = StringValue;
 END IF;
 -- PUT THE ITEM INTO THE RESULTS SET
 INSERT INTO Results(Items) VALUES(SLICE);
 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
 SET StringValue = RIGHT(StringValue,LENGTH(StringValue) - INDX);
 -- BREAK OUT IF WE ARE DONE
 IF LENGTH(StringValue) = 0 THEN
  LEAVE MyLoop;
 END IF;
 END WHILE;
END

Step 1: Execute above Stored Procedure in your MySQL.

Step 2: Call Stored Procedure and select result.
CALL SP_SPLIT("HELLO,WORLD,JAVAQUERY,THIS IS LONG STRING. YOU CAN DO EVEN BETTER",",");
/* Results is table created in SP_SPLIT stored procedure. */
SELECT * FROM RESULTS;

To process further on your result read "MySQL: How to select data from Stored Procedure in to Stored Procedure"

MySQL: How to select data from Stored Procedure in to Stored Procedure



Few days back I wrote an article about How To Call Stored Procedure In Hibernate. After following few migration process of stored procedure. I was struggling to select data from another stored procedure within stored procedure. Finally I came up with solution after Googling few pages and reading books.

TEMPORARY table in MySQL is our wildcard in this database programming. Well TEMPORARY tables should be used carefully or you'll face few problems in MySQL. We'll discuss more about TEMPORARY tables in MySQL in my next article.

STORED PROCEDURE 1
Stored Procedure 1 is where we want to return something after processing some of our inputs.
/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_CALL;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_CALL()
BEGIN
 /* Drop TEMPORARY table if exists. */
 DROP TEMPORARY TABLE IF EXISTS Results;
 /* Create TEMPORARY table */
 CREATE TEMPORARY TABLE Results(Firstname VARCHAR(200));
 
 /* Insert value in TEMPORARY table */
 INSERT INTO Results 
 SELECT Firstname FROM user_master WHERE uid = 1;
END $
DELIMITER ;
STORED PROCEDURE 2
Stored Procedure 2 is where you want to select output of Stored Procedure 1. Here you can access TEMPORARY table Results declared in SP_CALL()
/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_SELECT;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_SELECT()
BEGIN
 /* Declare variable */
 DECLARE F_NAME VARCHAR(100);
 
 /* Call Stored Procedure */
 CALL SP_CALL();
 /* Select from TEMPORARY table declared in SP_CALL */
 SELECT Firstname INTO F_NAME FROM Results;
 
 /* Print value of variable */
 SELECT F_NAME;
END $
DELIMITER ;

MSSQL to MySQL Guide to Migration Part - 1

Database Migration is very important process of product based application. You may find bunch of software to migrate table structure from MSSQL to MySQL. There are very few software (Not any with full conversation) available to migrate Stored Procedure, Functions and Views.

I did this before so better not to waste your precious time to search for tools. You have to manually convert all Stored Procedure, Functions and Views. I came up with some basic rules that may help you to convert all of above.


1. "DECLARE" a variable in MSSQL and MySQL. 
You may think is it really matter? Yes, It is. MSSQL and MySQL follows different rules for declaring variables.

  • MSSQL: Variable name starts with '@'.
  • MySQL: You can't use '@'.
  • -------------------------------------------------------------------------
  • MSSQL: You can declare variable anywhere in Stored Procedure, Functions.
  • MySQL: You have to declare variable at the starting of Stored Procedure, Functions.

2. "ISNULL" in MSSQL is similar to "IFNULL" in MySQL.

3. Using "IF".
-- MSSQL
IF (condition)
BEGIN
     ......
END
ELSE IF (condition)
BEGIN
     ......
END
ELSE
BEGIN
     ......
END
--------------------------------
-- MySQL
IF (condition) THEN
     ......
ELSEIF (condition) THEN
     ......
ELSE
     ......
END IF;
4. "LEN" in MSSQL is similar to "LENGTH" in MySQL

5. Function in MSSQL and MySQL
You may wonder that MSSQL can return table from function but unfortunately MySQL can't. If you have function in MSSQL which returns table, then you've to convert that function in stored procedure in order to get table as return value. There ain't other option in MySQL. MySQL function can only return single value.

6. MySQL: How to select data from Stored Procedure in to Stored Procedure
Article posted at http://www.javaquery.com/2014/01/mysql-how-to-select-data-from-stored.html

7. Temporary tables in MySQL.

Its not written here because its very important part of conversion. We need to discuss it with proper examples. I'll write separate articles for it.

MSSQL to MySQL Guide to Migration Part - 2 is published at http://www.javaquery.com/2014/02/mssql-to-mysql-guide-to-migration-part-2.html

How To Call Stored Procedure In Hibernate

What is STORED PROCEDURE?
A stored procedure is a pre-compiled SQL subroutine used to perform multiple procedural operations.

"A stored procedure is a subroutine available to applications that access a relational database system" - WikiPedia
"A stored procedure is a group of Transact-SQL statements compiled into a single execution plan" - Microsoft

What will we cover in this article?
  • Sample MSSQL and MYSQL stored procedure.
  • How to call MSSQL and MYSQL stored procedure.
  • What should be taken care while executing stored procedure in Hibernate?

MSSQL Sample Stored Procedure
IF (OBJECT_ID('SP_MSSQL_HIBERNATE') IS NOT NULL)
  DROP PROCEDURE SP_MSSQL_HIBERNATE
GO
CREATE PROCEDURE SP_MSSQL_HIBERNATE
@PARAM1 INT,
@PARAM2 INT,
@PARAM3 VARCHAR(50)
AS 
BEGIN
 BEGIN TRANSACTION
 BEGIN TRY
    /* 
     * Uncomment below code to get custom row in hibernate.
     * ------------------------------------------------
     * DECLARE @X, @Y INT;
     * DECLARE @RESULT INT;
     * SET @RESULT = @X + @Y;
     * SELECT @RESULT AS RESULT, 'javaQuery' AS STRING_RESULT;
     */
 
    /* Your custom operation */ 
    UPDATE user_master SET Firstname = @PARAM3 WHERE UID = 1;
           
    /* Insert record */
    INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');
           
    /* Returns user object (row) */
    SELECT * FROM user_master WHERE UID = 1;

 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
          ROLLBACK TRANSACTION;
          PRINT @@ERROR
 END CATCH;
END
MYSQL Sample Stored Procedure
DROP PROCEDURE IF EXISTS SP_MYSQL_HIBERNATE;
DELIMITER $
CREATE PROCEDURE SP_MYSQL_HIBERNATE(IN PARAM1 INT, IN PARAM2 INT, IN PARAM3 VARCHAR(100))
BEGIN
 /*
  * Uncomment below code to get custom row in hibernate.
  * ------------------------------------------------
  * DECLARE X, Y INT DEFAULT 10;
  * DECLARE RESULT INT;
  * SET RESULT = X + Y;
  * SELECT RESULT AS RESULT, 'javaQuery' AS STRING_RESULT; 
  */

  /* Your custom operation */ 
  UPDATE user_master SET Firstname = PARAM3 WHERE UID = 1;
        
  /* Insert record */
  INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');

  /* Returns user object (row) */
  SELECT * FROM user_master WHERE UID = 1;
END $
DELIMITER ;

How to call MSSQL and MYSQL stored procedure in Hibernate?
/**
 * Create hibernate configuration.
 */
Configuration c = new Configuration();
c.configure("hibernate.cfg.xml");

/**
 * Open session and begin database transaction for database operation.
 */
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
Transaction t = session.beginTransaction();

/**
 * Create SQL Query for Stored Procedure and pass required parameters.
 * MSSQL : EXEC Name_Of_Stored_Procedure :param1, :param2
 * MYSQL : CALL Name_Of_Stored_Procedure :param1, :param2
 * 
 * `.addEntity(User.class)` will map output result as per User bean.
 */

/**************************************************/
/* Call MSSQL Stored Procedure and MAP it to bean */
/* Un-comment the code                            */
/**************************************************/
/*Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3").addEntity(User.class);
callStoredProcedure_MSSQL.setInteger("param1", 10);
callStoredProcedure_MSSQL.setInteger("param2", 10);
callStoredProcedure_MSSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
/*List userList = callStoredProcedure_MSSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}*/


/**************************************************/
/* Call MYSQL Stored Procedure and MAP it to bean */
/**************************************************/
Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
List userList = callStoredProcedure_MYSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}

/******************************************************************/
/* Process custom result of Stored Procedure                      */
/* Un-comment the code, This will be the same for MSSQL and MYSQL */
/******************************************************************/
/*Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
/*List customResult = callStoredProcedure_MYSQL.list();
if (customResult != null && !customResult.isEmpty()) {
 Object[] obj = customResult.get(0);
 System.out.println(obj[0]);
 System.out.println(obj[1]);            
}*/

/* Commit the transaction and close session. */
t.commit();
session.close();
Above code contains 3 portion for execution. Un-comment your required code and test it.

  1. How to Execute MSSQL Stored Procedure in Hibernate (code is commented)
  2. How to Call MYSQL Stored Procedure in Hibernate
  3. How to Process custom result of Stored Procedure in Hibernate (code is commented)

What should be taken care while executing stored procedure in Hibernate?

  • If you have single Insert, Update or Delete operation in your stored procedure then you have to beginTransactin() and commit() it in order to take effect.
  • Hibernate will only select first result of stored procedure. e.g: If you write two select statement then first result will be mapped for hibernate in case of bean and without bean it'll only return first result as list of object.
  • If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
    e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
  • If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
    e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)