SolveItproblems

SolveITProblems is a project of QFormat.net intellectual club

SolveITProblems это проект интеллектуального клуба QFormat.net

пятница, 12 ноября 2010 г.

MS SQL Server 2000 Stored Procedure and Java CallableStatement

Hi. i will show an example of the creating stored procedure on MS SQL Server 2000 (you can use other version too) and how to run it from Java program using JDBC CallableStatement.

Lets begin from Stored Procedure.

This example i will take from my real project. Here i use stored procedure to add some data to table, i use variables and system function getdate() etc. i think it useful too...




CREATE PROCEDURE insert_order_head 
(@order_dep_id int,@budget_code varchar(50),@order_owner_id int,@order_req_number int output)
AS

DECLARE @datetime datetime
SET @datetime= getdate()

DECLARE @order_status int
SET @order_status = 2

insert into tbl_orders_head  
( 
order_department_id,budget_code,
order_creation_time,order_status,order_owner_id
) 
values 
(
@order_dep_id,@budget_code,
@datetime,@order_status,@order_owner_id
)

select @order_req_number = order_requsition_number from tbl_orders_head where
order_creation_time = @datetime

return @order_req_number
GO


As you see, here i have a value which returns like a function. So procedure can return some value and act like a function.

So the next step is to execute the stored procedure from the Java program. as i said before we will use CallableStatement from JDBC. Here the example code:

try {
      CallableStatement stmt = Controller.connect.getConnection().prepareCall("{call insert_order_head (?,?,?,?)}");
        stmt.setInt(1, 1);
        stmt.setString(2, "code143");
        stmt.setInt(3, 1);
        stmt.registerOutParameter(4, java.sql.Types.INTEGER);
        stmt.execute();
        int order_req_id = stmt.getInt(4);

Using stored procedures helps to reduce program code and often makes your system faster... Use it :)

Комментариев нет:

Отправить комментарий