Thread: ODBC, ADO, Return Value from SP problem

Hello

    I need use ADODB client via ODBC. I can call stored proc
without problems. I am sure so proc is called (I am loging params)
but retern value is always null.

dim cn as new ADODB.Connection()
cn.Open("DSN=intra","nepi")
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "testcallsp"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Referesh()
cmd.Parameters(1).Value = 22
cmd.Execute()
MsgBox cmd.Parameters(0).Value 'only null :-((


Stored Proc is simply

CREATE OR REPLACE FUNCTION testcallsp(integer) RETURNS integer AS '
BEGIN
  RAISE LOG ''% '', $1;
  RETURN coalesce($1,10) + 1;
END; ' LANGUAGE plpgsql;

What is wrong please?

Thank You very much
Pavel Stehule

--- Pavel Stehule <stehule@kix.fsv.cvut.cz> wrote:
> Hello
>
>     I need use ADODB client via ODBC. I can call stored
> proc
> without problems. I am sure so proc is called (I am
> loging params)
> but retern value is always null.
>
> dim cn as new ADODB.Connection()
> cn.Open("DSN=intra","nepi")
> Dim cmd As New ADODB.Command
> Set cmd.ActiveConnection = cn
> cmd.CommandText = "testcallsp"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Referesh()
> cmd.Parameters(1).Value = 22
> cmd.Execute()
> MsgBox cmd.Parameters(0).Value 'only null :-((

A PostgreSQL function is not a stored procedure in the
same sense as in Oracle or MS SQL Server, for example,
so you cannot use the syntax you would use for those.
You need to use normal SQL select syntax, i.e. "select
testcallsp()", and the return value comes back as a
recordset (which happens to have one column and one
row).

>
>
> Stored Proc is simply
>
> CREATE OR REPLACE FUNCTION testcallsp(integer)
> RETURNS integer AS '
> BEGIN
>   RAISE LOG ''% '', $1;
>   RETURN coalesce($1,10) + 1;
> END; ' LANGUAGE plpgsql;
>
> What is wrong please?
>
> Thank You very much
> Pavel Stehule
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

Jeff Eckermann wrote:

>--- Pavel Stehule <stehule@kix.fsv.cvut.cz> wrote:
>
>
>>Hello
>>
>>    I need use ADODB client via ODBC. I can call stored
>>proc
>>without problems. I am sure so proc is called (I am
>>loging params)
>>but retern value is always null.
>>
>>dim cn as new ADODB.Connection()
>>cn.Open("DSN=intra","nepi")
>>Dim cmd As New ADODB.Command
>>Set cmd.ActiveConnection = cn
>>cmd.CommandText = "testcallsp"
>>cmd.CommandType = adCmdStoredProc
>>cmd.Parameters.Referesh()
>>cmd.Parameters(1).Value = 22
>>cmd.Execute()
>>MsgBox cmd.Parameters(0).Value 'only null :-((
>>
>>
>
>A PostgreSQL function is not a stored procedure in the
>same sense as in Oracle or MS SQL Server, for example,
>so you cannot use the syntax you would use for those.
>You need to use normal SQL select syntax, i.e. "select
>testcallsp()", and the return value comes back as a
>recordset (which happens to have one column and one
>row).
>
>
>
I am currently working on an extension to the OLE DB provider that will
support this syntax. It already supports returning multiple rowsets. I'm
hoping to also support the above syntax (no ETA, though).

Mind you, however, that you will have to rewrite the stored procedure a
little for that to work. It will have to be a function returning setof
refcursor for that to work.

             Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/