Class com.ibm.etools.xmltosql.XMLToSQL

java.lang.Object
        com.ibm.etools.xmltosql.XMLToSQL

public class XMLToSQL extends Object

XMLToSQL is used to insert, update, or delete rows in a database table using an XML document. The mapping between the XML structure to the table structure is based on a set of simple mapping rules. The following XML fragment illustrates the mapping rules:

   <rootElement>
     <EMPLOYEE>
       <FIRSTNAME>CHRISTINE</FIRSTNAME>
       <LASTNAME>HAAS</LASTNAME>
     </EMPLOYEE>
     <EMPLOYEE>
       <FIRSTNAME>MICHAEL</FIRSTNAME>
       <LASTNAME>THOMPSON</LASTNAME>
     </EMPLOYEE> 
     ...
   </rootElement>

A good way to create a valid XML document is to first use the SQLToXML library to generate one, then modify it to supply new values for update. You can also use the XML to SQL wizard to unit test the XML document for validity before writing your code that uses the XMLToSQL library.

Table 1. Constructor Index
Constructor Description
XMLToSQL(SQLProperties) This is the only constructor.

Table 2. Method Index
Method Description
void addToKeyColumns(String) Adds to the list of the key column names that are used in building the where-clause for UPDATE or DELETE.
void addToUpdateColumns(String) Adds to the list of the column names that are updated or inserted.
void execute(Document, Boolean) Updates a table from a DOM Document.
void execute(InputStream) Updates a table from an XML input stream.
void execute(InputStream, Boolean) Updates a table from an XML input stream.
void execute(String) Updates a table from an XML file.
void execute(String, Boolean) Updates a table from an XML file.
void finalize() Closes the connection if this was created internally.
Vector getFailedStatements() Returns a collection of SQL statements that have been failed during execution.
String getTableName(InputStream) Returns the database table name to be updated.
String getTableName(String) Returns the database table name to be updated.
void setConnection(Connection) Provides a JDBC connection to be used for updating the tables.
void setTrace(Boolean) Sets the trace flag.
void setTrace(Boolean, PrintWriter) Sets the trace flag.
Boolean updateMultipleRows(String, Vector) Determines if multiple rows would be updated once any of the execute() method was called.

 

Constructors

XMLToSQL

public XMLToSQL(SQLProperties sqlProperties)

This is the only constructor. Any information necessary for updating a database table is provided through SQLProperties.

 

Methods

addToKeyColumns

public void addToKeyColumns(String columnName) throws Exception

Adds to the list of the key column names that are used in building the where-clause for UPDATE or DELETE. If the table has primary keys, those primary key columns are used instead. This method is useful if a table does not have any primary key columns defined, but requires caution in this case since multiple rows can be updated unintentionally. A column cannot be both a key column and an update column. This method does not have any effect for INSERT.

addToUpdateColumns

public void addToUpdateColumns(String columnName) throws Exception

Adds to the list of the column names that are updated or inserted. If this method is not called before any of the execute() methods, all of the columns excluding any key columns are added to the list by XMLToSQL for UPDATE or INSERT. A column cannot be both a key column and an update column. This method does not have any effect for DELETE.

execute

public void execute(Document doc,
                    Boolean continueOnSQLError)
           throws SQLException, ClassNotFoundException, IOException,
                  SAXException, ParserConfigurationException

Updates a table from a DOM Document. Different transaction mode is applied depending on the Boolean value of continueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLError is false, rollback is done on all of the rows processed should an error occurs.

execute

public void execute(InputStream inputStream)
           throws SQLException, ClassNotFoundException, IOException,
                  SAXException, ParserConfigurationException

Updates a table from an XML input stream. If an error occurs while updating a row, rollback is performed for all of the rows processed so far.

execute

public void execute(InputStream inputStream,
                    Boolean continueOnSQLError)
           throws SQLException, ClassNotFoundException, IOException,
                  SAXException, ParserConfigurationException

Updates a table from an XML input stream. Different transaction mode is applied depending on the Boolean value of continueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLError is false, rollback is done on all of the rows processed should an error occurs.

execute

public void execute(String filename)
           throws SQLException, FileNotFoundException,
                  ClassNotFoundException, IOException, SAXException,
                  ParserConfigurationException

Updates a table from an XML file. If an error occurs while updating a row, rollback is performed for all the rows processed so far.

execute

public void execute(String filename,
                    Boolean continueOnSQLError)
           throws SQLException, FileNotFoundException,
                  ClassNotFoundException, IOException, SAXException,
                  ParserConfigurationException

Updates a table from an XML file. Different transaction mode is applied depending on the Boolean value of continueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLError is false, rollback is done on all of the rows processed should an error occurs.

finalize 

public void finalize()

Closes the connection if this was created internally. If the connection was supplied externally (by using setConnection()), nothing is done. This method is called by the JVM when XMLToSQL is garbage-collected.

getFailedStatements

public Vector getFailedStatements()

Returns a collection of SQL statements that have been failed during execution. This method is only meaningful when the continueOnError flag is true.

getTableName

public String getTableName(InputStream inputStream)
             throws IOException, SAXException,
                    ParserConfigurationException

Returns the database table name to be updated. This method is provided to determine the table name from the XML input stream before executing a SQL statement. This is for the convenience of any user interface components.

getTableName

public String getTableName(String filename)
             throws IOException, SAXException,
                    ParserConfigurationException

Returns the database table name to be updated. This method is provided to determine the table name from the XML input stream before executing a SQL statement. This is for the convenience of any user interface components.

setConnection

public void setConnection(Connection jdbcConnection)

Provides a JDBC connection to be used for updating the tables. This method becomes useful when an application program wants to use its own connection management mechanism such as connection pooling. If this method is not called before any of the execute() methods, XMLToSQL creates its own connection using the values from SQLProperties.

setTrace

public void setTrace(Boolean flag)

Sets the trace flag. The SQL statements being executed is printed out to the console when flag is true. By default, trace is turned off.

setTrace

public void setTrace(Boolean flag,
                     PrintWriter writer)

Sets the trace flag. The SQL statements being executed is printed out to the writer when flag is true. By default, trace is set to false.

updateMultipleRows

public Boolean updateMultipleRows(String uri,
                                  Vector keys) throws Exception

Determines if multiple rows would be updated/deleted once any of the execute() method was called. There is a chance of multiple rows being changed in UPDATE/DELETE mode when the operation is performed against a table that does not have any primary keys. This method returns false if a single row is going to be affected from its corresponding source XML element, and true otherwise.

 

Related tasks

Generating XML from SQL using a wizard
Updating tables using the XMLToSQL class

Related reference
Class com.ibm.etools.xmltosql.SQLProperties
Class com.ibm.etools.sqltoxml.SQLToXML