Skip site navigation (1) Skip section navigation (2)

Re: dotnet stored procedures with postgresql

From: "PaulS" <psemenick(at)computer-systems(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: dotnet stored procedures with postgresql
Date: 2004-04-06 19:14:05
Message-ID: c4uvqn$24hf$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-novice
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)
>



In response to

pgsql-novice by date

Next:From: Michael GuerinDate: 2004-04-07 02:11:51
Subject: estimated row count way off.
Previous:From: Tom LaneDate: 2004-04-06 19:12:57
Subject: Re: 7.4 dramatically slower than 7.3?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group