• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

stored procedures

 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can I get the results of a stored procedure to the browser? When I had regular SQL code, it was working fine, but when I replace it with a stored procedure, it did not give me a result back, just caught an exception.
Why?
 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is Stored Procedure for EMPID
Stored procedure Name : INSERT_PROCEDURE
EMPID SEQUENCE NAME : EMPSEQ
IN PARAMETERS : ALL PARAMETER EXCEPT EMPID
OUT PARAMETER : EMPID (U CAN GET THE EMPID AFTER INSERTING THE DATA INTO DATABASE)

CREATE OR REPLACE PROCEDURE INSERT_PROCEDURE(
NAME VARCHAR2(100),
CREATEDBY VARCHAR2(20),
EMPID OUT NUMBER) AS
BEGIN
SELECT EMPSEQ.NEXTVAL INTO EMPID FROM DUAL;
INSERT INTO EMP VALUES(EMPID,NAME,SHORTDESC,SUPERUSERFL,CREATEDBY,
MODIFIEDBY,CREATIONDATE,MODIFIEDDATE );
COMMIT;
END;

-----------------------------------------------------
//This normal bean or accessor class
class EmpTB{
//setXXX()....
//getXXX()...
}

//This is main class
class MainClass{
.......
public long insert(EmpTB myrecord)throws CreateException {
CreateException ce = null;
Connection dbConnection=null;
ResultSet rs=null;
CallableStatement cstmt=null;
long empId=0
try {
dbConnection = getConnection();
strQry="{call INSERT_PROCEDURE(?, ?, ? , ? )}";
cstmt = dbConnection.prepareCall(strQry);
cstmt.setString(1,myrecord.getName());
cstmt.setString(2,myrecord.getCreatedBy());
cstmt.setString(3,myrecord.getModifiedBy());
cstmt.registerOutParameter(4,java.sql.Types.NUMERIC);
int i=cstmt.executeUpdate();
if(i!=0)
{
//throw exception
}
empId=cstmt.getLong(8);
} catch(SQLException se) {
throw new CreateException(se.getMessage());
} finally {
try {
if( cstmt !=null) cstmt.close();
if (dbConnection != null ) dbConnection.close();
} catch (Exception e) {
throw new CreateException(e.getMessage());
}
return empId;
}
}//EOF INSERT
....
}

[This message has been edited by DAYANAND BURAMSHETTY (edited August 07, 2001).]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic