Too few parameters. Expected 3

[Kumanan Guna]

Hi,

I m trying to run select query with "group by" to get the customer information (using MS Access DB), but getting "Too few parameters. Expected 3" exception.

Noticed when I run select query along with subquery to pull last 24 hrs of sum of records I m getting this exception, without subquery getting expected output.
Joining 3 tables to get list of all records. Please let me know, is there a way I can run below mentioned query along with subquery or do I need to split into 2 parts.
One without subquery and otherone running subquery as seprate.

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import com.test.LocalUtil;

public class CustomerConnectionTest {
	public static void main(String[] args) {
		String dataSourceName = "CustomerDashboardDB";
		String dbURL = "jdbc:odbc:" + dataSourceName;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultset = null;
		PreparedStatement preparedstatment;
		LocalUtil cuc = new LocalUtil();
		try {
connection = DriverManager.getConnection(dbURL ,"","");
statement = con.createStatement();
//with subquery not working..
//sub query is (select SUM(GT5SEC) from CUSTOMER_SUMMARY WHERE OUT_ITEM >= Now()-1 )

String query = "select SM.SRVC_NAME, SM.SRVC_TYPE, SUM(CS.TOTAL_COUNT) AS TOTAL_TCOUNT, SUM(CS.LT1SEC) AS LT1SEC_COUNT, (SUM(CS.LT1SEC)*100.0/SUM(CS.TOTAL_COUNT)) AS LT1SEC_PER, SUM(CS.GT1SEC) AS GT1SEC_COUNT, (SUM(CS.GT1SEC)*100.0/SUM(CS.TOTAL_COUNT)) AS GT1SEC_PER, SUM(CS.GT5SEC) AS GT5SEC_COUNT, (select SUM(GT5SEC) from CUSTOMER_SUMMARY WHERE OUT_ITEM >= Now()-1 ) as DAYCOUNT from CUSTOMER_SERVICE_MAPPING SM, CUSTOMER_SUMMARY CS, CUSTOMER_DEPT_MAPPING LM where CS.OUT_TIME between ? and ? and CS.SRVC_TYPE=SM.SRVC_TYPE_CD and CS.LOB_ID=LM.LOB_ID and LM.LOB_TYPE='SALES' group by SM.SRVC_NAME, SM.SRVC_TYPE";

//Without subquery working fine..
//  String query = "select SM.SRVC_NAME, SM.SRVC_TYPE, SUM(CS.TOTAL_COUNT) AS TOTAL_TCOUNT, SUM(CS.LT1SEC) AS LT1SEC_COUNT, (SUM(CS.LT1SEC)*100.0/SUM(CS.TOTAL_COUNT)) AS LT1SEC_PER, SUM(CS.GT1SEC) AS GT1SEC_COUNT, (SUM(CS.GT1SEC)*100.0/SUM(CS.TOTAL_COUNT)) AS GT1SEC_PER, SUM(CS.GT5SEC) AS GT5SEC_COUNT from CUSTOMER_SERVICE_MAPPING SM, CUSTOMER_SUMMARY CS, CUSTOMER_DEPT_MAPPING LM where CS.OUT_TIME between ? and ? and CS.SRVC_TYPE=SM.SRVC_TYPE_CD and CS.LOB_ID=LM.LOB_ID and LM.LOB_TYPE='SALES' group by SM.SRVC_NAME, SM.SRVC_TYPE";

Timestamp starttime = cuc.getStartTimestamp();
Timestamp endtime = cuc.getEndTimestamp();

preparedstatment.setTimestamp(1, starttime);
preparedstatment.setTimestamp(2, endtime);
//  System.out.println("query:"+query);
resultset = preparedstatment.executeQuery();

if (resultset != null){
while ( resultset.next() )
{
 System.out.println("Just getting first column to check:"+resultset.getString("SRVC_NAME"));
   }
}



}
catch(Exception e){
System.out.println("Error: " + e);
e.printStackTrace();
}
}
}
			

Exception:

Error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
closing statement
closing connection
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(Unknown Source)

Please advice.

[Jeanne Boyarsky]

Are you sure that is the code? As written, it looks like it shouldn't work at all.

Here's what I see happening:

create a Statement object without SQL (which implies the query would be passed later - except that this object is never used)

set parameters on the PreparedStatement - which is null - this would throw a NullPointer

[Wei Dai]

>CS.OUT_TIME between ? and ? and ...
Try (CS.OUT_TIME between ? and ?) and ... May your sql syntax is too complicated to be understood by MS Access.