Multiple Parameters, "Not Enough Values For Host Variables"

[mdugger]

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]

Not enough values for host variables:"

DECLARE @DStart DATE

DECLARE @DEnd DATE

SET @DStart = $P{Date_1}

SET @DEnd = $P{Date_2}

 

Assigning parameter values to more than one SQL variable always results in this error, but if I use just one parameter and hard-code the value of the other variable into the SQL script the report runs as expected.  I also tried eliminating variables and adding the parameter results to the query where necessary, but received the same error.  Changing to parameter class doesn't seem to make a difference.  Is there a reason why this might be happening?

[Tom C]

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:

http://codeverge.com/sybase.sqlanywhere.general/err-not-enough-values-for-host-var/991758

"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"

[hozawa]

You've provided a link to "Symantec" web site instead of Sybase site. (FYI, Sybase is a subsidiary of SAP)

[hozawa]

Have you checked the following SAP SQL Anywhere forum.

http://sqlanywhere-forum.sap.com/questions/21948/sql-anywhere-error-188-not-enough-values-for-host-variables

[teodord]

Hi,

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.

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeQuery%28%29

I hope this helps.
Teodor