469,331 Members | 1,757 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
2 2431
--- Pavel Stehule <st*****@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

ma*******@postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
Jeff Eckermann wrote:
--- Pavel Stehule <st*****@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/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Sean Anderson | last post: by
3 posts views Thread by Joe | last post: by
11 posts views Thread by DJJ | last post: by
2 posts views Thread by Indiana Epilepsy and Child Neurology | last post: by
5 posts views Thread by Todd Huish | last post: by
4 posts views Thread by Dave | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.