Forum OpenACS Q&A: This is a little off topic, its a nstcl question

Request notifications

Hello All, I am having a bit of a problem with nstcl 0.6, postgres 7.1.3 and my own thick skull. What I am trying to do is the following logic:
  1. update a row
  2. if it fails to update insert it
I have tried a db_transaction { update }on_error{insert} block modeled on example #3 of the acs4 db api whitepaper( around page 10) with no luck. I have also tried a few ugly hacks with no luck getting it to update, it inserts fine the first time and then thing just do not work.
here is the table I am trying to work with:
ubr varchar(40), route cidr, first_seen timestamp , last_seen timestamp
primary key(ubr, route)
I just want to update the last_seen timestamp if the row exists or insert a new row.
When I get into work tomarrow I will post the code, if there is any interest.

Good night

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

Thanks.  After sleeping on it and reading your post I think I will just select for the row I want and if it exists I will do an update otherwise I will do an insert.  The table is around 3000 rows so it should not be too bad.

Thank you

This is even farther off-topic, but I thought you might like to hear it. IBM and Oracle have proposed (and SQL4 has accepted) a new DML operation, the 'MERGE' statement, that combines the typical 'update or insert' functionality into a single call. It has a bit more generality as well:
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")
oracle 9i has it already. DB2 does not yet, from what I can digest of the IBM documentation. Postgres supporting this could be very cool.

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.

That is cool, I ended up using db_0or1row with a select on the primary key and that worked fine.
Marc, keep in mind that with your "select to see if the row exists
yet" method, technically, you need to lock the whole table first.
Otherwise, your select could say "no, there is no such row", then
another user could insert that row, and then when you try to insert
that row it will fail.

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.

Thank you for the pointer about locking the table.  I dont need to do it here though.  The job that does the select is also the only thing that writes to the table.  And I realy need to get some reports done on this thing to keep people happy.  I will try to clean it up later.

the sql 99 merge thing looks useful but I do not know if it would have helped me with this project because I wanted to update only 1 field or insert all of them.  Well if I continue doing more DB work I will continue to learn more stuff and hopefuly have better questions( and on topic ones) in the future.

thanks again

I didn't include db_resultrows in nstcl 0.6 because at least soltcl (Solid) didn't have a way to get at the information. (I'm fairly sure oratcl does, and can't honestly remember about pgtclsh, but I would bet it does too.)

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

First let me thank you for your work and letting me use it for free.  And please post an announcement for the new version, I am looking foward to it.

I am using it for simple stuff mostly.  I am working on another db, ok table, that will do more of the same, it is going to look like this:

mac address, device ip , router index(fk), first seen, last seen

nothing deep, but the kicker is there will be around 1,000,000+ rows to start with and it will be growing.  The main use for it is decision support about how ip space is being used and device reporting, how many of what type of cablemodems we have in an area.
And other stuff as needed.  I am trying to move the reports I generate into openacs and set up a management reporting site down the road.  Just so you know your code is being used in production here.

I do not realy have an oppinion about what else I would like to have from aolserver, I just have not played with it enough.  I can get it configured and almost understand what I am doing and some simple adp pages and that is about it.

for anybody who is interested the combo of postgres + scotty3 + nstcl is very nice for collecting and storing network information in a db and reporting on it.  The network functions and datatypes realy make your life easy for reporting.  I am aware of ns_snmp and it is nice but it depends on snmp++ and that does not compile/is not supported on freebsd.

marc

PS you need any beta testers for 0.7?  I could realy use db_resultrows to make my life easy.