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 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.
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 10.2 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
Postgresfrom the driver type combo box.Press the Add button to add the following
Postgres8.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
URL to the installed IBM DB2 engine.
- User
User name to access the IBM DB2 database engine.
- Password
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
URL to the configured ODBC source.
- User
User name to access the configured ODBC source.
- Password
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
URL to the installed SQLServer engine.
- User
User name to access the SQLServer database engine.
- Password
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
URL to the installed MySQL engine.
- User
User name to access the MySQL database engine.
- Password
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
URL to the installed Oracle engine.
- User
User name to access the Oracle database engine.
- Password
Password to access the Oracle engine.
Click OK.
![[Note]](img/note.gif) | Note |
|---|---|
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. |
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
URL to the installed PostgreSQL engine.
- User
User name to access the PostgreSQL database engine.
- Password
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 Database Explorer view:
Connection
Catalog
XML Schema Repository
XML Schema Component
Schema
Table
System Table
Table Column
The following actions are available in the view's toolbar:
The
Filters button
opens the Data Sources / Table
Filters
Preferences page, allowing you to decide
which table types will be displayed in the
Database Explorer
view.The
Configure Database Sources
button opens the Data
Sources
preferences
page where you can configure both data
sources and connections.
Below 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.
Refresh -
performs a refresh of the selected node's
subtree.- Configure Database Sources
- opens the Data
Sources
preferences
page where you can configure both data
sources and connections.
- Refresh -
performs a refresh of the selected node's
subtree.
Edit - opens the
selected table in the Table
Explorer View.
Export to XML -
opens the Export Criteria dialog (a thorough description
of this dialog can be found in the Import from
database chapter) .
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.
- Refresh -
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.
- Refresh -
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:
- Refresh -
performs a refresh of the selected node (and it's
subtree).
Unregister - removes the selected schema from the XML Schema Repository.
- View - opens the
selected schema in <oXygen/>.
- Refresh -
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:
- Refresh -
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.
- View - 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.
| Note |
|---|---|
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, the cell will be marked by a red square and it will remain in editing state until a correct value is inserted.
For example, in the above figure DEPARTMENT_ID contains NUMBER values. If a character or string was inserted, the cell will look like this:
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.
Insert row -
inserts an empty row in the table.
Duplicate row -
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.
Commit row -
commits the selected row.
Delete row -
deletes the selected row.
Copy - copies the
content of the cell.
Paste - performs
paste in the selected cell
Some of the above actions are also available on the Table Explorer toolbar:
- Export to XML -
opens the Export Criteria dialog (a thorough description
of this dialog can be found in the Import from
database chapter) .
- Refresh -
performs a refresh of the selected node's
subtree.
- Insert row -
inserts an empty row in the table.
- Duplicate row -
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.
- Commit row -
commits the selected row.
- Delete 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
Configure Transformation Scenario
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
Configure Database Sources
.
Place holders(?) 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.
To view a more complex value returned by the SQL query that cannot be displayed entirely in the query result table at the bottom of the <oXygen/> window, for example an XMLTYPE value or a CLOB value, you have to right click on that cell, select the action Copy cell from the popup menu for copying the value in the clipboard and paste the value where you need it, for example an opened XQuery editor panel of <oXygen/> .
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.