[an error occurred while processing this directive]

Insert Data in Table Using Stored Procedure 

                         

In this example we are inserting data into a table using stored procedure.

Steps:
1.Create database:
To create database we use syntax: create database database_name. We can create database with the name "Employee"  as 

mysql> create database Employee;

Query OK, 1 row affected (0.09 sec)

2.Change the database:
 We are using
use database_name; to change the database

mysql> use employee;
Database changed

3.Create the table:

mysql> create table emp(name char(12),fathername char(12),password char(12));

Query OK, 0 rows affected (0.55 sec)

4.Create procedure :
 1.We can use delimiter to create multiple statements. To create delimiter we can use following syntax.

mysql> DELIMITER // 

2.To create procedure we use following syntax:
mysql>create procedure procedure_name(IN |OUT | INOUT) param_name type)
3.We write sql statement into begin......end body. To start begin use  
mysql>begin and to end use: mysql>end;
4.To finish the procedure use
mysql>// delimiter.

mysql> delimiter //
mysql> create procedure empproc(in name char(12),in fathername char(12),in password char(12))
-> begin
-> insert into emp values(name,fathername,password);
-> end;
-> //
Query OK, 0 rows affected (0.22 sec)

5.Step to load driver:
To load the driver we are using syntax:
Class.forName("com.mysql.jdbc.Driver").newInstance();

6.Steps to make connection:
We are using getConnection("url/database","user","password") method to create a connection.
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","root","
root");

7.Step to call procedure:
We are using CallableStatement to execute a stored procedure into java code. The following syntax is used to create an object of CallableStatement interface.
CallableStatement calstat=conn.prepareCall("{call empproc(?,?,?)}");

8.Step to pass the values into procedure:
To set the values we are using setXXX(). Here XXX is data type Object class name, e.g setString() to pass string in procedure.

9.Step to execute query :
To execute query we can use:
ResultSet
 rs = calstat.executeQuery();

10.Close connection :
calstat.close();

The code of the program is given below:

import  java.sql.*;
public class Insert_EMP{
  public static void main(String a[])throws Exception {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection conn=DriverManager.getConnection("jdbc:mysql:
//localhost:3306/employee"
,"root","root");
    CallableStatement calstat=conn.prepareCall("{call 
empproc(?,?,?)}"
);
    calstat.setString(1,a[0]);
    calstat.setString(2,a[1]);
    calstat.setString(3,a[2]);
    ResultSet rs = calstat.executeQuery();
    conn.close();
    calstat.close();
    System.out.println("Your data has been inserted into table.");
  }
}

The code of the stored procedure is given below:

delimiter //
create procedure empproc(in name char(12),in fathername 
char(12),in password char(12))
begin
insert into emp values(name,fathername,password);
end;
//

 

The output of the program is given below:

C:\rajesh\mysql>javac Insert_EMP.java
C:\rajesh\mysql>java Insert_EMP Rajesh Ram  Raju
Your data has been inserted into table.

Download this example.(Java Code)

Download this example.(Procedure)

                         

[an error occurred while processing this directive]