Re: dotnet stored procedures with postgresql

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 in message
news:004301c41beb$9bb85570$6400a8c0(at)Dell4500(dot)(dot)(dot)
> 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)
>