Project Sections: Home | Specifications | Tips 'n' Tricks | QA | Development | Database Drivers
Tips 'n' Tricks Sections: Database Specific Problems

Database Specific Problems

MySQL
I get a strange error message about library versions and symbols - what's up?
How can I connect to MySQL?

I cannot edit a newly created table!

My strings are not correctly encoded when using MySQL connector (JDBC).
I get an error when inserting values into a numeric field.

I can't get write access to the database running on localhost when connecting via JDBC!
SQL Server

How to get the correct value for an auto value (auto increment) when inserting a new row?
MS Access

Auto increment fields are not shown by connecting over ODBC
SAP DB

How do I connect to a SAP DB through the Adabas driver?
PostgreSQL

When trying to connect (ODBC) to PostgreSQL my Office seems to hang.

Serial columns don't work.

When executing a SQL query I get the error message that column tcid is duplicate.

OOo crashes when I create a table in PostgreSQL

I get the error "ERROR: Attribute oid not found" when I try to open a table
Oracle

When using the ODBC driver to connect to Oracle, not all seems to work well.

All columns are shown as currency fields when using their JDBC driver.
DB2

When trying to create relations, the new relations aren't visible after reopening the relation design.

MySQL

I get a strange error message about library versions and symbols - what's up?

When you try to connect to a MySQL Database via ODBC you may receive the following error:

[unixODBC][Driver Manager]Can't open lib
'/usr/lib/libmyodbc.so.2' : /usr/lib/libmyodbc.so.2: symbol errno, version
GLIBC_2.0 not defined in file libc.so.6 with link time reference
This is because the ODBC Driver was compiled for kernel 2.4 and you are using kernel 2.6.
A workaround is to start Open Office with a command line like this: LD_ASSUME_KERNEL=2.4.22 '/path/to/OpenOffice.org1.1.1/soffice' -calc

Thanks to Othmar Edel for contributing this item.

OpenOffice.org - Database Access - Tips and Tricks - MySQL

How can I connect to MySQL?

We just introduced a new type, specifically for MySQL. But you have always to know which kind of connection you want to use. Please have a look at the MySQL spec page http://dba.openoffice.org/specifications/MySQL_data_source_page.html.
First you should decide which kind of driver you should use. At the moment there are two possibilities. Either you use an ODBC or a JDBC implementation.

via ODBC: A good first look at can be found on the unixODBC sites in the manuals section.

via JDBC: (These steps are only valid for the MySQL connector)
  1. Get the newest JDBC driver from www.mysql.com
  2. Start OpenOffice.org
    • Goto Tools/Options/Security
    • press the browse button near "Classpath" and browse for the *.jar file(s) of your driver.
  3. Restart OpenOffice.org
  4. Open the data source administration dialog (Tools/Data Sources...)
  5. Press the button "New Data Source", in the name field, enter a valid name like "MySQL JDBC", and keep "JDBC" as type.
  6. Enter the data source URL mysql://host:3306/DataabaseName
  7. Switch to the tab page JDBC. Now you have to enter the driver class name. For the MySQL connector driver you have to insert: com.mysql.jdbc.Driver.
    If you use any other kind of driver, you have to look at the manual for the correct driver name.
  8. Enter the name of the user you wish to connect, and mark the checkbox if this user needs a password.
  9. To be sure that your settings will be saved, press the Apply button.
  10. To test if you could connect to MySQL switch to the Tables page.
If all went well you should now see the tables which already exists in the database. If the connection has failed the tables control in the middle will be disabled.

OpenOffice.org - Database Access - Tips and Tricks - MySQL

I cannot edit a newly created table!

The first reason may be that the table was created without a primary key or a unique index. In this case open the table design for that specific table and select the columns which identify a row uniquely, open the context menu and select the entry "Primary key" and save the table. After that the table should be editable.

Second reason, the table privileges are not set right. This is the case when the table "Mysql.tables_priv" doesn't contain the right privileges. To verify this, open a new query design and insert in the text view the statement "SELECT * FROM mysql.tables_priv" and switch the mode to native in toolbar. When executing this statement, you should see your table name with the necessary privileges. If your table name doesn't appear in this list, you have to grant yourself the privileges. This isn't done automatically. Open the context menu upon your tables entry on the left side in your data source browser and choose SQL. In the appearing window insert "GRANT ALL ON yourtablename TO yourusername" (find more about the GRANT syntax at the MySQL documentation site) and execute this statement. Now your table should be editable.
If you're connecting to MySQL via JDBC, the useHostsInPrivileges=false option in the connection URL may be worth additional consideration. It tells MySQL to ignore the host which you are connecting from, when calculating your privileges.

Third reason, your driver which you are using isn't up to date. In earlier releases of MyODBC we recognize that the privileges and the currently username are not correctly returned.

OpenOffice.org - Database Access - Tips and Tricks - MySQL

My strings are not correctly encoded when using MySQL connector (JDBC).

You can enable the use of Unocode with extending the connection string for jdbc:mysql:

mysql://host:3306/DatabaseName?useUnicode=TRUE&characterEncoding=your encoding

When the character encoding is not set, then the encoding from server will be used. Please have a look at the MySQL documentation for more information about this topic.

OpenOffice.org - Database Access - Tips and Tricks - MySQL

I got an error when inserting values into a numeric field.

The current implementation of MySQL ODBC doesn't recognize the correct locale. So if you try to insert any value into a numeric field and you use an other locale than English, you got an error.

The workaround for this would be to switch your language setting to US-English.

Here is a link to the issue, describing the bug. http://dba.openoffice.org/project/www/issues/show_bug.cgi?id=6600

OpenOffice.org - Database Access - Tips and Tricks - MySQL

I can't get write access to the database running on localhost when connecting via JDBC!

You probably already granted permissions to user@localhost for the table in question, and you probably already convinced OOo to assume all privileges (ignoring what the driver tells). Then you may also need to grant privileges to user@127.0.0.1 (which is the IP address of "localhost").
Credits go to John McCreesh for finding this.

SQL Server

How to get the correct value for an "auto value" (auto increment) when inserting a new row?

To fetch auto increment values, insert "SELECT @@identity" into the field "Query of generated values" on the second page in the data source administration dialog.

OpenOffice.org - Database Access - Tips and Tricks - SQL server

MS Access

Auto increment fields are not shown by connecting over ODBC

The problem with Access and ODBC is that you can create tables which contain auto increment fields, but the driver never tell you again that this column was an auto increment one. So auto increment columns, Access and ODBC do not work well with each other. If possible, connecting over ADO is to prever.

OpenOffice.org - Database Access - Tips and Tricks - MS Access

SAP DB

How do I connect to a SAP DB through the Adabas driver?

To connect to SAP DB, you have to create a new folder (e.g. sapdb), in this folder you create a 2nd folder named sql. In this folder you copy all libs (on windows dlls) which are included in the ODBC driver of SAP DB. Then you define a new data source for your SAP DB. A sample for this can be found in your SAP DB installation (under Windows just open your ?ata Sources (ODBC)?. The next step will be to define a system variable named DBROOT. This variable has to point to the folder you created in the first step (sapdb). Now you can define a data source in your OpenOffice and access the database.
On Unix systems you also have to define soft link named odbclib.so to ContentStorage.so. (ln -s $DBROOT/depend74/lib/ContentStorage.so $DBROOT/depend74/lib/odbclib.so )
This lib can be found in your sap installation sapdb/depend74/lib (may be this folder is named different depending on your SapDB version you use).

Another possibility to connect to SapDB would be simply to use the existing ODBC or JDBC driver from SapDB.
OpenOffice.org - Database Access - Tips and Tricks - SAP DB

PostgreSQL

When trying to connect (ODBC) to PostgreSQL my Office seems to hang.

Check the ODBC driver you are using. If this driver has the number 7.02.0005 then you have to go back and use a prior one. 7.02.0003 works.

OpenOffice.org - Database Access - Tips and Tricks - PostgreSQL

Serial columns don't work.

Can't be solved until the ODBC driver returns correct information about this kind of type. :-( Sorry.
You might also be interested in this bug which requests the feature.
OpenOffice.org - Database Access - Tips and Tricks - PostgreSQL

When executing a SQL query I get the error message that column "tcid" is duplicate.

This is a bug of the driver with number 7.02.0005. The version 7.02.0003 works. You can download PostgreSQL ODBC drivers from here.

OpenOffice.org - Database Access - Tips and Tricks - PostgreSQL

OOo crashes when I create a table in PostgreSQL

If you're using unixodbc 2.2.4, please upgrade to 2.2.6, at least. This version fixes the problem.
If you're already using 2.2.6 or higher, or if you're using another driver manager, or another platform, then you encountered a new bug. In this case, please help us improving the product and submit an issue for this (component: "database access").

OpenOffice.org - Database Access - Tips and Tricks - PostgreSQL

I get the error "ERROR: Attribute oid not found" when I try to open a postgres table

Please UNcheck the "updateable cursors" option in the settings of the ODBC driver
OpenOffice.org - Database Access - Tips and Tricks - PostgreSQL

Oracle

When using the ODBC driver to connect to Oracle, not all seems to work well.

Try the newest JDBC driver from Oracle - older drivers are known to have some problems together with OpenOffice.org.

All columns are shown as currency fields when using their JDBC driver.

This is a bug in the JDBC driver from Oracle.
OpenOffice.org - Database Access - Tips and Tricks - Oracle

DB2

When trying to create relations, the new relations aren't visible after reopening the relation design.

This is a problem the ODBC driver for DB2 has. You should switch to their JDBC driver to avoid such problems. May be this driver is even better than the ODBC one.

OpenOffice.org - Database Access - Tips and Tricks - DB2