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

Posted on 2007-10-09
Last Modified: 2013-12-19
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, "");

//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");  

Question by:marsgitte
    LVL 73

    Expert Comment

    what happens?  error message?  no commit?  

    Author Comment

    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.

    LVL 73

    Expert Comment

    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.

    Author Comment

    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.

    Author Comment

    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
    LVL 73

    Accepted Solution

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

    Expert Comment

    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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    wordsFront challenge 8 47
    scoresSpecial  challenge 13 34
    oracle query help 29 45
    Oracle function works in 11g but not in 12c 21 31
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now