Forum OpenACS Q&A: Deleting a row in db_foreach

Collapse
1: Deleting a row in db_foreach
Posted by James Darwin on 10/05/02 08:18 PM 
Hi...

I want to select a row from a db table, and then once I've done 
whatever I've got to do, I want to delete that row... So, for 
example,

set sql "select name, position from table where name=james"

db_foreach statement_name $sql {
         #Do whatever.
	 #Now delete this row.
}

Is there someway of doing this assuming that there is no primary key 
in the table?

Also, are statement names of any intelligent use?
Collapse
2: Response to Deleting a row in db_foreach (response to 1)
Posted by Jeff Davis on 10/05/02 08:26 PM
postgres has OID and oracle has ROWID which would allow you to do this (I think they call them metacolumns so you could say (assume postgres for the moment): 
select oid, x.* from x;
then: 
delete from x where oid = :oid
As for the query names, they are useful when you are supporting multiple databases since the queries themselves all move into .xql files and meaningful names then become much more helpful.
Collapse
3: Response to Deleting a row in db_foreach (response to 1)
Posted by Ben Adida on 10/05/02 08:27 PM
statement names are used for multi-DB compatibility. You can place your query in a separate file (.xql, -oracle.xql, -postgresql.xql) and use the name to point to it with the query dispatcher.

As for deleting the rows, you can select the rowid field which exists for every PG table, or the oid field which exists for every Oracle table, and use that to key your delete.

In most cases, I would suggest you keep a Tcl list of your IDs and delete them after your db_foreach loop. But it depends on your exact application.

Collapse
4: Response to Deleting a row in db_foreach (response to 1)
Posted by Roberto Mello on 10/07/02 07:27 AM
It's not a safe assumption that OIDs will be present in PostgreSQL tables. As of PG 7.2 you can create tables without OIDs, and many applications are starting to take advantage of that since it cuts down on space usage for tables that already have their own IDs.
Collapse
5: Response to Deleting a row in db_foreach (response to 1)
Posted by Jeff Davis on 10/07/02 10:32 AM
If you are writing the datamodel you can insure that OIDs are present
and for tables where a primary key is not neccessary, adding one
simply to replicate an OID would be a mistake.