Showing posts with label Stored Procedure. Show all posts

Date [java.util.Date] conversion from Java to SQL

Datatype Conversion is silent part of any project, yet very important. You'd like to read importance of Datatype Conversion [http://en.wikipedia.org/wiki/Cluster_(spacecraft)]

Case Scenario:
  • Pass current date (java.util.Date) to database stored procedure in varchar / string format.
  • Convert string date to datetime at database level. 
  • Process our logic and return date in string format.
  • Get date in string format at java.
  • Convert string date in java to java.util.Date

java.util.Date > String > datetime(MSSQL Database) > String > java.util.Date

Step 1: We'll create sample stored procedure to test our conversion.
IF (OBJECT_ID('SP_DateTime') IS NOT NULL)
  DROP PROCEDURE SP_DateTime
GO
CREATE PROCEDURE SP_DateTime @DateTimeString VARCHAR(100)
AS
BEGIN
 /**
  * @author javaQuery
  * @test EXEC SP_DateTime '2012-12-15 16:30'
  */
 DECLARE @v_Datetime DATETIME;
 SET @v_Datetime = CONVERT(varchar,@DateTimeString, 120);
 /* Compare dates and other business logic */
 SELECT CAST(@v_Datetime AS VARCHAR(100));
END

Step 2. Calling above stored procedure from Java (Hibernate: How To Call Stored Procedure In Hibernate).
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class SP_DATETIME {

    public static void main(String[] args) {
        /* 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();

        /* Call Stored Procedure */
        Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_DateTime :param1");
        callStoredProcedure_MSSQL.setString("param1", String.valueOf(new Timestamp(new Date().getTime())));

        /* Get return value */
        List customResult = callStoredProcedure_MSSQL.list();
        if (customResult != null && !customResult.isEmpty()) {
            SimpleDateFormat formatter = new SimpleDateFormat("MMM dd yyyy hh:mma");
            try {
                Date d = formatter.parse(customResult.get(0));
                System.out.println(d.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        session.close();
    }
}
For other date format check http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Question: Why we choose string rather than timestamp?
Answer: Consider if you have common class that pass call to stored procedure. You don't know which datatype will come as an argument to your method. We'll set every parameter as string.

Question: Why we are returning date as string from Stored Procedure.
Answer: When we get NULL from Database hibernate will try to convert NULL to date and will throw an exception.

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 ;

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)

Microsoft SQL Function : lastIndexOf

I was working around Stored Procedure in MSSQL mean while I get to know that there ain't any function called lastIndexOf(). So I coded it and thought let me share it with you guys. Its one time execution for each database and you can use it in queries, stored procedure, etc...

lastindexof , database

CREATE FUNCTION [dbo].[lastIndexOf] (@String VARCHAR(max) ,@Find VARCHAR(100))
RETURNS INT
AS
BEGIN
 DECLARE @SPLITTED_STRING VARCHAR(500);
 DECLARE @INDEX INT;
 DECLARE @PREVIOUS_INDEX INT;
 DECLARE @LEN INT;
 
 SET @INDEX = 0;
 SET @PREVIOUS_INDEX = 0;
 SET @SPLITTED_STRING = @String;
 
 WHILE ((SELECT CHARINDEX(@Find, @SPLITTED_STRING)) > 0)
  BEGIN
   SELECT @PREVIOUS_INDEX = CHARINDEX(@Find, @SPLITTED_STRING);
   SELECT @LEN = LEN(@SPLITTED_STRING);
   SELECT @SPLITTED_STRING = SUBSTRING(@SPLITTED_STRING, @PREVIOUS_INDEX+1, @LEN);
   SET @INDEX = @PREVIOUS_INDEX + @INDEX;
  END
  
 RETURN @INDEX - 1;
END

How to run this script in MSSQL?
Just copy the above script and paste it in SQL query editor. Press F5 or execute it.

How to use lastIndexOf function?
To get index of specified text execute your query like...
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','C');
PRINT @location
//output : 2
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','A');
PRINT @location
//output : 0
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','F');
PRINT @location
//output : -1
DECLARE @location int;
SET @location = [dbo].[lastIndexOf] ('ABCXYZ','AT');
PRINT @location
//output : -1

How to create log file from/for Stored Procedure. Microsoft SQL Server

What is log file?
If you are experienced programmer then you know importance of log file. Beginner must understand the importance of log file. Log file is like light in the middle of dark jungle. Log file contains the information of any execution that you are working around. It doesn't matter you are working around computer or some machine in car manufacture unit.

Below example is for 'Microsoft SQL Server'. Change variables and input filename as per your requirement. If you want it in your stored procedure just append below code with in stored procedure.
Log file , Stored Procedure, Microsoft SQL Server


------------------------------- At the starting of Stored Procedure -------------------------------
USE [NAME_OF_DATABASE]
GO

/*
 * Microsoft SQL Server
 * Stored Procedure : logExample.sql
 * Date of Creation : 1st Aug, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

/* Start: Log Creation Settings */
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
/* End: Log Creation Settings */
------------------------------- At the starting of Stored Procedure -------------------------------
------------------------------- With in Stored Procedure -------------------------------
/* Start: Log variables */
DECLARE @cmd sysname, @var sysname
DECLARE @LogFilePath varchar(255)
/* End: Log variable */

SET @LogFilePath = 'D:\SPLog\log_file_name.log'
SET @var = 'Line you want to store to file/Message like: Start execution';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd

SET @var = 'Step 2 and so on...';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd
------------------------------- With in Stored Procedure -------------------------------

Introduction: Stored Procedure, Microsoft SQL Server

Stored Procedure Microsoft SQL Server

What is Stored Procedure?
Stored Procedure is nothing but the Program of database. In other words we can say its user defined function or method of database. That contains mixed database query like Insert, Select, Update, Delete, Join, Cursor, etc... in one file.

Why Stored Procedure?
Stored Procedure is very useful in case of complex project structure. Where you need to perform more than one operation on database. Write all you database queries in one SQL file, pass required parameter in it and rest will be on Stored Procedure.
Its not recommended to use Stored Procedure in case of one or two query execution.

Examples
Lets have hands on basic example of Stored Procedure. For better understanding and just to server basic idea. I'll use one query in my stored procedure however I won't recommend you to use it for one query.

1. Select all data of one organization (using organization id) and create new copy for new organization(using new organization id)
USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : copyData.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[copyData]
  -- Add the parameters for the stored procedure here
  @NewOrganizationID int,
  @OldOrganizationID int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        PRINT 'Copy data from Organization '+CAST(@OldOrganizationID AS VARCHAR(10))+' to '+CAST(@NewOrganizationID AS VARCHAR(10))
        /* Start: Copy rows */
        /* 
         * Below query find all rows where condition matches and insert new rows with new organizationID.
         * It doesn't matter how many rows are there.
         * To limit data you can use TOP n keyword after SELECT statement.
         */
        INSERT INTO [dbo].[TABLE_NAME]
               ([column_1] ,[column_2] ,[column_3] ,[ORGANIZATION_ID])
        SELECT  [column_1] ,[column_2] ,[column_3] ,@NewOrganizationID
        FROM TABLE_NAME
        WHERE ORGANIZATION_ID = @OldOrganizationID
        /* End: Copy rows */
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO

2. Example of IF and WHILE for Microsoft SQL Server
USE [NAME_OF_DATABASE]
GO
/*
 * Microsoft SQL Server
 * Stored Procedure : Example2.sql
 * Date of Creation : 24th July, 2013  
 * @author: Vicky Thakor
**/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Example2]
  -- Add the parameters for the stored procedure here
  /* bit used for true or false in form of 1 and 0 */
  @PrintIFFlag bit,
  @LOOP_TO int
AS
BEGIN/* PROCEDURE BEGIN */
 BEGIN TRANSACTION
        /* Stops the message that shows the count of the number of rows affected 
 * by a Transact-SQL statement or stored procedure from being returned as part of the result set. 
 * example : (1 row(s) affected)
 * Syntax : SET NOCOUNT { ON | OFF } 
 **/ 
        SET NOCOUNT ON;
        DECLARE INDEX_COUNT = 0;

        IF @PrintIFFlag = 1
 BEGIN
  PRINT 'YOU ARE IN IF STATEMENT'
 END

        WHILE @INDEX_COUNT != @LOOP_TO
        BEGIN
              PRINT 'INDEX:'+CAST(@INDEX_COUNT AS VARCHAR(10))
              SET @INDEX_COUNT = INDEX_COUNT + 1;
        END         
        COMMIT TRANSACTION
END/* PROCEDURE END */
GO
-- EXEC Example2 1,100

How to execute Stored Procedure?
This Stored Procedure created for Microsoft SQL Server. Follow the below steps for complete execution.
  1. Execute Stored Procedure so for the first time it creates copy of stored procedure in database. For better understanding use Microsoft SQL Server Studio.
  2. After First execution change CREATE to ALTER because when you update something in your Stored Procedure you need to just alter the existing Stored Procedure. Database won't allow you to create Stored Procedure with same name.
  3. Fire EXEC NAME_OF_SP parameters. Check last line in example2

You have to be very careful while executing the stored procedure. As Stored Procedure perform lots of operation in database. You are recommended to follow some rules.
  • Before you create Stored Procedure on client's database test it on your local database.
  • While testing Stored Procedure Print required information using PRINT command. Like inserting, deleting, etc... 
  • If you are creating copy of information using some ID print previous ID and new ID. It'll help you to  delete the data from database. Stored Procedure will create garbage data on your database if you are not deleting test data.