Open Office Ingres Data How To

From Ingres Community Wiki

Contents 1 Incorporating Ingres Data in OpenOffice 2 Create a Master / Detail OpenOffice Form 2.1 Preparing the database connection for master / detail access 2.2 Create a Master / Detail form with the new connection 3 Runing the new form 3.1 Insert new records with the form 4 Custom Queries in OpenOffice 4.1 Creating a Custom Query 4.2 Using a Query in your OpenOffice Writer Documents 4.3 Drag and Drop Paradigm 4.4 Drag and Drop Records 4.5 Drag and Drop Fields 4.6 Updating your document from the database 4.7 Add New Fields 5 Issues 6 See Also

Incorporating Ingres Data in OpenOffice

The database connection can be used in Open Office and you can also directly create Forms, Reports, Queries, view and update the contents of your database tables with the Database application.

Create a Master / Detail OpenOffice Form

To create a master detail form we use the OpenOffice forms wizard.

Note: The following examples use OpenOffice on Microsoft"s Windows operating system, showing that OpenOffice, like Ingres is available across multiple platforms.

A master detail form is where a master table contains overview information and detailed information is contained in a second table. They are often used and the demodb is no exception, however programmers often have to re-invent the wheel for each master / detail combination in a database, the OpenOffice forms wizard significantly reduces the work involved. It is worth pointing out, however, that superior functionality is available in OpenROAD.

The database is the Ingres Frequent Flyer demonstration database, demodb. This contains two tables, country and airport. The form will allow the user to enter new country and then airport names to the database. The demodb database constraints mean that no airport can exist without having a country entry associated with it.

Preparing the database connection for master / detail access

This form needs to keep two tables open at one time and OpenOffice uses a separate cursor for each table. We must therefore ensure that the JDBC connection URL will allow multiple cursors to be open. The necessary Ingres JDBC driver option and its value are shown below:

autocommit_mode

multi

The value multi allows multiple cursors to be open at the same time (otherwise only one cursor can be opened.) Other values are: dbms and single, in the first case the DBMS server performs the autocommit processing and in the second case the DAS only allows a single cursor to be open during autocommit. More details are available in the Ingres Connectivity Guide.

To set the value of autocommit_mode to multi, open the Database connection properties in the database application by accessing the menu:

Edit -> Database -> Properties...

The following dialog appears:

Update the Datasource URL to add the autocommit_mode setting:

And save your changes with [ Finish ].

Create a Master / Detail form with the new connection

You can now start the OpenOffice Database application and be sure to choose the database connection details that you updated previously.

Select the existing database file, e.g. demodb and the OpenOffice database application appears.

To create a master / detail form click 'Use Wizard to Create Form' when the Database application starts.

Use the Form Wizard to select all the fields from the country table

Select the 'country' table (as shown above) as the Master table and select all the fields.

Press [ Next ] or click on 'Set up a subform' to set up the Detail part of the form

Select the 'Subform based on manual selection of fields' radio button and press [ Next ] to select the fields for the subform

Select the detail table, 'airport' and include all fields from the airport table. Press [ Next ] to set the joining fields.

Select the 'ap_ccode' from the subform field drop down box and the corresponding field, 'ct_code' from the main form field drop down box. These are the only fields you need to select. Press [ Next ] to continue to control arrangement.

Accept the default layout by pressing [ Next ]. Move onto the Data Entry options.

Accept the defaults. Press [ Next ] to continue to style selection

Select the style 'Bright Blue', press [ Next ] to continue to set the Form name

Enter 'Countries & Airports' and click [ Finish ]

Runing the new form

You run the form by highlighting the name in the main application screen and using the context menu to Open the form or the Open Form icon in the tool bar:

The form opens

As you move through the upper data set (the countries) the lower data set is automatically updated with the airports corresponding to the selected country

Insert new records with the form

You can also insert new records by pressing the New Record button ( ). You can insert records in either form, but you clearly can not insert an Airport in a country that does not yet exist! The following screen capture shows that a new country, 'Switzerland' has been added in the upper (master) form. And then two of Switzerland"s airports have also been added in the lower (detail) form.

You can also create queries and reports, queries in particular can be used in other Office documents

Custom Queries in OpenOffice

You can use custom queries in your OpenOffice documents.

Creating a Custom Query

Create a new query using the 'Create Query in Design View' option in the database application

When you select the data tables, the application automatically mines the database schema to identify the primary keys ( ) and primary / foreign key relationships (the line joining the tables) and shows them graphically in the top part of the designer. You then choose the fields and their order in the result set in the lower part as shown in the screen capture.

Using a Query in your OpenOffice Writer Documents

You can incorporate data in your Open Office Writer Documents directly from your custom query. Open the document and press F4 to select the data source you created earlier

Drag and Drop Paradigm

A drag and drop paradigm is used to select the data you want.

Drag and Drop Records

Select a record and drag it to where you wish the data to appear, the data format dialog appears. You can choose between Tabular, Form or Text format. In this example we chose the format table

You select the columns you wish to use in the document and then press [ OK ] to insert the data into your document:

Drag and Drop Fields

You can also work with database fields by dragging the column names into your document. For example drag the 'airport' column heading into the document

And add the rest of the text and data fields you require:

Updating your document from the database

Select a record and then press the Data to Fields Button ( ) to populate the data fields with the current record indicated by the arrow symbol ( ).

Add New Fields

You can add new fields to your text if you need to, by dragging a column heading again

Select a record and then press the Data to Fields Button ( ) to refresh the data fields

Unfortunately there is no concept of event handling in the JDBC standard so you can not create "dynamic" documents. To update the database fields in your document you must use the 'Data to Fields' ( ) button.

Issues

You can not create a table using the Table Design View:

The next alternative is a create table wizard, but it can"t retrieve type data from Ingres.

You can create example tables from the wizard if you omit some columns.

First attempt using all the suggested columns from the wizard"s template resulted in an error:

A JDBC trace shows that not all columns have data types attached:

CREATE TABLE 'Employees' (
    'Address' ,
    'Birthdate' ansidate,
    'City' varchar(50),
    'CountryOrRegion' varchar(50),
    'Deductions' integer,
    'Department' varchar(50),
    'DepartmentID' integer,
    'EmailAddress' varchar(50),
    'EmployeeID' integer NOT NULL,
    'EmployeeNumber' varchar(50),
    'Extension' varchar(30),
    'FaxNumber' varchar(30),
    'FirstName' varchar(50),
    'LastName' varchar(50),
    'MiddleName' varchar(50),
    'MobileNumber' varchar(30),
    'Notes' long varchar,
    'OfficeLocation' varchar(50),
    'PhoneNumber' varchar(30),
    'Photo' long byte,
    'PostalCode' varchar(20),
    'SocialSecurityNumber' varchar(50),
    'SpouseName' varchar(50),
    'StateOrProvince' varchar(50),
    'SupervisorID' integer,
    'Title' varchar(50), 
    PRIMARY KEY  ('EmployeeID')
)

Omitting the Address column results in a successfully second attempt:

CREATE TABLE 'Employees' (
    'Birthdate' ansidate,
    'City' varchar(50),
    'CountryOrRegion' varchar(50),
    'Deductions' integer,
    'Department' varchar(50),
    'DepartmentID' integer,
    'EmailAddress' varchar(50),
    'EmployeeID' integer NOT NULL,
    'EmployeeNumber' varchar(50),
    'Extension' varchar(30),
    'FaxNumber' varchar(30),
    'FirstName' varchar(50),
    'LastName' varchar(50),
    'MiddleName' varchar(50),
    'MobileNumber' varchar(30),
    'Notes' long varchar,
    'OfficeLocation' varchar(50),
    'PhoneNumber' varchar(30),
    'Photo' long byte,
    'PostalCode' varchar(20),
    'SocialSecurityNumber' varchar(50),
    'SpouseName' varchar(50),
    'StateOrProvince' varchar(50),
    'SupervisorID' integer,
    'Title' varchar(50), 
    PRIMARY KEY  ('EmployeeID')
)

However the automatically generated form to insert data did not allow data to be inserted. Creating a custom form didn"t help either, on populating each field with values the following error was displayed:

See Also

Open Office How To