locked
Execution of User Defined Function fails with JDBC, when selectMethod=cursor is enabled RRS feed

  • Question

  • Hi,

    When I try to execute  a function using CallableStatement after setting selectMethod=cursor, it throws error com.microsoft.sqlserver.jdbc.SQLServerException: The request for procedure 'MYFUNCTION' failed because 'MYFUNCTION' is a function object. ErrorCode:2809 SQLState:S0001

     

    If I remove selectMethod=cursor, the function is executing properly

    Client details

    JDBC driver : JDBC driver 3.0(sqljdbc4.jar)

    Tested with JDBC driver for SQL server 2005 SP2 al;so.

    OS : Windows XP Professiional

    Java: 

    java version "1.6.0_22"

    Java(TM) SE Runtime Environment (build 1.6.0_22-b04)

    Java HotSpot(TM) Client VM (build 17.1-b03, mixed mode)

     

    Server details

    SQL Server 2005(Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3) )

     

    Did somebody face this? How to solve it?

    We need selectMethod=cursor because of issue mentioned here(http://connect.microsoft.com/SQLServer/feedback/details/295907/resultsetmetadata-gettablename-returns-null-or-inconsistent-results) and the solution as suggested.

    Regards

    Sunil

    Friday, April 8, 2011 11:48 AM

Answers

  • SQLserver does not support cursors on UDFs. That's the reason for the error. However, that can be worked around using t-sql in a jdbc preparedstatement/statement. Here's the sample code. 

    Class.forName(

    "com.microsoft.sqlserver.jdbc.SQLServerDriver");

    String url=<connectionString with select Method=cursor>

    ;

    Connection conn = DriverManager.getConnection(url) ;

    PreparedStatement pstmt = conn.prepareStatement(

    "SELECT dbo.MYFUNCTION(?, ?)");

    pstmt.setString(1,

    "1");

    pstmt.setString(2,

    "2");

    ResultSet rs = pstmt.executeQuery();

     

    while( rs.next() )

    System.

    out.println( rs.getString(1) ) ;
    • Proposed as answer by Papy Normand Saturday, May 14, 2011 7:43 AM
    • Marked as answer by Sumadhur Reddy Monday, May 16, 2011 6:53 PM
    Saturday, May 14, 2011 12:03 AM

All replies

  • Hello,

    Please, could you provide the code which fails ? ( don't forget that all the visitors of this forum are not super-specialists of jdbc )

    We are waiting for your feedback to try to help you more efficiently

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, April 9, 2011 5:59 AM
  • Hi,

    The below code will throw the exception.

    String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // Microsoft JDBC driver

    String url = "jdbc:sqlserver://myserver\\instance:<<port>>;selectMethod=cursor"; // a JDBC url 

    String username = "myusername";

    String password = "mypassword";

    String dbName = "mydb";

    Class.forName(driverName);

    Connection connection = DriverManager.getConnection(url, username, password);

    connection.setReadOnly(true);

    connection.setAutoCommit(true);

    connection.setCatalog(dbName);

    {

    String sql = "{?=call MYFUNCTION(?,?)}";

    CallableStatement cstmt = connection.prepareCall(sql);

    cstmt.registerOutParameter(1, Types.VARCHAR);

    cstmt.setString(2, "Test1");

    cstmt.setString(3, "Test2");

    boolean test= cstmt.execute();

    System.out.println("Execution of UDF is over!!!");

    }

     

    The function script is given below

     

    CREATE FUNCTION [MYFUNCTION] 

    (

      @PARAM1 varchar(1000),

      @PARAM2 varchar(1000)

    )

    RETURNS varchar(2000)

    AS

    BEGIN

    DECLARE @RESULT_DOCUMENT_KEY VARCHAR(4000)

    SET @RESULT_DOCUMENT_KEY = @PARAM1 + @PARAM2

    return @RESULT_DOCUMENT_KEY

    END --End of stored function

     

    Hope this helps

     

    Regards

    Sunil

     


    Zunil
    Monday, April 11, 2011 9:42 AM
  • Hi ,

    One more point. If I remove selectMethod=cursor from the URL of above code, it works

     

    Regards

    Sunil


    Zunil
    Monday, April 11, 2011 9:43 AM
  • Hello,

    I am very far to be a specialist in jdbc but in the BOL, i found :

    http://msdn.microsoft.com/en-us/library/ms378136.aspx

    the definition of prepareMethod is "Creates a SQLServerCallableStatement object for calling database stored procedures"

    Nothing about the call of a function .

    A suggestion maybe stupid : could you transform your UDF in a stored procedure ? ( you add a new parameter @returnvalue with the attribute OUTPUT and the type varchar(4000))

    Have a nice day

     


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, April 11, 2011 10:45 AM
  • Hi,

    1.It works if you use a stored procedure.

    2. Function(I tested the above function) also will work if remove selectMethod=cursor. so that means callable statement will work with function

     

    In one of our existing application, we faced an issue with metadata as mentioned here(http://connect.microsoft.com/SQLServer/feedback/details/295907/resultsetmetadata-gettablename-returns-null-or-inconsistent-results). Thats why we started using selectMethod=cursor. Then we see that it throws the stored function issue.

     

    So converting stored function to procedure is not an option in the production env as its an application specific function.

     

    Is there any other workaround? Is it a known issue?

     

    Regards

    Sunil


    Zunil
    Monday, April 11, 2011 10:52 AM
  • Hello,

    Please, could you have a look at this link ?

    http://connect.microsoft.com/SQLServer/feedback/details/295907/resultsetmetadata-gettablename-returns-null-or-inconsistent-results#

    I have posted to tell my annoyance about this problem which is appearing for SQL Server 2000 and 2005 and even wit a 3.0 driver.

    I suggest you to click on the green arrow to tell that you can reproduce this problem.And maybe to post a comment to explain your current problem.

    I am really sorry, but for the moment, i cannot do anything more to help you more efficiently. I will try on the blog of the jdbc team but , as i told you , i am a beginner with jdbc, so my research may be unsuccessful

    Have a nice day

    PS : stored is used only for stored procedures, for functions which are not system, they are called user-defined functions or UDF ( sorry for this little remark which is absolutely not aggressive, it is only to be sure that we are talking about the same things )


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, April 11, 2011 12:22 PM
  • Hello,

    I have found this thread with some useful suggestions :

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/55e8cbb2-b11c-446e-93ab-dc30658caf99/

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, April 11, 2011 12:32 PM
  • Thanks for your help. I added a comment to that link. We tested earlier with various resultset options and found that TYPE_FORWARD_ONLY works for all kinds of queries (simple, join, nested etc) . If someone from JDBC driver can help(at laest whether MS has any plans to fix this in the latest JDBC driver), it will be really appreciated.
    Zunil
    Tuesday, April 12, 2011 9:05 AM
  • Unfortunately, cursors in general should be employed carefully with SQL Server.  Each cursor type has an implicit set of statements that it can be applied to -- for instance one naturally could not apply a Dynamic cursor (allowing for updates of the values within the cursors) to the output of a stored procedure -- as the mapping of how to update that output in terms of base objects is not necessarily determinable.  Forward-only cursors are the most lenient in terms of the types of statements with which they may be used, but even they cannot be used with a query that produces multiple result sets.

    What I, personally, do when determining my cursor model is:
    1.) Decide -- do I really need this cursor?  Cursors have an overhead cost on the Database Engine, which is a central point of failure for all clients.
    2.) Test cursor compatability -- using the T-SQL CREATE CURSOR statement, I can then verify that the cursor I think I need is compatible with the statement I am executing.
    3.) Implement the code and test it with the set of viable cursors for performance, scalability, and functionality.

    Hope that helps!

    John


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Wednesday, April 13, 2011 5:38 PM
  • Hi John,

    Thanks for your detailed reply.

    Yes. I am not keen to use cursors, but was forced to do that. Because if I don't use cursors, MS JDBC driver don't even give the table name in ResultSetMetadata for very simple, single table queries(if no LOB fields exist). If LOB type fields present in the query, it gives table name without cursor.

     

    It was reported to MS (http://connect.microsoft.com/SQLServer/feedback/details/295907/resultsetmetadata-gettablename-returns-null-or-inconsistent-results) and it is closed saying that it is implemented for cursor.

     

    So I am forced to use cursor:)

    Else if given a choice, I don't want o

     

    Hope you understand.

     

    Regards

    Sunil


    Zunil
    Thursday, April 14, 2011 11:36 AM
  • SQLserver does not support cursors on UDFs. That's the reason for the error. However, that can be worked around using t-sql in a jdbc preparedstatement/statement. Here's the sample code. 

    Class.forName(

    "com.microsoft.sqlserver.jdbc.SQLServerDriver");

    String url=<connectionString with select Method=cursor>

    ;

    Connection conn = DriverManager.getConnection(url) ;

    PreparedStatement pstmt = conn.prepareStatement(

    "SELECT dbo.MYFUNCTION(?, ?)");

    pstmt.setString(1,

    "1");

    pstmt.setString(2,

    "2");

    ResultSet rs = pstmt.executeQuery();

     

    while( rs.next() )

    System.

    out.println( rs.getString(1) ) ;
    • Proposed as answer by Papy Normand Saturday, May 14, 2011 7:43 AM
    • Marked as answer by Sumadhur Reddy Monday, May 16, 2011 6:53 PM
    Saturday, May 14, 2011 12:03 AM
  • This did not work for me.  The first query executes fine, but the 2nd one that contains a sql server function I defined, just hangs.  I am using sqljdbc4.jar:

     

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        Properties props = new Properties();

        props.put("user", "xxx");

        props.put("password", "xxx");

        Connection conn = DriverManager.getConnection("jdbc:sqlserver://xxx;selectMethod=cursor", props);

        String query = "SELECT GETDATE()";

        PreparedStatement stmt = conn.prepareStatement(query);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

          System.out.println(rs.getString(1));

        }

     

        query = "SELECT dbo.MY_FUNC()";

        stmt = conn.prepareStatement(query);

        rs = stmt.executeQuery();

        while (rs.next()) {

          System.out.println(rs.getString(1));

        }

     

        stmt.close();

    Wednesday, November 23, 2011 10:48 PM