JDBC Prepared Statement

[Floetic]

import java.sql.*; 
import java.io.*; 
import java.sql.PreparedStatement;
import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;


class shipment_prac3_readtxtfile { 
    
    public static void main (String args []) throws SQLException, IOException { 
        
        
         // the following statement loads the Oracle jdbc driver

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        System.out.println ("Could not load the driver"); 
      }

    String user, pass, host,servicename;
    user = readEntry("userid  : ");
    pass = readEntry("password: ");
    host = readEntry("hostname or ip address: ");
    /*  userid, password and hostname are obtained from the console
     *  It is assumed that the service name is XE and the databse server
     *  listens on the default port 1521.
     */ 

    servicename="orcl.ijgj9.infj.ulst.ac.uk";

    Connection conn = DriverManager.getConnection
                      ("jdbc:oracle:thin:"+user+"/"+pass+"@"+host+":1521/"+servicename);

    /* JDBC default is to commit each SQL statement as it is sent to the database.  Setting autocommmit=false changes the default
       behaviour so that transactions have to be committed explicity.
     */
    conn.setAutoCommit(false);
			
    // Creating a statement lets us issue commands against the connection.

    Statement s = conn.createStatement();
			
     // Creating and populating Shipment table

    s.executeUpdate("drop table shipment");
     s.executeUpdate("create table Shipment(CoffeeName VARCHAR(20), SuppID VARCHAR(7), DateRecd Date, UnitPrice INT, Quantity INT, TotalPrice INT, PRIMARY KEY(CoffeeName, SuppID, DateRecd))");
     System.out.println("Created table Shipment");
            
     s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-24', 20, 130, 2600)");
     s.executeUpdate("insert into Shipment values('Kenco', 'S106', Date '2009-02-23', 40, 100, 4000)");
     s.executeUpdate("insert into Shipment values('Vella', 'S103', Date '2009-02-23', 10, 190, 1900)");
     s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-21', 70, 90, 6300)");
     s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-20', 100, 66, 6600)");
     s.executeUpdate("insert into Shipment values('Denvenna', 'S101', Date '2009-03-01', 28, 200, 5600)");
     s.executeUpdate("insert into Shipment values('Eden', 'S102', Date '2009-02-13', 89, 300, 26700)");
/*   s.executeUpdate("insert into Shipment values('Fenti' 'S104', Date '2009-01-19', 15, 45, 675)");
     s.executeUpdate("insert into Shipment values('Otus', 'S105', Date '2009-02-28', 33, 123, 4059)");
     s.executeUpdate("insert into Shipment values('Lenvu', 'S101', Date '2009-02-05', 34, 220, 7480)");
*/
      
        
        //Essential Java code for loading data from a text file
        
        String psq = "insert into Shipment values(?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(psq);
        
        String line; //Data from text file to be read one line at a time
        
        String[] tokens; //Line will be parsed into an array of Strings
        
        System.out.println("Inserting data from text file");
        
        String data_file = "/home/pinate/shipments";
        
        
        File inputFile = new File(data_file);
        FileReader inf = new FileReader(inputFile);
        BufferedReader inb = new BufferedReader(inf);
        
        
        //Next line emphasises that it is java.sql.Date and NOT java.util.Date
        
        java.sql.Date when = new java.sql.Date(0); //No default constructor without an argument
        
          
        System.out.println("Ready to read line");
        
        line = inb.readLine(); //read a line
        //System.out.println(line);
        
        
        while((line != null)) {
            
            tokens = line.split(","); //split line into tokens separated by a comma
            
            System.out.println(tokens[0]+" "+tokens[1]+" "+tokens[2]+" "+tokens[3]+" "+tokens[4]+" "+tokens[5]+" ");
            
            
            ps.setString(1, tokens[0]); //first ? in ps is a String
            ps.setString(2, tokens[1]); //second ? in ps is a String
            when = new java.sql.Date(0).valueOf(tokens[2]); //convert string yyyy-mm-dd format to date
            ps.setDate(3, when); //third ? in ps is a Date
            ps.setInt(4, tokens[3]); //fourth ? in ps is an Integer
            ps.setInt(5, tokens[4]); //fifth ? in ps is an Integer           
            ps.setInt(6, tokens[5]); //sixth ? in ps is an Integer
            
            ps.execute(); //execute the prepared statement
            ps.clearParameters();
            
            
            line = inb.readLine(); //read next line
            
        }
        
        
     ResultSet result1a = s.executeQuery("Select * From Shipment");
      System.out.println("Shipment Results: ");
       while(result1a.next()) {
	 System.out.println(result1a.getString(1) +"  "+ result1a.getString(2) + "  "+result1a.getString(3) +"  "+  result1a.getString(4)+"  "+ result1a.getString(5)+" "+ result1a.getString(6) );
     }
     
        
        conn.commit(); //commit after all data has been inserted
        inb.close(); //close the buffered reader
        inf.close(); //close the file
      
  }
    
    
    //readEntry function -- to read input string
  static String readEntry(String prompt) {
     try {
       StringBuffer buffer = new StringBuffer();
       System.out.print(prompt);
       System.out.flush();
       int c = System.in.read();
       while(c != '\n' && c != -1) {
         buffer.append((char)c);
         c = System.in.read();
       }
       return buffer.toString().trim();
     } catch (IOException e) {
       return "";
       }
   }
    
}

And I get this:


[pinate@ijgj9 ~]$ javac shipment_prac3_readtxtfile.java
shipment_prac3_readtxtfile.java:105: setInt(int,int) in java.sql.PreparedStatement cannot be applied to (int,java.lang.String)
ps.setInt(4, tokens[3]); //fourth ? in ps is an Integer
^
shipment_prac3_readtxtfile.java:106: setInt(int,int) in java.sql.PreparedStatement cannot be applied to (int,java.lang.String)
ps.setInt(5, tokens[4]); //fifth ? in ps is an Integer
^
shipment_prac3_readtxtfile.java:107: setInt(int,int) in java.sql.PreparedStatement cannot be applied to (int,java.lang.String)
ps.setInt(6, tokens[5]); //sixth ? in ps is an Integer
^
3 errors
[pinate@ijgj9 ~]$



Any suggestions?

[masijade]

You need to pass an int or Integer to the setInt method and you are passing Strings. Look at the API docs for Integer, and see if there is an easy way to create an int or Integer from that String.

[yemmynik1]

Moderator: hijack post deleted. Please start you own post.

[CJSLMAN]

yemmynik1... please don't post on other people's posts (it's called hijacking). Please open a new post for your question or problem.

Thanks,
CJSL

Edit: OK, got it Fubarable.

[Fubarable]

Chris, yemmynik1 did this in another thread and I deleted it (edited it actually)