Difference between revisions of "QA/Database"

From Apache OpenOffice Wiki
< QA
Jump to: navigation, search
(→‎Database Usage Guide)
Line 1: Line 1:
−
==<b>Database Usage Guide</b>==         
+
            '''Database Usage Guide'''
  
 
A data source can be a database, spreadsheet, or text document. such as MySQL or dBase or a spreadsheet or text document holding data.
 
A data source can be a database, spreadsheet, or text document. such as MySQL or dBase or a spreadsheet or text document holding data.
Line 7: Line 7:
 
|
 
|
 
notes:Need JRE, not only JDBC but new embedded database also need JRE. '''Tools &gt; Options &gt; <nowiki>OpenOffice.org</nowiki> &gt; Java'''
 
notes:Need JRE, not only JDBC but new embedded database also need JRE. '''Tools &gt; Options &gt; <nowiki>OpenOffice.org</nowiki> &gt; Java'''
 +
 
|}
 
|}
 +
=1. Start With Database Wizard=
  
−
= Start With Database Wizard=
+
==1.1. Database Wizard==
−
 
+
−
 
+
−
 
+
−
== Database Wizard==
+
−
 
+
−
 
+
−
 
+
−
    (1)Embedded database
+
−
 
+
−
*  Create a new database, use HSQL database as default setting(Pure Java DB)
+
  
−
*  open an existing database file, open created new database file and connect to database file.
 
  
−
    (2)External business database
 
  
−
*   connect to an existing database
+
# Embedded database
 +
* Create a new database, use HSQL database as default setting(Pure Java DB)
 +
* Open an existing database file, open created new database file and connect to database file.
 +
# External business database
 +
* Connect to an existing database
  
 
{| class="prettytable"
 
{| class="prettytable"
Line 135: Line 129:
 
'''Scenario 1: Create new embedded database'''
 
'''Scenario 1: Create new embedded database'''
  
−
    1)Create a new database
+
# Create a new database
−
 
+
# create table in design view,   
−
    2)create table in design view,   
+
# Name, type, Primary Key,  format example
−
 
+
# double click created table to input data  
−
      Name, type, Primary Key,  format example
+
# Check data from design view
−
 
+
# Create queries in SQL view(Grammar check when saving，Logical check on execution)
−
    3)double click created table to input data  
+
# create form from wizard(organize data with controller)
−
 
+
# create report to writer
−
    4)Check data from design view
+
−
 
+
−
    5)Create queries in SQL view(Grammar check when saving，Logical check on execution)  
+
−
 
+
−
    6)create form from wizard(organize data with controller)
+
−
 
+
−
    7)create report to writer
+
  
 
'''Scenario 2: Connect to an existing  database via JDBC'''
 
'''Scenario 2: Connect to an existing  database via JDBC'''
  
−
    1)install driver(ensure step 4 can pass)
+
# install driver(ensure step 4 can pass)
−
 
+
# Connection URL:mysql://127.0.0.1:3306/mysqldb,  
−
    2)Connection URL:mysql://127.0.0.1:3306/mysqldb,  
+
# Driver Class: <nowiki>com.mysql.jd</nowiki>bc.Driver
−
 
+
# click Test Class button, check driver can load correctly.
−
    3)Driver Class: <nowiki>com.mysql.jd</nowiki>bc.Driver
+
# Input User name, then Test Connection
−
 
+
# check load database, table ...from mysql db.
−
    4)click Test Class button, check driver can load correctly.
+
# Edit exist table: add column, delete column, but can't edit data record.
−
 
+
# Create new table on exist mysql database(load all database from connected db), field and data record all can be edited.
−
    5)Input User name, then Test Connection
+
# Create query, form, report base on exist table and new created table.
−
 
+
−
    6)check load database, table ...from mysql db.
+
−
 
+
−
    7)Edit exist table: add column, delete column, but can't edit data record.
+
−
 
+
−
    8)Create new table on exist mysql database(load all database from connected db), field and data record all can be edited.
+
−
 
+
−
    9)Create query, form, report base on exist table and new created table.
+
  
 
'''Scenario 3: Connect to an existing database via ODBC'''
 
'''Scenario 3: Connect to an existing database via ODBC'''
  
−
1)install odbc driver
+
:# install odbc driver
−
 
+
# connect to db info(ensuse name in step 2 can be browsed)
−
  connect to db info(ensuse name in step 2 can be browsed)
+
## connect to exist database via ODBC
−
 
+
# select name of odbc data source on system
−
2)connect to exist database via ODBC
+
## other operation like Scenario2 7)8)9)
−
 
+
−
  select name of odbc data source on system
+
−
 
+
−
3)other operation like Scenario2 7)8)9)
+
  
 
'''Scenario 4: Connect to an existing Spreadsheet'''
 
'''Scenario 4: Connect to an existing Spreadsheet'''
  
−
1)open spreadsheet with read-only mode, (one sheet one table)
+
# open spreadsheet with read-only mode, (one sheet one table)
−
 
+
# can't add table, edit table, include field definition and data record.
−
2)can't add table, edit table, include field definition and data record.
+
# Create query use Wizard(not easy to write sql to query Spreadsheet)
−
 
+
# create form and report base on sheet
−
3)Create query use Wizard(not easy to write sql to query Spreadsheet)
+
−
 
+
−
4)create form and report base on sheet
+
  
 
(All changes in a spreadsheet must be made in the spreadsheet itself, using Calc. After modifying the spreadsheet and saving it, you will see the changes in the database. If you create and save an additional sheet in your spreadsheet, the database will have a new table the next time you access it.）
 
(All changes in a spreadsheet must be made in the spreadsheet itself, using Calc. After modifying the spreadsheet and saving it, you will see the changes in the database. If you create and save an additional sheet in your spreadsheet, the database will have a new table the next time you access it.）
Line 197: Line 169:
 
'''Scenario 5 :Connect to an existing Text'''
 
'''Scenario 5 :Connect to an existing Text'''
  
−
    ■    '''open text'''
+
* '''open text'''
  
 
     1)create a text like table, with field separator
 
     1)create a text like table, with field separator
Line 205: Line 177:
  
  
−
    ■    '''open csv(address book file)'''
+
* '''open csv(address book file)'''
  
 
     1)use spreadsheet to save as to a text csv file.
 
     1)use spreadsheet to save as to a text csv file.
Line 215: Line 187:
  
  
−
    ■    '''open custom type file'''
+
* '''open custom type file'''
  
 
     1)create a custom type file with definition separator(Field separator)
 
     1)create a custom type file with definition separator(Field separator)
Line 221: Line 193:
  
  
−
      filed separator have : , ; tab space
+
        filed separator have : , ; tab space
  
  
Line 227: Line 199:
 
'''Scenario 6: Connect to an existing Microsoft Access 2007'''
 
'''Scenario 6: Connect to an existing Microsoft Access 2007'''
  
−
    1)browse Access accdb file  
+
# browse Access accdb file  
−
 
+
# Operation like other db(can't edit data record)
−
    2)Operation like other db(can't edit data record)
+
  
 
'''Scenario 7: Connect to an existing address book(Microsoft Outlook)'''
 
'''Scenario 7: Connect to an existing address book(Microsoft Outlook)'''
Line 237: Line 208:
 
'''Scenario 8: Register a database and Use it.'''
 
'''Scenario 8: Register a database and Use it.'''
  
−
1)Register database automatically(The last step in Create or Connect to existing database)
+
# Register database automatically(The last step in Create or Connect to existing database)
−
 
+
# Register database manually,File -&gt;Wizard-&gt;Address data source  
−
2)Register database manually,File -&gt;Wizard-&gt;Address data source  
+
# check registered database (Tools-&gt;Options-&gt;<nowiki>OpenOffice.org</nowiki> Bas-&gt;DataBases list all registered data bases. User also can remove registered databases)
−
 
+
# new Calc or Writer document
−
3)check registered database (Tools-&gt;Options-&gt;<nowiki>OpenOffice.org</nowiki> Bas-&gt;DataBases list all registered data bases. User also can remove registered databases)
+
# View-Data Sources or F4 to pop up registered data source.
−
 
+
# Select all data then click “data to text ” button, or drag&drop table to sheet.  Data from database will copy to sheet or writer.
−
4)new Calc or Writer document
+
−
 
+
−
5)View-Data Sources or F4 to pop up registered data source.
+
−
 
+
−
6)Select all data then click “data to text ” button, or drag&drop table to sheet.  Data from database will copy to sheet or writer.
+
  
  
Line 259: Line 225:
 
      
 
      
  
−
    1)Database objects: Table, Query, Form,Report
 
  
−
          ■  Table: a group of fields
 
  
−
          ■  Query:creates a new table from the existing tables based upon how you create the query
+
# Database objects: Table, Query, Form, Report
−
 
+
* Table: a group of fields
−
        ■  Form: data entry into the fields of one or more tables associated with the form.(A simple form consists of the fields from a table . More complex forms can contain much more, including additional text, graphics, selection boxes, and many other elements.)   
+
* Query: creates a new table from the existing tables based upon how you create the query
−
 
+
* Form: data entry into the fields of one or more tables associated with the form.(A simple form consists of the fields from a table . More complex forms can contain much more, including additional text, graphics, selection boxes, and many other elements.)   
−
        ■  Report: organizes the information of the fields of a query in a document according to your requirements.
+
* Report: organizes the information of the fields of a query in a document according to your requirements.
−
 
+
# Tasks zone: List the way to create database objects.
−
    2)Tasks zone: List the way to create database objects.
+
# Description: every task in Tasks list will activate a description.
−
 
+
# Object Zone:
−
    3)Description: every task in Tasks list will activate a description.
+
# can link to file and provide operation in context menu.
−
 
+
# support selecting entries by typing their name(focus object via keyboard, AOO3.4 Release notes)
−
      4)Object Zone:
+
# Preview (none and document). If form type, will have more operation.
−
 
+
# Status bar: include info about DataBase type, database name/database file path, user, IP.
−
    ■    can link to file and provide operation in context menu.
+
−
 
+
−
    ■  support selecting entries by typing their name(focus object via keyboard, AOO3.4 Release notes)
+
−
 
+
−
    4)Preview (none and document). If form type, will have more operation.
+
−
 
+
−
    5)Status bar: include info about DataBase type, database name/database file path, user, IP.
+
  
  
Line 291: Line 248:
 
'''     
 
'''     
  
−
'''    '''Field Properties: Entry Required, Auto Value, Auto-increment state, Length, Format example
+
    Field Properties: Entry Required, Auto Value, Auto-increment state, Length, Format example
−
 
+
−
    ■  Must have Primary Key
+
−
 
+
−
    ■  Provide shortcut of Field Type via keyboard
+
  
−
    ■  Provide Index design in Tools-&gt;Index Design
+
* Must have Primary Key
 +
* Provide shortcut of Field Type via keyboard
 +
* Provide Index design in Tools-&gt;Index Design
  
 
===2.2.2 Use Wizard to create table===
 
===2.2.2 Use Wizard to create table===
  
−
1)Select Fields
+
# Select Fields
  
−
  Select category: Business or Personal.
+
    Select category: Business or Personal.
  
−
2)Set types and format
+
# Set types and format
  
−
  Set types and format for selected fields.
+
      Set types and format for selected fields.
  
−
  Auto value is yes will pop up Auto-increment statement (auto value field must key) such as MySQL need input AUTO_INCREMENT to this statement box.
+
      Auto value is yes will pop up Auto-increment statement (auto value field must key) such as     MySQL need input AUTO_INCREMENT to this statement box.
  
−
3)Set primary key
+
# Set primary key
  
−
  use one or more field as primary key.
+
      use one or more field as primary key.
  
−
4)Create table
+
# Create table
  
 
===2.2.3 Create view===
 
===2.2.3 Create view===
Line 361: Line 316:
 
===2.4.2 Use wizard to create query===
 
===2.4.2 Use wizard to create query===
  
−
1)Field selection
+
# Field selection
  
 
         Specifies the table to create the query, and specifies which fields you wish to include in the query.
 
         Specifies the table to create the query, and specifies which fields you wish to include in the query.
  
−
2)Sorting Order
+
# Sorting Order
  
 
     Specifies the field by which the created query is sorted. May select 4 fields at most.
 
     Specifies the field by which the created query is sorted. May select 4 fields at most.
  
−
3)Search condition(can't save search condition, maybe defect)
+
# Search condition(can't save search condition, maybe defect)
  
 
     Specifies the search conditions to filter the query. Specifies 3 conditions at most.
 
     Specifies the search conditions to filter the query. Specifies 3 conditions at most.
  
−
4)Detail or summary
+
# Detail or summary
  
 
     Detail--&gt;show all results of the query.
 
     Detail--&gt;show all results of the query.
Line 379: Line 334:
 
     Summary--&gt;Select to show only results of aggregate functions. Only for numeric field.
 
     Summary--&gt;Select to show only results of aggregate functions. Only for numeric field.
  
−
5)Grouping?
+
# Grouping?
−
 
+
# Grouping conditions?
−
6)Grouping conditions?
+
# Aliases
−
 
+
−
7)Aliases
+
  
 
   Assign  Aliases of field if desired.
 
   Assign  Aliases of field if desired.
  
−
8)Overview
+
# Overview
  
  
Line 399: Line 352:
 
===2.5.2 Use Wizard to create form===
 
===2.5.2 Use Wizard to create form===
  
−
1)Field selection
+
# Field selection
  
 
         For table and query
 
         For table and query
  
−
2)    Setup a subform
+
# Setup a subform
  
 
     Specify if you want to use a subform and enter the subform's properties. A subform is a form that is inserted in another form.
 
     Specify if you want to use a subform and enter the subform's properties. A subform is a form that is inserted in another form.
  
−
3)    Add subform fields(Will use in Form-&gt;Subform based on existing relation. just choose one table in one-many table relation.)
+
# Add subform fields(Will use in Form-&gt;Subform based on existing relation. just choose one table in one-many table relation.)
−
 
+
# Get joined fields
−
4)    Get joined fields
+
# Arrange Controls
−
 
+
−
5)    Arrange Controls
+
  
 
     Layout of created form. Arrange of main form, Arrange or sub form.
 
     Layout of created form. Arrange of main form, Arrange or sub form.
  
−
6)    Set Data Entry
+
# Set Data Entry
  
 
     Specifies the data handling mode for the new form.
 
     Specifies the data handling mode for the new form.
  
−
➔    Creates a form that is only used for entering new data. Existing data will not be displayed.
+
* Creates a form that is only used for entering new data. Existing data will not be displayed.
 +
* Creates a form that can be used to display existing data and to enter new data.
 +
# Apply Styles
  
−
➔    Creates a form that can be used to display existing data and to enter new data.
+
    Specifies the page style for the form
  
−
7)    Apply Styles
+
# Set Name
  
−
     Specifies the page style for the form
+
     Specifies the name of the form and how to proceed.
  
−
8)    Set Name
 
  
−
    Specifies the name of the form and how to proceed.
 
  
 
notes and problem:
 
notes and problem:
  
−
1)“Data Source as table” button can combine table with form at one viewer.
+
# “Data Source as table” button can combine table with form at one viewer.
−
 
+
# Can't modify form with form created by wizard. may modify form created in design view. Need deeper investigate.
−
2) Can't modify form with form created by wizard. may modify form created in design view. Need deeper investigate.
+
  
  
Line 447: Line 397:
 
''    ''For table and query
 
''    ''For table and query
  
−
    1)Field selection
+
# Field selection
  
 
           Specifies the table or query for which you are creating the report
 
           Specifies the table or query for which you are creating the report
  
−
2)Labeling Fields
+
# Labeling Fields
  
 
   Label the field to the report
 
   Label the field to the report
  
−
3)Grouping
+
# Grouping
  
 
   Group records in a report
 
   Group records in a report
  
−
4)Sort options
+
# Sort options
  
 
   Select the fields by which to sort the report. Fields can be sorted by up to four levels, each either ascending or descending. Grouped fields can only be sorted within each group.
 
   Select the fields by which to sort the report. Fields can be sorted by up to four levels, each either ascending or descending. Grouped fields can only be sorted within each group.
  
−
5)Choose layout
+
# Choose layout
  
 
   Layout of data, layout of header and footer
 
   Layout of data, layout of header and footer
  
−
6) Create report
+
#  Create report
  
 
   You can create the report as a static or dynamic report
 
   You can create the report as a static or dynamic report
Line 479: Line 429:
 
==3.1 Copy/Paste/Paste Special==
 
==3.1 Copy/Paste/Paste Special==
  
−
    (1)copy/paste table
+
# copy/paste table
−
 
+
−
 
+
−
 
+
−
    ➔Definition and data: copy field definition and data
+
  
−
    ➔Definition: just copy field definition, not the data
 
  
−
    ➔As table view: copy as a table view with all definition and data
 
  
−
    ➔Append data: Appends the data of the table to be copied to an existing table. These two table's field definition and format definition must the same.
+
* Definition and data: copy field definition and data
 +
* Definition: just copy field definition, not the data
 +
* As table view: copy as a table view with all definition and data
 +
* Append data: Appends the data of the table to be copied to an existing table. These two table's field definition and format definition must the same.
  
−
                      Copy existing table, click another target table then right click to paste.
+
  Copy existing table, click another target table then right click to paste.
  
−
    (2)copy/paste special table
+
# copy/paste special table
  
 
     just table  have paste special function.
 
     just table  have paste special function.
Line 509: Line 456:
 
                     Copy and Paste special(data source table)
 
                     Copy and Paste special(data source table)
  
−
    (3)Copy/paste query/form/report
+
# Copy/paste query/form/report
  
 
       just paste with another name.
 
       just paste with another name.
  
−
    (4)Copy/ paste to Writer/Calc
+
# Copy/ paste to Writer/Calc
  
−
        to Calc, will auto copy the data
+
        to Calc, will auto copy the data
  
 
         to writer, will design table column
 
         to writer, will design table column
Line 529: Line 476:
 
===3.2.2 import data to base===
 
===3.2.2 import data to base===
  
−
    ➔You can import text files, spreadsheet files, and address book in read-only mode only.(via connect to an existing database)
+
* You can import text files, spreadsheet files, and address book in read-only mode only.(via connect to an existing database)
  
 
{| class="prettytable"
 
{| class="prettytable"
Line 545: Line 492:
 
     Notes:
 
     Notes:
  
−
    ➔When you import from a text or spreadsheet file, the file must have a first row of header information. The second row of the file is the first valid data row.  
+
* When you import from a text or spreadsheet file, the file must have a first row of header information. The second row of the file is the first valid data row.  
−
 
+
* On Windows systems, you can also use drag-and-drop instead of Copy and Paste.
−
    ➔On Windows systems, you can also use drag-and-drop instead of Copy and Paste.
+
  
  
Line 580: Line 526:
  
 
Click F4 or View-&gt;Data sources to Pop up window to operate registered database in Calc/Writer.
 
Click F4 or View-&gt;Data sources to Pop up window to operate registered database in Calc/Writer.
 +
 +
  
 
Operations:
 
Operations:
  
−
➔Drag and Drop table or query in navigate tree to sheet, All definition and data will copy to Sheet/Writer page
+
* Drag and Drop table or query in navigate tree to sheet, All definition and data will copy to Sheet/Writer page
 +
* Data to text：Click Upper left corner to select all data in specified registered database, then click "Data to text" button. All definition and data will copy to Sheet
 +
* Data to fields: Define fields with 2 ways,
  
−
➔Data to text：Click Upper left corner to select all data in specified registered database, then click "Data to text" button. All definition and data will copy to Sheet
+
    One is With writer, Add Insert-&gt;Fields-&gt;Other, define your fields in "Database" tab.
  
−
➔Data to fields: Define fields with 2 ways,  
+
    Another is With  writer, Mouse select field name，drag and drop field name to writer document。 
  
−
                          One is With writer, Add Insert-&gt;Fields-&gt;Other, define your fields in "Database" tab.
+
    Then select specified record then click "Date to field", field data will copy to specified field.
  
−
                          Another is With  writer, Mouse select field name，drag and drop field name to writer document。 
+
* Mail Merge: Mail Merge Wizard entry, this function is commonly used.(may be this button only enable in Writer )
  
−
                          Then select specified record then click "Date to field", field data will copy to specified field.
 
  
−
➔Mail Merge: Mail Merge Wizard entry, this function is commonly used.(may be this button only enable in Writer )
 
  
−
notes:
+
Notes:
  
 
Many place to use registered database, such as Writer-&gt;Field-&gt;Other-&gt;Database
 
Many place to use registered database, such as Writer-&gt;Field-&gt;Other-&gt;Database
Line 609: Line 557:
 
==3.4 Others==
 
==3.4 Others==
  
−
    ■Tools-&gt;SQL  for test sql command.
+
* Tools-&gt;SQL  for test sql command.
−
 
+
* Tools-&gt;extension manager, 用于添加extension plugins. Common used extensions  
−
    ■Tools-&gt;extension manager, 用于添加extension plugins. Common used extensions  
+
** “MySQL Connector for <nowiki>OpenOffice.org</nowiki>” ,
−
 
+
** Oracle  Report Builder  for more complex report. [http://extensions.services.openoffice.org/en/project/reportdesign http://extensions.services.openoffice.org/en/project/reportdesign]
−
    ■    “MySQL Connector for <nowiki>OpenOffice.org</nowiki>” ,
+
** Use Other database like PostgreSQL(commonly used) (Use other database via extensions)
−
 
+
−
    ■    Oracle  Report Builder  for more complex report. [http://extensions.services.openoffice.org/en/project/reportdesign http://extensions.services.openoffice.org/en/project/reportdesign]
+
−
 
+
−
    ■    Use Other database like PostgreSQL(commonly used) (Use other database via extensions)
+
  
−
    [http://wiki.services.openoffice.org/wiki/Base/connectivity/PostgreSQL    http://wiki.services.openoffice.org/wiki/Base/connectivity/PostgreSQL]
+
    [http://wiki.services.openoffice.org/wiki/Base/connectivity/PostgreSQL    http://wiki.services.openoffice.org/wiki/Base/connectivity/PostgreSQL]
  
−
    ■Tools-&gt;Options-&gt;<nowiki>OpenOffice.org</nowiki> Base connection pool...
+
* Tools-&gt;Options-&gt;<nowiki>OpenOffice.org</nowiki> Base connection pool...
  
−
        connection pool for performance, but only some defined drivers,  these drivers are not used frequently。Need deeper investigate it's benefit.
+
    connection pool for performance, but only some defined drivers,  these drivers are not used frequently。Need deeper investigate it's benefit.
  
−
    ■Tools-&gt;Tables Filter
+
* Tools-&gt;Tables Filter
  
 
       No effect as Help, not hide as filter definition, need deeper investigate.
 
       No effect as Help, not hide as filter definition, need deeper investigate.
  
−
    ■Small notes
+
* Small notes
  
 
         1)All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables,
 
         1)All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables,
Line 769: Line 713:
 
==5.1 Install MySQL JDBC driver==
 
==5.1 Install MySQL JDBC driver==
  
−
    1)download JDBC driver from official website
+
# download JDBC driver from official website
  
 
   like download  MySQL driver from [http://www.mysql.com/products/connector/ http://www.mysql.com/products/connector/]
 
   like download  MySQL driver from [http://www.mysql.com/products/connector/ http://www.mysql.com/products/connector/]
  
−
    2) Unzip file
+
# Unzip file
−
 
+
# add driver archive (jar)
−
    3)add driver archive (jar)
+
  
 
     Tools - Options - <nowiki>OpenOffice.org</nowiki> – Java  , then click Class path button , then click button “Add archive” then open appear in the list of Assigned folders and Archives
 
     Tools - Options - <nowiki>OpenOffice.org</nowiki> – Java  , then click Class path button , then click button “Add archive” then open appear in the list of Assigned folders and Archives
Line 783: Line 726:
 
==5.2 Install MySQL ODBC driver==
 
==5.2 Install MySQL ODBC driver==
  
−
    1)download ODBC driver from official website
+
# download ODBC driver from official website
−
 
+
# Navigate to Control Panel &gt; Administrative Tools and double-click on Data Sources (ODBC)  
−
    2)Navigate to Control Panel &gt; Administrative Tools and double-click on Data Sources (ODBC)  
+
# on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.  
−
 
+
# Input requires data in the appeared dialog.
−
    3)on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.  
+
−
 
+
−
    4)Input requires data in the appeared dialog.
+
  
  
Line 808: Line 748:
  
 
TBD3:  How to use Connection Pool to improve  performance with AOO know drivers
 
TBD3:  How to use Connection Pool to improve  performance with AOO know drivers
−
*item text
 

Revision as of 10:18, 27 July 2012

            Database Usage Guide

A data source can be a database, spreadsheet, or text document. such as MySQL or dBase or a spreadsheet or text document holding data.

notes:Need JRE, not only JDBC but new embedded database also need JRE. Tools > Options > OpenOffice.org > Java

1. Start With Database Wizard

1.1. Database Wizard

  1. Embedded database
  • Create a new database, use HSQL database as default setting(Pure Java DB)
  • Open an existing database file, open created new database file and connect to database file.
  1. External business database
  • Connect to an existing database 
Support Type

comments

JDBC

Oracle JDBC

Database url and driver class

need provide some commonly used db(provide JDBC)


Oracle JDBC, driver class is given—oracle.jdbc.driver.OracleDriver

ODBC

ODBC data source(dBase files,excel files, MS access database)

MySQL

Connect use JDBC or ODBC

Spreadsheet

Open Openoffice.org spreadsheet or MS excel in read-only mode.

LDAP address book

SeaMonkey address book

Microsoft Outlook address book

Microsoft Windows address book

Thunderbird address book

Import a address book in read-only mode

Text

Import a database in text format, Open txt, csv, custom type in read-only mode

Microsoft Access

Microsoft Access 2007

Access file

dBASE

Dbase file

ADO

ADO (Microsoft ActiveX Data Objects) database.

Adabas D



1.2 Common scenarios

Scenario 1: Create new embedded database

  1. Create a new database
  2. create table in design view,
  3. Name, type, Primary Key, format example
  4. double click created table to input data
  5. Check data from design view
  6. Create queries in SQL view(Grammar check when saving，Logical check on execution)
  7. create form from wizard(organize data with controller)
  8. create report to writer

Scenario 2: Connect to an existing database via JDBC

  1. install driver(ensure step 4 can pass)
  2. Connection URL:mysql://127.0.0.1:3306/mysqldb,
  3. Driver Class: com.mysql.jdbc.Driver
  4. click Test Class button, check driver can load correctly.
  5. Input User name, then Test Connection
  6. check load database, table ...from mysql db.
  7. Edit exist table: add column, delete column, but can't edit data record.
  8. Create new table on exist mysql database(load all database from connected db), field and data record all can be edited.
  9. Create query, form, report base on exist table and new created table.

Scenario 3: Connect to an existing database via ODBC

  1. install odbc driver
  1. connect to db info(ensuse name in step 2 can be browsed)
    1. connect to exist database via ODBC
  2. select name of odbc data source on system
    1. other operation like Scenario2 7)8)9)

Scenario 4: Connect to an existing Spreadsheet

  1. open spreadsheet with read-only mode, (one sheet one table)
  2. can't add table, edit table, include field definition and data record.
  3. Create query use Wizard(not easy to write sql to query Spreadsheet)
  4. create form and report base on sheet

(All changes in a spreadsheet must be made in the spreadsheet itself, using Calc. After modifying the spreadsheet and saving it, you will see the changes in the database. If you create and save an additional sheet in your spreadsheet, the database will have a new table the next time you access it.）

Scenario 5 :Connect to an existing Text

  • open text 
   1)create a text like table, with field separator

   2)one text one table, can't edit table.


  • open csv(address book file) 
   1)use spreadsheet to save as to a text csv file.

   2)Select folder where csv in. (one csv one table, if more csv in this folder, then will get all csv more tables)

   3)Can't add table, edit table.


  • open custom type file 
   1)create a custom type file with definition separator(Field separator)



        filed separator have : , ; tab space


Scenario 6: Connect to an existing Microsoft Access 2007

  1. browse Access accdb file
  2. Operation like other db(can't edit data record)

Scenario 7: Connect to an existing address book(Microsoft Outlook)

auto get windows address book, but how to check address book file need deeper investigate. If have address book file also can be opened by Scenario 5

Scenario 8: Register a database and Use it.

  1. Register database automatically(The last step in Create or Connect to existing database)
  2. Register database manually,File ->Wizard->Address data source
  3. check registered database (Tools->Options->OpenOffice.org Bas->DataBases list all registered data bases. User also can remove registered databases)
  4. new Calc or Writer document
  5. View-Data Sources or F4 to pop up registered data source.
  6. Select all data then click “data to text ” button, or drag&drop table to sheet. Data from database will copy to sheet or writer.


2. Database design entry

2.1 Entry

  1. Database objects: Table, Query, Form, Report
  • Table: a group of fields
  • Query: creates a new table from the existing tables based upon how you create the query
  • Form: data entry into the fields of one or more tables associated with the form.(A simple form consists of the fields from a table . More complex forms can contain much more, including additional text, graphics, selection boxes, and many other elements.)
  • Report: organizes the information of the fields of a query in a document according to your requirements.
  1. Tasks zone: List the way to create database objects.
  2. Description: every task in Tasks list will activate a description.
  3. Object Zone:
  4. can link to file and provide operation in context menu.
  5. support selecting entries by typing their name(focus object via keyboard, AOO3.4 Release notes)
  6. Preview (none and document). If form type, will have more operation.
  7. Status bar: include info about DataBase type, database name/database file path, user, IP.


2.2 Table

2.2.1 Create table in design view

   Field Properties: Entry Required, Auto Value, Auto-increment state, Length, Format example
  • Must have Primary Key
  • Provide shortcut of Field Type via keyboard
  • Provide Index design in Tools->Index Design

2.2.2 Use Wizard to create table

  1. Select Fields 
    Select category: Business or Personal.
  1. Set types and format 
     Set types and format for selected fields.

     Auto value is yes will pop up Auto-increment statement (auto value field must key) such as      MySQL need input AUTO_INCREMENT to this statement box.
  1. Set primary key 
     use one or more field as primary key.
  1. Create table

2.2.3 Create view

total same as Query Design Windows

can edit and edit in SQL view. Can't find difference with Query now. Like Database View.

2.2.4 Table Data view

Provide Data Filter and Data Statistics



2.3 Relation

Tools->Relationships to pop up Relationship Design Window


Drag and Drop or Insert->New Relation...




2.4 Query

2.4.1 Create query in design view

Field, Alias,Table,Sort,Visible


Function button: Run query, Clear query, Switch design view on/off(switch with SQL editor to check before save, after save this button is disabled)


2.4.2 Use wizard to create query

  1. Field selection 
        Specifies the table to create the query, and specifies which fields you wish to include in the query.
  1. Sorting Order 
    Specifies the field by which the created query is sorted. May select 4 fields at most.
  1. Search condition(can't save search condition, maybe defect) 
   Specifies the search conditions to filter the query. Specifies 3 conditions at most.
  1. Detail or summary 
   Detail-->show all results of the query.

   Summary-->Select to show only results of aggregate functions. Only for numeric field.
  1. Grouping?
  2. Grouping conditions?
  3. Aliases 
  Assign  Aliases of field if desired.
  1. Overview


2.5 Form

2.5.1 Create Form in design view

      may need macro to activate event, now still not create form in design view. Need deeper investigate.

2.5.2 Use Wizard to create form

  1. Field selection 
        For table and query
  1. Setup a subform 
   Specify if you want to use a subform and enter the subform's properties. A subform is a form that is inserted in another form.
  1. Add subform fields(Will use in Form->Subform based on existing relation. just choose one table in one-many table relation.)
  2. Get joined fields
  3. Arrange Controls 
   Layout of created form. Arrange of main form, Arrange or sub form.
  1. Set Data Entry 
   Specifies the data handling mode for the new form.
  • Creates a form that is only used for entering new data. Existing data will not be displayed.
  • Creates a form that can be used to display existing data and to enter new data.
  1. Apply Styles 
   Specifies the page style for the form
  1. Set Name 
   Specifies the name of the form and how to proceed.


notes and problem:

  1. “Data Source as table” button can combine table with form at one viewer.
  2. Can't modify form with form created by wizard. may modify form created in design view. Need deeper investigate.




2.6 Report

For table and query

  1. Field selection 
         Specifies the table or query for which you are creating the report
  1. Labeling Fields 
  Label the field to the report
  1. Grouping 
  Group records in a report
  1. Sort options 
  Select the fields by which to sort the report. Fields can be sorted by up to four levels, each either ascending or descending. Grouped fields can only be sorted within each group.
  1. Choose layout 
  Layout of data, layout of header and footer
  1. Create report 
 You can create the report as a static or dynamic report


3. Main Function

3.1 Copy/Paste/Paste Special

  1. copy/paste table


  • Definition and data: copy field definition and data
  • Definition: just copy field definition, not the data
  • As table view: copy as a table view with all definition and data
  • Append data: Appends the data of the table to be copied to an existing table. These two table's field definition and format definition must the same. 
  Copy existing table, click another target table then right click to paste.
  1. copy/paste special table 
   just table  have paste special function.


Inserts the contents of the clipboard into the current file in a format that you can specify. 


                    Copy as table view

   Table <<------------------------------->>Table View

                   Copy and Paste special(data source table)
  1. Copy/paste query/form/report 
     just paste with another name.
  1. Copy/ paste to Writer/Calc 
       to Calc, will auto copy the data

       to writer, will design table column


3.2 Export and import data of base

3.2.1 export data from base

You copy a table from Base to a new Calc sheet, then you can save or export the data to any file format that Calc supports.

3.2.2 import data to base

  • You can import text files, spreadsheet files, and address book in read-only mode only.(via connect to an existing database)

1. Choose Edit - Copy in imported file

2. In the Base window, click Tables in database navigate to view the tables.

3. In the Base window, choose Edit - Paste.

4. You see the Copy Table dialog. Most databases need a primary key, so you may want to check the Create primary key box. 

   Notes:
  • When you import from a text or spreadsheet file, the file must have a first row of header information. The second row of the file is the first valid data row.
  • On Windows systems, you can also use drag-and-drop instead of Copy and Paste.



3.3 Register database and Integrate Usage

If the database is not registered, it will not be accessible to the other OOo components such as Writer and Calc. If the database is registered, other components can access it.

3.3.1 Register database automatically

3.3.2 Register database manually

File ->Wizard->Address data source.（register your address book as the data source）.


Can register all kinds of database not only address book, field assignment page can be ignored when register.

notes:

Tools->Options->OpenOffice.org Base->DataBases list all registered data bases. User can remove registered databases in this dialog.


3.3.3 Usage registered database in Calc and Writer

Click F4 or View->Data sources to Pop up window to operate registered database in Calc/Writer.


Operations:

  • Drag and Drop table or query in navigate tree to sheet, All definition and data will copy to Sheet/Writer page
  • Data to text：Click Upper left corner to select all data in specified registered database, then click "Data to text" button. All definition and data will copy to Sheet
  • Data to fields: Define fields with 2 ways, 
    One is With writer, Add Insert->Fields->Other, define your fields in "Database" tab.

    Another is With  writer, Mouse select field name，drag and drop field name to writer document。  

    Then select specified record then click "Date to field", field data will copy to specified field.
  • Mail Merge: Mail Merge Wizard entry, this function is commonly used.(may be this button only enable in Writer )


Notes:

Many place to use registered database, such as Writer->Field->Other->Database




3.4 Others

  • Tools->SQL for test sql command.
  • Tools->extension manager, 用于添加extension plugins. Common used extensions 
    http://wiki.services.openoffice.org/wiki/Base/connectivity/PostgreSQL
  • Tools->Options->OpenOffice.org Base connection pool... 
   connection pool for performance, but only some defined drivers,  these drivers are not used frequently。Need deeper investigate it's benefit.
  • Tools->Tables Filter 
     No effect as Help, not hide as filter definition, need deeper investigate.
  • Small notes 
       1)All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables,

you must first combine these fields in a single query. Then you can create a report on this query.

       2)Report: The Author in Report is the name you listed in Tools > Options > OpenOffice.org > User Data.

       3)Have different context menu in CSV database client. With a CSV database the "MaxRowScan" setting allows to define how many  rows should be scanned to find the correct type of a column. In the past only the first row was scanned.



4 Common defects from AOO mail list

Mail title

Main content

comments

Crash in Calc with database

Memo field., register a database in Calc via macro


mailmerge from a Database query produces blank 

                  document

Mail merge from a database query


Dragging a query into a sheet

Drag a table or query to a sheet(export)(not registered database type, separate)

Copy and paste is ok, but drag and drop have the same problem

postgresql-sdbc-0.7.6b installs, Postgres not an option in "Connect to existing Database" pick list

Use extensions to add other database not in pick list.

Extensions about database need pay attention.


Postgresql is used frequently


OOo database denies to open the odb file

Use three not including Filter .

Open and reload

With Filter

RPT: report could not be reopened after 'save as' the database document

Save to another name, can't execute report normally.

Save and Reopen

Desktop frozen on database log-in

Registered database with password protect.

Need consider data with security protect.

Test use Spreadsheet, MS sheet is ok, MS sheet with password can't registered. OO sheet with/without password can't registered.

can not make form in Base only installation

Installation, If you select to install ONLY OpenOffice

Base, you have to install also OpenOffice Wrighter

Install base stand-alone.


TBD：what is OpenOffice Wrighter




5 Install JDBC and ODBC driver

http://wiki.services.openoffice.org/wiki/Connect_MySQLandBase

5.1 Install MySQL JDBC driver

  1. download JDBC driver from official website 
  like download  MySQL driver from http://www.mysql.com/products/connector/
  1. Unzip file
  2. add driver archive (jar) 
   Tools - Options - OpenOffice.org – Java  , then click Class path button , then click button “Add archive” then open appear in the list of Assigned folders and Archives

    Ok then restart the program(restart include the quick start), will take effect.(need verify db is running)

5.2 Install MySQL ODBC driver

  1. download ODBC driver from official website
  2. Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)
  3. on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.
  4. Input requires data in the appeared dialog.



http://wiki.services.openoffice.org/wiki/Base 

http://wiki.services.openoffice.org/wiki/Connecting_to_Microsoft_Access

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Using_SQLite_With_OpenOffice.org


FAQ& TBD

TBD 1: Use some kinds of address book.

TBD3: How to use Connection Pool to improve performance with AOO know drivers

Retrieved from "https://wiki.openoffice.org/w/index.php?title=QA/Database&oldid=203901"
Personal tools