*
The moose likes JDBC and the fly likes not all variables bound Error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "not all variables bound Error" Watch "not all variables bound Error" New topic
Author

not all variables bound Error

Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 573
I am getting the not all variables bound error message. Reserach on the internet states that this error is triggered by not supplying the right number of varaibles to the place holders.
But I have:
The store procedure is as follows:
CREATE OR REPLACE procedure INSERTINTOTIMESHEET(cName IN varchar2,
pName IN varchar2,
charge IN varchar2,
inv IN varchar2,
we IN DATE)
IS
BEGIN
DECLARE
tID NUMBER;
cID NUMBER;
pID NUMBER;
cursor tscursor IS select timeSheetId from TIMESHEET;
BEGIN
select CUSTOMERID into cID from CUSTOMER where CUSTOMERNAME=cName;
select PROJId into pID from PROJECT where PROJNAME=pName;
OPEN tscursor;
FETCH tscursor INTO tID;
dbms_output.put_line('SQL%ROWCOUNT '|| tscursor%ROWCOUNT);
IF tscursor%ROWCOUNT = 0 THEN
dbms_output.put_line('0 rows returned');
INSERT INTO TIMESHEET values (100,cID,pID,charge,0,inv,we);
ELSE
dbms_output.put_line('1 row returned');
select max(timeSheetId) into tID from TIMESHEET;
tID := tID+1;
INSERT INTO TIMESHEET values (tID,cID,pID,charge,0,inv,we);
END IF;

END;
END;
This procedure works by it self, fails when I call it from a JSP:
String Customer = request.getParameter("Customer");
String Project = request.getParameter("Project");
String Chargeble = request.getParameter("Chargeble");
String Invoiced = request.getParameter("Invoiced");
String weekEnding = request.getParameter("Week Ending");
try
{
CallableStatement cstmt =
conn.prepareCall("{call INSERTINTOTIMESHEET(?,?,?,?,?)}");
cstmt.executeQuery();
cstmt.setString(1, Customer);
cstmt.setString(2, Project);
cstmt.setString(3, Chargeble);
cstmt.setString(4, Invoiced);
cstmt.setString(5, weekEnding);
}
catch(SQLException se)
{
System.out.println(se);
}
Thanks for any help Tony.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Tony,
It's probably happening where you are trying to convert a java string to an oracle date. Can you use a java.util.Date when setting the parameter? (This is cleaner anyway since you don't have to worry about database data formats.)


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 573
I agree but I had a nightmare of a time tring to get Date to work in my Java application. The Date comes in as a string, I then tried to convert it into a date using DateFormat class, but although it supposed to return a Java.util.Date the complier complaine that it was expecting a Java.SQL.Date.
When I used Java.SQL.Date the system just crashed.
Tony
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60810
    
  65

To convert a java.util.Date, let's say in variable xyz to a java.sql.Date (not java.SQL.Date):

new java.sql.Date( xyz.getTime() )

What do you mean by "made the system crash?"


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: not all variables bound Error
 
Similar Threads
Callable Statement
java.sql.SQLException: ORA-06576: not a valid function or procedure name
Stored Procedure
SQL statement to java
Store Procedure Codes Badlu In need of Help!!