Table of Contents
XML is a storage and interchange format for structured data and it is supported by all major database systems. <oXygen/> offers the means of managing the interaction with some of the widely used databases, both relational ones and Native XML Databases. By interaction, one should understand browsing, querying, SQL execution support, content editing, importing from databases, generating XML Schema from database structure.
Relational databases use a relational model and are based on tables linked by a common key. <oXygen/> offers support for the following relational databases: IBM DB2, JDBC-ODBC Bridge, MySQL, Microsoft SQL Server, Oracle 10.2 like browsing the tables of these types of database in the Database Explorer view, executing SQL queries against them, calling stored procedures with input and output parameters.
In the following sections one can find the tools that <oXygen/> offers for working with relational databases and a description on how to configure a relational data source, a connection to a data source and also the views where connections can be browsed and results are displayed.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select DB2 from the driver type combo box.
Add the following IBM DB2 specific files:
In the Download links for database drivers section there are listed the URLs from where to download the drivers necessary for accessing IBM DB2 databases in <oXygen/>.
You can manually manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the most suited Driver class.
Click OK to finish the data source configuration.
<oXygen/>'s default configuration already contains a generic JDBC data source called JDBC-ODBC Bridge.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select Generic JDBC from the driver type combo box.
Click the Add button and find the driver file on your file system.
You can manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the most suited Driver class.
Click OK to finish the data source configuration.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select SQLServer from the driver type combo box.
Add the following Microsoft SQL Server specific files:
In the Download links for database drivers section there are listed the URLs from where to download the drivers necessary for accessing Microsoft SQL Server databases in <oXygen/>.
You can manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the most suited Driver class.
Click OK to finish the data source configuration.
<oXygen/>'s default configuration already contains a generic JDBC data source called MySQL.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select Generic JDBC from the driver type combo box.
Add the following MySQL specific files:
You can manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the most suited Driver class.
Click OK to finish the data source configuration.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select Oracle from the driver type combo box.
Add the following Oracle 10.2 specific files:
In the Download links for database drivers section there are listed the URLs from where to download the drivers necessary for accessing Oracle 10.2 databases in <oXygen/>.
You can manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the most suited Driver class.
Click OK to finish the data source configuration.
This section presents a set of procedures describing how to configure connections that use relational data sources.
Go to Preferences -> Data Sources. In the Connections panel click the New button.
Enter a unique name for this connection and select one of the previously configured DB2 data sources from the Data Source combo box.
Fill-in the Connection Details:
Click OK.
Go to Preferences -> Data Sources. In the Connections panel click the New button.
Enter a unique name for this connection and select one of the previously configured Generic JDBC data sources from the Data Source combo box.
Fill-in the Connection Details:
Click OK.
Go to Preferences -> Data Sources. In the Connections panel click the New button.
Enter a unique name for this connection and select one of the previously configured SQLServer data sources from the Data Source combo box.
Fill-in the Connection Details:
Click OK.
Go to Preferences -> Data Sources. In the Connections panel click the New button.
Enter a unique name for this connection and select one of the previously configured MySQL data sources from the Data Source combo box.
Fill-in the Connection Details:
Click OK.
Go to Preferences -> Data Sources. In the Connections panel click the New button.
Enter a unique name for this connection and select one of the previously configured Oracle data sources from the Data Source combo box.
Fill-in the Connection Details:
Click OK.
Registering,unregistering or updating a schema might involve dropping/creating types. For schema-based XMLType tables or columns in schemas, you need privileges like
To avoid granting these privileges to the schema owner, Oracle recommends that the operations requiring these privileges be performed by a DBA if there are XML schema-based XMLType table or columns in other users' database schemas.
This view presents in a tree-like fashion the database connections configured in Preferences -> Data Sources. You can connect to a database simply by expanding the connection node. The database structure can be expanded up to column level. <oXygen/> supports multiple simultaneous database connections and the connections tree provides an easy way to browse them.
The following objects are displayed by the Database Explorer view:
The following actions are available in the view's toolbar:
The Data Sources / Table FiltersPreferences page, allowing you to decide which table types will be displayed in the Database Explorer view.
button opens theThe Data Sources preferences page where you can configure both data sources and connections.
button opens theBelow you can find a description of the contextual menu actions available on the Database Explorer levels. Please note that you can also open an XML schema component in the editor by double-clicking it. To view the content of a table in the Table Explorer view double-click one of its fields.
- performs a refresh of the selected node's subtree.
Data Sourcespreferences page where you can configure both data sources and connections.
- opens the- performs a refresh of the selected node's subtree.
Table Explorer View.
- opens the selected table in theImport from database chapter). View.
- opens the Export Criteria dialog (a thorough description of this dialog can be found in theFor relational databases that support XML schema repository (XSR) in their database catalogs, the actions available at this level are presented in the following sections.
- performs a refresh of the selected node's subtree.
Register - Opens a dialog for adding a new schema file in the DB XML repository.
To add an XML Schema, enter the schema URI and location on your file system.
Local scope means that the schema will be visible only to the user who registers it.
Global scope means that the schema is public.
- performs a refresh of the selected node's subtree.
- opens a dialog for adding a new schema file in the XML Schema repository.
The XSR Information section of the above figure contains the following fields:
XML schema file - location on your file system.
XSR name - schema name.
Comment - short comment (optional).
Schema location - primary schema name (optional).
Decomposition means that parts of the XML documents are stored into relational tables. Which parts map to which tables and columns is specified into the schema annotations.
Schema dependencies management is done by using the Add and Remove buttons.
Actions available at schema level:
- performs a refresh of the selected node (and it's subtree).
Unregister - removes the selected schema from the XML Schema Repository.
- opens the selected schema in <oXygen/>.
- performs a refresh of the selected node's subtree.
Register - Opens a dialog for adding a new schema file in the DB XML repository.
To register a new schema, enter a collection name and the necessary schema files in the above dialog. XML Schema files management is done by using the Add and Remove buttons.
Actions available at schema level:
- performs a refresh of the selected node (and it's subtree).
Add - adds a new schema to the XML Schema files.
Unregister - removes the selected schema from the XML Schema Repository.
- opens the selected schema in <oXygen/>.
Every table from the Database Explorer can be displayed and edited by pressing the Edit button from the contextual menu or by double-clicking one of its fields. To modify a cell's content, double click it and start typing. When editing is finished, <oXygen/> will try to update the database with the new cell content.
You can sort the content of a table by one of its columns by clicking on its (column) header.
Note the following:
The first column is an index (does not belong to the table structure).
Every column header contains the field name and its data type.
The primary key columns are marked with this symbol:
.Multiple tables are presented in a tabbed manner
For performance issues, you can set the maximum number of cells that will be displayed in the Table Explorer view ( the "Limit the number of cells" field from the Data Sources Preferences page ). If a table having more cells than the value set in <oXygen/>'s options is displayed in the Table Explorer view, a warning dialog will inform you that the table is only partially shown.
A custom validator cannot be applied on files loaded through an <oXygen/> custom protocol plugin developed independently and added to <oXygen/> after installation. This applies also on columns of type XML.
You will be notified if the value you have entered in a cell is not valid ( and thus it cannot be updated).
If the content of the edited cell does not belong to the data type of the column, an Information dialog will appear, notifying you that the value you have inserted cannot be converted to the SQL type of that field.
For example, in the above figure DEPARTMENT_ID contains NUMBER values. If a character or string was inserted, you would get the following message:
If the constraints of the database aren't met (like primary key constraints for example), an Information dialog will appear, notifying you of the reason the database hasn't been updated.
For example, if you'd try to set the primary key DEPARTMENT_ID for the second record in the table to 10 also, you would get the following message:
The usual edit actions (Cut, Copy, Paste, Select All, Undo, Redo) are available in the popup menu of the edited cell
The contextual menu available on every cell has the following actions:
Set NULL - sets the content of the cell to (null). This action is disabled for columns that cannot be null.
- inserts an empty row in the table.
- makes a copy of the selected row and adds it in the Table Explorer view. You should note that the new row will not be inserted in the database table until all conflicts are solved.
- commits the selected row.
- deletes the selected row.
- copies the content of the cell.
- performs paste in the selected cell
Some of the above actions are also available on the Table Explorer toolbar:
Import from database chapter). View.
- opens the Export Criteria dialog (a thorough description of this dialog can be found in the- performs a refresh of the selected node's subtree.
- inserts an empty row in the table.
- makes a copy of the selected row and adds it in the Table Explorer view. You should note that the new row will not be inserted in the database table until all conflicts are solved.
- commits the selected row.
- deletes the selected row.
<oXygen/>'s support for writing SQL statements includes syntax highlight, folding and drag&drop(DND) from the Database Explorer View. It also includes transformation scenarios for executing the statements and the results are displayed in the Table Explorer View.
Configure a database connection as it was shown previously in this chapter and browse to the table you will use in your statement and drag it into the editor (where a sql file is open).
Next, select the type of statement from the popup menu that appears in the sql editor. Depending on your choice, one of the following statements will be inserted into the document:
SELECT `field1`,`field2`, .... FROM `catalog`. `table` (for this example: SELECT `DEPT`,`DEPTNAME`,`LOCATION` FROM `test`.`department` )
UPDATE `catalog`. `table` SET `field1`=, `field2`=,.... (for this example: UPDATE `test`.`department` SET `DEPT`=, `DEPTNAME`=, `LOCATION`=)
INSERT INTO`catalog`. `table` ( `field1`,`field2`, ....) VALUES (, , ) (for this example: INSERT INTO `test`.`department` (`DEPT`,`DEPTNAME`,`LOCATION`) VALUES (, , ))
DELETE FROM `catalog`. `table` (for this example: DELETE FROM `test`.`department`)
DND is available both on the table and on its fields. Click on the column and drag it into the editor. The same popup menu as above will appear. Depending on your choice, one of the following statements will be inserted into the document:
SELECT `field` FROM `catalog`. `table` (for this example: SELECT `DEPT` FROM `test`.`department` )
UPDATE `catalog`. `table` SET `field`= (for this example: UPDATE `test`.`department` SET `DEPT`=)
INSERT INTO`catalog`. `table` ( `field1) VALUES () (for this example: INSERT INTO `test`.`department` (`DEPT`) VALUES ())
DELETE FROM `catalog`. `table` (for this example: DELETE FROM `test`.`department` WHERE `DEPT`=)
Currently, SQL validation support is offered for IBM DB2. Please note that if you choose a connection that doesn't support SQL validation you will receive a warning when trying to validate. The SQL document will be validated using the connection from the associated transformation scenario.
First configure a transformation scenario. Click on the button from the Transformation toolbar. The dialog that appears contains the list of existing scenarios that apply to SQL documents. To configure a new scenario, click the New button.
Enter a name for the scenario and choose one of the available database connections. To
configure a new connection click
on .
Placeholders(?) for parameters are supported by <oXygen/>. For the following example SELECT * FROM `test`.`department` where DEPT = ? or DEPTNAME = ? two parameters can be configured for the transformation scenario. To do this, in the previous dialog click the Parameters button and add a new parameter for each placeholder. When the sql statement will be executed, the first placeholder will be replaced with the value set for the first parameter in the scenario, the second placeholder will be replaced by the second parameter value and so on.
The result of a SQL transformation will be displayed in the Table Explorer view.
This feature is explained in detail in the Import from database section of Importing Data chapter.
This feature is explained in detail in the Convert table structure to XML section of Importing Data chapter.