[Solved] Using MySQL with BASE

Creating tables and queries
Post Reply
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

[Solved] Using MySQL with BASE

Post by woofy613 »

I wish to use MySQL as the data base, and BASE as the front end.I have downloaded and installed the MySQL Connector and MySQL Community Server. As far as I can tell I am logged in as woofy613@rosenberg. When I try to create a database, I get the message "Unknown MySQL host woofy613@rosenberg". If it matters, I installed the server with apt-get install mysql-server.

What have I done wrong?
Last edited by Hagar Delest on Mon Aug 09, 2010 8:34 am, edited 1 time in total.
Reason: tagged [Solved].
openoffice 3.1.1
Debian
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Using MySQL with BASE

Post by rudolfo »

What makes you think that you installed the MySQL community server when you installed the one that comes with the debian distribution?
Anyways, if you are talking about the native MySQL connector that can be installed as an extension and not about the JDBC connector, you will need a server version of at least 5.1. With Debian Stable aka Lenny you will only have 5.0.x of MySQL. But you can check this with "dpkg -s mysql-server".
If you haven't modified the mysql.ini configuration file the mysql server is only accepting connections on the unix socket and on the TCP/IP localhost address 127.0.0.1. If the hostname "rosenberg" resolves to a IP address that refers to a ethernet interface it won't work.
But better test the connectivity first with the mysql internal tools. "man mysql" is you friend.
Try both mysql -u woofy613 -h rosenberg and mysql -u woofy613 -h localhost.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thanks.

These are the messages I received:

rosenberg:/media# dpkg -s mysql-server
Package: mysql-server
Status: install ok installed

rosenberg:/media# mysql -u ethan -h rosenberg
ERROR 2003 (HY000): Can't connect to MySQL server on 'rosenberg' (111)
rosenberg:/media# mysql -u ethan -h localhost
ERROR 1045 (28000): Access denied for user 'ethan'@'localhost' (using password: NO)

What should I do now?
openoffice 3.1.1
Debian
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Using MySQL with BASE

Post by Robert Tucker »

Have you started the server? Can you connect as root? Has the root password been set? Ethan has no password? How have you created the user Ethan? You are using a single computer, not a network? – Do you need to specify the host? From the forums JDBC is the more reliable connector (and the one I use on my computer).
LibreOffice 7.x.x on Arch and Fedora.
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Using MySQL with BASE

Post by rudolfo »

Sorry, I got something wrong. This happens if you quickly shoot out the answers without testing it: "dpgk -s mysql-server" doesn't show the version, because it is a meta-package. You need to run:

Code: Select all

$ dpkg -l "mysql-server*"
||/ Name                  Version               Description
+++-=====================-=====================-==========================================================
pn  mysql-server          <none>                (no description available)
pn  mysql-server-4.1      <none>                (no description available)
ii  mysql-server-5.0      5.0.51a-24+lenny3     MySQL database server binaries
So that's Mysql version 5.0.51 on my linux system. The reminder of the version string refer to debian patches. In other words my Mysql server is not new enough for the OOo native connector.

My second fault was to think that you are familiar with MySQL and this is the reason why you made it the database of your choice for Base. Seems to be a wrong assumption. To verify what Robert mentioned connect as root and check for existing users and databases. (Just another thought: mysql database users are not identical with Linux (shell) users.) Intially the root user of the database has an empty password, it is recommended to change this.

Code: Select all

user@linuxbox:~$ /usr/bin/mysql -p -u root mysql
The final mysql is the internal privilege database of the mysql database engine. Once you are logged in (respond with an empty password to the prompt). I think your server is running because the "Access denied" error can only come from a running server. At the mysql> prompt execute the following commands:

Code: Select all

mysql> show databases;
 ....
mysql> select host, user, password from user;
In older versions the init procedures that were executed by the installation did create a database "test" for testing purposes and usually all users are allowed to use and modify this database.
Of course if you only have the users "root" and "debian-sys-maint" you need to create a user.
Type "help create user" at the mysql prompt. Actually this is something for that you'd better contact a mysql forum or at least google for a mysql introduction.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thanks to all.

I've loaded the MySQL connector for OpenOffice. Is there any way to use it with OpenOffice 3.2? The documentation states that it only works with version 3.1. Is there a work-around or another program I can use? I'd would prefer to use BASE as my front end, rather than be forced to learn php.
openoffice 3.1.1
Debian
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Using MySQL with BASE

Post by r4zoli »

It is compatible with OOo 3.2, not mentioned in extension page.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thanks.

I cannot make it work. When I try to create a MySQL database, it never gives me the option to do so. What am I doing wrong. Does anyone have a walk-thru.
openoffice 3.1.1
Debian
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Using MySQL with BASE

Post by rudolfo »

Not sure about how it is exactly with the native MySQL connector. But for the JDBC connector you have to specify the database when setting up the connection. This means a database must already exist before you can connect with Base to it. Mysql comes with a command line tool to create databases:

$ mysqladmin create your_db_name

Maybe you can connect to the pre-existing database "mysql" or "test" with Base and open the SQL command window and run a "create database ..." DDL statement with direct mode enabled.
But be warned, one of the common opinions that you will find quite often in this forum is that Base is not a administrative tool for databases but a front end for data in database tables.

Or to refer back to your initial post: If you want to use Base for database prototyping (quickly create new tables, add columns to tables, etc.) you'd better use HDBSQL and once you have your database structure ready convert to MySQL.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Using MySQL with BASE

Post by r4zoli »

OOo wiki page with detailed description: Connect MySQL and Base
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thank you.

I am trying to connect using JDBC. What is the JDBC driver class? If I use com.mysql.jdbc.Driver I get an error "The JDBC driver could not be loaded", when I click on "Test Class". What am I doing wrong? Do I have to download the JDBC driver?
openoffice 3.1.1
Debian
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Using MySQL with BASE

Post by r4zoli »

Added jdbc driver to classpath?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Using MySQL with BASE

Post by Robert Tucker »

LibreOffice 7.x.x on Arch and Fedora.
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Using MySQL with BASE

Post by rudolfo »

Are we really on the right track if we try to explain the OP how to do the connection with JDBC?
I think his problem is that neither a database nor the a user to access the databases are setup in the correct way!
Sure, there are the typical pitfalls that OOo was not shutdown completely after changing the classpath to include the MySQL connector driver. But the root of the problem is really the database server.

The first thing that should be clarified is: Is a connection to the database possible with the mysql command line client?
No configuration is required for this tool (or precisely it is pre-configured by the Linux distribution). If it works, we will know that it is not a problem of user authentication or access rights for the database.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thanks to all.

I'm still stuck....

I followed the posting on setting up the connection. I generated a data base. At the mysql prompt:CREATE DATABASE /home/ethan/Databases/tester2;

Failed w/ the following message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/home/ethan/Databases/tester2' at line 1

Tried again: CREATE DATABASE tester2;
Query OK, 1 row affected (0.05 sec)

I have set myself up as a user w/ a password...

In the database Wizaard: [Steps seperated by ;]Connect to exisiting database; MySQL; JDBC; tester2; localhost; 3306; com.mysql.jdbc.Driver;

Test Class: failed w/ message: The JDBC driver could not be loaded.

Help Please....

PS I would like the data base to reside in /home/ethan/Databases

Much thanks in advance.
openoffice 3.1.1
Debian
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Using MySQL with BASE

Post by Robert Tucker »

Have you installed the JDBC? If so have you found where it is installed? If so have you followed these instructions:
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.
http://wiki.services.openoffice.org/wik ... L_and_Base
LibreOffice 7.x.x on Arch and Fedora.
woofy613
Posts: 24
Joined: Mon May 03, 2010 5:51 am

Re: Using MySQL with BASE

Post by woofy613 »

Thanks.

Yes. I must have done somthing totally wrong. I repeated what you suggested, and now it works!

One more question...

Much of the information that you mention is online. Without having to ask every time, how do I find specific information; eg, how would I have searched for the information that you provided?

Thanks ever so much.

BTW, is the spell checker in English?
openoffice 3.1.1
Debian
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: Using MySQL with BASE

Post by Robert Tucker »

woofy613 wrote:Much of the information that you mention is online. Without having to ask every time, how do I find specific information; eg, how would I have searched for the information that you provided?
Google is quite good! (Haven't tried Bing much.)
woofy613 wrote:BTW, is the spell checker in English?
OpenOffice has spellcheckers for many languages.
LibreOffice 7.x.x on Arch and Fedora.
Post Reply