Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3133
  • Last Modified:

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

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.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);

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

  • 4
  • 4
1 Solution
what happens?  error message?  no commit?  
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.

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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
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
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.
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.
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now