Help with prepared statment for crosstab

[Carl Shelbourne <Carl(dot)Shelbourne(at)stanleybet(dot)com>]

Hi.

I have been asked to produce a report for a new game. The report needs to show the gamers unique id, gamer_number, his nickname, the average score of the best 5 scores he got playing the game during a specified period and a list of the top five scores that made this average.

e.g.:
gamerNumber;nickname;average;score1;score2;score3;score4;score5
91692656912;"nickname1";"14.33";29.85;21.03;7.38;6.73;6.66

The SQL shown below works if I do not have any parameters, but when I add the parameters, I get:
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2155)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:3114)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:1358)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:420)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:255)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:230)


I am not too sure if this is possible, but I am trying to prepare a statement containing a crosstab:
SELECT
    gamehighscore.row_name[1] as gamer_number,
    gamehighscore.row_name[2] as nick_name,
    gamehighscore.row_name[3] as average_score, score1, score2, score3, score4, score5 from
    crosstab('SELECT ARRAY[g.gamer_number::text, pl.nick_name, (SELECT avg(tp.high_score) as myAvg
           from (select score as top_score from game_score t2 WHERE
           game_date BETWEEN ? AND ?
           and g.gamer_number=t2.gamer_number
           order by score desc limit 5) as tp)::text] as row_name,
           score::text as bucket, score from bet b
           left join player pl on g.gamer_number=pl.gamer_number
           WHERE g.game_date BETWEEN ? AND ? AND g.gamer_number=? order by score DESC limit 5')
as gamehighscore ( row_name text[], score1 numeric, score2 numeric , score3 numeric , score4 numeric , score5 numeric )

Running against Postgres 8.3.8 running on Windows XP. Tried with jdbc drivers 8.3-603.jdbc3 and 8.4-701.jdbc3

Is this possible? How do I escape the parameters within the crosstab? Is it the crosstab that is the issue even? Any help would be most welcome.

Thanks

Carl


*************************************************************************

This email and its attachments are intended for the above named only and may be 
confidential. If you have received them in error please notify the sender immediately 
and delete all records of the email and its attachments from your computer. Unless you 
are the intended recipient you must not read, copy, distribute, use or retain this email 
or any attachments or any part of them.

Although we aim to use efficient virus checking procedures for emails and attachments 
we accept no liability for viruses and it is the recipient's responsibility to ensure they 
are actually virus free.

Stanley International Betting, registered in England with no.3357517.

**************************************************************************