GETTING VALUES FROM SEQUENCE OBJECTS

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

GETTING VALUES FROM SEQUENCE OBJECTS

Post by JORGEMAL » Mon 02 Nov 2009 18:41

Can I get values from sequence objects?

I have a table with a serial data type field and I need to know which value was assigned to it just after inserting a new record.

I know there are PostgreSQL functions that work with sequence objects (currval, lastval, etc) but I have not been able to access them using dotConnect.

I am using dotConnect for PostgreSQL v4.0

Please advice.

Respectfully,
Jorge Maldonado

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 03 Nov 2009 09:51

Assuming that your table is like:

Code: Select all

CREATE TABLE withsequence
(
  id serial NOT NULL,
  "value" text
);
Please create your commands using the RETURNING clause:

Code: Select all

pgSqlDataTable1.InsertCommand.CommandText="INSERT INTO withsequence (value) VALUES (:value) RETURNING id";
This can be easily done with our PgSqlDataTable Editor (the Refreshing option in Command Generator).

As an alternative, you can execute the "select currval('withsequence_id_seq');" statement for the current session.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Fri 06 Nov 2009 18:55

Please help a little bit more.
If I have the following code where pss_clave is a serial type field:

String strQuery = "";
strQuery += "INSERT INTO cat_paises ";
strQuery += "(pss_clave, pss_nombre) ";
strQuery += "VALUES (DEFAULT, ";
strQuery += "'" + this.Nombre + "') ";
strQuery += "RETURNING pss_clave";

String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(strQuery, pgConn);
pgConn.Open();
pgCmd.ExecuteNonQuery();

How do I have access to the RETURNING value (pss_clave) so I can use it in fields of other tables?

Regards.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Mon 09 Nov 2009 23:39

I have solved my issue with the RETURNING clause in the INSERT command.

Thank you.

Post Reply