I'm running JasperSoft Studio 6.0.1, and have been attempting to design a report with input controls for starting and ending dates that will return data from a SQL Anywhere database. While doing so, I discovered that I am unable to use more than one SQL variable having a report parameter as a value in a query. Adding the following lines to the beginning of any query (or doing this sort of thing in general) returns the error "[Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables:"
This is a Sybase issue that has nothing to do with Jaspersoft product. You should go to their site looking for resolutions. For example:
http://www.symantec.com/business/support/index?page=content&id=TECH212015 (oops, wrong link)
Read this please:
"This error is sometimes difficult to track down. It can occur on INSERT or UPDATE statements as well as SELECT. In some cases the cause was multiple rows being returned when only one row was expected. The variables that were to hold the return values were insufficient. Another problem can occur when passing variables to procedures and also specifying them as OUT parameters when that really isn't necessary. In the cases I have seen it was a programming error, not a bug. -- Jim Egan [TeamPS] Dealer Solutions, LLC Houston, TX"
"Seems like I had this same problem in O++. If I remember correctly, I got around it at runtime by specifically preparing the query before running it. I never did get it solved for design time. I don't know if that will help under BCB but it's worth a shot. Good Luck Jeff"
Note that the built-in SQL query executer of JR Library, is only able to execute queries that return a ResultSet through JDBC call.
This is usually the case with SELECT statements.
If the sequence of SQL statements you have cannot be executed through the executeQuery() of the PreparedStatement interface of JDBC, then it would not work with JR's built-in SQL query executer and you would either have to wrap them in a stored procedure call, or implement your own query executer for JR.
I hope this helps.