Using Stored Procedure in Provisioning

Steps to create a procedure in MySQL

1. Starts MySql and use the required database with the help of use query.

 (Example: - mysql>use identityiq;)

 2. Use delimiter to change the end point of query.

 (Example:- mysql>delimiter /)

 3. Run the query to make a procedure.

 Example: - mysql>create procedure insert_emp
                   (IN EmpId varchar(20),IN firstName varchar(30),IN lastName varchar(30),IN managerId varchar(20),IN FullName varchar(70),IN email varchar(50),IN department varchar(30))
                  begin
                  insert into employees(EmpId,firstName,lastName,managerId,FullName,email,department) values(EmpId,firstName,lastName,managerId,FullName,email,department);
                  end;
                  /

 4. Procedure has been created successfully.

Using procedure in Provision Rule to write the data in database

 1. Import java.sql.CallableStatement in your code.

 2.   Paste this line into your code after editing as per your code requirement.

     <CallableStatement statement = connection.prepareCall("{call insert_emp(?,?,?,?,?,?,?)}");>

 3. Save your code and try to provision one identity.

 After provision a new identity will reflect in your table.

Procedure to fetch data from database

1. Run the query to make a procedure. Example: -

  mysql> delimiter /
  mysql> create procedure getdata_emp()
          -> begin
          -> select * from employees;
          -> end;
          -> / 

 2. Procedure has been created successfully. Call it in your application SQL query and test connection.

 

 3. Hit save and you have successfully called the procedure to fetch data from database.