Lists: | pgsql-novice |
---|
From: | "Paul Semenick" <PSemenick(at)computer-systems(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | dotnet stored procedures with postgresql |
Date: | 2004-03-30 22:10:32 |
Message-ID: | 6A1C87CA83BA8C419B1262B3728B12D101B154@csiserv1.computer-systems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Looking for some examples of stored procedures with
parameters using dotnet, postgresql and odbc
Thanks,
Paul
From: | George Weaver <gweaver(at)shaw(dot)ca> |
---|---|
To: | Paul Semenick <PSemenick(at)computer-systems(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: dotnet stored procedures with postgresql |
Date: | 2004-04-06 15:26:59 |
Message-ID: | 004301c41beb$9bb85570$6400a8c0@Dell4500 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
>