Re: Problem about executeUpdate and stored procedure that

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 returns a row. But I do need 
>> to call a function that returns no result, but do some updates. I defined 
>> the function as "create function ... 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@xxxxxxxxxxxx
>> 　　　　　　　　　　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@xxxxxxxxxxxxxx)
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html

        Regards!

                                
　　　　　　　　Han
　　　　　　　　zhouhanok@xxxxxxxxxxxx
　　　　　　　　　　2003-05-19





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx