OpenOffice and MySQL with ODBC

[InnSun]

An open source Office Suite like Open Office is a great idea, but it currently lacks a "robust" database functionality, i.e., like Access has. There are reports, however, of those who have successfully implemented MySQL (or postgresql, etc.) to provide this thru an ODBC connection. One such description is found here:

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

I am trying out Open Office 1.0.1 on Red Hat 7.3 and have tried some of the steps in the above document (I'm really a newbie to both Linux and ODBC's, and am pretty much in the dark about this %-) ). Some initial steps involve installing MyODBC and unixODBC. Everything went fairly smoothly until the test for the ODBC software. This is done with a unixODBC-supplied program called "isql". Running this from my user directory:

$isql MySQL-test

results in:

[unixODBC][Driver Manager]Data source name not found, and no default driver specified.

Some background:
o MySQL works fine. I can run it in text mode stand-alone and select * from table-whatever; etc.;

o unixODBC has a .ini file in which a default driver is defined as MySQL. This .ini file is stored in three places (simply because i don't know where they want it):
/home/<user>/odbc.ini
/home/<user>/.odbc.ini
/etc/odbc.ini
o In the isql command, the first argument is the DSN or Data Name Source. The above is copied from the pdf document referenced above. The name they gave is apparently a reference the the database name of &quot;test&quot; that is automatically set up when you install mysql. i tried to change that and give it an exact path of /var/local/mysql/test but it didn't change anything.

Has anyone tried this type of configuration? Any help would be appreciated very much Thanks in advance!! :-D

 

[sleipnir214]

Some stuff I ran into.

I put odbc.ini in /etc. The document doesn't explicitely say to do so, but it does mention that all users on the system will be reading the file.

I could not get isql to behave until the DSN name matched the MySQL table name.

If the userid you use to connect to MySQL requires a password, you must supply it. The format for isql is &quot;isgl <DSN> [[userid] [password]] [options]&quot;. ______________________________________________________________________
TANSTAAFL!

 

[InnSun]

Thanks sleipnir214!

Regarding your comment:
I could not get isql to behave until the DSN name matched the MySQL table name.

Shall I try to put in the Data Source Name with the full path? Here is the setup with MySQL:

[root@localhost mysql]# lmd
total 12
drwx------ 2 mysql mysql 4096 Aug 30 21:46 test
drwx------ 2 mysql mysql 4096 Aug 30 21:46 mysql
drwx------ 2 mysql mysql 4096 Aug 31 22:57 budget_A
srwxrwxrwx 1 mysql mysql 0 Sep 7 07:45 mysql.sock

Yet, specifying the DSN with the path:

/var/lib/mysql/budget_A OR /var/lib/mysql/test

Doesn't help! My theory is that ODBC's (in general) resolve a DSN reference by first going through a server(?) where it is already specified with the path, etc.

One thing I am missing so far is a utility called ODBCconfig which gives you a window with a series of tabs where you associate all your DSN references to a server (or something?). I just downloaded unixODBC-kde (which has this utility in the filelist) from RedHat. Back to testing...

Regarding your other comments: 1st, I will try various odbc.ini configurations in /etc (hopefully built thru ODBCconfig), and on the 3rd one If the userid you use to connect to MySQL requires a password, you must supply it. I haven't put in any passwords, but that doesn't mean it isn't looking for one. Could be something as simple as that...

 

[InnSun]

After setting up /home/<user>/.odbc.ini with ODBCconfig I am able to connect to the MySQL-supplied database &quot;test&quot;. Connecting to a database that I set up appears to be somewhat more of a challenge

As for OpenOffice, I have now seen that it can display the MySQL table rows thru the ODBC. This is a step in the right direction...

 

[sleipnir214]

With any testing using isql, remember to use the -v option. That verbose mode can give you a lot of information about what's going wrong if you can't connect. ______________________________________________________________________
TANSTAAFL!

 

[InnSun]

Thanks, i'll reboot and try it !

(at the present time all my e-mail / internet is done in Win98. Going back to Linux requires a reboot. I can't get the ethernet card working there yet, for a DSL connection.)

 

[mbramble]

Anyone still working this problem?? I just got it working tonight and thought I might be able to offer some pointers if anyone is still trying and having problems.

Also, if there is anyone using this setup, I have a couple of questions I'd like to ask......

 

[InnSun]

hi mbramble,
thanks for joining in!
I just started testing this again after getting distracted by some other projects (installing Red Hat 8.0, etc.)
The main issue for me is finding a viable replacement for Access:
Just a few things that came up recently:
- what's the best way to transfer data from Access to MySQL (e.g., save as CSV file, then use &quot;LOAD DATA INFILE...&quot; inside MySQL)
- In the Open Office Data Sources Screen, nearly all of the Access Query functionality is provided for. However, I don't see anyplace to do Relationships (as in the &quot;Tables&quot; Object screen of Ac.)
- How to set up a macro or script of some type to run whenever user opens the Data Source Link. Currently, the database I want to convert has an AutoExec macro that calculates ages based on a birthdate column.

 

[mbramble]

Yea, I spent the weekend moving from Mandrake 9 to Redhat 8 and back to Mandrake. Just some things I didn't care for in RH. Mayhe it was mostly their BlueCurve? desktop and I could probably have gotten rid of that but there were several other things that just didn't seem to work right. So, I'm back to MDK9 and think I'll stay there.

I too am looking for a good replacement fro Access. I can get to my SQL database thru Openoffice.org but I seem to have a problems. I don't know if they are peculiar to me or if they are inherent with the MySQL and/or ODBC and/or Oo.

#1 If I (in Oo) go to Tools, Dadat Sources, and go to Data Source URL on the General tab, then, click on the ... to the Data Source box, and finally -- click on Organize I get an error &quot;Could not loca the program library libodbc.so.1 or it is corrupted.......&quot;. I don't know exactly &quot;Organizing&quot; the data source would allow me to do but I don't like the error. I don't know if Oo is looking for that file in a different location, or it is an incompatible version or what ????

#2 When I am viewing my database tables in Oo, I am unable to go in and edit the existing structure of an existing table. The structure of all the existing fields is greyed out.

#3 I can create a new table and put in whatever fields that I desire. But, if I want to go back and edit my work (after saving the new table and opening it back up to edit the structure), the new fields are greyed out. I can add new fields with no problem.

#4 I can ONLY edit the structure of a table that does not have a primary key. If it does, and I try to save my changes, I get an error.

I have been using MyODBC 2.5x and last night installed 3.5x. No change in the problems I am having so I went back to 2.5

Trying to figure these things out have kept me from getting to the point of worrying about other things you have mentioned like macros, etc.

To get data from Access to MySQL, I have Access Queries that I use and Export them to Text files. I send them to comma delimited files and use nothing (like &quot;&quot around the fields. I run them thru dos2unix to fix the 'carriage return' difference. I then have a phpmyadmin setup on my home web server that I use to read the files into empty MySQL tables that I built a long time ago. I'm sure there are better ways, but this one works and I don't have to think too much about it.

I am really hoping there are some improvements to this (or I figure out how to implement it all correctly?) cause a gui operated database is one of the things I really want from Linux. I still have Winders set up on an old PC and have Access installed on it for doing major work to my database. I then export the data to MySQL for use in my web site. It's a bit of a pain......

 

[InnSun]

&quot;#1 If I (in Oo) go to Tools, Dadat Sources, and go to Data Source URL&quot;...
OK same thing happens on mine. Must be one of those &quot;under development&quot; features.

&quot;#2 When I am viewing my database tables in Oo, I am unable to go in and edit the existing&quot;...
&quot;#3 I can create a new table and put in whatever fields that I desire. But, if I want to go back&quot;...
(if you need to modify existing rows) You can right click on
an existing row and delete it; then re-add it (yes, that would be a pain!)

#4 I can ONLY edit the structure of a table that does not have a primary key. If it does, &quot;...
All I can say is that the &quot;Data Source&quot; feature of OpenOffice is not advertised very loudly at the present time: once enough people request these features they will probably get added.(I took the on-line survey when I registered my install and briefly mentioned some of the things lacking...)

On Red Hat and Mandrake, yes I pretty much agree with you... RH changed some things like the ethernet-DSL connection software so that I can't get DNS translation to work (so no I/N browsing there currently): I tried to download Mandrake 9 at first but couldn't find iso-s, only directory trees. One download on a tree was about halfway thru the rpms folder after 14 hours so I cancelled it...

Why don't we move this to another forum maybe more along the lines of &quot;Database&quot; or MySQL? (If you do this before I get to it, just set a forward and backward thread link in the respective posts... Thanks!! )

 

[InnSun]

Ok, here's a new thread to take this discussion to:

thread436-409703

 

[vitali398]

Is it possible Mail Merge in Kword? For instance, I have a file with data, delimited with |, and Doc file with merge fields now I have to produce a Doc file with merged data.

Thank you,
Vitali