Marc, "on_error" is not going to do what you want. If 0 rows are
updated, that's perfectly fine and not an error. In the ACS 4.x
db_api, what you'd want it
db_resultrows, which returns
the number of rows affected by the last DML command. So,
if {
[db_resultrows] == 0 } then you'd do an insert - all within a
single transaction of course, like you already said.
Unfortunately, at least as of v. 0.6, nstcl does not have
db_resultrows - I think because nstcl's underlying
database drivers (Oratcl, etc.) do not support that functionality.
The AOLserver ns_db API doesn't have the "resultrows"
feature either - in ACS, it's implemented with with a feature specific
to each AOLserver database driver, and a given driver may or may not
support it. For example, on my (modified) ACS 4.2 system:
ad_proc db_resultrows {{ -dbn "" }} {
Returns the number of rows affected by the last DML command.
@param dbn The database name to use. If empty_string, uses the default database.
} {
upvar "#0" [db_state_array_name_is -dbn $dbn] db_state
set driver_is [db_driver_is [db_driver_type_is -dbn $dbn]]
switch $driver_is {
oracle {
return [ns_ora resultrows $db_state(last_used)]
}
postgres {
return [ns_pg ntuples $db_state(last_used)]
}
nsodbc {
error "db_resultrows is not supported for this database."
}
default {
error "Unknown database driver. db_resultrows is not supported for this database."
}
}
}
There's probably some other way to do this, but off hand, I can't
think of one. I would dig into the code and/or examples of whatever
your underlying Tcl database driver is. I'm not familiar with
pgtclsh, so maybe it supports resultrows functionality
even though Michael didn't wrap it in his nstcl package.
Well, you could always start the transaction, try the insert
first, and then do the update in the on_error
block of db_transaction, when the insert dies due to referential
integrity constraints. Make very sure you have all the correct
referential integrity constraints first, of course...
