Beware when using prepared statements ( such as the stored procedure call ).
We found that ODBC was twice as fast as some type 4 JDBC drivers due to
their implementation. Implementation is visible by tracing the SQL fired
off.
Dont worry too much if you are not coding a high transaction app
Regards
Dan
-----Original Message-----
From: James Tuan [mailto:[EMAIL PROTECTED]]
Sent: 21 May 1999 11:19
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedures.
Here are code samples for the Database Call, Data retrieval and The Database
Package. I wish I had this for my first project. We did a lot of embed SQL
calls instead of the stored procedure calls. Good luck.
//
***************************************************************************
//
***************************************************************************
public ResultSet getPERSON (int person_id){
ResultSet rs=null;
try {
CallableStatement call = con.prepareCall ("{ ? = call
pk_PERSON.queryPERSON(?) }");
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setInt(2,person_id);
call.execute ();
rs = (ResultSet)call.getObject (1);
}
catch ( SQLException sqlexecp) {
sqlexecp.printStackTrace();
}
return (rs) ;
}
//
***************************************************************************
//
***************************************************************************
public void getDataFromDatabase( ResultSet rs ) {
try {
Vector plist = new Vector();
while ( rs.next() ){
Person aPerson = new Person();
aPerson.personid= rs.getInt("PERSON_ID");
aPerson.personlogin= rs.getString("PERSON_LOGIN");
aPerson.personpassword= rs.getString("PERSON_PASSWORD");
aPerson.personfname= rs.getString("PERSON_FNAME");
aPerson.personlname= rs.getString("PERSON_LNAME");
aPerson.personssnum= rs.getString("PERSON_SS_NUM");
aPerson.personbdate= rs.getString("PERSON_BDATE");
aPerson.personemail= rs.getString("PERSON_EMAIL");
aPerson.personworkphone= rs.getString("PERSON_WORK_PHONE");
aPerson.personbirthfatherpersonid=
rs.getInt("PERSON_BIRTH_FATHER_PERSON_ID");
aPerson.personbirthmotherpersonid=
rs.getInt("PERSON_BIRTH_MOTHER_PERSON_ID");
plist.addElement(aPerson);
}
}
catch( Exception ex ) {
ex.printStackTrace();
}
}
//
***************************************************************************
//
***************************************************************************
create or replace package pk_PERSON IS
TYPE PERSONRecord IS RECORD
(
personid PERSON.PERSON_ID%type,
personlogin PERSON.PERSON_LOGIN%type,
personpassword PERSON.PERSON_PASSWORD%type,
personfname PERSON.PERSON_FNAME%type,
personlname PERSON.PERSON_LNAME%type,
personssnum PERSON.PERSON_SS_NUM%type,
personbdate varchar2(80),
personemail PERSON.PERSON_EMAIL%type,
personworkphone PERSON.PERSON_WORK_PHONE%type,
personbirthfatherpersonid PERSON.PERSON_BIRTH_FATHER_PERSON_ID%type,
personbirthmotherpersonid PERSON.PERSON_BIRTH_MOTHER_PERSON_ID%type
);
type byPERSON is ref cursor return PERSONRecord;
function queryPERSON(Id number) return byPERSON;
END pk_PERSON;
//
***************************************************************************
//
***************************************************************************
create or replace package body pk_PERSON IS
function queryPERSON (ID number)return byPERSON is rc byPERSON;
BEGIN
open rc for
SELECT
t1.PERSON_ID,
PERSON_LOGIN,
PERSON_PASSWORD,
PERSON_FNAME,
PERSON_LNAME,
PERSON_SS_NUM,
to_char(PERSON_BDATE,'MM-DD-YYYY') PERSON_BDATE,
PERSON_EMAIL,
PERSON_WORK_PHONE,
PERSON_BIRTH_FATHER_PERSON_ID,
PERSON_BIRTH_MOTHER_PERSON_ID
FROM
PERSON t1, FAMILY_PERSON t2
WHERE
t1.person_id = t2.person_id and
t2.STATUS > 0 and
t2.FAMILY_ID in ( select FAMILY_ID from FAMILY_PERSON where
PERSON_ID = ID and STATUS > 0);
return rc;
END;
END pk_PERSON;
Carlo Indolfi wrote:
> Hi to all!!!
>
> Is possible with servlets, or more in general, with Java a kind of
> management of Oracle's Stored Procedures, such as query,
> bring a ResultSet returned from the Stored Procedure, bring a
> value or more values returned from the Stored Procedure??
>
> Thank's in advance
> Regards
> Carlo :^)
>
>
___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
