Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] UpdateOrInsert action...

Expand Messages
  • Ronald Bourret
    Hello, Sorry it has taken so long to reply. See my answers below. -- Ron ... UpdateOrInsert means that XML-DBMS will first attempt the update the row in the
    Message 1 of 2 , Sep 17, 2009
    View Source
    • 0 Attachment
      Hello,

      Sorry it has taken so long to reply. See my answers below.

      -- Ron

      jazzgeir wrote:
      > First of all, when updating, if you leave out properties or
      > attributes from the XML, these are set to "NULL" in DB when
      > importing back. I find this unnatural, wouldn't it be better to
      > update just the fields given in XML instead of setting the
      > non-existing columns = NULL? Or will I have to make a separate
      > mapping file for entering new data opposed to editing data?
      > Editing data always include entering new data anyway, so it is
      > not feasable to separate the two...

      UpdateOrInsert means that XML-DBMS will first attempt the update the row
      in the database. If no row is found, then will insert a new row.

      Because XML-DBMS might insert a new row, the document must represent the
      complete current state of the data. (If the document did not have
      complete data, it would not make sense to insert new rows.) When
      data is missing from the document, this corresponds to a NULL in the
      database.

      If the document only represents some of the data in the database, then
      you should use the Update action. With the Update action, the action
      document specifies which elements and attributes contain updated data.
      For example, if you change only the Address child of the Customer
      element, you would use the following action document:

      <Actions>
      <Action>
      <ElementType Name="Customer" />
      <Update>
      <ElementType Name="Address" />
      </Update>
      </Action>
      </Actions>

      In this case, if the Address child is missing, XML-DBMS will know to set
      the value of the corresponding column to NULL. If any other children are
      missing, they will be ignored (and their columns not set to NULL). This
      is because you did not tell XML-DBMS to update those columns.

      Thus, you can use the Update action with a complete document -- that is,
      a document containing all of the data. Or you can use it with a document
      that contains only the updated data (and enough structure to find that
      data in the database).

      Thus, you have two choices:

      1) Use UpdateOrInsert and make sure that the document contains all of
      the data. In this case, missing data corresponds to NULLs in the
      database. (This would happen if the data was originally NULL or a
      researcher deleted the data.) This is the easiest solution.

      2) Use an xmldiff program to determine what data was changed. (You will
      need a copy of the document before it was edited; you will compare this
      to the edited document.) Using this information, determine which fields
      were changed and generate an action document that specifies which fields
      to change in the database. Use this action document with the Update action.

      Note that this will work only if the document contains a single set of
      data. (A single set of data might span multiple tables.) If the document
      contains multiple sets of data, it is likely that different fields were
      changed in different data sets. In this case, you cannot create a single
      action document that will contain the correct fields to update, since
      these are different for different parts of the document.

      Can you explain what kind of data you are using and why the documents do
      not contain complete data?

      > Next, if you're using an auto incremented primary key, and leave out
      > the primary key from the XML data, and import again, the record is
      > still updated if all the columns are equal to the referred row, like
      > I said even if the key is not given in XML. And I get errors like
      > "data changed"...

      I don't know what is happening here. The code tries to update the row
      using the primary key. Because the primary key is not in the XML
      document, it will use a primary key value of NULL. This update will fail
      -- there are no rows with a NULL primary key -- so it will insert a new
      row of data.

      The solution to this problem is to include the primary key in the data.
      Without the primary key, XML-DBMS cannot identify the row to be updated.

      > It would be nice to get an explanation of when
      > records are updated, and when they are inserted when using the
      > UpdateOrInsert action for a given table...

      I agree. However, this is far beyond the current capabilities of
      XML-DBMS. If you want to modify the code, I can give you some ideas of
      what to do. However, this is not an easy change.

      > As soon as I start importing newly entered new records (i.e. records
      > not present in DB) in XML, I get the error "Couldn't retrieve
      > inserted row due to changed values.". I can't specify the primary
      > key, since this is generated by the DB, and the user would not have
      > any idea of what to enter there anyway...

      I'm not sure what is happening here either.

      When you use keys generated by the database, XML-DBMS needs to retrieve
      the generated key value from the database. It inserts this value into
      child rows as a foreign key.

      Some databases have a special JDBC method that returns the generated
      key. To use these methods, you need to implement the DataHandler
      interface -- usually by extending the DataHandlerBase class and
      implementing the insert method. (For example, see
      org.xmlmiddleware.xmldbms.datahandlers.external.PostgresHandler.)

      XML-DBMS provides an implementation of DataHandler called
      GenericHandler, which is what you are using. To retrieve the generated
      key, GenericHandler tries to retrieve the row just inserted by executing
      a SELECT statement. The WHERE clause of the SELECT statement contains
      all of the values that are not in primary key or unique key columns.

      The error you are getting occurs when this SELECT statement fails. That
      is, it can't find the row it just inserted. (The error message does not
      make sense...) I do not know why this is failing. Could you check that
      the row was actually inserted?

      Note that JDBC 3.0 has a method for retrieving database-generated keys.
      If you are using a JDBC 3.0 or later driver, the best solution would be
      to implement the DataHandler interface and use this method. For a
      partial implementation, see
      org.xmlmiddleware.xmldbms.datahandlers.external.JDBC3Handler.
    Your message has been successfully submitted and would be delivered to recipients shortly.