Multiple Parameters, "Not Enough Values For Host Variables"

0

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

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?
mdugger's picture
Joined: Feb 17 2015 - 2:25pm
Last seen: 2 months 2 days ago

3 Answers:

0

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-fo...

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

tchen's picture
38108
Joined: Feb 27 2008 - 7:33am
Last seen: 2 hours 30 min ago

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

hozawa - 4 years 4 weeks ago
1

Have you checked the following SAP SQL Anywhere forum.

http://sqlanywhere-forum.sap.com/questions/21948/sql-anywhere-error-188-...

hozawa's picture
40895
Joined: Apr 24 2010 - 4:31pm
Last seen: 12 hours 43 min ago
0

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

I hope this helps.
Teodor

 

teodord's picture
4161
Joined: Jun 30 2006 - 9:00am
Last seen: 6 days 13 hours ago
Feedback
randomness