java.sql.SQLException: No value specified for parameter 5, but the string length is 4, not 5

Question

i plan to use yahoo finance csv api to generate url to get stock realtime price and time. The url is like this http://download.finance.yahoo.com/d/quotes.csvs=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv, and it returns a table with 4 columns.

I use jdbc to store the data to Mysql, but there is always an error:"No value specified for parameter 5". The code is as below: public class Quote_Saver { private void connection(){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } }

private String retrieveQuote() {
    StringBuffer buf = new StringBuffer();
    try {
        URL page = new URL("http://download.finance.yahoo.com/d/quotes.csv?s=%40%5YHOO,GOOG,MSFT,BAC&f=nsl1o&e=.csv");
        String line;
        URLConnection conn = page.openConnection();
        conn.connect();
        InputStreamReader in = new InputStreamReader(conn.getInputStream());
        BufferedReader data = new BufferedReader(in);
        while ((line = data.readLine()) != null) {
            buf.append(line + "\n");
        }
    } catch (MalformedURLException mue) {
        System.out.println("Bad URL: " + mue.getMessage());
    } catch (IOException ioe) {
        System.out.println("IO Error:" + ioe.getMessage());
    }
    return buf.toString();
}

//use the retrieveQuote class , pass it to data in ConnectionToMysql
private void ConnectionToMysql(String data){
    StringTokenizer tokens = new StringTokenizer(data, ",");
    String[] fields = new String[4];
    for (int i = 0; i < fields.length; i++) {
        fields[i] = stripQuotes(tokens.nextToken());
    }

    connection();
    String host ="jdbc:mysql://localhost/yahoo";
    String username = "root";
    String password = ".....";
    try {
        Connection connection = DriverManager.getConnection(host, username, password);
        String query = "insert into `stocks`(?,?,?,?) values (?,?,?,?);";
        PreparedStatement statement = (PreparedStatement) connection.prepareStatement(query);
        statement.setString(1, fields[0]);
        statement.setString(2, fields[1]);
        statement.setString(3, fields[2]);
        statement.setString(4, fields[3]);
        statement.executeUpdate();
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private String stripQuotes(String input) {
    StringBuffer output = new StringBuffer();
    for (int i = 0; i < input.length(); i++) {
        if (input.charAt(i) != '\"') {
            output.append(input.charAt(i));
        }
    }
    return output.toString();
}

public static void main (String args[])
{
    Quote_Saver qd= new Quote_Saver();
    String data = qd.retrieveQuote();
    qd.ConnectionToMysql(data);
}

}

The error is

java.sql.SQLException: No value specified for parameter 5
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2594)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2569)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
at Quote_Saver.ConnectionToMysql(Quote_Saver.java:68)
at Quote_Saver.main(Quote_Saver.java:89)

But clearly, the string fields only has size of 4,so does my table columns, so my question is where is the parameter 5? and how to solve this? I'm new to java,plz help me. Thanks a lot!

John Humphreys · Accepted Answer · 2014-03-09 20:03:12Z

12

Actually, you just made a minor mistake - probably a copy paste error.

The following line requires 8 parameters instead of 4 because you put question marks where you should have put column names.

insert into `stocks`(?,?,?,?) values (?,?,?,?);";

If you modify it as follows (replacing the column names with your real names from the stocks table) then it should function as you were expecting.

insert into stocks(ColumnNameOne, ColumnNameTwo, ColumnNameThree, ColumnNameFour)
values (?, ?, ?, ?);

edited Mar 9, 2014 at 20:03

answered Mar 9, 2014 at 19:42

John Humphreys

39.8k44 gold badges167 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salah · Accepted Answer · 2014-03-09 19:45:05Z

4

Your sending 8 parameters by using, because each ? represent a parameter that required a value to be set at Run time.

String query = "insert into `stocks`(?,?,?,?) values (?,?,?,?);";

Your query should look like this:

String query = "insert into `stocks`(clmName1, clmName2, clmName3, clmName4) values (?,?,?,?);";

answered Mar 9, 2014 at 19:45

Salah

8,7173 gold badges29 silver badges45 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLException: No value specified for parameter 5, but the string length is 4, not 5

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related