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?
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 referenceThis 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.
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.
OpenOffice.org
- Database Access -
Tips and Tricks - MySQL
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
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
Credits go to John McCreesh for finding this.
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.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").