Connecting to MySQL using Open Office Base via ODBC...

Wed May 30 19:55:59 UTC 2007

On 05/29/2007 08:55 PM, Gabriel Dragffy wrote:
> On Tue, 2007-05-29 at 14:06 -0700, NoOp wrote:
>> On 05/29/2007 11:04 AM, Gabriel Dragffy wrote:
>> 
>> > Hi
>> > 
>> > Thanks for the reply.
>> > 
>> > I installed and correctly configured libmyodbc.
>> > 
>> > I am using Feisty, and that has OpenOffice 2.2.
>> > 
>> > So what I do is start up Base. It asks me what database to use. I choose
>> > to "connect to an exisiting database" and select the type as MySQL. I
>> > tell it to connect using ODBC, successfully find the datasource I
>> > defined in the config files and tell it to use that. I can put in my
>> > userame and password, and test the connection - it works, and after I am
>> > shown the main area of Base, where to the left you can choose: "Tables",
>> > "Queries", "Forms", "Reports".
>> > 
>> > This is where things start to go wrong.
>> > If I click on "Tables", and I double click on one of my tables, a new
>> > windows opens up and it correctly displays my table, including it's
>> > structure and data.
>> > 
>> > If I go to "Queries" and choose to "Use wizard to create query" I get a
>> > dialogue box pop up saying "the field names for database.table could not
>> > be retrieved". This happens also when I choose to "create query in
>> > design view", "use wizard to create form", "use wizard to create a
>> > report".
>> > 
>> > Therefore most of the functionality has been blown out of the water :(
>> > 
>> > The server I am connecting to is on the same LAN, that is also running
>> > Feisty, with an Apache2 server, MySQL, and Samba. As you may have
>> > guessed I use it as a file server and as a testing web server.
>> > 
>> > Would appreciate any insight. Thank you.
>> > 
>> > Gabe
>> > 
>> > 
>> 
>> My guess is that the Ubuntu (U)OOo wizards are screwed up (see:
>> <https://bugs.launchpad.net/ubuntu/+source/openoffice.org/?field.searchtext=&orderby=-datecreated&search=Search&field.status%3Alist=Unconfirmed&field.status%3Alist=Confirmed&field.status%3Alist=In+Progress&field.status%3Alist=Needs+Info&field.status%3Alist=Fix+Committed&field.assignee=&field.bug_reporter=&field.omit_dupes=on&field.has_patch=&field.has_no_package=>)
>> That said; I decided to test using a local, know working database that I
>> have on my system. The database was a converted ACT (old Windows contact
>> manager) dBase file that I converted to an OOo have use on a regular
>> basis. Here is what I tested, using the exact same local odb:
>> 
>> 1. In OOo 2.2 official - (O)OOo base I open the tables, create queries,
>> forms, reports with no problem. Everything works.
>> 
>> Ditto for StarOffice8 (w/Update 6).
>> 
>> 2. In (Ubuntu)OOo 2.2:
>> 	- I can open the tables
>> 	- Use Wizard to creat query does nothing
>> 	- Create Query in Design View opens & works
>> 	- Forms wizard does not work
>> 	- Create Form in Design View works
>> 	- Reports wizard does not work - in only brings up a start form
>> 
>> Note: Java JRE is Sun 1.6.0_01. I can also test w/1.6.0 and 1.5.0_07.
>> 
>> So as to ensure that there was no common use of (U)OOo and (O)OOo files
>> I tested (U)OOo and StarOffice8 on one machine, and the (O)OOo and
>> StarOffice8 on a different machine - same database.
>> 
>> So, before you throw in a MySql connection to complicate matters even
>> more, I'd test a local simple local database first. Then after you
>> verify the above, unload (U)OOo 2.2 and install (O)OOo 2.2 directly from
>> http://openoffice.org. Note: make sure you download the non JRE version
>> & then load your own JRE from Sun directly.
>> 
>> Gary
>> 
>> 
>> 
> Thanks for that info. It's appalling if Ubuntu has messed up OpenOffice.
> 
> I'll like to try the official version - do you have any instructions for
> doing so?

Sure (modified slightly from
http://leetturtle.com/guides/guide_ubuntuopenoffice.php):

1) First, go to OpenOffice.org and download the latest version for
Linux. http://download.openoffice.org/2.2.0/index.html

[NoOp note: before downloading, uncheck the "Include the Java JRE with
this download" - you don't need it as you'll install separately & the
version they have included has a bug in it]

2) Use Archive Manager or whatever and extract the file you downloaded
into a directory.

[NoOp: I recommend changing the name of the directory to something
simple like 'OOo22' so you don't have to try to type the long name chen
cd'ing into the directory]

3) Change directory (cd) into the RPMS directory:

cd /where/you/extracted/openoffice/RPMS

4) Let's go install alien, which converts the files in the RPMS
directory into files that Ubuntu can actually use:

sudo apt-get install alien

5) Let's use alien! w00t! (the * selects any file)

sudo alien --scripts --keep-version *.rpm

[NoOp note: if you have a slow machine w/limited memory, go to lunch...
the conversion will take awhile]

6) If you still have the OpenOffice that Ubuntu includes by default,
remove it so this version works:

sudo apt-get remove openoffice*

[NoOp note: this will remove ubuntu-desktop. That's OK. You can
reinstall it later if you do a major dist-upgrade. However, the only
reason for removing the (U)OOo version is so that it will remove the OOo
debian menus. You can keep it and still install (O)OOo as (O)OOo gets
installed into the /opt/openoffice.org2.2 directory and can co-exist
with (U)OOo]

7) After you're done uninstalling the OpenOffice installed with Ubuntu,
let's finally install the version of OpenOffice you wanted!

dpkg -i *.deb

[should be sudo dpkg -i *.deb]

8) Now we got to install the file that will give us lovely menu entries!

cd desktop-integration && dpkg -i *.deb

[cd desktop-integration
 sudo dpkg -i *.deb]

[NoOp note: this will only work if you have uninstalled the
ubuntu-desktop - see below for adding (O)OOo 2.2 to the menus]

9) You can now remove the file you downloaded and the files that you
extracted.

=====================================================================
Adding (O)OOo 2.2 to the Application menu if you've kept unbuntu-desktop:

Adding (O)OOo 2.2 to Applications|Internet:

   1. Right click the Applications icon, select 'Edit Menus'. When the
Menus selection opens up click on the Office icon.

2. Select 'New Item'. That will bring up the Create Launcher. Fill in:

Name: (O)2.2 Writer

Command: - click the 'Browse' button and find your way to File System
/opt/openoffice.org2.2/program/swriter - click open. Command will now
read: /opt/openoffice.org2.2/program/swriter. (You could have typed
manually).

Comment: you can add whatever you want.

Now click the 'No Icon' button. Click the 'Browse' button and find your
way to /usr/share/icons/hicolor/48x48/apps/ and click 'Open'.
You'll now find the default OOo icons. Click on 'oo-writer.png' and
click OK. That will take you back to 'Create Launcher', click 'OK'. That
will place you back in 'Main Menu' and you'll see your (O)OOo in the
Items section. Click 'Close'.

Repeat above for: sbase, scalc, sdraw, smath, simpress, and spadmin.

Now: Application|Office|(O)OOo Writer. That will launch the registration
wizard. Accept the license, turn off check automatically for updates,
and answer that you do not want to register.

Once in Writer: click Tools|Options|Openoffice.org|Java. If you do not
have Sun Java JRE 6 installed, I recommend that you do this via Synaptic
or directly. OOo Base just seems to work better w/JRE 6.
=========================================================================

> 
> Also I can choose to connect to my database using ODBC or JDBC. Since
> JDBC doesn't work could I use JDBC?

Of course.

> If so, how do I make it work?
> Honestly I don't even understand what JDBC or ODBC is, I just wanna
> connect to my database :)
> 

These might help:
http://applications.linux.com/article.pl?sid=07/02/12/1736203&tid=13&tid=73
http://dba.openoffice.org/
 http://wiki.services.openoffice.org/wiki/Category:Database
http://documentation.openoffice.org/manuals/
 [OpenOffice.org2.x User Guide - Chapter 7]