CODEWITHSUNDEEP
eclipsedatasetreportbirt
veljasije
veljasije

Reputation: 7092

BIRT report dataset based on SP error

I have installed Eclipse IDE and create BIRT project. I've successfully added jdbc for MySQL connector and create a data source, which successfully passed.

When I create dataset that will rely on stored procedure I get an error that states:

Cannot set preparedStatement parameter int value. SQL error #1:Parameter index out of range (1 > number of parameters, which is 0)

I don't know why I am getting this error.

I execute my SP directly through MySQL Workbench like this way:

call getUserForCity(1);

and I got some results, around 100 rows. I've tried with hard-coded value and with parameter value separately and in both case I've got same exception.

Upvotes: 0

Views: 850

Answers (2)

veljasije
veljasije

Reputation: 7092

Here is the catch, and also I would explain complete process of configuring dataset in BIRT.

After you successfully create data source to MySQL database, next step is creating a one or more datasets that rely on MySQL stored procedures.

On Data Explorer right click on Dataset and choose New Dataset. In dialog window, choose appropriate data source, give a name for dataset and choose one of two possible option for getting data from database: Query or Stored procedure.

Whatever option you choose, way of configuring parameters is same. In query editor write a code for calling stored procedure like this way:

call procedure_name(?, ?, ?, ...);

where number of '?' signs is related to number of parameters the procedure expecting. On dataset configuration window choose Parameters option to define all parameters that your dataset accepts. Order of defined parameters must be same as order of stored procedure parameters or you can get error or unappropriate results.

Also, if you want to fill your dataset parameter with value of another parameter (often filled through another dataset) you can set this option in Parameter window choosing one of available parameter in section Linked to Report Parameter.

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 77030

You are getting the error because the expected number of parameters for your stored procedure is 0 and instead of calling without parameters you are passing a parameter. You need either to modify your stored procedure to expect and handle the parameter or call it without paramters.

Upvotes: 0

Related Questions