GETTING VALUES FROM SEQUENCE OBJECTS

GETTING VALUES FROM SEQUENCE OBJECTS

Postby 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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby 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.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby 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: 165
Joined: Thu 03 Jul 2008 23:55

Postby JORGEMAL » Mon 09 Nov 2009 23:39

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

Thank you.
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55


Return to dotConnect for PostgreSQL