8 Replies Latest reply: Apr 1, 2009 8:07 PM by 807588 RSS

    unable to insert date of Date type into Oracle database(10g)

    807588
      Hi,

      I am unable to insert date of Date type from a java piece of code into Oracle database(10g) which has a column of type date. The exception thrown is

      java.sql.SQLException: [Oracle][ODBC][Ora]ORA-01858: a non-numeric charact
      found where a numeric was expected
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
      at ext.customApp.util.MainInsertData.main(MainInsertData.java:65)

      Will be thankful for your valuable responses.
        • 2. Re: unable to insert date of Date type into Oracle database(10g)
          807588
          package ext.customApp.util;

          import java.io.*;
          import java.sql.*;
          import java.util.Date;
          import java.util.Vector;
          import java.text.*;

          public class MainInsertData {

               public static void main(String args[]) {

                    String str[] = new String[5];

                    try {

                         //Get the name of the file to be read
                         String getTheFileName = "";
                         getTheFileName = ext.customApp.util.GetTheFileNameUtil.get_FileName();

                         //Returns the Tokens from the file in a vector
                         Vector tokenVec = new Vector();
                         tokenVec = ext.customApp.util.GetTokenfromFileUtil.getToken(getTheFileName);
                         System.out.println("@@@" + tokenVec.size());
                         for(int i=0 ;i<tokenVec.size(); i=i+5)
                         {
                              //System.out.println("-----"+tokenVec.elementAt(i+2));
                              //Emp no
                              str[0] = (String)tokenVec.elementAt(i);
                              int eno = Integer.parseInt(str[0].trim());
                              System.out.println("-----"+eno);
                              //Emp name
                              str[1] = (String)tokenVec.elementAt(i+1);
                              String ename = str[1].trim();
                              System.out.println("-----"+ename);
                              //Address
                              str[2] = (String)tokenVec.elementAt(i+2);
                              String addr = str[2].trim();
                              System.out.println("-----"+addr);
                              //DOB
                              String strOutDt = "";
                              Date dtDOB = new Date();
                              str[3] = (String)tokenVec.elementAt(i+3);
                              String strdob = str[3].trim();
                              try {
                                   dtDOB = new SimpleDateFormat("yyyy-mm-dd").parse(strdob);
                                   //strOutDt = new SimpleDateFormat("dd-mm-yy").format(dtDOB);
                                   System.out.println("-----"+strOutDt);
                              } catch(Exception exp) {
                                   exp.printStackTrace();
                              }
                              //Phone no
                              str[4] = (String)tokenVec.elementAt(i+4);
                              String s = str[4].trim();
                              String phone = modifyPhone(s);
                              System.out.println("-----"+phone);

                              //Get Database connection
                              Connection connect = ext.customApp.util.GetDatabaseConnUtil.get_dbcon();

                              //Create statement and insert the data
                              try
                              {
                                   Statement stmt = connect.createStatement();
                                   int i1 = stmt.executeUpdate("INSERT INTO test2 VALUES('"+eno+"','"+ename+"','"+addr+"','"+dtDOB+"','"+phone+"')");
                              }
                              catch(Exception e)
                              {
                                   e.printStackTrace();
                              }

                         }
                    } catch (Exception e) {
                         e.printStackTrace();
                    }
               }

          }

          The date is read from a file !
          • 3. Re: unable to insert date of Date type into Oracle database(10g)
            807588
            Use a PreparedStatement instead.
            • 4. Re: unable to insert date of Date type into Oracle database(10g)
              807588
              Thanks flounder
              I'll get back with the result
              • 5. Re: unable to insert date of Date type into Oracle database(10g)
                807588
                There is an exception thrown when trying to insert date into dB.

                se ext.customApp.util.MainInsertData
                @@@10
                -----12
                -----Richard
                -----1700 Halford Ave Santa Clara
                java.lang.IllegalArgumentException
                at java.sql.Date.valueOf(Unknown Source)
                at ext.customApp.util.MainInsertData.main(MainInsertData.java:46)
                -----120-345-6789
                -----13
                -----George
                -----1700 Fair Oaks Santa Clara
                java.lang.IllegalArgumentException
                at java.sql.Date.valueOf(Unknown Source)
                at ext.customApp.util.MainInsertData.main(MainInsertData.java:46)
                -----123-216-7890

                The code is :
                java.sql.Date dtDOB = new java.sql.Date(0);
                try {
                                         dtDOB = new java.sql.Date(0).valueOf(str[3]);
                                    } catch(Exception exp) {
                                         exp.printStackTrace();
                                    }
                                    
                                    //Phone no
                                    str[4] = (String)tokenVec.elementAt(i+4);
                                    String s = str[4].trim();
                                    String phone = modifyPhone(s);
                                    System.out.println("-----"+phone);

                                    //Get Database connection
                                    Connection connect = ext.customApp.util.GetDatabaseConnUtil.get_dbcon();

                                    //Create statement and insert the data
                                    String command = "";
                                    try
                                    {
                                         command = "INSERT INTO test2 VALUES(?,?,?,?,?)";
                                         PreparedStatement ps = connect.prepareStatement(command);
                                         ps.setInt(1,eno);
                                         ps.setString(2,ename);
                                         ps.setString(3,addr);
                                         ps.setDate(4,dtDOB);
                                         ps.setString(5,phone);
                                         ps.executeUpdate();
                .......

                Will be thankful for your responses ..
                • 6. Re: unable to insert date of Date type into Oracle database(10g)
                  807588
                  Check your database schema. I suspect the values you are trying to insert do not match.
                  • 7. Re: unable to insert date of Date type into Oracle database(10g)
                    807588
                    USed java.sql.Date to insert date .... and code is working fine.Thanks flounder
                    • 8. Re: unable to insert date of Date type into Oracle database(10g)
                      807588
                      I had missed a line of code to select the date from the string.Now things are fine !