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

Re: dotnet stored procedures with postgresql

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 (view raw or flat)
Thread:
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



In response to

Responses

pgsql-novice by date

Next:From: Nabil SayeghDate: 2004-04-06 15:36:21
Subject: Re: 7.4 dramatically slower than 7.3?
Previous:From: Stephan SzaboDate: 2004-04-06 15:15:41
Subject: Re: 7.4 dramatically slower than 7.3?

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