Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » PostgreSQL: serious mistake when call GetInsertedId()
PostgreSQL: serious mistake when call GetInsertedId() [message #43742] Wed, 01 October 2014 00:24 Go to next message
sergeynikitin is currently offline  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 );
}
Re: PostgreSQL: serious mistake when call GetInsertedId() [message #43746 is a reply to message #43742] Wed, 01 October 2014 20:37 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13617
Registered: November 2005
Ultimate Member
Well, it was "by design" (as it is a good common practice to name numeric synthetic primary keys "ID" anyway), but your solution is definitely better.

But I would/will add caching because the column for given table does not change....

Mirek
Re: PostgreSQL: serious mistake when call GetInsertedId() [message #43747 is a reply to message #43746] Wed, 01 October 2014 20:49 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13617
Registered: November 2005
Ultimate Member
Like this:

Value PostgreSQLConnection::GetInsertedId() const
{
	String pk = session.pkache.Get(last_insert_table, Null);
	if(IsNull(pk)) {
		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 );
		pk = sqlc.Execute() && sqlc.Fetch() ? sqlc[0] : "ID";
		session.pkache.Add(last_insert_table, pk);
	}
	Sql sql("select currval('" + last_insert_table + "_" + pk +"_seq')", session);
	if(sql.Execute() && sql.Fetch())
		return sql[0];
	else
		return Null;
}
Re: PostgreSQL: serious mistake when call GetInsertedId() [message #43751 is a reply to message #43747] Thu, 02 October 2014 15:37 Go to previous messageGo to next message
sergeynikitin is currently offline  sergeynikitin
Messages: 748
Registered: January 2008
Location: Moscow, Russia
Contributor

yes, it's better!


SergeyNikitin<U++>( linux, wine )
{
    under( Ubuntu || Debian || Raspbian );
}
Re: PostgreSQL: serious mistake when call GetInsertedId() [message #43807 is a reply to message #43751] Wed, 22 October 2014 11:04 Go to previous message
mirek is currently offline  mirek
Messages: 13617
Registered: November 2005
Ultimate Member
A have changed the code again:

Since 8.1 (released 2005) PGSQL has a nice little function "lastval()", which does exactly the same thing. GetInsertedId now changed to use that.

Mirek
Previous Topic: PostgreSQL: error in PostgreSQLSchema.h
Next Topic: Firebird - Update
Goto Forum:
  


Current Time: Sat May 21 15:00:31 CEST 2022

Total time taken to generate the page: 0.01928 seconds