calling a stored procedure(with no return parameters) from jdbc

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » calling a stored procedure(with no return...

calling a stored procedure(with no return parameters) from jdbc

Rate Topic

Display Mode

Topic Options

Author

Message

royston_pledger

Posted Monday, May 26, 2008 5:32 AM

Grasshopper

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

royston_pledger

Posted Tuesday, May 27, 2008 3:25 AM

Grasshopper

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... Hehe

Post #506765

« Prev Topic | Next Topic »

Permissions