U++ forum: Welcome to the forum

PostgreSQL: serious mistake when call GetInsertedId() [message #43742]

Wed, 01 October 2014 00:24

sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia

Contributor

In a function body of GetInstertedId() it is supposed that the field name of primary key will always have the name id.

Value PostgreSQLConnection::GetInsertedId() const
{
	Sql sql("select currval('" + last_insert_table + "_id_seq')", session);
	if(sql.Execute() && sql.Fetch())
		return sql[0];
	else
		return Null;
}

(uppsrc/PostgreSQL/PostgreSQL.cpp)

But in a case when it not so, becomes difficult an audible error.

SQL expression for request of a column of primary key here:

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute 
WHERE 
  pg_class.oid = 'TABLENAME'::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary

Respectively I suggest to modify function. For example so:

Value PostgreSQLConnection::GetInsertedId() const
{
	String sqlc_expr; 
	sqlc_expr <<
	"SELECT " <<
	  "pg_attribute.attname " <<
	"FROM pg_index, pg_class, pg_attribute " <<
	"WHERE " <<
	  "pg_class.oid = '" << last_insert_table << "'::regclass AND " <<
	  "indrelid = pg_class.oid AND " <<
	  "pg_attribute.attrelid = pg_class.oid AND " <<
	  "pg_attribute.attnum = any(pg_index.indkey) " <<
	  "AND indisprimary" ;
	Sql sqlc( sqlc_expr );
	if( !(sqlc.Execute() && sqlc.Fetch()) )
		return Null;
	Sql sql("select currval('" + last_insert_table + "_"+AsString(sqlc[0])+"_seq')", session);
	if(sql.Execute() && sql.Fetch())
		return sql[0];
	else
		return Null;
}

SergeyNikitin<U++>( linux, wine )
{
under( Ubuntu || Debian || Raspbian );
}

Report message to a moderator