This discussion is archived
8 Replies Latest reply: Apr 1, 2009 6:07 PM by 807588 RSS

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

807588 Newbie
Currently Being Moderated
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.
  • 1. Re: unable to insert date of Date type into Oracle database(10g)
    807588 Newbie
    Currently Being Moderated
    and your code is?
  • 2. Re: unable to insert date of Date type into Oracle database(10g)
    807588 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Use a PreparedStatement instead.
  • 4. Re: unable to insert date of Date type into Oracle database(10g)
    807588 Newbie
    Currently Being Moderated
    Thanks flounder
    I'll get back with the result
  • 5. Re: unable to insert date of Date type into Oracle database(10g)
    807588 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I had missed a line of code to select the date from the string.Now things are fine !