java.sql.SQLException: Parameter index out of range (2 > number of parameters, which

pontovic

Hi

Im having a problem with my web database driven website. I am using JSP and the connector/J library to connect to a mysql database online, and I am using a PreparedStatement to execute an update on the database.

The table name in the database is called 'news_records' and it has the following makeup.

post_id mediumint
subject varchar
content blob
date date

I used the java.sql.PreparedStatement to insert data to the database.

java.sql.PreparedStatement pst = db.initPreparedStatement("insert into news_records values(?)");
		String s = "large string variable";
		byte[] string_bytes = s.getBytes();
		InputStream is = new ByteArrayInputStream(string_bytes);

		pst.setString(2, news.getSubject());
		pst.setAsciiStream(3, is, bytes.length);
		pst.setDate(4, news.getDate());	
		pst.executeUpdate();
		out.println("supposed to be adding to databse");

but i get the following error.

Could not update database: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

Any ideas, details on the net are sketchy and Im stumped. Any help would be well appreciated. Thanks

pontovic

Ok i got it fixed, the problem was that I followed a bad example on the web. Strange that, although there was little to explain to me what was going wrong.

Here is the correct code to use

//See below I have three question marks instead of one like last time
java.sql.PreparedStatement pst = db.initPreparedStatement("insert into news_records (subject, content, date) values(?, ?, ?)");
		String s = news.getContent();
		byte[] string_bytes = s.getBytes();
		InputStream is = new ByteArrayInputStream(string_bytes);
// and i set back each parameter index by one     

		pst.setString(1, news.getSubject());
		pst.setAsciiStream(2, is, string_bytes.length);
		pst.setDate(3, news.getDate());	
		pst.executeUpdate();
		out.println("supposed to be adding to databse");

amen

I'm not a Java person but I think your problem is

[code]insert into news_records values(?)

basically news_records needs 4 values but you are telling it you are only passing 1. I think you need to do

insert into news_records values(?,?,?,?)

OR

insert into news_records(post_id) values(?)

assuming the other columns allow nulls