1 Reply Latest reply on Jun 13, 2013 8:31 AM by "Andrei Costache, Oracle-Oracle"

    How to update record of Primary db through Secondary db?

      I have one secondary db associated with its primary db.

      primary db field:

      uint64 id; // index
      string name;
      string age;

      each record on primary db has unique id and unique name, and here name is working as login name.
      secondary db has name as its key, as I said, it's unique.

      sometimes, user want to update his/her age through name, it's naturally to select secondary db, right?

      but I am so unfortunately to get "DBcursor->put forbidden on secondary indices" error message when I want to put new age back into secondary db.

      is it possible to do that? update primary helped by secondary db?
        • 1. Re: How to update record of Primary db through Secondary db?
          "Andrei Costache, Oracle-Oracle"



          First of all, the error that you see is expected.  You can do reads directly from the secondary database, through get() operations, but you cannot do write operations, put() operations in the secondary database directly.  There is one exception though to this rule;  deletes, hence del() operations are allowed on the secondary database.


          It seems to me there is not much use of that "id" field. Is it something like a social security number, person identification / numeral code or alike? That is, are you really using it to retrieve records or it's just an artificial key so that you can sort on it?

          If so, then why not use "name" as the key directly (in the primary database)?  This would allow you to avoid the need to have a secondary index (secondary database) on "name", and at most have a secondary on "age" in case you want to perform queries involving ranges of ages etc.


          If that "id" field in the primary database is really needed, and you need to use it as the primary key, then you can try one of the following:

          1.  Since the "name" values are unique you could do a pget() call either using the DB handle or through a DBcursor handle in he secondary database.  The retrieved data can be update to reflect the new age, then using this new DBT and the primary key retrieved from the pget() call, do a put() call (either DB->put() or DBcursor->put()) which will update/overwrite the existing (old) record for the user.

          2.  Or maybe even simpler, to avoid two calls to update a user record, try a composite key in the (primary) database.  The composite key will be comprised of the "name" and "id" values.  If needed you can even have two secondary database with the "name" and "id" as indexes.

          In the (primary) database when you need to update a user's age, do a DBcursor->get() call using the DB_SET_RANGE flag -- use a search key DBT that only points the searched name; this allows partial key matches, so the cursor will retrieve the record having in the composite key, the name you want.