Database Specific Problems
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.
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)
-
Get the newest JDBC driver from www.mysql.com
-
Start OpenOffice.org
-
Goto Tools/Options/Security
-
press the browse button near "Classpath" and browse for the *.jar file(s) of
your driver.
-
Restart OpenOffice.org
-
Open the data source administration dialog (Tools/Data Sources...)
-
Press the button "New Data Source", in the name field, enter a valid name like
"MySQL JDBC", and keep "JDBC" as type.
-
Enter the data source URL mysql://host:3306/DataabaseName
-
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.
-
Enter the name of the user you wish to connect, and mark the checkbox if this
user needs a password.
-
To be sure that your settings will be saved, press the Apply button.
-
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.
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.
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.
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.
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.
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.
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.
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.
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.
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").
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
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.
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.