store procedure problem

[2001 JI Open Discussion]

By: Mario Dino - ddb1212

store procedure problem

2002-06-07 04:13

I am having a problem when trying to execute store procedure and immidiatelly after that I try to execute select statement to retrieve my result set. ( Driver error is saying "Invalid column name" even though the column exists ). But when I remove store procedure - and leave only select statement then it execute without problems,

. Please help.

 

My query String looks somewhat like this:

--------------------------

exec my_store_proc par2, par2

 

select col1 , col2 from table_name

 

---------------------------

 

I am using jdbc driver for MS SQL2000

 

Thank you in advance

 

Mar

 

 

By: Samanth Athrey - samanth_athrey

RE: store procedure problem

2002-06-07 04:27

hi, this definitely is not a problem with jasperreports. pls check ur stored procedure that u have coded. probably there is a mistake in the way u have represented that column name!

 

hth

samanth

 

 

By: Mario Dino - ddb1212

RE: store procedure problem

2002-06-07 04:57

Thanks Samanth, but store procedure and select statement have been tested in SQL manager - outside of jasper and it works fine. I was wondering if there is a special way of calling store proc , something like

CALL sp_name , or maybe if I can execute it programmaticaly using Jasper's API.

 

Thanks

 

Mar

 

 

By: Teodor Danciu - teodord

RE: store procedure problem

2002-06-07 07:26

 

Hi,

 

I'm not sure what you have tried is correct.

 

JasperReports uses a JDBC PreparedStatement

behind the scenes to execute the report query.

You tried to send simulations two SQL commands

in the same call.

The first is a stored procedure call and the second

is a normal SELECT.

 

I don't think you can do that.

 

A solution would be to create another stored

procedure that will embed those two.

Or why don't you put the SELECT at the end of the

store procedure.

The purpose would be to have in the report query

only one stored procedure call.

You can't have them both simultaneously.

 

Yet another approach would be to execute the

stored procedure call before launching the report

generation with only the SELECT in it.

 

I hope this helps.

Teodor