error while Executing stored procedure ,Help

0
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.
2004 IR Help's picture
Joined: Aug 17 2006 - 3:49am
Last seen: 7 years 11 months ago

Answers:

No answers yet
Feedback