2

In Drupal 7 with a developed custom module that insert data into a postgreSQL db, I use the following query :

$query = Database::getConnection('default', 'move')
  ->query("INSERT INTO clients (client_ref,client_lastname,client_firstname) VALUES('E11366235','DUPOND') RETURNING client_id")
  ->execute();

But the returned client_id is always 1.

If I set INSERT INTO clients (client_ref,client_lastname,client_firstname) VALUES('E11366235','DUPOND') RETURNING client_id in pgAdmin 3, the returning id is correct.

In both case data are inserted into database but Drupal doesn't return the good id.

Any explanation ?

1

According to all messages there are two ways to solve the problem :

1) db_insert

Using db_insert() to insert data and create a specific query for postgreSQL database to get the last id after insert :

// Default insert    
(...)->insert("...");

// Call this query to get last id only for pgSQL
(...)->query("SELECT currval('sequence')")->fetchAll();

You juste need to replace sequence by the key provided in your postgreSQL database.

You have to call currval right after insert to stay in the same context.

2) db_query

Rewriting the entire query with specific synthax for postgreSQL database to get the last id :

(...)->query("INSERT INTO {$table} ($fields) VALUES($value) RETURNING $id")->fetchField();

Conclusion

The first solution seems to be the best to avoid rewriting all db_insert() functions.

0

I'm guessing that function you're using is returning the number of rows affected.

You should use ->insert for insert queries, or better yet use drupal's db_insert function: https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_insert/7

As you can see it uses ->insert and that will hopefully return what you need

  • I can't use db_insert because the returning of last insert id doesn't works with PGSQL driver. If I want a custom select query and this is the case I have to use db_query. – Promo Dec 17 '15 at 10:48
  • db_insert() works at the same way for every database engine; if it doesn't, it is a bug in Drupal core. – kiamlaluno Dec 17 '15 at 16:46
0

Looks like DatabaseConnection_pgsql::query() has an option for that. Try:

$sql = "INSERT INTO clients (client_ref,client_lastname,client_firstname) VALUES('E11366235','DUPOND') RETURNING client_id";
$new_id = Database::getConnection('default', 'move')
  ->query($sql, array(), ['return' => Database::RETURN_INSERT_ID]);
  • If I use this query I get this error "Fatal error: Call to a member function execute() on a non-object" and the page stop render but data are inserted into the database. If I remove ->execute() data is still inserted but $query is set to false. I'm lost. – Promo Dec 17 '15 at 12:25
  • Right yeah, my fault - no point changing the return type and then trying to use the original return type. I changed the code, that should work – Clive Dec 17 '15 at 12:28
  • After editing, new_id is set but empty :( – Promo Dec 17 '15 at 12:41
  • Did you try ->query(...)->execute()->fetchField(), without the options set? – Clive Dec 17 '15 at 12:43
  • ->query()->fecthField() works with my query. I posted a complete solution based on your messages. thanks :) – Promo Dec 17 '15 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.