Uploading a String Array of dates into oracle

Question

I keep getting the following error when I try to upload my books table through eclipse:

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

I have been able to upload other tables in my database using this basic code, just not this one. I think it might be something to do with how I am uploading my dates, but I am not sure.

package uploadDatabase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import oracle.jdbc.OracleDriver;
import java.sql.DatabaseMetaData;
import java.sql.Date;


public class StatesTable {

    public static void main(String[] args) {


        //private static Connection connection;


        try{

            DriverManager.registerDriver(new OracleDriver());

            //make strings for database connections
            String url = "jdbc:oracle:thin:@localhost:1521:xe";
            String userName = "BOOKSTORE";
            String password = "***********";

            //make database connection
            Connection conn =               DriverManager.getConnection(url,userName,password); 

            DatabaseMetaData meta = conn.getMetaData();

            System.out.println(meta.getDatabaseProductVersion());

            String isbn[] =    {"00000000110","00000000111","00000000112","00000000113","00000000114"};

            String title[] = {"THE SHINING", "THE GIRL WITH THE DRAGON TATTO", "PRIDE AND PREJUDICE", "BOSSYPANTS", "THE HUNGER GAMES"};

            String author[] = {"STEPHEN KING", "STIEG LARSSON", "JANE AUSTEN", "TINA FEY", "SUZANNE COLLINS"};

            String publishDate[] ={"19750115","19990805","18731015","20160105","19821115"};

            String edition[] = {"6TH","3RD","26TH","1ST","7TH"};

            double cost[] = {15.75,17.95,8.95,9.95,12.95};

            String genre[] = {"HORROR", "MYSTERY", "ROMANCE","COMEDY", "ACTION"};



            //Database statement for inserting values into BOOKS table
            String sqlStatement = "INSERT INTO BOOKS VALUES(?,?,?,?,?,?,?)";

            //Prepared statement for database connection
            PreparedStatement pstmt = conn.prepareStatement(sqlStatement);

            //loop uploads data into database;
            for(int i = 0; i < 5; i++){
                //insert values into dbms statement
                  String isb = isbn[i];
                  String tit = title[i];
                  String auth = author[i];
                  SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
                  java.util.Date parsed = df.parse(publishDate[i]);
                  Date publishDat = new Date(parsed.getTime());
                  System.out.println(publishDat);
                  String editio = edition[i];
                  double cos = cost[i];
                  String gen = genre[i];

                  pstmt.setString(1,isb);
                  pstmt.setString(2,tit);
                  pstmt.setString(3,auth);
                  pstmt.setDate(4,publishDat);
                  pstmt.setString(5,editio);
                  pstmt.setDouble(6, cos);
                  pstmt.setString(7, gen);


                //Execute update
                pstmt.executeUpdate();

            }

             //close pstmt statement
            pstmt.close();
            //close database connection
            conn.close();



        }
            catch(SQLException e){
                e.printStackTrace();
            } catch (ParseException e) {
             e.printStackTrace();
            }

        }  
    }

Can you post the table description of books table in the question? — Gurwinder Singh
– Gurwinder Singh, Commented Dec 31, 2016 at 4:17
I suspect the table has an identifier primary key column as the first column. — Andreas
– Andreas, Commented Dec 31, 2016 at 4:23

beastlyCoder · Accepted Answer · 2016-12-31 04:28:15Z

2

The reason you are getting this error is because one of the column data types you are passing into the insert command is not the same as what is actually defined. Make sure to check your table and it's column values and data types and make sure those data types align with which you are trying to insert into that database. As what Andreas said in the comment below you should ALWAYS name your columns in INSERT statements, without them you will not know what is going into your database. Hope this helps :)

edited Dec 31, 2016 at 4:28

answered Dec 31, 2016 at 4:15

beastlyCoder

2,4215 gold badges27 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andreas Over a year ago

A highly likely candidate would be for the first column of the table to be a integer identifier/primary-key column. That is why you should always name your columns in INSERT statements. Programs should never do INSERT without naming columns, and should never do SELECT *, since column order is ... subject to change.

beastlyCoder Over a year ago

@Andreas this should be the answer instead of mine :D

Andreas Over a year ago

Your answer is good. My comment just expands on it with an educated guess, and you can update your answer to include my observations, in your own words if you want.

Bob Over a year ago

Thanks everyone. It was the edition column. I set it up as a number. Of course the above code has it as a string. I believe that the database needs to be changed to a varchar2.

Collectives™ on Stack Overflow

Uploading a String Array of dates into oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related