calling oracle function from jsp

[Anurag Pandey]

hi all..
I m working with JAVA for the first time..and i have a problem here..
i m working on oracle 9i application server...with the database on
oracle 9i database server...
i have a function on the database called 'next_value' with two in
parameters (Field In Varchar2, year_value In Number) and it Returns
VarChar2..
This function takes the values in ..compares the value of the
parameter 'Field' with the entries in the columns of a table called
'Sequence'..increment the column value by 1 and returns the
incremented value...

now the problem is that when I m calling this function from within my
jsp page on the Application server...i m getting errors.... the code
i m using is

CallableStatement cs = conn.prepareCall("{fld = Call
next_value(field,year_value)}");
cs.setString(1 ,"EZ_USER_CODE");
cs.setInt(2 , 0000);
cs.registerOutParameter(3,java.sql.Types.VARCHAR);
cs.execute();
String iuser_code = cs.getString(3);
cs.close();

if someone could help me here i'll be very thankfull...
my email id is anu...@teri.res.in
i m also available on msn at hi_a...@hotmail.com

thanks and regards
Anurag Pandey

[Andy Flowers]

Try this code

CallableStatement cs = conn.prepareCall("{? = Call next_value(?,?)}");
cs.registerOutParameter(1,java.sql.Types.VARCHAR);
cs.setString(2 ,"EZ_USER_CODE");
cs.setInt(3 , 0);

cs.execute();
String iuser_code = cs.getString(3);
cs.close();

Parameters in a callable statement are identified by a ?.

Take a look at http://java.sun.com/j2se/1.3/docs/api/ and llok at the API
definitions for CallableStatement and PreparedStatement for more details.

"Anurag Pandey" <anu...@teri.res.in> wrote in message
news:7be8fe69.03011...@posting.google.com...

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.443 / Virus Database: 248 - Release Date: 10/01/2003

[Anurag Pandey]

Dear Andy....
first of all thanks for responding......
i have tried the code exactly as given by u ...now my code is:

CallableStatement cs = conn.prepareCall("{? = Call NEXT_VALUE(?,?)}");

cs.registerOutParameter(1,java.sql.Types.VARCHAR);
cs.setString(2 ,"EZ_USER_CODE");

cs.setInt(3 , 0000);
cs.execute();
String iuser_code = cs.getString(1);//not cs.getString(3)
cs.close();
I hv used the question marks(?)..as told by u...i mean this is my
exact code....... but now it says..

There was an error doing the query:
java.sql.SQLException: Malformed SQL92 string at position: 5.
Expecting "call"

what do i do now i hv tried all the options.......

thanks once again...
Anurag Pandey

"Andy Flowers" <Andrew....@spamno.ntlworld.com> wrote in message news:<LdFU9.1079$iX6....@newsfep4-win.server.ntli.net>...

[Andy Flowers]

Apologies.

The 'Call' needs to be replaced by 'call' - note no capitals.

http://java.sun.com/j2se/1.4/docs/guide/jdbc/index.html helps explain the
intricacies of JDBC.