0 Replies Latest reply: May 22, 2012 3:01 PM by 911056 RSS

    Java Prepared Statements Bind Variable not getting set in Eclipse

    911056
      Hi,

      I have a simple Prepared Statement written in Java. I am using Oracle 11g and Weblogic and Indigo(OEPE). My bind variable are not getting set. I am using oracle varchar/char columns in 'Customer' table. Infact all my table, but for starters here is 'Customer' table. I am not getting any type of error message from Oracle. I am using Weblogic 10.3.6 server and default character set.

      I believe I have given all privailages to user 'STRUTS'.

      CREATE TABLE "STRUTS"."CUSTOMER" ("CUSTOMERID" NUMBER, "FIRSTNAME" CHAR(60), "MIDDLENAME" CHAR(60), "LASTNAME" CHAR(60), "ADDRESS1" CHAR(60), "ADDRESS2" CHAR(60), "CITY" CHAR(40), "STATE" CHAR(2), "ZIPCODE" CHAR(9), "COUNTRY" CHAR(60), "EMAIL" VARCHAR2(60), "EMAIL2" VARCHAR2(60), "PHONENUMBER" VARCHAR2(10), "PHONENUMBER2" VARCHAR2(10),
      constraint "CUSTOMER_PK" primary key("CUSTOMERID") using index tablespace USERS ) tablespace USERS;

      create sequence "STRUTS"."CUSTOMER_SEQ" start with 1 increment by 1;


      try {
                     // Load the MySQL JDBC driver
                               try {
                               // Put connection properties in to a hashtable.
                     Hashtable<String,String> ht = new Hashtable<String,String>();
                     ht.put(Context.INITIAL_CONTEXT_FACTORY,
                     "weblogic.jndi.WLInitialContextFactory");
                     ht.put(Context.PROVIDER_URL,
                     "t3://localhost:7001");

                     // Get a context for the JNDI lookup
                     ic = new InitialContext(ht);
                     // Look up the data source
                     ds
                     = (javax.sql.DataSource) ic.lookup("Oracle11g");
                     
                               } catch (NamingException e) {                              
                                         e.printStackTrace();
                                    
                                    } catch ( Exception e) {                              
                                         e.printStackTrace();
                                    }
                                    //con = ds.getConnection("system", "Janice21a");
                               con = ds.getConnection();
                               
                               //transaction manager; rollsback if any of it fails
                               con.setAutoCommit(false);

      String insertString = "INSERT INTO STRUTS.CUSTOMER(CUSTOMERID , FIRSTNAME , MIDDLENAME , " +
                                    "LASTNAME , ADDRESS1 , ADDRESS2 , CITY , STATE , "+
                                    "ZIPCODE , COUNTRY , EMAIL , EMAIL2 , PHONENUMBER , PHONENUMBER2 ) " +
                                              " VALUES(STRUTS.CUSTOMER_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

                          String select = "SELECT STRUTS.CUSTOMER_SEQ.CURRVAL FROM DUAL";

      insertCustomer = con.prepareStatement(insertString);           

      //insertCustomer.setInt(1,custNextVal);
                          insertCustomer.setString(1,(String) session.get("firstName"));
                          insertCustomer.setString(2,(String) session.get("middleName"));
                          insertCustomer.setString(3,(String) session.get("lastName"));
                          insertCustomer.setString(4,(String) session.get("address1"));
                          insertCustomer.setString(5,(String) session.get("address2"));
                          insertCustomer.setString(6,(String) session.get("city"));
                          insertCustomer.setString(7,(String) session.get("state"));
                          insertCustomer.setString(8,(String) session.get("zipCode"));
                          insertCustomer.setString(9,(String) session.get("country"));                     
                          insertCustomer.setString(10,(String) session.get("email"));
                          insertCustomer.setString(11,(String) session.get("email2"));
                          insertCustomer.setString(12,(String) session.get("phone"));
                          insertCustomer.setString(13,(String) session.get("cell"));

                          insertCustomer.executeUpdate();
                          
                          

      insertCustomer.close();      

      rs.close();
                               
                          //transacton object
                               con.setAutoCommit(true);
                     
                               
                          } catch (SQLException e ) {
                               e.printStackTrace();
                               log.info(String.valueOf(e.getErrorCode() ));
                               
                          log.info(e.getMessage());
                          if (con != null) {
                          try {
                          System.err.print("Transaction is " +
                          "being rolled back");
                          con.rollback();
                          con.close();     
                                    rs.close();
                          } catch(SQLException excep) {
                               log.info(excep.getMessage());
                          }
                          }
                          }