Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

calling a stored procedure(with no return parameters) from jdbc Expand / Collapse
Author
Message
Posted Monday, May 26, 2008 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:09 AM
Points: 17, Visits: 37
Hi,

I have a stored procedure that requires two input parameters and no return parameter.. The proc is called from java...as

CallableStatement csmt = null;
csmt = con.prepareCall("{call dbo.MSsqlCommonProc(?,?)}");
csmt.setString(1,tableName);
csmt.setString(2,outputTableName);
int result = csmt.executeUpdate();

I get the following error when the proc is called...

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near '1'.]

I dont have an idea as to what this error means or is the call statement correct...

Need help on this..

thanks
Roy
Post #506454
Posted Tuesday, May 27, 2008 3:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:09 AM
Points: 17, Visits: 37
Hi,

i got this resolved. The problem was due to an invalid statement in the stored procedure...the error did not appear when the stored procedure was compiled..but when called from a java program

---------------------------------------------------------------------
The code:

CREATE PROC myProcedure @tableA_name varchar(30),@tableA_column varchar(30)
AS
BEGIN
DECLARE @param1 varchar(50),
@param2 varchar(50),
@param3 varchar(50),
@functionString varchar(1000),
@updateQuery varchar(1000)

SET @updateQuery = 'update '+@tableA_name+' set '
SET @param1 = 'tableB';
SET @param2 = 'STATE';
SET @param3 = '';
-- this EXEC statement caused the error
EXEC ('insert into tableC(tab_Name,colm_Name) values ('''+@tablename+''','''+@col+''')');
-- the corrected statement is
-- insert into tableC(tab_Name,colm_Name) values(@tablename,@col)
SET @functionString = '(myFunction('''+@param1+''','''+@param2+''','''+@param3+''')),';
SET @updateQuery = @updateQuery + @tableA_column +'='+@functionString;
EXECUTE(@updateQueryFinal);
END
--------------------------------------------------------------------

It was my mistake of not providing the procedure code too...
Post #506765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse