error while Executing stored procedure ,Help

[2004 IR Help]

By: pandu - pandu16

error while Executing stored procedure ,Help

2004-07-27 15:52

All,

 

I have written a stored procedure in MS Sql Server and tried to exec it from iReport as

EXEC postingReport $P{StateFips}, $P{CountyFips}, $P{SRR}, $P{TypeCode}, $P{SoilCode}

I am getting this error

==================================

java.sql.SQLException: [Microsoft][sqlServer 2000 Driver for JDBC]Syntax error at token }, line 0 offset 10.

 

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

==================================

 

When i use the values it works fine and i can view the Report

=======================

exec postingReport '41', '001', 19, 'G', 'OR604'

=========================

This one works fine but

==========================

EXEC postingReport $P{StateFips}, $P{CountyFips}, $P{SRR}, $P{TypeCode}, $P{SoilCode}

=======================

Gives error????????

 

Has any one gone thru this passing multiple IN parameters to stored procedures?

 

Please let me know, if you have come accross this error and you have found an work arround.

 

Thanks

Pandu

pandu16@yahoo.com

 

 

 

 

 

By: Chuck Deal - cdeal

RE: error while Executing stored procedure ,Help

2004-07-28 05:08

The difference between those two statements is that in the first, you used quotes. in the second, you did not. Try the quotes in the second and see if that works.

 

BTW, since that is going in the QueryString tag, you will want to use $P!{} instead of $P{}. Don't know how to explain it, you'll have to search for an answer as to why.

 

 

 

 

By: bhaskar - bhaskar01

RE: error while Executing stored procedure ,Help

2005-02-07 02:15

All,

 

i'm facing the prob while calling stored procedure from jasper assistant. please help me regarding this.

and i ve done all permutation and combinations as earlier done by others but it not works. i could not able to detect the error in my code is as follows.

 

I ve set property Query in report section with

++++++++++++++++++++++++++

{call myprocs($P{PRJ_NME},$P{TRGT_CURR_OUT})}

++++++++++++++++++++++++++

exec myprocs $P{PRJ_NME},$P{TRGT_CURR_OUT}

***************************

CREATE PROCEDURE Myprocs

(PRJ_NME IN VARCHAR2,

TRGT_CURR_OUT OUT NUMBER)

AS

--PRJ_NME VARCHAR2(60);

trgt_curr_local NUMBER(19,2);

 

BEGIN

--PRJ_NME:='Project1';

DBMS_OUTPUT.PUT_LINE('PROCESS START HERE ::' || PRJ_NME);

 

SELECT YRLY_TRGT_AMT

INTO trgt_curr_local

FROM FRCST_YR

WHERE YR_NME=TO_CHAR(SYSDATE,'yyyy')

AND ACTL_FRCST_INDCR_CD='F'

AND PROJ_NME=PRJ_NME;

 

TRGT_CURR_OUT := trgt_curr_local;

 

DBMS_OUTPUT.PUT_LINE('YEARLY FOR CURR :: ' || TRGT_CURR_OUT);

 

--SELECT YRLY_TRGT_AMT

--INTO TRGT_FIRST_OUT

--FROM FRCST_YR

--WHERE YR_NME=TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))+1)

--AND PROJ_NME=PRJ_NME

--AND ACTL_FRCST_INDCR_CD='F';

 

--DBMS_OUTPUT.PUT_LINE('YEARLY FOR FIRST :: ' + TRGT_FIRST_OUT);

 

--SELECT YRLY_TRGT_AMT

--INTO TRGT_SEC_OUT

--FROM FRCST_YR

--WHERE YR_NME=TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))+2)

--AND PROJ_NME=PRJ_NME

--AND ACTL_FRCST_INDCR_CD='F';

 

--DBMS_OUTPUT.PUT_LINE('YEARLY FOR SEC :: ' + TRGT_SEC_OUT);

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NO DATA FOUND ::' || PRJ_NME);

TRGT_CURR_OUT := 0;

--NULL;

WHEN OTHERS THEN

-- Consider logging the error and then re-raise

RAISE;

END Myprocs;

/

*************************************

any earliest help will greatful to me.

 

regards,

 

bhaskar.