Strange Exception PreparedStatement in Java : Parameter index out of range

Question

I have a String as

    final static String OPENTICID="SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j "+ 
"                     INNER JOIN "+
"                      ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x "+
"                      ON j.uid=x.u "+
"                     WHERE convert_tz(createdDate,'+05:30','-05:00') > ? "+
"                      AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation) "+
"                      AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev') "+
"                     AND convert_tz(updatedDate,'+05:30','-05:00') < ? ";

The query has 2 ? to be set using PreparedStatement

following is my code snippet

con = DBConnector.getInstance().getConnection("travelclick");
            PreparedStatement pst=con.prepareStatement(new StringBuilder("select `group` , count(*) cnt from (" +
                                                        OPENTICID +
                                                        ") y group by `group`").toString());
            System.out.println("select `group` , count(*) cnt from (" +
                                                        OPENTICID +
                                                        ") y group by `group`");
            System.out.println(pst);
            pst.setTimestamp(1, new java.sql.Timestamp(octDate.getTimeInMillis()));
            System.out.println(pst);
            pst.setTimestamp(2, new java.sql.Timestamp(end.getTimeInMillis()));

            ResultSet rs=pst.executeQuery();

and following is the output

select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > ?                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                       AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
com.mysql.jdbc.PreparedStatement@1484a05: select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > ** NOT SPECIFIED **                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                       AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
com.mysql.jdbc.PreparedStatement@1484a05: select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > '2012-10-01 12:00:00'                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                     AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2569)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2600)
    at com.mysql.jdbc.PreparedStatement.setTimestampInternal(PreparedStatement.java:3552)
    at com.mysql.jdbc.PreparedStatement.setTimestamp(PreparedStatement.java:3507)
    at com.cybage.WeeklyStatusReport.OpenTicketsCountReport(WeeklyStatusReport.java:873)
    at com.cybage.WeeklyStatusReport.generateReport(WeeklyStatusReport.java:319)
    at com.cybage.WeeklyStatusReport.main(WeeklyStatusReport.java:1193)

I don't know why there is a exception I have 2 ?'s and 2 pst.setTimeStamp()

try to run that query at database client console and check whether query is correct or not — Rais Alam, Apr 17 '13 at 11:40

score 2 · Accepted Answer · answered Apr 17 '13 at 11:35

2

You are missing a quotation mark:

'partenered investigation) "+
                        ^

                        |
here --------------------

On a side note, thats one heck of a query - may be easier to maintain as a stored procedure (it's quite hard to read in code).

answered Apr 17 '13 at 11:35

Perception

79,279
19
185
195

@Bhavik: all the string-concatenation doesn't help. i would put queries like this in a properties file. – Nathan Hughes Apr 17 '13 at 12:52
@NathanHughes : That is a very good idea. I know how to use properties file. Thanks I'll implement it. and it is not possible to keep it in stored procedure. I want the result in java so that I can update it in excel using apache poi – Bhavik Shah Apr 17 '13 at 13:06
@Bhavik: with spring 3 storing your sql in xml is an option: see http://stackoverflow.com/a/8500844/217324. with the properties file you have to keep all the sql on 1 line. – Nathan Hughes Apr 17 '13 at 13:54

Strange Exception PreparedStatement in Java : Parameter index out of range

1 Answers1