Re: dotnet stored procedures with postgresql

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@computer-systems.com>
To: <pgsql-novice@postgresql.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@postgresql.org so that your
      message can get through to the mailing list cleanly