PostgreSQLDirect.NET with Postgre stored procedures

PostgreSQLDirect.NET with Postgre stored procedures

Postby Arno » Fri 28 Jan 2005 15:54

I would like to use PostgreSQLDirect.NET with Postgre stored procedures.

For example, to edit data of a table using PgSqlDataAdapter and 4 PgSqlCommands (insert,update,delete,select) where these PgSqlCommands are type of
System.Data.CommandType.StoredProcedure.

Do you have a sample of sql scripts to create the stored procedures in postgre. (based on tables of your demo project?)

Thanks
Arno.
Arno
 

Re: PostgreSQLDirect.NET with Postgre stored procedures

Postby Yuri » Mon 31 Jan 2005 16:26

You can find text of sql script used to create a stored procedure in source code of our StoredProc demo project. This procedure is used by SelectCommand. Below are scripts that create stored functions you can use with Select and InsertCommand.

Code: Select all
select function:

CREATE OR REPLACE FUNCTION getdept()
  RETURNS SETOF dept AS
  'DECLARE rc dept%ROWTYPE;
  BEGIN FOR rc IN SELECT * FROM public.dept  ORDER BY deptno LOOP   
      RETURN NEXT rc;     
  END LOOP;   RETURN;  END; '
  LANGUAGE 'plpgsql' VOLATILE;


Code: Select all
Insert function:

CREATE OR REPLACE FUNCTION insert_dept(int4, "varchar", "varchar")
  RETURNS int4 AS
  'DECLARE
  num ALIAS FOR $1;
  name ALIAS FOR $2;
  location ALIAS FOR $3;
  BEGIN
   INSERT INTO  public.dept  VALUES (num ,name, location);
   return 1;
  END;'
  LANGUAGE 'plpgsql' VOLATILE;


Code: Select all
C# code:
  PgSqlDataAdapter dataAdapter = new PgSqlDataAdapter();
  dataAdapter.SelectCommand = pgSqlConnection.CreateCommand();
  dataAdapter.SelectCommand.CommandText = "getdept";
  dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
  dataAdapter.InsertCommand = pgSqlConnection.CreateCommand();
  dataAdapter.InsertCommand.CommandText = "insert_dept";
  dataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;
  PgSqlCommandBuilder.DeriveParameters(dataAdapter.InsertCommand);
Yuri
 
Posts: 140
Joined: Mon 08 Nov 2004 12:07

Postby Arno » Wed 02 Feb 2005 15:08

Thank a lot for your answer. That's help me.

I have 3 questions/issues with PostgreSQLDirect.NET.

1- PgSqlDataAdapter doesn't throw DBConcurrencyException. The exception that is thrown by the DbDataAdapter during the update operation if the number of rows affected equals zero.
(PgSqlDataAdapter inherits from DbDataAdapter).
(It can be be solved by throwing in the postgre stored procedure.)

2- How to retrieve a PgSqlParameter with Output.Direction ?
I didn't succeed.


3- Postgre functions is limited to 32 arguments. The "recommendation" is to use function with composite type.
for example
CREATE FUNCTION double_sal(emp) RETURNS numeric AS $$
SELECT $1.sal * 2 AS salary;
$$ LANGUAGE SQL;

where emp is table (or can be a view). Is PostgreSQLDirect.NET allows this usage ?


Thanks.
Arno.
Arno
 

Postby Yuri » Thu 03 Feb 2005 10:41

Arno wrote:How to retrieve a PgSqlParameter with Output.Direction ?

There is no way to retrieve OUT parameters. PostgreSQL only has IN parameters.
Arno wrote:Postgre functions is limited to 32 arguments. The "recommendation" is to use function with composite type.
for example
CREATE FUNCTION double_sal(emp) RETURNS numeric AS $$
SELECT $1.sal * 2 AS salary;
$$ LANGUAGE SQL;
where emp is table (or can be a view). Is PostgreSQLDirect.NET allows this usage ?

Now this feature is not supported. We are planning to support it at the next version of PostgreSQLDirect .NET.
Yuri
 
Posts: 140
Joined: Mon 08 Nov 2004 12:07

PostgreSQL can returns output parameter.

Postby Visitor » Sat 05 Mar 2005 06:42

Yuri wrote:There is no way to retrieve OUT parameters. PostgreSQL only has IN parameters.

This is not truth. Others data providers returns output parameter.
e.g: Npgsql e PgSqlClient.
Visitor
 

PostgreSQL can returns output parameter.

Postby Yuri » Sat 05 Mar 2005 17:06

Another providers really can support Output direction but only for a result of the function. PostgreSQLDirect .NET doesn't support this ability yet.

P.S.
PostgreSQL server doesn't support OUT parameters for functions.
Pay attention to parameter_mode column from information_schema.parameters view, http://www.postgresql.org/docs/8.0/static/infoschema-parameters.html -only IN parameters are supported.
Yuri
 
Posts: 140
Joined: Mon 08 Nov 2004 12:07

Composite type as parameter.

Postby rwalrond » Thu 14 Apr 2005 18:52

Yuri wrote:
Arno wrote:How to retrieve a PgSqlParameter with Output.Direction ?

There is no way to retrieve OUT parameters. PostgreSQL only has IN parameters.
Arno wrote:Postgre functions is limited to 32 arguments. The "recommendation" is to use function with composite type.
for example
CREATE FUNCTION double_sal(emp) RETURNS numeric AS $$
SELECT $1.sal * 2 AS salary;
$$ LANGUAGE SQL;
where emp is table (or can be a view). Is PostgreSQLDirect.NET allows this usage ?

Now this feature is not supported. We are planning to support it at the next version of PostgreSQLDirect .NET.


Using the example from above If I set my command text to:
double_sal(row(id,firstname, lastname, etc..))
I get an error, so does this mean that you have not added support for this feature in the lastest release? If not, then when?
rwalrond
 
Posts: 4
Joined: Thu 14 Apr 2005 18:37

Composite type as parameter

Postby Yuri » Mon 18 Apr 2005 06:13

PostgreSQLDirect .NET supports record types only to obtain a result of the function.
A possibility to pass record types through parameters will be added at the next version of the provider.
Yuri
 
Posts: 140
Joined: Mon 08 Nov 2004 12:07


Return to dotConnect for PostgreSQL