using(PgSqlCommand sql = PostgreSQL.CreateCommadn()) & Store Procedure

using(PgSqlCommand sql = PostgreSQL.CreateCommadn()) & Store Procedure

Postby mr-owl » Tue 08 Apr 2008 14:57

Hi,

I have problem with my application, I have 2 tables: rm_bag & rm_bag_item

Code: Select all
CREATE TABLE rm_bag
(
  bag_id serial NOT NULL,
  bag_computer text NOT NULL,
  bag_type character(1) NOT NULL,
  bag_sortcode integer NOT NULL,
  report_id integer,
  bag_items_count integer NOT NULL DEFAULT 0,
  bag_weight integer NOT NULL DEFAULT 0,
  bag_start_time timestamp without time zone NOT NULL DEFAULT now(),
  bag_done boolean NOT NULL DEFAULT false,
  bag_done_time timestamp without time zone
)
WITH (OIDS=FALSE);

Code: Select all
CREATE TABLE rm_bag_item
(
  bag_item_id serial NOT NULL,
  bag_id integer NOT NULL,
  bag_item_weight integer NOT NULL,
  bag_item_date timestamp without time zone NOT NULL DEFAULT now()
)
WITH (OIDS=FALSE);

plpgsql function
Code: Select all
CREATE OR REPLACE FUNCTION rm_reset_bag(id integer) RETURNS void AS $$
BEGIN
  DELETE FROM rm_bag_item WHERE bag_id=id;
  DELETE FROM rm_bag WHERE bag_id=id;
END;
$$ LANGUAGE 'plpgsql';

and C# code
Code: Select all
if(dataGridView.CurrentRow.Index >= 0 && PostgreSQL.State == ConnectionState.Open)
{
  using (PgSqlCommand sql = PostgreSQL.CreateCommand())
  {
    sql.CommandType = CommandType.StoredProcedure;
    sql.Parameters.Add("id", PgSqlType.Int).Value = (int)dataGridView[0, dataGridView.CurrentRow.Index].Value;

    sql.CommandText = @"rm_reset_bag";

    try
    {
      sql.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message);
    }
  }
}

When I am executing rm_reset_bag function from pgAdmin3 function is working OK but from C# code they don't remove records from tables. What is wrong?

mr-owl

PostgreSQL 8.3.1, Visual Studio 2005 Standard SP1, PostgreSQLDirect .NET 3.50.25
mr-owl
 
Posts: 7
Joined: Thu 14 Sep 2006 17:09

Postby Alexey.mdr » Wed 09 Apr 2008 08:55

Did you get any exception?
Try checking all values in debug mode.
It seems like the procedure receives some wrong argument value.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby mr-owl » Wed 09 Apr 2008 11:38

Alexey.mdr wrote:Did you get any exception?
Try checking all values in debug mode.
It seems like the procedure receives some wrong argument value.


When I replace code to:

Code: Select all
if(dataGridView.CurrentRow.Index >= 0 && PostgreSQL.State == ConnectionState.Open)
{
    using (PgSqlCommand sql = PostgreSQL.CreateCommand())
    {
        sql.CommandType = CommandType.Text;
        sql.CommandText = String.Format("SELECT * FROM rm_reset_bag({0});", (int)dataGridView[0, dataGridView.CurrentRow.Index].Value);

        try
        {
            sql.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

everythink working fine. I don't know why my old version doesn't work. I have another store procedure with this same name but with different parameter type (text) and when I run DBMonitor I see that yours driver call wrong function.
Code: Select all
sql.Parameters.Add("id", PgSqlType.Int).Value = (int)dataGridView[0, dataGridView.CurrentRow.Index].Value;

This look that is something wrong with the driver.

mr-owl

P.S. Sorry, I don't speak English very well
mr-owl
 
Posts: 7
Joined: Thu 14 Sep 2006 17:09

Postby Alexey.mdr » Wed 09 Apr 2008 13:53

When working with overloaded stored procedures, use following syntax for executing specific overloaded routine: "StoredProcName:1" or "StoredProcName:5". First example executes first overloaded stored procedure, while second example executes fifth overloaded procedure. This notation affects only describing procedures if ParameterCheck is true.

For more details please see CommandText Property of PgSqlCommand in the documentation.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby mr-owl » Wed 09 Apr 2008 19:40

Hi,

Thank You for explanation, I didn't know about that.

mr-owl
mr-owl
 
Posts: 7
Joined: Thu 14 Sep 2006 17:09

Postby Alexey.mdr » Thu 10 Apr 2008 11:11

This is just the way overloads are handled in PostgreSQLDirect .NET.
Feel free to contact us if you encounter any other issue with the product.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for PostgreSQL