Question: Uploading a String Array of dates into oracle

Question

Uploading a String Array of dates into oracle

Answers 1
Added at 2016-12-31 05:12
Tags
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();
            }

        }  
    }
Answers
nr: #1 dodano: 2016-12-31 05:12

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 :)

Source Show
◀ Wstecz