Calling a table-valued function using JDBC

[LakshmiVaidhyam]

Hello all,

i have SQL 2005 installed. i have written a table-valued function getdepartments() which is as below:

CREATE FUNCTION getdepartments() 
RETURNS @departments TABLE(
                                                  DNAME VARCHAR(25), 
                                                  DEPTID VARCHAR(10), 
                                                   DBONUS DECIMAL(7,2)) 
AS  
BEGIN 
           INSERT @departments SELECT * FROM DEPARTMENT; 
RETURN
 END

i need to invoke this statement from java program using JDBC.
for that i have written the following code in my java program.

try
{
	getConnection();       //to establish connection
	CallableStatement cStmt = conn_.prepareCall("{? = call getdepartments()}");
	cStmt.registerOutParameter(1,Types.BINARY);
	cStmt.executeQuery();
	rs = (ResultSet) cStmt.getObject(1);
}

but this code doesn't seem to work properly
i get an error saying "stored procedure getdepartments not found".
hence please let me know how to make a call to table-valued functions using JDBC

Thank you,
Lakshmi

[Ramy Mahrous]

It may need full qualified name [DatabaseName].[SchemeName].[FunctionName]

Try that and reply me!

[peter_budo]

Have look at this post in JSP section. It does show connection to MySQL, but that can be easily modified to MS SQL