Results 1 to 12 of 12
  1. #1
    eriktonev is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Red face Adding user input to a database

    Hello guys I'm quite a rookie when it comes to Java and programming as a whole but I managed to combine some stuff and got almost what I need.
    Java Code:
    import java.sql.*;
    import java.util.Scanner;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class AddSession {
        public static void main (String[] args) throws SQLException {
            Scanner scanner=new Scanner(System.in);
                    
                    Connection conn = null; 
            try {
                conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Project1","toneve","12345");
            } catch (SQLException ex) {
                Logger.getLogger(AddSession.class.getName()).log(Level.SEVERE, null, ex);
            }
                    System.out.println("Connection to database was successfully established !");
                    Statement st = conn.createStatement();
    
                    System.out.println("Enter session id: ");
                    int idid = scanner.nextInt();
                    System.out.println("Enter name: ");
                    String name=scanner.next();
                    System.out.println("Enter day: ");
                    String day=scanner.next();
                    System.out.println("Enter hour: ");
                    int hour=scanner.nextInt();
                    System.out.println("Instructor: ?");
                    String instructor=scanner.next();
                    
                    st.executeUpdate("INSERT INTO AddSession VALUES('"+idid+"', '"+name+"', '"+day+"', '"+hour+"',  '"+instructor+"')");
                    System.out.println("All values were successfully added to the database !"); 
        }    
    }
    It is just supposed to make connection to the database, get the input from the user and put it inside the database but I get strange errors everytime I try something.

  2. #2
    jim829 is offline Senior Member
    Join Date
    Jan 2013
    Location
    Northern Virginia, United States
    Posts
    6,226
    Rep Power
    13

    Default Re: Adding user input to a database

    What do you mean by "strange" errors and "try" something?

    Regards,
    Jim
    The JavaTM Tutorials | SSCCE | Java Naming Conventions
    Poor planning on your part does not constitute an emergency on my part

  3. #3
    eriktonev is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Default Re: Adding user input to a database

    Well, it manages to connect to the database and I enter the data but after the last input i get the following:
    Java Code:
    Exception in thread "main" java.sql.SQLSyntaxErrorException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'. 
    	at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    	at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    	at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)
    	at AddSession.main(AddSession.java:30)
    Caused by: org.apache.derby.client.am.SqlException: Columns of type 'INTEGER' cannot hold values of type 'CHAR'. 
    	at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    	at org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source)
    	at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
    	at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
    	at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
    	at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
    	at org.apache.derby.client.am.Statement.readExecuteImmediate(Unknown Source)
    	at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
    	at org.apache.derby.client.am.Statement.executeUpdateX(Unknown Source)
    	... 2 more
    Java Result: 1
    BUILD SUCCESSFUL (total time: 20 seconds)
    I'm also pretty sure this part is not working:
    Java Code:
    st.executeUpdate("INSERT INTO AddSession VALUES('"+idid+"', '"+name+"', '"+day+"', '"+hour+"',  '"+instructor+"')");
    Last edited by eriktonev; 03-27-2017 at 11:48 PM.

  4. #4
    jim829 is offline Senior Member
    Join Date
    Jan 2013
    Location
    Northern Virginia, United States
    Posts
    6,226
    Rep Power
    13

    Default Re: Adding user input to a database

    Well, it looks like you are trying to store an integer value where the db column only holds values of type char. I am not that knowledgeable in DB syntax. Is there a way you can verify the column types of your db and see how they match up with the types you are trying to insert?

    Regards,
    Jim
    The JavaTM Tutorials | SSCCE | Java Naming Conventions
    Poor planning on your part does not constitute an emergency on my part

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: Adding user input to a database

    You're sticking quotes around everything, so telling the database "this is a CHAR datatype".
    I would suggest using a PreparedStatement, which solves all these issues (and more).
    And the tutorial for them.
    They're not complicated and they mean you can concentrate on using proper datatypes, and forget about quotes, escaping, and all that other nonsense.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,989
    Rep Power
    8

    Default Re: Adding user input to a database

    Read up on PreparedStatement. No, seriously, stop what you're doing, and do it. Now.

    Here's why: all the quote hell is gone as soon as you start using prepared statements. On top of that you get SQL injection prevention for free!

    Let me give you a head start here:
    Java Code:
    PreparedStatement ps = connection.prepareStatement("INSERT INTO AddSession VALUES(?,?,?,?,?)");
    ps.setInt(1, idid);
    ps.setString(2, name);
    ps.setInt(3, day);
    ps.setInt(4, hour);
    ps.setString(5, instructor);
    
    ps.executeUpdate();
    See? No more quotes, no more SQL injection!
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  7. #7
    eriktonev is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Default Re: Adding user input to a database

    Thanks, how far off am I this time around
    Java Code:
    import java.sql.*;
    import java.util.Scanner;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class AddSession {
        private static Object connection;
        public static void main (String[] args) throws SQLException {
            Scanner scanner=new Scanner(System.in);
                    
                    Connection conn = null; 
            try {
                conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Project1","toneve","12345");
            } catch (SQLException ex) {
                Logger.getLogger(AddSession.class.getName()).log(Level.SEVERE, null, ex);
            }
                    System.out.println("Connection to database was successfully established !");
                    Statement st = conn.createStatement();
    
                    System.out.println("Enter session id: ");
                    int idid = scanner.nextInt();
                    System.out.println("Enter name: ");
                    String name=scanner.next();
                    System.out.println("Enter day: ");
                    String day=scanner.next();
                    System.out.println("Enter hour: ");
                    int hour=scanner.nextInt();
                    System.out.println("Instructor: ?");
                    String instructor=scanner.next();
                    
                    PreparedStatement ps = connection.prepareStatement("INSERT INTO AddSession VALUES(?,?,?,?,?)");
                    ps.setInt(1, idid);
                    ps.setString(2, name);
                    ps.setInt(3, day);
                    ps.setInt(4, hour);
                    ps.setString(5, instructor);
     
                    ps.executeUpdate();
                    System.out.println("All values were successfully added to the database !"); 
        }    
    }
    Much less errors this time, only this:
    Java Code:
    Exception in thread "main" java.lang.RuntimeException: Uncompilable source code - Erroneous sym type: java.lang.Object.prepareStatement
    	at AddSession.main(AddSession.java:31)
    Java Result: 1

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: Adding user input to a database

    You're trying to run something that doesn't compile.
    There is no point doing that.

    Your IDE will have a little red 'x' on the line (or lines) causing the issue, so those need fixing.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,989
    Rep Power
    8

    Default Re: Adding user input to a database

    Apparently you copied and pasted my example code without understanding what it does. I used "connection" as variable and your code has "conn". It even says what line it is: 31.

    Next time try to study the code you're given before blindly pasting and compiling that.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  10. #10
    eriktonev is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Default Re: Adding user input to a database

    Quote Originally Posted by SurfMan View Post
    Apparently you copied and pasted my example code without understanding what it does. I used "connection" as variable and your code has "conn". It even says what line it is: 31.

    Next time try to study the code you're given before blindly pasting and compiling that.
    Yes, you caught me. Problem is I was involved in a group project and my group just decided they want to work with Java which is a bit of a dark area for me. But yeah, I actually managed to get it working. Only problem is when I input first and second names for example John Smith, it puts John in the name column and Smith in the day column. Any idea how to fix this ?

  11. #11
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,989
    Rep Power
    8

    Default Re: Adding user input to a database

    Try scanner.readLine();
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: Adding user input to a database

    It's nextLine(), and you'll hit issues with the left over newline character from the previous call to nextInt().

    You'd do better simply using a BufferedReader and reading each line and then parsing it, IMO.
    I really find Scanner unintuitive in these situations.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Retrieving data from database based on user input
    By Madhatter40 in forum New To Java
    Replies: 5
    Last Post: 01-19-2015, 01:08 AM
  2. Replies: 1
    Last Post: 07-11-2014, 09:18 AM
  3. Replies: 7
    Last Post: 05-19-2014, 03:57 PM
  4. Asking user for input twice
    By csanch11 in forum New To Java
    Replies: 4
    Last Post: 04-19-2014, 08:54 AM
  5. Replies: 0
    Last Post: 11-03-2011, 07:33 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
  •