This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to call procedure in to java for inserting values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to call procedure in to java for inserting values " Watch "how to call procedure in to java for inserting values " New topic
Author

how to call procedure in to java for inserting values

sarika Gupta
Greenhorn

Joined: Apr 27, 2005
Posts: 18
HI,
i made a procedure in SQLserver for inserting values into database
my poblem is how to call it in to java

my pocedure is


CREATE PROCEDURE INS_USER
@mst01_companyVARCHAR(50),
@mst01_uidVARCHAR(8),
@mst01_user_nameVARCHAR(30),
@mst01_PWDVARCHar(8),
@mst01_deptVARCHAR(3),
@mst01_user_lvlinteger,
@mst01_statusVARCHAR(10),
@mst01_entry_uidVARCHAR(8),
@mst01_modify_uidvarchar(8)

as


and i call it in java as is it rightway



Statement st = con.createStatement();
st.executeQuery("insert into INS_USER values('"+companyName+"','"+ userId+"','"+ userName+"','" + password+ "','"+ department+"',"+ lvl+",'"+ Status+"','"+userId+"','"+userId+"')");
flag = true;
}
catch (SQLException ex)
{
ex.printStackTrace();
}

please help
Nathan Pruett
Bartender

Joined: Oct 18, 2000
Posts: 4121

Moving to the JDBC forum...


-Nate
Write once, run anywhere, because there's nowhere to hide! - /. A.C.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

You need to use CallableStatment in order to use stored procedure.

A little googling will yield you lot of help

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Byron Estes
Ranch Hand

Joined: Feb 21, 2002
Posts: 313
Shailesh is right...but here's a code snipet and link to get you started...

CallableStatement





[ May 12, 2005: Message edited by: Byron Estes ]

Byron Estes<br />Sun Certified Enterprise Architect<br />Senior Consulant<br />Blackwell Consulting Services<br />Chicago, IL<br /><a href="http://www.bcsinc.com" target="_blank" rel="nofollow">www.bcsinc.com</a>
Manjit Barman
Greenhorn

Joined: Apr 04, 2005
Posts: 25
If you are using ref cursor.....even more detail codes for your reference!

[CODE]

Here is the Procedure

CREATE OR REPLACE PACKAGE Report
AS
TYPE Report_Cursor IS REF CURSOR;
PROCEDURE SELECT_REPORT (
report_Id IN number,
get_Id OUT Report_Cursor,
get_User OUT Report_Cursor,
get_Room OUT Report_Cursor,
get_Equipment OUT Report_Cursor,
get_Department OUT Report_Cursor);




CREATE OR REPLACE PACKAGE BODY Report

AS

PROCEDURE SELECT_REPORT (
report_id IN number,
get_Id OUT Report_Cursor,
get_User OUT Report_Cursor,
get_Room OUT Report_Cursor,
get_Equipment OUT Report_Cursor,
get_Department OUT Report_Cursor);


BEGIN

OPEN get_Id FOR
SELECT user_id, department_id, room_id, equipment_id, problem_description FROM der_reports WHERE report_id in report_Id;

OPEN get_User FOR
SELECT u.person_id, p.first_name, p.last_name, p.email, p.phone, u.alt_phone, upper(u.is_administrator),

upper(u.is_engineer) FROM der_users u, people@aruser p WHERE u.user_id =user_Id AND p.person_id = u.person_id
union
SELECT u.person_id, p.first_name, p.last_name, p.email, p.phone, u.alt_phone, upper(u.is_administrator),

upper(u.is_engineer) FROM der_users u, people_hist@aruser p WHERE u.user_id = user_Id AND p.person_id = u.person_id;




OPEN get_Department FOR
SELECT department_name FROM der_departments WHERE department_id = department_id;



OPEN get_Equipment FOR
SELECT e.manufacturer_id, m.manufacturer_name, e.category_id, c.category_name, e.equipment_name, e.equipment_model,

e.equipment_serial FROM der_equipment e, der_manufacturers m, der_categories c WHERE e.equipment_id = equipment_Id

AND m.manufacturer_id = e.manufacturer_id AND c.category_id = e.category_id;




OPEN get_Room FOR
SELECT r.room_name, r.building_id, b.building_name, r.floor_id, f.floor_name FROM der_rooms r, der_buildings b,

der_floors f WHERE room_id = room_Id AND b.building_id = r.building_id AND f.floor_id = r.floor_id AND
r.active = 'Y' ORDER BY 1;

close get_Id;
close get_User;
close get_Room;
close get_Equipment;
close get_Department;

END SELECT_REPORT;

END REPORT;
/



Java Code

CallableStatement cstmt =conn.prepareCall("{call REPORT.SELECT_REPORT(?,?,?,?,?,?)}");

//cstmt.setInt(1, reportIds[i]);

cstmt.setInt(1, java.lang.reflect.Array.getInt(reportIds,i));
cstmt.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);
cstmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
cstmt.registerOutParameter(4, oracle.jdbc.driver.OracleTypes.CURSOR);
cstmt.registerOutParameter(5, oracle.jdbc.driver.OracleTypes.CURSOR);
cstmt.registerOutParameter(6, oracle.jdbc.driver.OracleTypes.CURSOR);

cstmt.execute();
rslt1 = (ResultSet)cstmt.getObject(2);
rslt2 = (ResultSet)cstmt.getObject(3);
rslt3 = (ResultSet)cstmt.getObject(4);
rslt4 = (ResultSet)cstmt.getObject(5);
rslt5 = (ResultSet)cstmt.getObject(6);
System.out.println("ResultSet rslt1:"+rslt1);
while (rslt1.next())
{
userId = rslt1.getInt(1);
departmentId = rslt1.getInt(2);
roomId = rslt1.getInt(3);
equipmentId = rslt1.getInt(4);
problemDescription = rslt1.getString(5);
System.out.println("rslt1.next()");
}
[CODE]
 
Don't get me started about those stupid light bulbs.
 
subject: how to call procedure in to java for inserting values