Apache OpenOffice Wiki - User contributions [en]

Documentation/BASIC Guide/Working With Forms

2010-09-26T10:12:24Z

Ottoshmidt:

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Forms
|NextPage=Documentation/BASIC Guide/Control Element Forms
|form=block
}}
{{DISPLAYTITLE:Working with Forms}}
__NOTOC__
{{OOo}} forms may contain text fields, list boxes, radio buttons, and a range of other control elements, which are inserted directly in a text or spreadsheet. The Form Functions Toolbar is used for editing forms.

A {{OOo}} form may adopt one of two modes: the draft mode and the display mode. In draft mode, the position of control elements can be changed and their properties can be edited using a properties window.

The Form Functions Toolbar is also used to switch between modes.

== Determining Object Forms ==

{{OOo}} positions the control elements of a form at drawing object level. The actual object form can be accessed through the Forms list at the drawing level. The objects are accessed as follows in text documents:

<source lang="oobas">
Dim Doc As Object
Dim DrawPage As Object
Dim Form As Object

Doc = ThisComponent
DrawPage = Doc.DrawPage
Form = DrawPage.Forms.GetByIndex(0)
</source>

The <tt>GetByIndex</tt> method returns the form with the index number 0.

When working with spreadsheets, an intermediate stage is needed for the Sheets list because the drawing levels are not located directly in the document but in the individual sheets:

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim DrawPage As Object
Dim Form As Object

Doc = ThisComponent
Sheet = Doc.Sheets.GetByIndex(0)
DrawPage = Sheet.DrawPage
Form = DrawPage.Forms.GetByIndex(0)
</source>

As is already suggested by the <tt>GetByIndex</tt> method name, a document may contain several forms. This is useful, for example, if the contents of different databases are displayed within one document, or if a 1:n database relationship is displayed within a form. The option of creating sub-forms is also provided for this purpose.

== The Three Aspects of a Control Element Form ==

A control element of a form has three aspects:

* The '''Model''' of the control element is the key object for the {{OOo}} Basic-programmer when working with control element forms.
* The counterpart to this is the '''View''' of the control element, which administers the display information.
* Since control element forms within the documents are administered like a special drawing element, there is also a '''Shape object''' which reflects the drawing element-specific properties of the control element (in particular its position and size).

== Accessing the Model of Control Element Forms ==

The models of the control elements of a form are available through the <tt>GetByName</tt> method of the Object form:

<source lang="oobas">
Dim Doc As Object
Dim Form As Object
Dim Ctl As Object

Doc = ThisComponent
Form = Doc.DrawPage.Forms.GetByIndex(0)
Ctl = Form.getByName("MyListBox")
</source>

The example determines the model of the <tt>MyListBox</tt> control element, which is located in the first form of the text document currently open.

If you are not sure of the form of a control element, you can use the option for searching through all forms for the control element required:

<source lang="oobas">
Dim Doc As Object
Dim Forms As Object
Dim Form As Object
Dim Ctl As Object
Dim I as Integer

Doc = ThisComponent
Forms = Doc.Drawpage.Forms

For I = 0 To Forms.Count - 1
Form = Forms.GetbyIndex(I)
If Form.HasByName("MyListBox") Then
Ctl = Form.GetbyName("MyListBox")
Exit For
End If
Next I
</source>

The example uses the <tt>HasByName</tt> method to check all forms of a text document to determine whether they contain a control element model called <tt>MyListBox</tt>. If a corresponding model is found, then a reference to this is saved in the <tt>Ctl</tt> variable and the search is terminated.

== Accessing the View of Control Element Forms ==

To access the view of a control element form, you need the associated model. The view of the control element can then be determined with the assistance of the model and using the document controller.

<source lang="oobas">
Dim Doc As Object
Dim DocCrl As Object
Dim Forms As Object
Dim Form As Object
Dim Ctl As Object
Dim CtlView As Object
Dim I as Integer

Doc = ThisComponent
DocCrl = Doc.getCurrentController()
Forms = Doc.Drawpage.Forms

For I = 0 To Forms.Count - 1
Form = Forms.GetbyIndex(I)
If Form.HasByName("MyListBox") Then
Ctl = Form.GetbyName("MyListBox")
CtlView = DocCrl.GetControl(Ctl)
Exit Function
End If
Next I
</source>

The code listed in the example is very similar to the code listed in the previous example for determining a control element model. It uses not only the <tt>Doc</tt> document object but also the <tt>DocCrl</tt> document controller object which makes reference to the current document window. With the help of this controller object and the model of the control element, it then uses the <tt>GetControl</tt> method to determine the view (<tt>CtlView</tt> variable) of the control element form.

== Accessing the Shape Object of Control Element Forms ==

The method for accessing the shape objects of a control element also uses the corresponding drawing level of the document. To determine a special control element, all drawing elements of the drawing level must be searched through.

<source lang="oobas">
Dim Doc As Object
Dim Shape as Object
Dim I as integer

Doc = ThisComponent

For i = 0 to Doc.DrawPage.Count - 1
Shape = Doc.DrawPage(i)
If HasUnoInterfaces(Shape, "com.sun.star.drawing.XControlShape") Then
If Shape.Control.Name = "MyListBox" Then
Exit Function
End If
End If
Next
</source>

The example checks all drawing elements to determine whether they support the <idl>com.sun.star.drawing.XControlShape</idl> interface needed for control element forms. If this is the case, the <tt>Control.Name</tt> property then checks whether the name of the control element is <tt>MyListBox</tt>. If this is true, the function ends the search.

=== Determining the Size and Position of Control Elements ===

As already mentioned, the size and position of control elements can be determined using the associated <tt>shape</tt> object. The control element shape, like all other <tt>shape</tt> objects, provides the <tt>Size</tt> and <tt>Position</tt> properties for this purpose:

;<tt>Size (struct)</tt>:size of control element (<idl>com.sun.star.awt.Size</idl> data structure)
;<tt>Position (struct)</tt>:position of control element (<idl>com.sun.star.awt.Point</idl> data structure)

The following example shows how the position and size of a control element can be set using the associated shape object:

<source lang="oobas">
Dim Shape As Object
Dim Point As New com.sun.star.awt.Point
Dim Size As New com.sun.star.awt.Size

' ... Initialize Shape object, as previously shown ...

Point.x = 1000
Point.y = 1000
Size.Width = 10000
Size.Height = 10000

Shape.Size = Size
Shape.Position = Point
</source>

The <tt>shape</tt> object of the control element must already be known if the code is to function. If this is not the case, it must be determined using the preceding code.

{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Working With Forms}}
{{PDL1}}

Documentation/BASIC Guide/Properties

2010-09-11T13:46:05Z

Ottoshmidt:

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Working With Dialogs
|NextPage=Documentation/BASIC Guide/Events
|dial=block
}}
{{DISPLAYTITLE:Properties}}
__NOTOC__
== Name and Title ==

Every control element has its own name that can be queried using the following model property:

;<tt>Model.Name (String)</tt>:control element name

You can specify the title that appears in the title bar of a dialog with the following model property:

;<tt>Model.Title (String)</tt>:dialog title (only applies to dialogs)

== Position and Size ==

You can query the size and position of a control element using the following properties of the model object:

;<tt>Model.Height (long)</tt>:height of control element (in ma units)
;<tt>Model.Width (long)</tt>:width of control element (in ma units)
;<tt>Model.PositionX (long)</tt>:X-position of control element, measured from the left inner edge of the dialog (in ma units)
;<tt>Model.PositionY (long)</tt>:Y-position of control element, measured from top inner edge of the dialog (in ma units)

To ensure platform independence for the appearance of dialogs, {{OOo}} uses the '''Map AppFont (ma)''' internal unit to specify the position and size within dialogs. An ma unit is defined as being one eighth of the average height of a character from the system font defined in the operating system and one quarter of its width. By using ma units, {{OOo}} ensures that a dialog looks the same on different systems under different system settings.

If you want to change the size or position of control elements for runtime, determine the total size of the dialog and adjust the values for the control elements to the corresponding part ratios.

{{Documentation/Note|The Map AppFont (ma) replaces the Twips unit to achieve better platform independence.}}

== Focus and Tabulator Sequence ==

You can navigate through the control elements in any dialog by pressing the Tab key. The following properties are available in this context in the control elements model:

;<tt>Model.Enabled (Boolean)</tt>:activates the control element
;<tt>Model.Tabstop (Boolean)</tt>:allows the control element to be reached through the Tab key
;<tt>Model.TabIndex (Long)</tt>:position of control element in the order of activation

Finally, the control element provides a <tt>setFocus</tt> method that ensures that the underlying control element receives the focus:

;<tt>setFocus</tt>:control element receives the focus (only for dialogs)

== Multi-Page Dialogs ==

A dialog in {{OOo}} can have more than one tab page. The <tt>Step</tt> property of a dialog defines the current tab page of the dialog whereas the <tt>Step</tt> property for a control element specifies the tab page where the control element is to be displayed.

The <tt>Step</tt>-value of 0 is a special case. If you set this value to zero in a dialog, all of the control elements are visible regardless of their <tt>Step</tt> value. Similarly, if you set this value to zero for a control element, the element is displayed on all of the tab pages in a dialog.

[[Image:documentation_basicguide_dlg_03.gif|none|thumb|500px|Designing Page 1 of the dialog]]

In the preceding example, you can also assign the <tt>Step</tt> value of 0 to the dividing line as well as the <tt>Cancel</tt>, <tt>Prev</tt>, <tt>Next</tt>, and <tt>Done</tt> buttons to display these elements on all pages. You can also assign the elements to an individual tab page (for example page 1).

The following program code shows how the <tt>Step</tt> value in event handlers of the <tt>Next</tt> and <tt>Prev</tt> buttons can be increased or reduced and changes the status of the buttons.

<source lang="oobas">
Sub cmdNext_Initiated

Dim cmdNext As Object
Dim cmdPrev As Object

cmdPrev = Dlg.getControl("cmdPrev")
cmdNext = Dlg.getControl("cmdNext")
cmdPrev.Model.Enabled = Not cmdPrev.Model.Enabled
cmdNext.Model.Enabled = False
Dlg.Model.Step = Dlg.Model.Step + 1

End Sub

Sub cmdPrev_Initiated

Dim cmdNext As Object
Dim cmdPrev As Object

cmdPrev = Dlg.getControl("cmdPrev")
cmdNext = Dlg.getControl("cmdNext")
cmdPrev.Model.Enabled = False
cmdNext.Model.Enabled = True
Dlg.Model.Step = Dlg.Model.Step - 1

End Sub
</source>

A global <tt>Dlg</tt> variable that references an open dialog must be included to make this example possible. The dialog then changes its appearance as follows:

[[Image:documentation_basicguide_dlg_05.gif|none|thumb|400px|Page 1]]

[[Image:documentation_basicguide_dlg_06.gif|none|thumb|400px|Page 2]]
{{Documentation/Tip|You can find an [[Going_further_with_Dialog_and_Component#Multi-Page_Dialog|other OOoBasic example here]].}}

== Dialogs supporting several languages ==

The strings of a Dialog can be localized, see the Developer's Guide chapter [[Documentation/DevGuide/Basic/Dialog_Localization|Dialog Localization]].

{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Properties}}
{{PDL1}}

Documentation/BASIC Guide/Properties

2010-09-11T13:45:35Z

Ottoshmidt:

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Working With Dialogs
|NextPage=Documentation/BASIC Guide/Events
|dial=block
}}
{{DISPLAYTITLE:Properties}}
__NOTOC__
== Name and Title ==

Every control element has its own name that can be queried using the following model property:

;<tt>Model.Name (String)</tt>:control element name

You can specify the title that appears in the title bar of a dialog with the following model property:

;<tt>Model.Title (String)</tt>:dialog title (only applies to dialogs)

== Position and Size ==

You can query the size and position of a control element using the following properties of the model object:

;<tt>Model.Height (long)</tt>:height of control element (in ma units)
;<tt>Model.Width (long)</tt>:width of control element (in ma units)
;<tt>Model.PositionX (long)</tt>:X-position of control element, measured from the left inner edge of the dialog (in ma units)
;<tt>Model.PositionY (long)</tt>:Y-position of control element, measured from top inner edge of the dialog (in ma units)

To ensure platform independence for the appearance of dialogs, {{OOo}} uses the '''Map AppFont (ma)''' internal unit to specify the position and size within dialogs. An ma unit is defined as being one eighth of the average height of a character from the system font defined in the operating system and one quarter of its width. By using ma units, {{OOo}} ensures that a dialog looks the same on different systems under different system settings.

If you want to change the size or position of control elements for runtime, determine the total size of the dialog and adjust the values for the control elements to the corresponding part ratios.

{{Documentation/Note|The Map AppFont (ma) replaces the Twips unit to achieve better platform independence.}}

== Focus and Tabulator Sequence ==

You can navigate through the control elements in any dialog by pressing the Tab key. The following properties are available in this context in the control elements model:

;<tt>Model.Enabled (Boolean)</tt>:activates the control element
;<tt>Model.Tabstop (Boolean)</tt>:allows the control element to be reached through the Tab key
;<tt>Model.TabIndex (Long)</tt>:position of control element in the order of activation

Finally, the control element provides a <tt>getFocus</tt> method that ensures that the underlying control element receives the focus:

;<tt>setFocus</tt>:control element receives the focus (only for dialogs)

== Multi-Page Dialogs ==

A dialog in {{OOo}} can have more than one tab page. The <tt>Step</tt> property of a dialog defines the current tab page of the dialog whereas the <tt>Step</tt> property for a control element specifies the tab page where the control element is to be displayed.

The <tt>Step</tt>-value of 0 is a special case. If you set this value to zero in a dialog, all of the control elements are visible regardless of their <tt>Step</tt> value. Similarly, if you set this value to zero for a control element, the element is displayed on all of the tab pages in a dialog.

[[Image:documentation_basicguide_dlg_03.gif|none|thumb|500px|Designing Page 1 of the dialog]]

In the preceding example, you can also assign the <tt>Step</tt> value of 0 to the dividing line as well as the <tt>Cancel</tt>, <tt>Prev</tt>, <tt>Next</tt>, and <tt>Done</tt> buttons to display these elements on all pages. You can also assign the elements to an individual tab page (for example page 1).

The following program code shows how the <tt>Step</tt> value in event handlers of the <tt>Next</tt> and <tt>Prev</tt> buttons can be increased or reduced and changes the status of the buttons.

<source lang="oobas">
Sub cmdNext_Initiated

Dim cmdNext As Object
Dim cmdPrev As Object

cmdPrev = Dlg.getControl("cmdPrev")
cmdNext = Dlg.getControl("cmdNext")
cmdPrev.Model.Enabled = Not cmdPrev.Model.Enabled
cmdNext.Model.Enabled = False
Dlg.Model.Step = Dlg.Model.Step + 1

End Sub

Sub cmdPrev_Initiated

Dim cmdNext As Object
Dim cmdPrev As Object

cmdPrev = Dlg.getControl("cmdPrev")
cmdNext = Dlg.getControl("cmdNext")
cmdPrev.Model.Enabled = False
cmdNext.Model.Enabled = True
Dlg.Model.Step = Dlg.Model.Step - 1

End Sub
</source>

A global <tt>Dlg</tt> variable that references an open dialog must be included to make this example possible. The dialog then changes its appearance as follows:

[[Image:documentation_basicguide_dlg_05.gif|none|thumb|400px|Page 1]]

[[Image:documentation_basicguide_dlg_06.gif|none|thumb|400px|Page 2]]
{{Documentation/Tip|You can find an [[Going_further_with_Dialog_and_Component#Multi-Page_Dialog|other OOoBasic example here]].}}

== Dialogs supporting several languages ==

The strings of a Dialog can be localized, see the Developer's Guide chapter [[Documentation/DevGuide/Basic/Dialog_Localization|Dialog Localization]].

{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Properties}}
{{PDL1}}

Talk:Documentation/BASIC Guide/Properties

2010-09-11T13:45:10Z

Ottoshmidt: Created page with '''getfocus'' method gives error and ''setfocus'' works. So I changed it --~~~~'

''getfocus'' method gives error and ''setfocus'' works. So I changed it --[[User:Ottoshmidt|Ottoshmidt]] 13:45, 11 September 2010 (UTC)

Documentation/How Tos/Using SQLite With OpenOffice.org

2010-09-03T16:51:33Z

Ottoshmidt:

=Introduction=
OpenOffice Base provides functions to connect to other databases than the default HSQL database. This Tutorial should guide you through the steps to connect OpenOffice to a [http://www.sqlite.org/ SQLite] database file.

==What is SQLite?==

[http://www.sqlite.org/ SQLite] is a basic database engine that implements most of the features of SQL92.
Unlike PostgreSQL and MySQL, SQLite stores a whole data base with all its tables a single file. Other
benefits are: database access requires no database server, database files can be freely shared between
machines with different byte orders and databases can be up to 2 terabytes (2<sup>41</sup> bytes) in size.
Plus it is fast (twice as much as PostgreSQL and MySQL for most operations) and has a small memory footprint.

Data management can be achieved in the following ways:
# Via a C/C++ Linux library or Windows DLL.
# Via an in-line program (sqlite: available under Linux and Windows) that makes it possible to create and to manage the files of data bases.
# Via the SQLite PHP module or, if you have, PHP version 5 internally to a SQLite database.
# Via ODBC (Linux and Windows) which allows any application supporting this standard to reach a SQLite database.
# Using the [http://dba.openoffice.org/drivers/sqlite/index.html experimental SDBC SQLite driver]

This guide addresses ODBC which OpenOffice.org uses to attach to databases and SQLite, in particular.

==Why use SQLite with OpenOffice.org?==

The use of SQLite has many advantages:

* The data base is in only one file, in contrast to dBASE which creates a file per table, making it easy to exchange databases between various users.
* Java JRE or SDK is not required to support SQLite.
* No server is needed (local or remote) to access a database.
* ODBC drivers exist for *NIX and for Windows, thus enabling users of both environments to access data and exchange them.
* A [http://dba.openoffice.org/drivers/sqlite/index.html new SDBC driver] (native to OpenOffice.org) is available for experimentation.
* Sophisticated graphic interfaces, such as those of msaccess, can be easily developed using the forms, queries, states and macros of OpenOffice.org. These interfaces are stored separately in OpenOffice .odb files and can be exchanged independent from the data between users under UNIX or Windows.

and some disadvantages:

* Modifying the structure of a SQLite table after its creation is not directly possible. This means that you cannot easily add fields nor to modify their properties once that the table is created. Therefore you must prepare the structure of your tables before creating them.
* OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice.org. The native SDBC driver should correct this situation when it is production ready.
* The initial database file cannot be created with OpenOffice.org but by using other tools.

In short, the principal advantages of the use of SQLite with OpenOffice.org are the format of data storage and the portability from one environment to another. In the list of disadvantages, the two last are not specific to SQLite, but are requirements of using ODBC. However, these are not as awkward as they do not take place during the initial creation of the database.

{{Documentation/Note|SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.}}

=Installation=

{{Documentation/Linux|'''Under Linux'''}}
==ODBC drivers==
Connection to a SQLite database will be established through ODBC drivers. ODBC drivers for SQLite are independent of the SQLite project.

You can install <tt>unixodbc</tt> with your packetmanager - or download the sources from the [http://www.unixodbc.org/ unixODBC] website. The <tt>unixodbc-dev</tt> and <tt>unixodbc-bin</tt> packages will also be required to complete the configuration for OpenOffice.

sudo apt-get unixodbc unixodbc-dev unixodbc-bin

==SQLite==
Obviously you will need to install [http://www.sqlite.org SQLite] if it is not installed already as this will be the database you want to connect to.

There will likely be a version in the repositories although it might be dated. You can always [http://www.sqlite.org/download.html download] and install the latest version from source.

==sqliteODBC==
For SQLite to work through the ODBC standards you will also need to install and configure a special set of drivers.

There are no precompiled packages so you will need to [http://ch-werner.de/sqliteodbc/ download the source] and compile it yourself:

tar xvzf sqliteodbc-0.''XX''.tar.gz
./configure –prefix=/usr
make
sudo make install

<tt>--prefix=/usr</tt> is optional, but is especially useful with Mandrake distributions, to install the driver in <tt>/usr/lib</tt> rather than in the default location, <tt>/usr/local/lib</tt>.

{{Documentation/Note|If you get an error message claiming ''"ODBC header files and/or libraries not found"'' check that you have installed the <tt>unixODBC-dev</tt> package while installing the [[#ODBC_drivers|ODBC drivers]].}}

To complete the installation the driver has to be integrated into unixODBC. To do this, launch the ODBCConfig utility as root in an terminal window:

sudo ODBCConfig

{{Documentation/Note|If ODBCConfig is not found check that you also installed the <tt>unixODBC-bin</tt> package while installing the [[#ODBC_drivers|ODBC drivers]].}}

Now the a GUI to manage the configuration shows up.
[[Image:doc_howto_sqlite_odbcadmin.png|390px]]

Click the <tt>Drivers</tt> tab and choose <tt>Add...</tt> Then fill the fields as follows:

Name: SQLite
Description: SQLite for ODBC
Driver: /usr/lib/libsqlite3odbc.so
Driver64:
Setup: /usr/lib/libsqlite3odbc.so
Setup64:

{{Documentation/Note|If you are still using a 2.8.x version of libsqlite the driver names will be different. Try browsing the folder by clicking on the arrow at the right and select one that seems suitable.}}

Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the <tt>Drivers</tt> tab:
[[Image:doc_howto_sqlite_odbcadmin-2.png|450px]]

The driver installation under Linux is now finished and you can exit <tt>ODBCConfig</tt>.

For those who do not have <tt>ODBCConfig</tt> or who prefer to carry out this configuration manually, it is necessary to edit the file <tt>/etc/odbcinst.ini</tt> as root and add the following lines:
<nowiki>[SQLite]</nowiki>''
Description<nowiki>= ODBC for SQLite</nowiki>''
Driver<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
Setup<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
FileUsage<nowiki>= 1</nowiki>''
CPTimeout<nowiki>= </nowiki>''
CPReuse<nowiki>= </nowiki>''

==Under Windows==
{{Documentation/Windows|Under Windows the installation and configuration is much easier. Simply [http://ch-werner.de/sqliteodbc/ download the sqliteODBC.exe], the installer will automatically setup the ODBC drivers including the SQLite configurations for it and also provides you with the sqlite command line tool to create and modify SQLite databases.}}

=Setup of SQLite database=

==Creating a SQLite database file==
An initial database file cannot be created directly in OpenOffice.org but rather this is accomplished by using either the utility “<tt>sqlite</tt>” on the command line or by using a graphical management tool, a list of GUIs is available at [http://www.sqlite.org/cvstrac/wiki?p=ManagementTools SQLite.org]

The utility "<tt>sqlite</tt>" should be already installed if you use Linux and have installed SQLite from rpm. For other *NIX systems, you must build SQLite from source which you can find at [http://www.sqlite.org/download.html]

Performance and usage under Linux and Windows is identical.

===Using sqlite===
{{Documentation/Linux|Open a terminal window and change to the directory where you want to create your database. Run <tt>sqlite</tt> with the filename of your database as argument (e.g. ''$ sqlite mydatabase''}}
{{Documentation/Windows|Run <tt>sqlite</tt> from the start menu or where it was installed. A standard file open dialog box will open and ask you to select a database file.}}
If this database file does not exist yet it will be created automatically. Using <tt>sqlite</tt> you can also create tables and modify your database with standard SQL-commands:

<nowiki>$ sqlite <filename of database></nowiki>
SQLite version 2.8.12
Enter ".help" for instructions
sqlite> CREATE TABLE mytable (id INT, text VARCHAR(100));
sqlite> .exit

{{Documentation/Note|You can then check that the file was, indeed, created in your filesystem. The name of the file does not require a file extension, but you can give it the extension <tt>db</tt> so that is easy to locate as a database.}}

Using <tt>sqlite</tt> on the command line program makes it possible to create tables and indices, to enter and post data and
to make queries provided you know the SQL language - however, it is more practical for beginners to carry out these operations later on using
OpenOffice.

===Using SQLite Database Browser===
{{Documentation/Caution|This information might be outdated.}}

After unpacking the file, launch <tt>sqlitebrowser</tt> and select <tt>File/New Database</tt> or the corresponding icon:

[[Image:doc_howto_sqlite_dbbrowser.png|410px]]

Choose the directory in which to place file and then enter name of the database file and click on <tt>Save</tt>.

Using this program, you can also create the tables, the fields and the indices, to access data and to visualize them. While
<tt>sqlitebrowswer</tt> is suitable for these actions, there is the disadvantage that it does not show the full list of field types during
field creation. In this case, it may be preferable to do it later using OpenOffice.org.

==ODBC-setup of new database==
After creating a new SQLite database above you now have to configure this database in ODBC so you can connect to it in OpenOffice later on.

{{Documentation/Linux|'''Under Linux:'''
Launch <tt>ODBCConfig</tt> again as normal user and go to the tab <tt>User DSN</tt>. Here add a new entry. A window appears in which you must choose
the <tt>SQLite</tt> driver you have configured earlier. In the next window, as shown below, you enter a name for this connection (for example the name of your database) and the complete access path to the database file which you created previously.

[[Image:doc_howto_sqlite_driverproperties-2.png|380px]]

Validate the data on the screen, click the checkmark and you should see your new data source in the tab <tt>User DSN</tt>.

If you do not have <tt>ODBCConfig</tt> or simply prefer to configure manually, launch your favorite editor, open (or create)
the file <tt>odbc.ini</tt> in your home directory, and add the following lines to it:

<nowiki>[My Base]</nowiki>
Description<nowiki>= My test database</nowiki>
Driver<nowiki>= SQLite</nowiki>
Database<nowiki>= /home/<user>/basename.db</nowiki>
Timeout<nowiki>= 1000</nowiki>
StepAPI<nowiki>= No</nowiki>

{{Documentation/Note|If this data source must by other users on the system, launch <tt>ODBCConfig</tt> as root and add this source in the tab <tt>System DSN</tt> or <tt>data Source system</tt>, or create a <tt>/etc/odbc.ini</tt> file.}}

You are now ready to access your data base with OpenOffice.org!}}

{{Documentation/Windows|'''Under Windows:'''
You will reach the configuration through the OpenOffice dialog in the next step, just read on.}}

=Connecting OpenOffice to a SQLite database=
You had to spend a lot of time installing and configuring other things but finally we can move on to OpenOffice. Only a few more configuration windows and you will be done.

Start ''OpenOffice Base'' and in the first step '''1. Select database''' chose the third option, connect to an existing database using ODBC.

Connect to an existing database
ODBC

In the second step '''2. Set up ODBC connection''' you can select your SQLite database. Clicking on ''Browse'' you should get a list of all databases you have [[#ODBC-setup of new database|configured]] in ODBC.

If nothing is showing up here you have a problem. Check that you actually [[#ODBC-setup of new database|set up the database in ODBC]] and that the drivers are properly configured as described.

{{Documentation/Windows|'''Under Windows:'''
Before you will find your database listed here you need to register it with ODBC as mentioned above. Click on ''Organize...'' in the bottom right of the window.

[[Image:doc_howto_sqlite_odbcdriver-windows-1.png]]

In the ''User DSN'' (or the ''System DSN'') tab click on ''Add...'' to setup your database file.

[[Image:doc_howto_sqlite_odbcdriver-windows-2.png]]

Give your database a meaningful name as ''Data Source Name'' - this will be the name you find your database listed as. For ''Database Name'' browse to the SQLite database file you have created earlier. Leave the rest on its default values and press ok.

[[Image:doc_howto_sqlite_odbcdriver-windows-3.png]]

The database will now be listed in the ''ODBC Data Source Administrator'' and leaving this window you should now also be able to select this database in the ''OpenOffice Base'' dialog.
}}

=Working on the database with OpenOffice=
The rest is ''OpenOffice Base'' just as you know it.

Only note a few things:
Creating new tables using OpenOffice is possible. Those tables - along with all data you enter in them - are saved in the external SQLite database file to which you connected your OpenOffice .odb file.

Fields defined as type "integer" are auto-increment, that is they automatically increment the value of the field when adding a new record in this table.

{{Documentation/Caution|Once you have saved your table, you cannot modify it! I.e. you will not be able to modify the properties of the fields any more, nor add or remove!}}

This rather awkward constraint is specific to SQLite, which does not handle the SQL command ALTER COUNTS, and not the ODBC driver. This limitation is designed in by the authors in order to make the basic database engine as light as possible. However, this limitation may be eliminated in a future version. Therefore you are advised to check your table structure very carefully before recording it.

If you must modify a table, the only solution is to create a new table and save it under different name. By doing do, you can transfer the data from the original table to the new table.

Creating and using Forms, Queries and Reports are all independent of the datasource you use. They will function just as with the default integrated HSQL-database and will still be saved in your odb-file. For more information on these topics refer to the [[Base|other documentation]].

{{Documentation/Caution|If you want to use subforms you need to activate "parameter substitution" first.
Select "Edit/Database/Advanced Settings..." in the main menu of your OpenOffice Base window. There, in the "Special Settings" tab check the option "Replace named parameters with '?'". You might need to restart OpenOffice but then you should be able to use subforms without problems.}}

=Exchanging databases between users=

Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice.org, of course!), a SQLite
database SQLite can be easily exchanged between users of the two environments. It is necessary that each has an installed
ODBC driver that is aware of the target SQLite file and which is defined as a data source in OpenOffice.org. Likewise OpenOffice Base files (.odb) containing forms and macros can also be exchanged between users of the two environments.

{{Documentation/Caution|If forms are to function correctly for each user, it is absolutely necessary that each user applies exactly the same name to the data source during its creation.}}

{{PDL1}}

[[Category:Database]]
[[Category:Documentation/How Tos/Database]]

Talk:Documentation/BASIC Guide/Control Element Forms

2010-03-30T07:24:31Z

Ottoshmidt:

== Option Button grouping method ==

The code described in this article used to group Option Buttons returns an error:

[[File:Lens_diff.png‎]]

this error is caused by the following line:

<source lang="oobas">
Ctl = Form. GetGroupbyName("MyOptions")
</source>

However I found this method that works fine:

<source lang="oobas">
Function test
Dim oGroup as object
Dim Doc As Object
Dim Forms As Object
Dim Form As Object
Dim Ctl As Object
Dim I as Integer

Doc = ThisComponent
Forms = Doc.Drawpage.Forms

Form = Forms.GetbyIndex(0)

oForm.getGroupByName("MyOptions", oGroup)
msg=""
for i=0 to UBound(oGroup)
oCtl = oGroup(i)
msg = msg + oCtl.Name + " " + (str(oCtl.State)) + chr(10)
next i
MsgBox msg

end Function

</source>

See: [http://www.oooforum.org/forum/viewtopic.phtml?t=28446] and [http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=12517]

--[[User:Ottoshmidt|Ottoshmidt]] 07:24, 30 March 2010 (UTC)

Talk:Documentation/BASIC Guide/Control Element Forms

2010-03-30T07:24:02Z

Ottoshmidt: Created page with '== Option Button grouping method == The code described in this article used to group Option Buttons returns an error: File:Lens_diff.png‎ this error is caused by the fo…'

File:Lens diff.png

2010-03-30T07:17:07Z

Ottoshmidt: error message box

error message box

Connect MySQL and Base

2010-03-26T14:19:48Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and/etc/odbc.ini should have (or create ~/.odbc.ini so that you can modify it as normal user):

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

In above configuration we assumed that database named "test" exists on local server. Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Using JDBC on OS X ==
=== Installation ===

1) Download MySQL Connector/J from [http://www.mysql.com/downloads/ MySQL Downloads]

Choose '''Platform independent'''
Choose '''Zip File''' or '''Tar File''' (either will do)

2) Double click file to extract

3) Make a directory under your user name (I use a combination of connectorj and the version number.)

4) Copy the contents of the zip file into this new directory.

=== OpenOffice.org settings ===

Set up OpenOffice.org for use via JDBC (Warning! This will require a restart of OpenOffice.org!):

OpenOffice.org > Perferences > Java

Click "CLASSPATH"

Click "Add Archive"

Browse to the jar file from the zip/tar file (mysql-connector-java-x.x.x-bin.jar where x.x.x is the version number).

Click OK. OpenOffice.org will warn a restart of the application is required.

=== Setting up a database for use ===

''These instructions assume the use of an existing database.''

Start OpenOffice.org and choose a file type of "Database."

Click the radio button "Connect to an existing database" with the drop down set to JDBC.

Click Next.

In the URL following jdbc: fill in with the proper settings in the format mysql://servername:port/databasename (if default port, you can ignore the port portion.)

Fill in the Driver Class with '''com.mysql.jdbc.Driver''' and click "Test Class." You should see a message "The JDBC driver was loaded successfully."

Click Next.

Enter the user name to connect to the DB with. If a password is required, check the "Password required" box.

Click "Test Connection" and it should prompt with the user name and a password prompt. You should see a pop-up saying "The connection was established successfully."

Click Next.

Best to leave the defaults on this next screen set.

Click Finish.

You will be prompted on where to put the database configurations for use with OpenOffice.org. (I usually make a small directory in my Documents folder.)

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-26T19:18:39Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and/etc/odbc.ini should have (or create ~/.odbc.ini so that you can modify it as normal user):

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

In above configuration we assumed that database named "test" exists on mysql-server. Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-22T14:31:46Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have (or create ~/odbc.ini so that you can modify it as normal user):

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-22T14:31:28Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have (or create ~/etc/odbc.ini so that you can modify it as normal user):

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T20:13:16Z

Ottoshmidt: /* Using ODBC on Windows */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input requires data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T14:02:30Z

Ottoshmidt:

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and from the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input required data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click Browse and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T14:01:24Z

Ottoshmidt: /* Using ODBC on Windows */ (checkpoint save)

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website] and install it on your system. Then:

1) Navigate to Control Panel > Administrative Tools and double-click on Data Sources (ODBC)

2) on the User DSN tab Click Add button and From the list of available drivers, select the MySQL ODBC Driver, and click OK.

3) Input required data in the appeared dialog:

Data Source Name: it's optional. Let it be "Mysql-test" in our case.

Description: is optional as well.

Server: '''localhost''' (in case mysql server is installed on the local machine, or IP Address if it's on network machine)

fill out user and password fields respectively. You should be able to see mysql databases under Database drop-down list, choose one. Click OK.

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T13:36:02Z

Ottoshmidt: /* Using ODBC on Windows */ (checkpoint save)

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

Obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:34:12Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://dev.mysql.com/downloads/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:30:05Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed) or obtain [http://www.mysql.com/products/connector/odbc/ mysql-connector-odbc] from the [http://www.mysql.com MySQL website].

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:21:31Z

Ottoshmidt:

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed).

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:17:31Z

Ottoshmidt: /* Debian/Ubuntu */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed).

You need to have 2 files configured:

1) /etc/odbcinst.ini should contain:

[MySQL]

Description = MySQL driver

Driver = /usr/lib/odbc/libmyodbc.so

Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]

Description = MySQL database test

Driver = MySQL

Server = localhost

Database = test

Port = 3306

Socket =

Option = 3

ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.

4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.

5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:14:05Z

Ottoshmidt: /* Using ODBC on Linux */

Here is a tutorial to connect MySQL database with OpenOffice.org Base.

OpenOffice.org comes directly with support of JDBC connectivity to multiple databases. OpenOffice.org needs [http://www.java.com/en/download/index.jsp Java Runtime Environment] to be able to use JDBC. Verify that you have JRE on your OOo by going to '''Tools - Options - OpenOffice.org General - Java'''.

== Using JDBC on Windows ==
=== Installation ===
First, download and unzip the java archive. Obtain the [http://www.mysql.com/products/connector/j/ mysql-connector-J] from the [http://www.mysql.com MySQL website]. Follow the instructions on the [http://www.mysql.com MySQL website]to find and then download a gzipped and zipped set of files to a convenient location on the local computer. The target file will be named something like "mysql-connector-java-x.x.x.zip." Following the appropriate method for your zip program, unzip the downloaded file to a permanent location, for example, c:\Program Files\mySQL-Connector\. (NOTE: In Windows systems, double-clicking a zipped file usually gives access to the contents of the zip file, whereupon you may select from the resulting explorer window "Extract Files.")

Next, make OOo aware of the newly downloaded java archive. Start OpenOffice.org and on the menu select '''Tools - Options - OpenOffice.org - Java'''. Click on the button '''Class Path...'''; then click on the button '''Add Archive...'''; and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located (see previous step). Click on the mysql-connector-x.x.x-java-bin.jar file once to highlight and click on the button '''Open'''. The mysql-connector-x.x.x-java-bin.jar file should then appear in the list of '''Assigned folders and Archives'''. Click on '''OK''' to close the dialog. OpenOffice.org will notify you that you need restart the program for the changes to take effect.

Finally, restart OOo and check functionality. Close OpenOffice.org '''completely''', that is, close all open OOo windows and shut down the quickstarter program located in the system tray (if it is running). Restart OpenOffice.org, and the JDBC connector should then be available and functioning.

=== Operation ===
See Operation below under Linux.

== Using ODBC on Windows ==

== Using JDBC on Linux ==
=== Installation ===
Download the [http://www.mysql.com/products/connector/j/ mysql-connector-J] which you can download from the [http://www.mysql.com MySQL website]. [http://www.mysql.com/products/connector/j/ MySQL Connector/J] adds JDBC connectivity to MySQL. Unpack, untar or unzip the downloaded file anywhere where you can access it as a normal user, for example, to your desktop. On KDE, you can use Ark, on Gnome FileRoller, or even the Nautilus scripts that are included in some Linux distributions, or the usual "tar -zvf" from a console. A directory is created at the desired location named mysql-connector-java-x.x.x, where the x.x.x represents the version number of the JDBC connector you have downloaded. Inside this directory, you will find a file called mysql-connector-x.x.x-java-bin.jar.

Start OpenOffice.org and go to the menu '''Tools - Options - OpenOffice.org General Java'''. Click on the button '''Class Path...''', then choose '''Add Archive...''' and navigate to where the mysql-connector-x.x.x-java-bin.jar file is located. Click on '''Open''', and the file should then appear in the list of '''Assigned folders and Archives'''. You can then click on '''OK''' and close OpenOffice.org completely. You may receive a message indicating that you have to do that anyway in order for OpenOffice.org to be able recognize your changes correctly. If the OpenOffice.org quickstarter is running, you should close that application as well.

Instead of downloading the connector from the MySQL download site, you can also use the corresponding packages of your distribution like
* libmysql-java for debian and ubuntu

Note that the use of the MySQL Java Connector requires MySQL to be installed and running on your Linux system. On Redhat/Enterprise Linux, MySQL can be installed and made to run via the following commands in a terminal;

::su root
::yum install mysql-server mysql
::cd /sbin
::./chkconfig mysqld on
::/etc/init.d/mysqld start

=== Operation ===

When you restart OpenOffice.org after installing the MySQL Java connect driver, you should now be able to create a new OpenOffice.org Base database document that can connect to your existing mysql database. Note that you will need to have first created your database on your mysql server.

:: One method to do this requires using the mysql command line client;
:::mysqladmin -u root password NEWPASSWORD
:::mysql -u root -p
:::CREATE DATABASE newdatabasename;
:::CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'password';
:::GRANT ALL ON *.* TO 'newusername'@'localhost';

:: Another method to do this requires the use of a graphical user interface to administer your mysql server (for example PHPMyAdmin).

Now do the following :

1) '''File > New > Database'''

2) Click on the radio button '''Connect to an existing database''' and choose '''MySQL''' from the dropdown menu. Click on '''Next'''.

3) In the next dialog, you should accept the default choice of '''Connect using JDBC''', and then click on '''Next'''.

4) The next step varies depending upon the exact version of openoffice.org installed.

::If the fields '''Name of the database''' and '''Server URL''' are present, perform the following;

:::In the '''Name of the database''' field, enter the name of your mysql database as it is called on the mysql server, paying attention to the spelling.
:::In the '''Server URL''' field, enter either the fully qualified internet domain name of the server on which your mysql server is running, or its IP address or, if you are running the mysql server locally on your own computer, simply type localhost. You can adjust the connection port or leave default port 3306 in the field '''Port number'''. An example Server URL would be: localhost, 192.168.1.1, or 96.115.86.166, or something like that.

::If only the field '''Data Source URL''' is present perform the following;

:::In the '''Data Source URL''' field, enter a combination of the servername, port (optional), and data base name, in the following format; jdbc:mysql://serverAddress:3306/databaseName (or jdbc:mysql://serverAddress/databaseName without the port number). An example Server Address would be: '''localhost''' (if mysql server is installed on local computer), 192.168.1.1, or 96.115.86.166, or something like that if mysql server is installed on network computer.

::For the field '''MySQL JDBC driver class:''' enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is '''com.mysql.jdbc.Driver''' for version 5.1.6. Click on '''Test Class''' just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says '''The JDBC driver was loaded correctly'''. Now click on '''Next'''.

5) Now enter the name of the user and the password (if required by the mysql server) that you have previously created for use of the database on the mysql server. If you click on '''Test Connection''', you will be asked for a password if you have ticked the password box beforehand. If the connection to the database succeeds, you will get a message '''Connection test: The connection was established successfully'''. Click on '''Next'''. If you get an error indicating a character conversion error, edit both the [client] and [mysqld] sections of my.cnf to include default-character-set=utf8 in both sections. Restart the mysql server.

6) On the final screen of the database wizard dialog, you can in general leave the default settings as they are, i.e. '''Yes, register the database for me''' and '''Open the database for editing'''. Click on '''Finish'''.

7) You will be asked for a file name to give to your database document. Once you have done this and clicked on '''Save''', the database document will open, and the Forms button on the left will be highlighted. Congratulations, you have now connected to your MySQL database via the JDBC connector.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

== Using ODBC on Linux ==
OpenOffice.org also support ODBC connectivity to MySQL, you will need [http://www.unixodbc.org/ Unix-ODBC] in order to make a connection through it.

=== Debian/Ubuntu ===

Install package libmyodbc from synaptic package manager (assuming package unixodbc is already installed).

You need to have 2 files configured:
1) /etc/odbcinst.ini should contain:

[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so

2) and /etc/odbc.ini should have:

[MySQL-test]
Description = MySQL database test
Driver = MySQL
Server = localhost
Database = test
Port = 3306
Socket =
Option = 3
ReadOnly = No

Then, Establishing connection by Creating New Base file:

1) File > New > Database

2) Click on the radio button Connect to an existing database and choose MySQL from the dropdown menu. Click on Next.

3) In the next dialog, you should accept the default choice of Connect using ODBC, and then click on Next.
4) Click '''Browse''' and select created connection ([MySQL-test] should be in the list in our case) and next.
5) then enter username and Finish. Save file.

For other Linux distros procedure should be pretty much the same in my opinion. You should just know the installation locations for files: libmyodbc.so and libodbcmyS.so, and replace them in the example above.

== Recommendations settings ==

Known issues regarding MySQL and OpenOffice.org can be avoided by having specific settings here are some of those:

=== MySQL settings ===

* Make sure that MySQL is using TCP/IP as opposed to sockets. Edit the my.cnf file at /etc/ and comment '''skip-networking'''. NOTE: In Ubuntu my.cnf is located in /etc/mysql

=== OpenOffice.org settings ===

* OpenOffice.org uses Java for JDBC, please make sure you have JRE at Tools - Options - OpenOffice.org General - Java
* Declare the '''mysql-connector-j.bin''' at the classpath at Tools - Options - OpenOFfice.org General - Java
* MySQL and JDBC on Debian Testing: Package sun-java6-jre from the non-free repository is needed and has to be activated in "Options-> OpenOffice.org -> Java". The FSF java environment will not work returning a SQL syntax code whenever connecting to the MySQL server (July 27th 2008).

[[Category:Database]]

Connect MySQL and Base

2010-01-21T12:05:05Z

Ottoshmidt: /* Operation */

Database/Drivers/MySQL Native/Beta

2009-07-31T15:11:04Z

Ottoshmidt:

{{DISPLAYTITLE:Sun MySQL Connector/OOo (Beta)}}
== Introduction ==

MySQL Connector/OpenOffice.org is a MySQL driver for [http://www.openoffice.org/ OpenOffice.org]. It can be used to connect from OpenOffice.org 3.1 to a MySQL server 5.1 or newer.

Before MySQL Connector/OpenOffice.org became available you'd have to use [http://dev.mysql.com/downloads/connector/j/ MySQL Connector/J (JDBC)] or [http://dev.mysql.com/downloads/connector/odbc/ MySQL Connector/ODBC] to connect to a MySQL server.

Therefore the driver is also referred to as a native driver in the context of OpenOffice.org. The term native driver clashes with the MySQL definition of a native driver. MySQL Connector/OpenOffice.org does not implement the MySQL Client Server protocol. Technically speaking the MySQL Connector/OpenOffice.org is implemented as a proxy on top of the [http://dev.mysql.com/doc/refman/5.1/en/connector-cpp.html MySQL Connector/C++].

The driver is delivered as an OpenOffice.org extension.

=== Advantages ===

Using MySQL Connector/OpenOffice.org has the following advantages:

* Easy installation through the OpenOffice.org Extension Manager.
* Seamless integration into OpenOffice.org.
* You can work on multiple MySQL schemata (databases) simultaneously
* No need to go through an additional Connector installation routine (ODBC/JDBC)
* No need to configure or register an additional Connector (ODBC)
* No need to install or configure a driver manager (ODBC)
* No need for a Java Runtime Environment (JDBC)

=== Status ===

Please note, the MySQL Connector/OpenOffice.org is released as a beta version. We do not recommend using the driver in production environments or systems with critical data. Please note, official support is not available.

== Installation ==

=== Install or upgrade to OpenOffice.org 3.1 ===

OpenOffice.org 3.1 is available from OpenOffice.org's [http://download.openoffice.org download site]. The driver does not work together with any other OpenOffice.org version.

Please use "vanilla" OpenOffice.org: As with all extensions containing binary code, they're potentially incompatible with other OpenOffice.org distributions (like the one from your favorite Linux distribution), and are not necessarily expected to work with them.

Caution: The installation wizard of OpenOffice.org 3.1 will remove older OpenOffice.org installations from your system by default. If you do not want this, check out for the corresponding settings of the installation wizard.

=== Download MySQL Connector/OpenOffice.org ===

The extension is available from the [http://extensions.services.openoffice.org/project/mysql_connector OpenOffice.org extensions repository]. It is provided for the usual 6 platforms (Windows, Linux Intel 32 Bit, Linux Intel 64 Bit, Solaris Sparc, Solaris Intel 32 Bit, Mac OS X Intel).

Download the file to a location of your choice, for example <code>My Documents</code> or <code>~/Documents</code>.

=== Installing the extension ===

Add the <code>.oxt</code> extension through the Extension Manager of OpenOffice.org. In OpenOffice.org, select <code>Tools, Extension Manager...</code> and specify the <code>.oxt</code> file as a new extension. When done, MySQL Connector/OpenOffice.org should show up as a new extension under <code>My Extensions</code>.
: <br/>[[Image:Conn_ooo_alpha_extensions.png]]

Restart OpenOffice.org to complete the installation.

== Getting Started: Connecting to MySQL ==

MySQL Connector/OpenOffice.org allows you to access the MySQL Server and its schemata from the OpenOffice.org suite.

The following example demonstrates the creation of a new OpenOffice.org Base database which uses a local MySQL Server for storage and the new connector for connecting.

=== Select the database ===

Create a new database (<code>File, New, Database</code>). This starts a wizard that allows you to create a new, open an existing, or connect to existing database. Select the latter option.

From the drop-down list, select <code>MySQL</code>. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard1.png]]

=== Select the type of the driver ===

You can use three different MySQL drivers for connecting to MySQL from OpenOffice.org. You can choose a generic ODBC or JDBC connection method or the native driver. Select <code>Connect native</code>. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard2.png]]

=== Setting up the database connection ===

Set up the database connection. Put the schema (database) you want to connect to into <code>Name of the database</code>. Enter the URL (host name, or simply type <code>localhost</code> when connecting to the server on your own PC), Port and Socket (or leave socket empty) of your MySQL Server. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard3.png]]

=== Fill in user credentials ===

Provide the user credentials for connecting to your MySQL Server. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard4.png]]

=== Save and proceed ===

Finish the wizard. All defaults are a safe choice. Click <code>Finish >></code>.

[[Image:Conn_ooo_alpha_wizard5.png]]

== Getting Started: Usage Examples ==

=== Listing Tables ===

In the Database area of the Base main window, select Tables. If this is the first time you're accessing the database you'll be prompted for your credentials (username and password); you can store these settings for your current Base session.

[[Image:Conn ooo alpha base.png]]

Depending on your connection settings you will now see all databases with all their tables, or just the database you've specified in the connection settings.

== Known Bugs ==

There are currently [[Database/Drivers/MySQL Native/Beta/Known Issues|3 known bugs]] in the Beta version of the driver, which will be fixed 'till the final release.

If you discover a bug, please send an email to [mailto:users@dba.openoffice.org users@dba.openoffice.org]. It's a low-volume list with less than 10 mails per day. If you prefer reading it by means other than subscribing to it, see [[Base Mailing List|here]].

== Contact ==

To discuss the new MySQL Connector/OpenOffice.org, please subscribe to the mailing list [http://dba.openoffice.org/servlets/ProjectMailingListList users@dba.openoffice.org]. It is a low-volume list with less than 10 mails per day.

Database/Drivers/MySQL Native/Beta

2009-07-31T15:07:45Z

Ottoshmidt:

{{DISPLAYTITLE:Sun MySQL Connector/OOo (Beta)}}
== Introduction ==

MySQL Connector/OpenOffice.org is a MySQL driver for [http://www.openoffice.org/ OpenOffice.org]. It can be used to connect from OpenOffice.org 3.1 to a MySQL server 5.1 or newer.

Before MySQL Connector/OpenOffice.org became available you'd have to use [http://dev.mysql.com/downloads/connector/j/ MySQL Connector/J (JDBC)] or [http://dev.mysql.com/downloads/connector/odbc/ MySQL Connector/ODBC] to connect to a MySQL server.

Therefore the driver is also referred to as a native driver in the context of OpenOffice.org. The term native driver clashes with the MySQL definition of a native driver. MySQL Connector/OpenOffice.org does not implement the MySQL Client Server protocol. Technically speaking the MySQL Connector/OpenOffice.org is implemented as a proxy on top of the [http://dev.mysql.com/doc/refman/5.1/en/connector-cpp.html MySQL Connector/C++].

The driver is delivered as an OpenOffice.org extension.

=== Advantages ===

Using MySQL Connector/OpenOffice.org has the following advantages:

* Easy installation through the OpenOffice.org Extension Manager.
* Seamless integration into OpenOffice.org.
* You can work on multiple MySQL schemata (databases) simultaneously
* No need to go through an additional Connector installation routine (ODBC/JDBC)
* No need to configure or register an additional Connector (ODBC)
* No need to install or configure a driver manager (ODBC)
* No need for a Java Runtime Environment (JDBC)

=== Status ===

Please note, the MySQL Connector/OpenOffice.org is released as a beta version. We do not recommend using the driver in production environments or systems with critical data. Please note, official support is not available.

== Installation ==

=== Install or upgrade to OpenOffice.org 3.1 ===

OpenOffice.org 3.1 is available from OpenOffice.org's [http://download.openoffice.org download site]. The driver does not work together with any other OpenOffice.org version.

Please use "vanilla" OpenOffice.org: As with all extensions containing binary code, they're potentially incompatible with other OpenOffice.org distributions (like the one from your favorite Linux distribution), and are not necessarily expected to work with them.

Caution: The installation wizard of OpenOffice.org 3.1 will remove older OpenOffice.org installations from your system by default. If you do not want this, check out for the corresponding settings of the installation wizard.

=== Download MySQL Connector/OpenOffice.org ===

The extension is available from the [http://extensions.services.openoffice.org/project/mysql_connector OpenOffice.org extensions repository]. It is provided for the usual 6 platforms (Windows, Linux Intel 32 Bit, Linux Intel 64 Bit, Solaris Sparc, Solaris Intel 32 Bit, Mac OS X Intel).

Download the file to a location of your choice, for example <code>My Documents</code> or <code>~/Documents</code>.

=== Installing the extension ===

Add the <code>.oxt</code> extension through the Extension Manager of OpenOffice.org. In OpenOffice.org, select <code>Tools, Extension Manager...</code> and specify the <code>.oxt</code> file as a new extension. When done, MySQL Connector/OpenOffice.org should show up as a new extension under <code>My Extensions</code>.
: <br/>[[Image:Conn_ooo_alpha_extensions.png]]

Restart OpenOffice.org to complete the installation.

== Getting Started: Connecting to MySQL ==

MySQL Connector/OpenOffice.org allows you to access the MySQL Server and its schemata from the OpenOffice.org suite.

The following example demonstrates the creation of a new OpenOffice.org Base database which uses a local MySQL Server for storage and the new connector for connecting.

=== Select the database ===

Create a new database (<code>File, New, Database</code>). This starts a wizard that allows you to create a new, open an existing, or connect to existing database. Select the latter option.

From the drop-down list, select <code>MySQL</code>. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard1.png]]

=== Select the type of the driver ===

You can use three different MySQL drivers for connecting to MySQL from OpenOffice.org. You can choose a generic ODBC or JDBC connection method or the native driver. Select <code>Connect native</code>. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard2.png]]

=== Setting up the database connection ===

Set up the database connection. Put the schema (database) you want to connect to into <code>Name of the database</code> (simply type <code>localhost</code> when connecting to the server on your own PC). Enter the URL (host name), Port and Socket (or leave socket empty) of your MySQL Server. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard3.png]]

=== Fill in user credentials ===

Provide the user credentials for connecting to your MySQL Server. Click <code>Next >></code>.

[[Image:Conn_ooo_alpha_wizard4.png]]

=== Save and proceed ===

Finish the wizard. All defaults are a safe choice. Click <code>Finish >></code>.

[[Image:Conn_ooo_alpha_wizard5.png]]

== Getting Started: Usage Examples ==

=== Listing Tables ===

In the Database area of the Base main window, select Tables. If this is the first time you're accessing the database you'll be prompted for your credentials (username and password); you can store these settings for your current Base session.

[[Image:Conn ooo alpha base.png]]

Depending on your connection settings you will now see all databases with all their tables, or just the database you've specified in the connection settings.

== Known Bugs ==

There are currently [[Database/Drivers/MySQL Native/Beta/Known Issues|3 known bugs]] in the Beta version of the driver, which will be fixed 'till the final release.

If you discover a bug, please send an email to [mailto:users@dba.openoffice.org users@dba.openoffice.org]. It's a low-volume list with less than 10 mails per day. If you prefer reading it by means other than subscribing to it, see [[Base Mailing List|here]].

== Contact ==

To discuss the new MySQL Connector/OpenOffice.org, please subscribe to the mailing list [http://dba.openoffice.org/servlets/ProjectMailingListList users@dba.openoffice.org]. It is a low-volume list with less than 10 mails per day.

Talk:Connect MySQL and Base

2009-05-31T09:44:15Z

Ottoshmidt:

I cut "jdbc:mysql://serverAddress:3306/databaseName, just as one would code a connection in a Java application." from page because that would be relevant to JDBC connection and there is discussed an example when MySql is chosen in drop-down list. ottoshmidt, 31 May 2009

Talk:Connect MySQL and Base

2009-05-31T09:43:24Z

Ottoshmidt: New page: I cut "jdbc:mysql://serverAddress:3306/databaseName, just as one would code a connection in a Java application." from page because that would be relevant to JDBC connection and there is di...

Connect MySQL and Base

2009-05-31T09:41:06Z

Ottoshmidt: /* Operation */

Connect MySQL and Base

2009-05-31T09:40:18Z

Ottoshmidt: