Devart Forums

Posted: **Tue 26 Feb 2008 01:02**

Hi,
I am having trouble calling a postgres stored procedure (via a table adapter) using input and output parameters. Here is my code:

Code: Select all

 PgSqlConnectionStringBuilder connBuilder = new PgSqlConnectionStringBuilder();
            connBuilder.Database = "sharpy";
            connBuilder.Host = "bjdev01";
            connBuilder.Password = "password";
            connBuilder.Port = 5432;
            connBuilder.Protocol = ProtocolVersion.Ver30;
            connBuilder.Schema = "sharpy";
            connBuilder.UserId = "sharpy";
            PgSqlConnection conn = new PgSqlConnection(connBuilder.ConnectionString);
            PgSqlDataAdapter adp = new PgSqlDataAdapter();
            adp.InsertCommand = new PgSqlCommand();
            adp.InsertCommand.CommandText = "usp_cds_i";
            adp.InsertCommand.CommandType = System.Data.CommandType.StoredProcedure;
            adp.InsertCommand.Connection = conn;
            adp.InsertCommand.Parameters.Add(new PgSqlParameter(":name_cds", PgSqlType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "name_cds", System.Data.DataRowVersion.Current, null));
            adp.InsertCommand.Parameters.Add(new PgSqlParameter(":id_cds", PgSqlType.BigInt.ToString(), ParameterDirection.Output, false, "id_cds", DataRowVersion.Current, null));
            adp.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.OutputParameters;
            adp.UpdateBatchSize = 1000; // This is important, I do not want to have a batch size of 1 because I am working with thousands of rows

            PgSqlDataTable dt = new PgSqlDataTable("select * from sharpy.cds", conn);
            dt.Columns.Add("id_cds", typeof(long));
            dt.Columns.Add("name_cds", typeof(string));

            System.Data.DataRow row = dt.NewRow();
            row["name_cds"] = "test_" + new Random().Next();
            dt.Rows.Add(row);

            // Do the update with the data adapter. This throws exception.
            // We want it to populate the id_cds value into the data row.
            conn.Open();
            try
            {
                adp.Update(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

I get this exception:

Code: Select all

 [System.ArgumentOutOfRangeException]: {"Parameter name is missing.\r\nParameter name: id_cds"}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    InnerException: null
    Message: "Parameter name is missing.\r\nParameter name: id_cds"
    Source: "System.Data"
    StackTrace: "   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)\r\n   at DBUtilities.OutputParametersProblem.TestOutputParameters() in C:\dev\trunk\gui\common\DBUtilities\DBUtilities_NUnit\OutputParametersProblem.cs:line 46"
    TargetSite: {Int32 UpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs, BatchCommandInfo[], Int32)}

My database table create script:

Code: Select all

CREATE TABLE sharpy.cds
(
  id_cds bigint NOT NULL DEFAULT nextval('sharpy.seq_cds'::regclass),
  name_cds character varying(50) NOT NULL,
  CONSTRAINT pk_id_cds PRIMARY KEY (id_cds)
) 
WITHOUT OIDS;

My stored procedure create script:

Code: Select all

CREATE OR REPLACE FUNCTION sharpy.usp_cds_i(IN character varying, OUT bigint) AS
$BODY$

INSERT INTO sharpy.cds (name_cds) VALUES ($1); 
SELECT currval('sharpy.seq_cds') as id_cds;

$BODY$
  LANGUAGE 'sql' VOLATILE;

Note that when I comment out the following line, I don't get an error (but I also fail to get the id_cds value populated into my datatable):

Code: Select all

adp.InsertCommand.Parameters.Add(new PgSqlParameter(":id_cds", PgSqlType.BigInt.ToString(), ParameterDirection.Output, false, "id_cds", DataRowVersion.Current, null));

Can you please tell me how I can fix this issue so that the output parameter id_cds is populated into the id_cds row of my dataset?

Thanks
Phoebe

Posted: **Tue 26 Feb 2008 02:02**

Here is the code to create the sequence 'seq_cds':

Code: Select all

CREATE SEQUENCE sharpy.seq_cds
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

Posted: **Tue 26 Feb 2008 13:07**

Please try modifying the code like this

Code: Select all

adp.InsertCommand.Parameters.Add(new PgSqlParameter("id_cds", PgSqlType.BigInt.ToString(), ParameterDirection.Output, false, "id_cds", DataRowVersion.Current, null));

:id_cds -> id_cds

Posted: **Tue 26 Feb 2008 21:50**

Hi, Unfortunately that didn't work. I still get the exact same exception. Were you able to run the code successfully? Thanks again

Posted: **Wed 27 Feb 2008 13:16**

This function should solve the problem

Code: Select all

CREATE OR REPLACE FUNCTION sharpy.usp_cds_i(IN name_cds character varying, OUT id_cds bigint) AS
$BODY$

INSERT INTO sharpy.cds (name_cds) VALUES (name_cds);
SELECT currval('sharpy.seq_cds') into id_cds;

$BODY$
  LANGUAGE 'sql' VOLATILE;

Probably you can simplify your program if you use PgSqlCommandBuilder class.
In this case you'll need to specify only SELECT command and RefreshMode property.
INSERT and UPDATE commands will be generated automatically.

Posted: **Wed 27 Feb 2008 21:51**

Great, this seems to have worked. Thanks for that. I had to make a minor change to your SQL, though.

First I was getting an error saying that "column name_cds does not exist" so I changed name_cds --> $1. Then the function worked first time and failed second time because "relation id_cds already exists" so I added the TEMP keyword so that postgres would delete your table that was being created when you do 'SELECT INTO'.

I changed

Code: Select all

INSERT INTO sharpy.cds (name_cds) VALUES (name_cds);
SELECT currval('sharpy.seq_cds') into id_cds;

To

Code: Select all

INSERT INTO sharpy.cds (name_cds) VALUES ($1);
SELECT currval('sharpy.seq_cds') into TEMP id_cds;

I'm a little concerned that I'm creating temp tables as this may not be the most efficient way to do things. I will also consider your advice about using the PgSqlCommandBuilder class. Cheers.

Posted: **Thu 28 Feb 2008 12:27**

I guess, TEMP tables may not be a suitable solution in this case.
Because they only exist a limited period of time.
Could you please describe what result would you like to achieve?
What program logic are you developing?

Posted: **Thu 28 Feb 2008 21:59**

I am trying to develop a fairly generic data access layer using data sets. All of my database tables have auto-incrementing IDs that are not created by the application, but created from database sequences.

Hence, when I do an insert, I need to be able to populate the ID number back into the data set row that has been inserted.

I did some further work using the stored proedure that you specified with my minor modifications and this was fine for one record, but as soon as my dataset contains more than one row, I get this exception (I expect because I am trying to create the temp table twice):

Code: Select all

ex
{"relation \"id_cds\" already exists"}
    [CoreLab.PostgreSql.PgSqlException]: {"relation \"id_cds\" already exists"}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    InnerException: null
    Message: "relation \"id_cds\" already exists"
    Source: "System.Data"
    StackTrace: "   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)\r\n   at Tibra.Common.Test.DBUtilities.OutputParametersProblem.TestOutputParameters() in C:\dev\trunk\gui\common\DBUtilities\DBUtilities_NUnit\OutputParametersProblem.cs:line 65"
    TargetSite: {Int32 UpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs, BatchCommandInfo[], Int32)}

I also tried using the command builder to do this but it doesn't seem to support populating fields back into a datarow into the dataset. Here is some sample code for this:

Code: Select all

PgSqlConnectionStringBuilder connBuilder = new PgSqlConnectionStringBuilder();
            connBuilder.Database = "sharpy";
            connBuilder.Host = "bjdev01";
            connBuilder.Password = "password";
            connBuilder.Port = 5432;
            connBuilder.Protocol = ProtocolVersion.Ver30;
            connBuilder.Schema = "sharpy";
            connBuilder.UserId = "sharpy";
            PgSqlConnection conn = new PgSqlConnection(connBuilder.ConnectionString);
            PgSqlDataAdapter adp = new PgSqlDataAdapter("select * from sharpy.cds", conn);
            adp.UpdateBatchSize = 1000; // This is important, I do not want to have a batch size of 1 because I am working with thousands of rows

            PgSqlDataTable dt = new PgSqlDataTable();
            dt.Columns.Add("id_cds", typeof(long));
            dt.Columns.Add("name_cds", typeof(string));

            dt.Columns["id_cds"].AutoIncrement = true;
            dt.Columns["id_cds"].AutoIncrementSeed = -1;
            dt.Columns["id_cds"].AutoIncrementStep = -1;
            
            Random rand = new Random();

            System.Data.DataRow row = dt.NewRow();
            row["name_cds"] = "test_" + rand.Next();
            dt.Rows.Add(row);

            //System.Data.DataRow row2 = dt.NewRow();
            //row2["name_cds"] = "test_" + rand.Next();
            //dt.Rows.Add(row2);
            
            PgSqlCommandBuilder cmdBuilder = new PgSqlCommandBuilder(adp);
            cmdBuilder.Quoted = true;

            // Do the update with the data adapter. This throws exception.
            // We want it to populate the id_cds value into the data row.
            conn.Open();

            cmdBuilder.RefreshMode = CoreLab.Common.RefreshRowMode.AfterInsert;
            cmdBuilder.KeyFields = "id_cds";
            cmdBuilder.RefreshingFields = "id_cds, name_cds";
            cmdBuilder.SetAllValues = false;
            cmdBuilder.UpdatingFields = "name_cds";

            try
            {
                adp.Update(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            Assert.LessOrEqual(1, Convert.ToInt32(dt.Rows[0]["id_cds"]));

Finally, I tried using the command builder to attempt to update more than one row (by uncommenting the row2 variable in the example above), and got this exception:

Code: Select all

{"When batching, the command's UpdatedRowSource property value of UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid."}
    [System.InvalidOperationException]: {"When batching, the command's UpdatedRowSource property value of UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid."}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    InnerException: null
    Message: "When batching, the command's UpdatedRowSource property value of UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid."
    Source: "System.Data"
    StackTrace: "   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)\r\n   at Tibra.Common.Test.DBUtilities.OutputParametersProblem.TestOutputParametersCommandBuilder() in C:\dev\trunk\gui\common\DBUtilities\DBUtilities_NUnit\OutputParametersProblem.cs:line 131"
    TargetSite: {Int32 UpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs, BatchCommandInfo[], Int32)}

I think that what I'm trying to achieve is simple, but I can't come up with a solution that will work.

Note that I have also tried using version 2 of the protocol and appending a select statement to the end of each of my insert statements to retrieve the inserted row into the adapter using UpdatedRowSource = FirstRowReturned. The problem here is that using UpdatedRowSource = FirstRowReturned isn't supported if I want to use an UpdateBatchSize > 1.

I would be happy to go with the commandbuilder approach if I can get it to work.

Thanks again

Posted: **Fri 29 Feb 2008 14:58**

We have reproduced the error and now we are investigating the problem.
I'll notify you on the result as soon as possible.

Posted: **Mon 03 Mar 2008 10:38**

If you would like to use MySqlCommandBuilder refresh mode, like

Code: Select all

cmdBuilder.RefreshMode = CoreLab.Common.RefreshRowMode.AfterInsert;

then the UpdateBatchSize property of MySqlDataAdapter will be automatically hard-coded to “1”.
If it is crucially important for you to use larger update batch size blocks, please follow your previous program logic.

Posted: **Tue 04 Mar 2008 21:59**

Hi, On your advice I tried going back to my previous program logic. Unfortunately there seems to be a bug when attempting to update more than one row.

Here is the c# code I am running:

Code: Select all

PgSqlConnectionStringBuilder connBuilder = new PgSqlConnectionStringBuilder();
            connBuilder.Database = "sharpy";
            connBuilder.Host = "bjdev01";
            connBuilder.Password = "password";
            connBuilder.Port = 5432;
            connBuilder.Protocol = ProtocolVersion.Ver30;
            connBuilder.Schema = "sharpy";
            connBuilder.UserId = "sharpy";
            PgSqlConnection conn = new PgSqlConnection(connBuilder.ConnectionString);
            PgSqlDataAdapter adp = new PgSqlDataAdapter("select * from sharpy.cds", conn);
            adp.UpdateBatchSize = 1000; // This is important, I do not want to have a batch size of 1 because I am working with thousands of rows

            PgSqlDataTable dt = new PgSqlDataTable();
            dt.Columns.Add("id_cds", typeof(long));
            dt.Columns.Add("name_cds", typeof(string));

            dt.Columns["id_cds"].AutoIncrement = true;
            dt.Columns["id_cds"].AutoIncrementSeed = -1;
            dt.Columns["id_cds"].AutoIncrementStep = -1;
            
            Random rand = new Random();

            System.Data.DataRow row = dt.NewRow();
            row["name_cds"] = "test_" + rand.Next();
            dt.Rows.Add(row);

            System.Data.DataRow row2 = dt.NewRow();
            row2["name_cds"] = "test_" + rand.Next();
            dt.Rows.Add(row2);
            
            PgSqlCommandBuilder cmdBuilder = new PgSqlCommandBuilder(adp);
            cmdBuilder.Quoted = true;

            // Do the update with the data adapter. This throws exception.
            // We want it to populate te id_cds value into the data row.
            conn.Open();

            cmdBuilder.RefreshMode = CoreLab.Common.RefreshRowMode.AfterInsert;
            cmdBuilder.KeyFields = "id_cds";
            cmdBuilder.RefreshingFields = "id_cds, name_cds";
            cmdBuilder.SetAllValues = false;
            cmdBuilder.UpdatingFields = "name_cds";

            try
            {
                adp.Update(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            Assert.LessOrEqual(1, Convert.ToInt32(dt.Rows[0]["id_cds"]));

Here is the revised procedure and an additional function for getting sequence values volatilely:

Code: Select all

CREATE OR REPLACE FUNCTION sharpy.volatile_nextval(text)
  RETURNS bigint AS
' SELECT CAST(nextval($1) as bigint); '
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION sharpy.volatile_nextval(text) OWNER TO sharpy;

CREATE OR REPLACE FUNCTION sharpy.usp_cds_i(IN name_cds character varying, OUT id_cds bigint) AS
$BODY$
INSERT INTO sharpy.cds (id_cds,name_cds) VALUES (sharpy.volatile_nextval('sharpy.seq_cds'), $1);
SELECT currval('sharpy.seq_cds');
$BODY$
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION sharpy.usp_cds_i(IN name_cds character varying, OUT id_cds bigint) OWNER TO sharpy;

Here is the exception I get:

Code: Select all

{"Object reference not set to an instance of an object."}
    [System.NullReferenceException]: {"Object reference not set to an instance of an object."}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    InnerException: null
    Message: "Object reference not set to an instance of an object."
    Source: "CoreLab.PostgreSql"
    StackTrace: "   at CoreLab.PostgreSql.PgSqlDataAdapter.ExecuteBatch()\r\n   at System.Data.Common.DbDataAdapter.UpdateBatchExecute(BatchCommandInfo[] batchCommands, Int32 commandCount, RowUpdatedEventArgs rowUpdatedEvent)\r\n   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)\r\n   at Common.Test.DBUtilities.OutputParametersProblem.TestOutputParameters() in C:\dev\trunk\gui\common\DBUtilities\DBUtilities_NUnit\OutputParametersProblem.cs:line 60"
    TargetSite: {Int32 ExecuteBatch()}

I thought that the exception could possibly be because I didn't have a data table mapping so I added the code that you can see commented out above. I get the same exception whether or not this code is commented out.

If I comment out these rows:

Code: Select all

           System.Data.DataRow row2 = dt.NewRow();
            row2["name_cds"] = "test_" + rand.Next();
            dt.Rows.Add(row2);

... I don't get the exception anymore. I assume this is because the batch size is only 1.

Please advise ASAP if there is a way to get this to work. Unfortunately my time to trial this product is running out and unless I can get this working, we won't be purchasing licenses for our company. Thanks in advance.

Posted: **Wed 05 Mar 2008 09:56**

Following standard ADO.NET architecture we could not support refreshing and batching simultaneously.
This means that without additional tricks you cannot use batch size (greater than 1) with refresh mode turned on on insert/update command execution.

Posted: **Wed 05 Mar 2008 21:41**

Thank you

Posted: **Thu 06 Mar 2008 06:21**

You are welcome.
Sorry, it took so much time.
If you would like to endure the trial period please
notify me by email (alexeyman*crlab.com).

Devart Forums

How to use output parameters with stored procedures and data adapters

How to use output parameters with stored procedures and data adapters