Quote:>It can be if you do not call any setter for "?"
>and call execute().
>Please, post several more lines of code and
>try to identify exact place where exception is thrown.
I knocked up a small JSP page to demonstate this. The last part of the
log, including the stack trace, follows the program listing.
As you can see from the log, the program apears to fail on this:
myPs = conn.prepareStatement(myAgmntSQL);
I'm baffled by the "ORA-01008: not all variables bound" error message
because I'm binding the variable by using setString method, and this
works when doing a direct connection to ORACLE with the thin JDBC
driver.
The variable is defined in the database as NUMBER. I've experimented
with setString and setLong, but to no avail.
I've scoured the web sites and newsgroups, but nobody who has had a
similar problem seems to have had a resolution. None of the Java guys
in my office can help either.
Roger.
************start of JSP page***************
language="java"
import="java.sql.Connection, java.sql.SQLException,
java.sql.PreparedStatement, java.sql.ResultSet, javax.naming.Context,
javax.naming.InitialContext,
javax.sql.DataSource, javax.naming.NamingException,
java.io.StringWriter, java.lang.Long"
session="true"
buffer="8kb"
autoFlush="true"
isThreadSafe="true"
isErrorPage="false"
contentType="text/html; charset=ISO-8859-1"
%>
<html>
<body>
<p>
Test to show connection pooling connectionto DB
</p>
<%
String myAgmntStartDate = null;
String myAgmntEndDate = null;
Connection conn = null;
PreparedStatement myPs = null;
ResultSet myRs = null;
String myAgmntSQL = "select start_dt, end_dt " +
"from sma_agmnts " +
"where agmnt_sys_id = ?";
String myAgmntSysId = "767";
//long myAgmntSysIdLong = Long.parseLong(myAgmntSysId.trim());
try
{
Context myCtx = new InitialContext();
System.out.println("Created Context");
DataSource ds = (DataSource)myCtx.lookup("Keown");
System.out.println("Keown has a big datasource");
conn = ds.getConnection();
System.out.println("About to call prepareStatement");
myPs = conn.prepareStatement(myAgmntSQL);
System.out.println("About to call setString");
myPs.setString(1,myAgmntSysId);
// myPs.setLong(1,myAgmntSysIdLong);
System.out.println("About to call executeQuery");
myRs = myPs.executeQuery();
if (myRs.next())
{
System.out.println("Obtaining agreement start/end dates for "
+ myAgmntSysId);
myAgmntStartDate = myRs.getString(1);
myAgmntEndDate = myRs.getString(2);
System.out.println("Start date: " + myAgmntStartDate);
System.out.println("End date: " + myAgmntEndDate);
}
Quote:}
catch(SQLException sqle)
{
System.out.println("Error on getting datasource connection: " +
sqle.getMessage());
StringWriter mySw = new StringWriter();
sqle.printStackTrace(new PrintWriter(mySw));
String myTrace = mySw.toString();
System.out.println(myTrace);
Quote:}
catch (NamingException ne)
{
System.out.println("Error in retrieving Keown: " +
ne.getExplanation());
Quote:}
catch (Exception e)
{
System.out.println("Keown is ugly: " + e.getMessage());
Quote:}
finally
{
try
{
if (myRs != null)
{
myRs.close();
}
if (myPs != null)
{
myPs.close();
}
}
catch (SQLException sqle)
{
System.out.println("Error in close: " + sqle.getMessage());
}
Quote:}
%>
</body>
</html>
***********LOG********************
Wed Aug 14 08:49:02 GMT+00:00 2002:<I> <ServletContext-General>
Generated
java file:
/be/home/bedev02/weblogic/myserver/classfiles/jsp_servlet/_sma/_Test2.java
Created Context
Keown has a big datasource
About to call prepareStatement
Error on getting datasource connection: ORA-01008: not all variables
bound
java.sql.SQLException: ORA-01008: not all variables bound
at
weblogic.db.oci.OciCursor.getCDAException(OciCursor.java:228)
at
weblogic.jdbcbase.oci.Statement.private_parse(Statement.java:717)
at
weblogic.jdbc.oci.Connection.prepareStatement(Connection.java:42)
at
weblogic.jdbc20.pool.Connection.prepareStatement(Connection.java:45)
at
weblogic.jdbc20.rmi.internal.ConnectionImpl.prepareStatement(ConnectionImpl.java:80)
at
weblogic.jdbc20.rmi.SerialConnection.prepareStatement(SerialConnection.java:55)
at jsp_servlet._sma._Test2._jspService(_Test2.java:123)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:27)
at
weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:105)
at
weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:123)
at
weblogic.servlet.internal.ServletContextImpl.invokeServlet(ServletContextImpl.java:742)
at
weblogic.servlet.internal.ServletContextImpl.invokeServlet(ServletContextImpl.java:686)
at
weblogic.servlet.internal.ServletContextManager.invokeServlet(ServletContextManager.java:247)
at
weblogic.socket.MuxableSocketHTTP.invokeServlet(MuxableSocketHTTP.java:361)
at
weblogic.socket.MuxableSocketHTTP.execute(MuxableSocketHTTP.java:261)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
by