Java jdbc records from console to database java.sql.sqlexception:

Question

Code for values that are taken from user and inserted into database but gives error

"java.sql.SQLException: The column position '45' is out of range. The number of columns for this ResultSet is '2'."

when user enters 45 as number, the code takes that as column number which is 2 what must be changed so that number is the literal that goes in the column id the table has two columns named ID and Name;

package Rdbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

class Jdbcfrnd {

    public static void main(String[] args) throws SQLException {

        String drv = "org.apache.derby.jdbc.ClientDriver";
        String url = "jdbc:derby://localhost:1527/Oracle;create=true;";
        String user = "Android";
        String password = "java";

        Connection myConn = null;
        PreparedStatement myStmt = null;
        Connection dbConnection = null;

        Scanner scanner = null;

        try {
            // 0. read user input from command line: last name, first name and email
            scanner = new Scanner(System.in);

                        Scanner sc = new Scanner(System.in);
                        System.out.print("Enter number 1: ");
                        int a;
                        a = sc.nextInt();                                                                                   

            System.out.print("Enter your Name: ");
            String firstName = scanner.nextLine();

                        //System.out.print("Enter your email: ");
                        //String email = scanner.nextLine();

            // 1. Get a connection to database
            myConn = DriverManager.getConnection(url, user, password);

            // 2. Create a statement
            String sql = "insert into GOOD "
                    + " (ID, Name)" + " values (?, ?)";

            myStmt = myConn.prepareStatement(sql);

            // set param values
            myStmt.setString(a , firstName);
            //myStmt.setString(ID, "Android");

            // 3. Execute SQL query
            myStmt.executeUpdate();

            System.out.println("Insert complete.");
            } catch (Exception exc) {
            exc.printStackTrace();
            } finally {
            if (myStmt != null) {
                myStmt.close();
            }

            if (myConn != null) {
                myConn.close();
            }

            if (scanner != null) {
                scanner.close();
            }
        }
    }

 }

score 1 · Answer 1 · answered Jan 31 '18 at 14:55

1

java.sql.SQLException: The column position '45' is out of range. The number of columns for this ResultSet is '2'

As you mentioned, when user enters 45 as number, the code takes that as column number which is 2. Now, same input you have specified as column position

int a;
a = sc.nextInt();
...
...
myStmt.setString(a , firstName);

Please note, first argument of setString is parameterIndex, which your misunderstood with value. Instead, you should have

myStmt.setInt(1, a);
myStmt.setString(2, firstName);

answered Jan 31 '18 at 14:55

Ravi

30,829
42
119
173

what do i have to change so that user could enter number and name that goes in the database – four systems Jan 31 '18 at 15:02
thanks works now, while you are still there is it possible to add records from csv file to database cause there are lot of records, thanks – four systems Jan 31 '18 at 15:07
@foursystems there are many ways you could achieve. I would suggest to research yourself on this topic, which suits your requirement. Write some code and comeback to us with your specific problem (if any). All the best:-) – Ravi Jan 31 '18 at 15:08
@foursystems make sure you read this https://meta.stackexchange.com/a/5235 – Ravi Jan 31 '18 at 15:09
@foursystems did you accepted the answer ? you need to mark accepted as mentioned in that link. Let me know, if you have any confusion/queries on how to accept an answer – Ravi Jan 31 '18 at 15:19
@foursystems https://stackoverflow.com/a/38730595/8686562 simplest approach – Mahendra Kapadne Jan 31 '18 at 15:21
@foursystems https://examples.javacodegeeks.com/core-java/apache/commons/csv-commons/writeread-csv-files-with-apache-commons-csv-example/ – Mahendra Kapadne Jan 31 '18 at 15:26
1

@MahendraKapadne It is not recommended to share 3rd party links, they might get broken tomorrow and your comment/answer would be useless. If you have something different answer. Then, please go ahead and post it as an answer – Ravi Jan 31 '18 at 15:27

score 0 · Accepted Answer · answered Jan 31 '18 at 14:56

0

when user enters 45 as number, the code takes that as column number which is 2

That does not make sense. Your code is making 45 the column number, as the error says

The column position '45' is out of range.

Try making a separate variable for which column you're setting

myStmt = myConn.prepareStatement(sql);
int col = 1;

Scanner sc = new Scanner(System.in);
System.out.print("Enter number 1: ");
int a= sc.nextInt();
myStmt.setInt(col++, a);

System.out.print("Enter your Name: ");
String firstName = sc.nextLine();
myStmt.setString(col++ , firstName);

answered Jan 31 '18 at 14:56

OneCricketeer

179,855
19
132
245

thanks, is it possible to add records from csv file to database cause there are lot of records, thanks – four systems Jan 31 '18 at 15:13
1

@foursystems, yes it's possible. Write some code that would do it. – M. Prokhorov Jan 31 '18 at 15:32
@foursystems I have no experience with loading data to a Derby database. MySQL, Postgres, Mongo, etc all have import tools that are specific to them. – OneCricketeer Jan 31 '18 at 17:46

Java jdbc records from console to database java.sql.sqlexception:

2 Answers2