Cannot insert record into Oracle database table when PreparedStatement is used with variables

Hi,
I am trying to insert 1 record of data into an Appointment table (AACAp) in Oracle 10g database. I am passing parameters from a jsp page to a servlet which then uses PreparedStatement to insert the record values, as variables, into the table. If I use literal strings and integers when I am inserting data it works fine, but if I use variables that I print to the page to ensure that they are OK before inserting them into the database, it does not work. Here is an excerpt of my code. The functional data is commented off, the non-functional data in variables is shown below uncommented. All variables have been declared as global variables. I'm using Apache Tomcat server 5.5.20.and jdk1.5

//Insert values into Appointment table AACAp in database using java.sql.PreparedStatement
                             
sql = "INSERT INTO AACAP (sid,pdate,adate,cdate,advlname,advfname,confmethod,stime," +
          "etime, mobile, hphone,bsurmphone,email)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement ps = con.prepareStatement(sql);

//Get current date and time using a Calendar instance
cal           = Calendar.getInstance();
int year      = cal.get(Calendar.YEAR);
int month     = cal.get(Calendar.MONTH);
int date      = cal.get(Calendar.DATE);
int hour      = cal.get(Calendar.HOUR);
int minute    = cal.get(Calendar.MINUTE);
int second    = cal.get(Calendar.SECOND);

/*                              
ps.setString(1, "2048017");
ps.setDate(2, new java.sql.Date(2007,10,8));
ps.setDate(3, new java.sql.Date(2007,10,9));
ps.setTimestamp(4, new java.sql.Timestamp(year,month,date,hour,minute,second,0));
ps.setString(5, "DOE");
ps.setString(6, "Jane");
ps.setString(7, "Email");
ps.setTimestamp(8, new java.sql.Timestamp(2007,10,8,10,0,0,0));
ps.setTimestamp(9, new java.sql.Timestamp(2007,10,8,11,0,0,0));
ps.setString(10, "222-111-222");
ps.setString(11, "301-322-1234");
ps.setString(12, "301-322-4321");
ps.setString(13, "jdoe@hotmail.com");
*/

//check variables to see their contents                              
out.println ("<p>" + pDate_yr + " " + aDate_yr + " " + pDate_mt + " " + aDate_mt + " " + pDate_dy + " " + aDate_dy + sid + " " + aData[2].trim() + " " + aData[1].trim() + " " + confirmn + " " + cPhone + " " + hPhone +
 rmPhone + " " + email+"</p>");

ps.clearParameters();
ps.setString(1, sid);
ps.setDate(2, new java.sql.Date(pDate_yr,pDate_mt,pDate_dy));
ps.setDate(3, new java.sql.Date(aDate_yr,aDate_mt,aDate_dy));
ps.setTimestamp(4, new java.sql.Timestamp(year,month,date,hour,minute,second,0));
ps.setString(5, aData[2].trim());
ps.setString(6, aData[1].trim());
ps.setString(7, confirmn);
ps.setTimestamp(8, new java.sql.Timestamp(pDate_yr,pDate_mt,pDate_dy,sTime_hr, 0, 0,0));
ps.setTimestamp(9, new java.sql.Timestamp(pDate_yr,pDate_mt,pDate_dy,eTime_hr, 0, 0,0));
ps.setString(10, cPhone);
ps.setString(11, hPhone);
ps.setString(12, rmPhone);
ps.setString(13, email);

ps.executeUpdate();
out.println ("it's done");  

marsgitte
marsgitteAsked:
Who is Participating?
 
sdstuberCommented:
The deprecation shouldn't be the problem, although deprecated they should still be supported.  
If they aren't you should be getting an exception in your catch block.  It seems very odd that an insert isn't happening but you're not getting an error anywhere.  Are you catching exceptions and throwing them away?  What's your try-catch logic look like?

A trigger "might" prevent an insert from happening but if it did, it would do so by raising an exception.
0
 
sdstuberCommented:
what happens?  error message?  no commit?  
0
 
marsgitteAuthor Commented:
If I use literal strings or data in quotation marks and integers when I am inserting data it works fine. A record is inserted into the table. But if I use variables that I print to the page to ensure that they are OK before inserting them into the database, it does not work. No records are added to the database table.

marsgitte
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
no exception is raised?  can you print messages in your catch block?

is there a trigger on the table?  can you see if the database is "trying" to do an insert?

I believe you that data isn't getting inserted, but need more info as to what's happening.
0
 
marsgitteAuthor Commented:
I haven't used triggers before so I am not sure what it is exactly. I cannot see if the data is trying to do an insert. To check if an insert was done, I open SQL*Plus and manually do a "Select * from AACAP" to see if any records were inserted. With the variables no records. with hard coded data 1 record is inserted.
marsgitte
0
 
marsgitteAuthor Commented:
I just recompiled my data and I think that 2 of the classes that I used in my servlet have been deprecated. java.sql.Date & java.sql.Timestamp. Could this be the problem since I am using jdk 1.5
0
 
sdstuberCommented:
Did you find your problem? If not, can you show what your try-catch logic is and I can try to help figure out what's going on.
0
 
marsgitteAuthor Commented:
Hi ,
I'm sorry for not replying sooner. You were right. In setting up my Appointment table in the database the STime(Start Time) and ETime(End Time) columns were declared with constraints. On my input form the 12 hour clock was being used whereas in the table a 24hr clock was being used. There was a constraint for example, that the end times could not be later than  7pm nor earlier than 9am but on a 24 hour clock 7pm was being seen as 7 am and was being refused entry. I systematically went through each column value insert and was able to arrive at the offending one. To solve the problem all times on the 12 hr clock had to be adjusted to a 24hr clock.

Also, I have all my servlet processing in one massive try/catch  block. I need to use better OOP  principles and separate some of the logic into separate methods so that I can see the separate exceptions more easily.

Thanks for your help.
marsgitte
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.