ODBC with OpenOffice (was What Excites You?)

[Greg Rundlett]

On Tuesday 25 March 2003 02:19 pm, Cole Tuininga wrote:

 > Ok - I'll bite.  How do I do this?  8)

Here is the link to the HOWTO on OpenOffice and MySQL and ODBC

http://www.unixodbc.org/doc/OOoMySQL.pdf

This is the same document that I used to set this up on both Windows and 
Linux workstations.


 > I went into OpenOffice Calc, selected Tools->Data Sources.  In the
 > windows that came up, I selected ODBC.  The Data source URL field filled
 > in with "sdbc:odbc".  Not knowing the syntax for the url (I'm trying to
 > connect to a local mysql database) I tried clicking on the elipses
 > button - which gave me the following error:
 >
 > "Could not load the program library libodbc.so or it is corrupted.  The
 > ODBC data source selection is not available."

The trick is to define a system DSN in windows via the control panel, or 
similar 'DSN' in linux using ODBC Data Source Administrator (found on my 
RH7.3 system under the system->ODBCConfig).  I got the same error you 
did, which I assumed was because I was logged in as 'Greg' instead of 
root.  (I think the file that ODBCConfig wants to write is either owned 
by root, or in a directory owned by root.) So, rather than logout, I 
just fired up a shell, su'd, and manually editted the /etc/odbcinst.ini 
to look like this:

# Included in the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1


# From the MyODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc.so
FileUsage       = 1

Then I editted the /etc/odbc.ini file like this

[MySQL-test]
Description = MySQL database test
Driver      = MySQL
Server      = localhost
Database    = test
Port        = 3306
Socket      =
Option      =
Stmt        =

[Freephile]
Description = Free Co-op Catalog (example)
Driver      = MySQL
Server      = freephile.com
Database    = coop
Port        = 3306
Socket      =
Option      =
Stmt        =

Then I was able to select the "Freephile" Data Source URL in the 
aforementioned dialog box that pops up by clicking on the elipsis button.

Once that is done, then you can select "View->DataSources" from any Open 
Office application (even Writer).  You can use the data source viewer 
just like Access to view, edit, delete your data, and even to do queries 
by design (right-click on the Queries item) or design reports based on 
data fields and queries.  In my example, I would be able to edit 
products in my website Catalog using Open Office from my desktop at home.

See the HOWTO doc by John McCreesh on unixodbc.org for more information.

This mini-howto is also reproduced on my website for free software in 
business: http://freephile.com/tiki/tiki-index.php?page=Linux

(Paul, in reference to your direct question...I covered a couple of 
other interesting topics briefly on that same page, and would be glad to 
speak at one of the meetings sometime about how I set it all up.)

As an aside, and please pardon me for the solicitation, I would love to 
see anyone on this list help me grow the freephile website into a great 
community resource.  Feel free to add to the tiki there.

Thanks,

Greg Rundlett