- 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...
Sorry it has taken so long to reply. See my answers below.
> First of all, when updating, if you leave out properties orUpdateOrInsert means that XML-DBMS will first attempt the update the row
> 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...
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
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:
<ElementType Name="Customer" />
<ElementType Name="Address" />
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 outI don't know what is happening here. The code tries to update the row
> 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"...
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 whenI agree. However, this is far beyond the current capabilities of
> records are updated, and when they are inserted when using the
> UpdateOrInsert action for a given table...
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. recordsI'm not sure what is happening here either.
> 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...
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
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