Hi Paul,

I'm not sure if this is what you're looking for, but hopefully it will point
you in the right direction.

Create a stored procedure in postgresql with parameters:

CREATE OR REPLACE FUNCTION uspGetParameter(varchar(20), int4, float4,
float4)
RETURNS float4
AS
'SELECT $3 * $4;'
STABLE
LANGUAGE SQL;
GRANT EXECUTE ON FUNCTION public."uspgetparameter"(varchar(20), int4,
float4, float4) TO PUBLIC;

Call the procedure from dotnet (VB in this case):

Dim Connection As String = "DRIVER={PostgreSQL}; SERVER=localhost;
UID=George Weaver; DATABASE=test; Readonly=0;"

Dim cnPgSQL As New OdbcConnection(Connection)

Dim myCmdString As String = "Select uspGetParameter(?, ?, ?, ?);"

Dim myCommand As New OdbcCommand(myCmdString, cnPgSQL)

Dim myParamCollection As OdbcParameterCollection = myCommand.Parameters

Dim p1 As New OdbcParameter("", OdbcType.Text)
p1.Value = "apple"
myParamCollection.Add(p1)

Dim p2 As New OdbcParameter("", OdbcType.Int)
p2.Value = 1
myParamCollection.Add(p2)

Dim p3 As New OdbcParameter("", OdbcType.Real)
p3.Value = 1.5
myParamCollection.Add(p3)

Dim p4 As New OdbcParameter("", OdbcType.Real)
p4.Value = 5.6
myParamCollection.Add(p4)

myCommand.CommandType = CommandType.StoredProcedure

cnPgSQL.Open()

Dim ReturnValue As Single
ReturnValue = CSng(myCommand.ExecuteScalar)

cnPgSQL.Close()

Should return the value of 8.4

Regards,
George


----- Original Message -----
From: "Paul Semenick" <PSemenick (AT) computer-systems (DOT) com>
To: <pgsql-novice (AT) postgresql (DOT) org>
Sent: Tuesday, March 30, 2004 5:10 PM
Subject: [NOVICE] dotnet stored procedures with postgresql


Looking for some examples of stored procedures with
parameters using dotnet, postgresql and odbc
Thanks,
Paul

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

George,
Yes this is exactly what I needed. I copied the vb code
from your post into vb.net and it worked on the second try.
On the first try I hadn't created the stored procedure
Thanks,
Paul

"George Weaver" <gweaver (AT) shaw (DOT) ca> wrote