Results 1 to 5 of 5
  1. #1
    Floetic's Avatar
    Floetic is offline Member
    Join Date
    Feb 2008
    Location
    Belfast, Northern Ireland, UK
    Posts
    8
    Rep Power
    0

    Question JDBC Prepared Statement

    Java Code:
    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?

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  3. #3
    yemmynik1 is offline Member
    Join Date
    Jan 2009
    Posts
    2
    Rep Power
    0

    Default

    Moderator: hijack post deleted. Please start you own post.
    Last edited by CJSLMAN; 05-21-2009 at 01:38 AM. Reason: Hijacked posted deleted

  4. #4
    CJSLMAN's Avatar
    CJSLMAN is offline Moderator
    Join Date
    Oct 2008
    Location
    Mexico
    Posts
    1,159
    Rep Power
    8

    Default

    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.
    Last edited by CJSLMAN; 05-21-2009 at 01:40 AM. Reason: Answering Fubarable's note below
    Chris S.
    Difficult? This is Mission Impossible, not Mission Difficult. Difficult should be easy.

  5. #5
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,315
    Blog Entries
    1
    Rep Power
    26

    Default

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

Similar Threads

  1. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 06:29 PM
  2. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-12-2009, 12:21 AM
  3. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 10:49 AM
  4. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 10:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •