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 11g like browsing the tables of these types of database in the Data Source 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.
Press the Add button to add the following IBM DB2 specific files:
db2jcc.jar
db2jcc_license_cisuz.jar
db2jcc_license_cu.jar
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.
Press the Add button to add the following Microsoft SQL Server specific files:
sqljdbc.jar
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.
Press the Add button to add the following MySQL specific files:
mysql-com.jar
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.
Press the Add button to add the following Oracle 11g specific files:
ojdbc5.jar
In the Download links for database drivers section there are listed the URLs from where to download the drivers necessary for accessing Oracle 11g 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.
Go to Preferences -> Data Sources. In the Data Sources panel click the New button.
Enter a unique name for this data source and select Postgres
from the driver type combo box.
Press the Add button to add the following Postgres
8.3
specific files:
postgresql-8.3-603.jdbc3.jar
In the Download links for database drivers section there are listed the URLs from where to download the drivers necessary for accessing PostgreSQL databases in <oXygen/>.
You can manage the Driver Files using Add, Remove, Detect and Stop(detection) buttons.
Select the org.postgresql.Driver class in the Driver class combo box.
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:
URL to the installed IBM DB2 engine.
User name to access the IBM DB2 database engine.
Password to access the IBM DB2 engine.
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:
URL to the configured ODBC source.
User name to access the configured ODBC source.
Password to access the configured ODBC source.
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:
URL to the installed SQLServer engine.
User name to access the SQLServer database engine.
Password to access the SQLServer engine.
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:
URL to the installed MySQL engine.
User name to access the MySQL database engine.
Password to access the MySQL engine.
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:
URL to the installed Oracle engine.
User name to access the Oracle database engine.
Password to access the Oracle engine.
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
CREATE ANY TABLE
CREATE ANY INDEX
SELECT ANY TABLE
UPDATE ANY TABLE
INSERT ANY TABLE
DELETE ANY TABLE
DROP ANY TABLE
ALTER ANY TABLE
DROP ANY INDEX
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.
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 PostgreSQL data sources from the Data Source combo box.
Fill-in the Connection Details:
URL to the installed PostgreSQL engine.
User name to access the PostgreSQL database engine.
Password to access the PostgreSQL engine.
Click OK.
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 Data Source 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 Data Source 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 Data Source 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 the- opens the Export Criteria dialog .
For 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.
- 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. In this dialog the following fields can be set:
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).
- removes the selected schema from the XML Schema Repository.
- opens the selected schema in <oXygen/>.
- performs a refresh of the selected node's subtree.
Add and Remove buttons.
- Opens a dialog for adding a new schema file in the DB XML repository. In this dialog you enter a collection name and the necessary schema files. XML Schema files management is done by using theActions available at schema level:
- performs a refresh of the selected node (and it's subtree).
- adds a new schema to the XML Schema files.
- removes the selected schema from the XML Schema Repository.
- opens the selected schema in <oXygen/>.
Every table from the Data Source 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 error message that a String value cannot be converted to the requested SQL type (NUMBER).
If the constraints of the database are not met (like primary key constraints for example), an Information dialog will appear, notifying you of the reason the database has not 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:
- opens the Export Criteria dialog .
- 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.