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...

From: Serge Rielau (srielau@ca.ibm.com)
Subject: Re: Efficiency; advanced/future SQL constructs
Newsgroups: comp.databases.theory, comp.databases.ms-sqlserver
View: Complete Thread (20 articles) | Original Format
Date: 2001-08-24 05:11:00 PST

> 1. For each record, do a SELECT to find an existing record, > and
update on success, insert on failure. This is called a MERGE
statement. The paper was submitted jointly by Oracle and
IBM and has been accepted for SQL4.
MERGE, in its full form will be more general than what you describe.
It will
e.g. also support DELETE ("If I didn't see the product in my
inventory, yank it
from the product list")

Anyhow, that's the best-smelling revision in SQL4. Most everything else are extensions to take us back^H^H^H^Hforward into the 1960^H^H90's with network^H^H^H^H^H^H^Hobject model databases. Yippee.

This same issue exists with when using db_resultrows also, of course,
so yeah, this new SQL "merge" operation does sound useful, as it would
presumably do the "insert or update" all in one atomic operation,
without having to either explicitly lock the table first, or catch the
failure of the insert.

Anyway, having in the past month finished porting from AOLserver 2.3/Solid to AOLserver 3/ACS 4.2/Oracle there probably aren't any Solid/nstcl users left in the world! So, as of nstcl 0.7 (~a week or two) those databases which do provide a way to tell how many rows were affected by a DML statement will have db_resultrows.

It's kind of nice to know there are actually people out there using nstcl. Are you using it with/because of OpenFTS or for some other reason? Any other AOLserver APIs that you'd find useful from Tcl/Tk? (Just curious.