Preparing to Create Data Sources

If you completed the tutorial, or if you were already all over the database thing, here's where you start creating the data sources.

Note

OpenOffice.org cannot edit Oracle databases. This is a known problem, so check the sun.com/staroffice site periodically to see if they've come up with a patch.

Understand the Kinds of Data You Can Connect to Using a Data Source

General Notes

Read the following before you begin.

Do a Dry Run

If this is the first data source you set up, you might want to consider doing a practice run, just connecting to a dBase database. (Create a basic spreadsheet of data, save it as dBase). Make sure it's working correctly by editing the data (Editing Data Using the Data Source Viewer on page 914) using data entry forms (Using Forms for Data Entry on page 955), or doing a mailing (Creating Mail Merge Letters and Faxes With Data Sources Using AutoPilots on page 367). In addition, use a test application to make sure that your database is running and accessible from the computer you're using.

Platform Notes

It's a good idea to use JDBC rather than ODBC on Linux and Solaris.

If you're on Windows, you should have an ODBC driver already, in the MDAC package. You can also go on microsoft.com and look for it in http://www.microsoft.com/data/mdac21info/manifest_intro.htm.

Database Notes: What Connection Types to Use for What Databases

Use ODBC or JDBC if you want to connect to databases like Oracle.

If you're connecting to an Access database, we recommend that you connect via ADO. See Connecting to an Access or Other Database Using ADO on page 897. If you have problems doing that, export the databases to . dbf , dBase.

If you're working with Oracle or any similarly robust and complex database, do it hand in hand with your Oracle administrator (and not the internget the head person). Get some pizza and Mountain Dew, and sit down to do this together. You'll need to have installed the Oracle Database Client software, and created a new service name. Your Oracle system administrator can tell you how to get the software and create the service name; with enough pizza, he or she might just take care of it. Just make sure you get the exact service name , however; you'll need it to set up your ODBC data source here in OpenOffice.org.

Data Source Types

You can create different types of data sources, as simple as a text file or two, or as complex as connecting to any database that supports JDBC or ODBC connectivity.

Figure 35-8 shows the data source type dropdown list in the data source setup window; Table 35-1 provides more information about each.

Figure 35-8. Formats your files can be in order to make them into data sources

Many of the technologies involved (such as Oracle, DB2, ADO, ODBC, and JDBC) require specialized knowledge to use. If all you want to do is create a simple database, use dBase.

Table 35-1 gives you a basic overview of the types of data sources you can create, and it provides important factors to consider for creating each type.

Table 35-1. Types of data sources you can create

Database Type	Description
dBase	dBase is perhaps the most useful type of database and the easiest to use for single, non-relational databases. Calc can open and save to dBase files, and you can save a file as a dBase database. This means, for example, that you can open a delimited text file in Calc (possibly created from something like an external address book) and convert it to a dBase file, or easily convert a dBase database to a spreadsheet file. The dBase tables must have the .dbf extension in lower case.
ADO	Lets you connect to a data source using a Microsoft ActiveX Data Objects (ADO) interface. Password protection for ADO connections is supported. To use ADO, you must have Microsoft Access 2000 or the ISGData Control for ADO (available from www.microsoft.com/data) installed on your system. If you have problems, export the databases to . dbf , dBase.
ODBC	Use this type or JDBC to connect to any database, such as Oracle or Access, that is not listed as a specific database type. The 32bit ODBC functions required here can be installed on your system at any time with the help of the setup program supplied with your database. You can then update the properties via the Control Panel. Password protection for ODBC database connections is supported. A table must have a clear index , if you want to edit or add records through OpenOffice.org. Your system must have the right ODBC drivers installed to create an ODBC connection; obtain them from the database manufacturer. Only the ODBC 3 standard is supported.
JDBC	Use this type or ODBC to connect to any database, such as Oracle or Access, that is not listed as a specific database type. JDBC is platform independent, while ODBC drivers are platform dependent. A JDBC driver must be installed on your system to use JDBC. A Sun Microsystems JDBC driver is provided in OpenOffice.org by default, and is displayed in the JDBC Driver Class field of the JDBC tab. You must enter the driver classes in the java.ini file or by choosing Tools > Options > OpenOffice.org > Security and entering the classes in the Class path field. This is covered in Connecting to an Oracle, MySQL, or Other Database Using JDBC on page 893. Password protection for JDBC database connections is supported.
Address book	You can select an address book in Outlook Express (a.k.a. Windows address book), Outlook, LDAP, or Mozilla (Netscape) formats. Note that Mozilla must be version 6.0 or later.
Text	Lets you open a delimited text file as a database. (Delimited just means a space, tab, or something separates every field.) Text data sources are mostly read-only. The only way to modify or enter data in them is in the text file itself using a text editor or Calc. In text format databases, data is stored as unformatted ASCII files. Every data record has its own row. The data fields are divided by separators such as tabs or commas, and text in the data fields is typically in quotation marks. Text database connections are read-only in OpenOffice.org. You must edit the text source directly to change the data. If you want to convert the text database to one you can edit in the data source viewer in OpenOffice.org, open the file in Calc (selecting the file type Text CSV) and save it as a dBase file.
Spreadsheet	Lets you open a StarOffice, OpenOffice.org, or Microsoft spreadsheet as a data source. Spreadsheet data sources are read-only. The only way to modify or enter data in them is in the file itself.

Prepare Files and Databases For Use as Data Sources

Before you begin, think about what data you want to be able to get into forms, mail merges, and other documents. Is it in:

• A StarBase database?

• A text document?

• A spreadsheet?

• An Oracle database?

Your data source can be any of the formats listed in Figure 35-8 and Table 35-1.

Got drivers?

Make sure you've got the drivers you need to connect to Oracle, Access, or anything else driver-oriented. You'll also probably want a database administrator.

If your file is plain text

If you've got a plain text file, just make sure that the fields (i.e., first names, last names , email addresses or January sales, February sales) are separated by a semicolon (;), comma (,), colon (:), tab, space, or another character, which you can enter when you're setting up the data source.

If your file is Word, WordPerfect, etc.

If the text file is something more complicated than just plain text, export it to plain text before you start. If you get a choice, export it as tab-delimited or something-delimited; it'll make things simpler and potentially avoid unexpected results when you create the data source.

If your address book format isn't supported

If your address book isn't one of the supported formats (Netscape/Mozilla address books need to be version 6.x), export the address book to a character-delimited text file.

If your file is a non-OpenOffice.org spreadsheet

It's safest to convert it to Calc (just open it in Calc and save it in Calc format).

If the database is from StarOffice 5.2

If the source is an old format from StarOffice 5.2, see Migrating a StarOffice 5.2 Database and Creating a Data Source on page 902.

If you want to make an editable database from a text file or spreadsheet

If you want to be able to edit the data through forms or other means, save the file as a dBase file. (First, get a text file into spreadsheet format using the instructions in See Importing a Text File or Spreadsheet on page 513.)

If the source is a database

Make sure it's accessible through one of the formats in Figure 35-8 and Table 35-1. If it isn't, follow these steps:

• From the application that you use to work with the external database, save the database as a delimited text file.

• Open the delimited text file in Calc. See Importing a Text File or Spreadsheet on page 513.

• Save the spreadsheet and use the Spreadsheet data type when setting up the data source. Or save the spreadsheet as a dBase file by choosing File > Save As and setting the file type to dBase.

Note

If you want to be able to edit the data from a form or similar viewing/input format, save it as dBase.

If you want your data source divided up

Sometimes you might just have one data source with all the data together. If you're just keeping your mailing list in one data source and you only send a letter once a month to everyone on that list, you don't need to go any further. However, what if you realize after a while that you only want to send your holiday newsletter to friends and family, your daily "I'm still unemployed, is anyone hiring?" letters to professional acquaintances , and your triweekly amusing short stories to the other members of the Fertile, Minnesota Writers' Club?

Then it's time for tables or queries.

Tables are just subdivisions of the data source.

• When you create a data source based on text files, everything in the directory you specify becomes a table. So if you set up the Contacts data source and point it at your bulging C:\mycontacts directory, you get three tables: holiday , jobsearch , and stories .

• When you create a data source based on a spreadsheet, every sheet in the spreadsheet is a table.

• For all the other databases, it depends on the database structure you set up.

So now, consider whether you'll need to structure your text files, spreadsheets, or other data differently, for the tables you might need, and subdivide and organize your data sources as necessary.

Queries are subsections of your data too, but they're created differently. You create a query for a particular table. "Give me the phone numbers of all the people in the hiring table of the Contacts data source who live in Colorado and whose emails end in @enron.com" would be a good query. Assuming that you have fields in your hiring table for phone numbers , state, and emails. And assuming that you're not bothering to ask them for a job.

You can create queries at any time; don't worry about it right now, just know you can do it. See Exporting Data Sources to Another Format on page 919.