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

UpdateOrInsert action...

Expand Messages
  • jazzgeir
    I m using xml-dbms in a project where I want researchers to be able to update and insert data in the database by way of XML-DBMS, preferrably both updating and
    Message 1 of 2 , Sep 7, 2009
    • 0 Attachment
      I'm using xml-dbms in a project where I want researchers to be able to update and insert data in the database by way of XML-DBMS, preferrably both updating and inserting using the same mapping file.

      Typical process: Export a material to xml, edit xml with xml editor, upload to DB.

      Another typical process: Start writing a new XML file and upload to DB.

      It is very convenient to use the very same mapping file to make sure there is a well-defined XML structure

      I've been experimenting a bit with the different actions, and found out, eventually that UpdateOrInsert is quite flexible, but there are a couple of things that look strange to me.

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

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

      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 will not post map, action and xml files here since they tend to get longish. I can post fragments if neede, but I thought I'd start with asking a more general question first...

      ---Asgeir---
    • 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 2 of 2 , Sep 17, 2009
      • 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.