CODEWITHSUNDEEP
javamysql
Emma
Emma

Reputation: 23

How to create stored procedure in mysql and call it in JAVA. i need some help on creating a callable procedure. below id my code

 drop PROCEDURE if exists insert_poo;
 DELIMITER $$

 CREATE PROCEDURE insert_poo(IN barcode varchar(250),IN qty float,IN amount float,IN vat float,IN 
description varchar(250),IN clrk_code varchar(20),IN mechno varchar(20),IN bill_date datetime)
BEGIN
DECLARE unit_pric float;
DECLARE itemcode varchar(150);
SET  unit_pric =(select retail1 FROM `mytable` WHERE  `mytable`.barcode = barcode);
SET  itemcode =(select prod_id FROM `mytable` WHERE `mytable`.barcode = barcode);
INSERT into mytable2(clrk_code,tran_code,tran_desc,tran_qty,unit_price,tran_amt,bill_date,tax)values(clrk_code,barcode,description,qty, unit_pric,amount,bill_date,vat)

END $$
DELIMITER ;

Kindly help with any solution on how to create and call it. thanks in advance

Upvotes: 0

Views: 81

Answers (1)

MauricioTL
MauricioTL

Reputation: 443

Probably you can use the following example as a reference:

The below one is the Store Procedure itself:

connect anyDbName/anyDbName

CREATE OR REPLACE PROCEDURE any_storeProcedure_name


-- Following are some example parameters you may use in your SP
(
  id varchar2,
  name_param            varchar2,

-- The control status of the operation
   statusOperation_out   OUT   VARCHAR2
)

AS
BEGIN
    statusOperation_out := 'in_proccess';

  insert into property_name       values('Name',        id, name_param);

  COMMIT;
statusOperation_out := 'ok';


EXCEPTION 
  WHEN OTHERS THEN
  statusOperation_out := 'error';
      ROLLBACK;
END;
/

And the following is the Java method call that is using the previous SP:

public long addProperties(String id, String  name) {


        //The string sql syntax for calling the store procedure
        String sql = "{CALL any_storeProcedure_name("
                + "id    => ?, "
                + "name  => ?)}";

        try (Connection conn = CreateConnection.getDSConnection();
                CallableStatement cs = conn.prepareCall(sql)) {


            //The following are the parameters for the store procedure
            cs.setString    (1, id);
            cs.setString    (2, name);

            //Following are the parameters to get some outputs from the store procedure
            cs.registerOutParameter(3, Types.VARCHAR);

            cs.executeQuery();

            //The return varible from the store procedure is the one
            //that is being used for feedback on whether the SP ran fine. 
            if (cs.getString(3).equalsIgnoreCase("ok")) {
                System.out.println("Feedback from SP is: " + cs.getString(3));
                return 1;
            } else {
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

So hope this can give some reference, by the way the DB I'm using is Oracle 11g. But I recall it's very similar to MySQL DB.

Upvotes: 1

Related Questions