Thread: Re: Problem about executeUpdate and stored procedure that

Re: Problem about executeUpdate and stored procedure that

From
"Han"
Date:
Barry Lind,
 function:
    create function testfunc() returns void language sql as 'update table1 set col1=0';
 Test.java:
    ...
    CallableStatement stmt = null;
    try
    {
        stmt = conn.prepareCall("{call testfunc()}");
        stmt.executeUpdate();
    }
    catch(Exception e)
    {
        System.out.println(e.toString());
    }
    ...
 output:
    java.sql.SQLException: ERROR:  Unknown kind of return type specified for function testfunc

 I checked the jdbc code, and find this in AbstractJdbc1Statement::modifyJdbcCall(string):
    if (connection.haveMinimumServerVersion("7.3")) {
            l_sql = "select * from " + l_sql + " as " + RESULT_ALIAS + ";";
        } else {
            l_sql = "select " + l_sql + " as " + RESULT_ALIAS + ";";
        }
 I changed "7.3" to "7.4" here. And then the error became :    A result was returned when none was expected.

>Han,
>
>Could you send a more complete test case, that I could compile and run?
>
>thanks,
>--Barry
>
>
>Han wrote:
>> pgsql-jdbc
>>
>>     When using executeUpdate with {call func_name(?,?,?)}, there's an error saying that no result should be
returned.But the value is updated successfully in the database by the function. 
>>     I know that it's because {call func_name(?,?,?)} be transfered to "select func_name(...) as result" and this sql
returnsa row. But I do need to call a function that returns no result, but do some updates. I defined the function as
"createfunction ... returns void as'...'". But it still returns a row contains one field with null value. 
>>     I don't want to use executeQuery. Is there anyother way to do this? Or should the jdbc executeUpdate should be
fixed?
>>     Thanks!
>>
>>         
>>
>>
>>         Han
>>         zhouhanok@vip.sina.com
>>           2003-05-16
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html

    Regards!


        Han
        zhouhanok@vip.sina.com
          2003-05-19