Devart Forums

Posted: **Fri 26 Nov 2004 07:28**

Hi there...

I noticed using the PostgreSQLDirect.NET provider that PgSQLCommand doesn't contain a property "InsertID" which is present in the MySQLCommand equivalent. There is a property called "InsertOid" but that's not the same thing.

So, supposing I create a table "animal" with two columns:

Code: Select all

CREATE TABLE animal (
   id serial NOT NULL,
   description varchar(80)
);

... and then I use a PgSqlCommand called InsertCommand to add a new animal to the table (using the following code):

Code: Select all

/// 
/// this method should insert a new chicken into the database and 
/// return the new chicken's auto-incremented id value.
/// 
private int insertAnotherChicken() {
    // the command to be used for chicken insertion
    PgSqlCommand InsertCommand = new PgSqlCommand();
    ...
    InsertCommand.CommandText = "INSERT INTO animal(description) VALUES ('Chicken')";
    ...
    InsertCommand.ExecuteNonQuery();

    // HELP!!!!
    // how do I return the id of the newly inserted chicken
    return ???;
}

... is there some way to interrogate the InsertCommand object to find out the value of the most recently inserted value for 'id' ?

Thanks in advance for your help...

Cheers,

DaveW.

Posted: **Fri 26 Nov 2004 11:02**

You quite right noticed that MySqlCommand.InsertId in MySQLDirect .NET and
PgSqlCommand.InsertOid in PostgreSQLDirect .NET are different by meaning properties.
The fact is that PostgreSQL server in difference from MySQL by "INSERT" command
always returns oid of the inserted record but not a value of serial(bigserial) field. To
retrieve this value you need create an additional query to the server.

For example,

Code: Select all

  private long insertAnotherChicken() {
   // the command to be used for chicken insertion
   PgSqlCommand InsertCommand = new PgSqlCommand();
   ...
   InsertCommand.CommandText = "INSERT INTO animal(description) VALUES ('Chicken')";
   ...
   InsertCommand.ExecuteNonQuery();

   // get a value of id field of the inserted record

   InsertCommand.CommandText = "SELECT id  FROM animal WHERE oid= :OID";
   InsertCommand.Parameters.Add("OID",  InsertCommand.InsertOid);
   long animalId = (long)InsertCommand.ExecuteScalar();

   return animalId ;
}

Posted: **Fri 26 Nov 2004 13:02**

Thanks Yuri... your suggestion is exactly what I needed.

Just out of interest, I also found another way to get the last insert id...

PostgreSQL implements serial fields in a nifty way so that it uses a sequence (by default called "tablename_fieldname_seq") and then when it wants to insert a new row in the table, it calls a function that returns the sequence value and increments it for next time. You can't call that function yourself, because it increments the sequence EVERY time it's called (which would mean your serial field would increment by 2 for each new record!) but you CAN get the value of the sequence as follows:

Code: Select all


 private long insertAnotherChicken() { 
   // the command to be used for chicken insertion 
   PgSqlCommand InsertCommand = new PgSqlCommand(); 
   ... 
   InsertCommand.CommandText = "INSERT INTO animal(description) VALUES ('Chicken')"; 
   ... 
   InsertCommand.ExecuteNonQuery(); 

   // get a value of id field of the inserted record 
   InsertCommand.CommandText = "SELECT currval('animal_id_seq') AS lastinsertid;"; 
   long animalId = (long)InsertCommand.ExecuteScalar(); 

   return animalId ; 
}

Can you think of any reason this way might be inferior, or are they both fine?

Thanks again,

Dave.

Posted: **Mon 29 Nov 2004 08:57**

Thank you for information. Certainly, getting id field using currval is more
convenient. To answer your question what from the two specified methods is better we made
performance tests. Tests for server 7.3.x and 7.4.x show that getting id with help of InsertOid
works in several times faster.

Code: Select all

     private void TestInsertOid (int count, out long time) {
      
      time = 0;
      ...
      long testTime = Environment.TickCount;
      command.CommandText = "select id from test.autoinc where oid=:OID";
      command.Parameters.Add("OID", command.InsertOid);
      command.ExecuteScalar();
      time += Environment.TickCount - testTime;
      ...
    }

    private void TestCurrval(int count, out long time) {

      time = 0;
      ...
      long testTime = Environment.TickCount;
      command.CommandText = "SELECT currval('test.autoinc_id_seq') AS lastinsertid;";
      command.ExecuteScalar();
      time += Environment.TickCount - testTime;
      ...
    }

Devart Forums

How do I retrieve the last insert id for a 'serial' field?

How do I retrieve the last insert id for a 'serial' field?

Thank you! (and an alternative solution)