Welcome!

Linux Containers Authors: Automic Blog, Yeshim Deniz, Elizabeth White, Liz McMillan, Pat Romanski

Related Topics: Linux Containers

Linux Containers: Article

Making MySQL, ODBC & OpenOffice 1.0 work together, Part 2

25-gazillion easy steps to making mailmerge work with OpenOffice & MySQL

(LinuxWorld) — The forum postings on last week's column about the installation of MySQL and ODBC to gain access to the secret power of OpenOffice.org 1.0 were a mixed bag: some good, some bad, some "so what." I was variously described as an stupid, foolish, an incompetent sysadmin and a writer of more fiction than fact.

I'll come clean about one thing: I am an incompetent sysadmin, and I have a failing score on the first LPI exam to prove it. But then so are most small businesspeople in the SOHO market. That makes my experiences a more-valuable litmus test for SOHO users than those of some yahoo guru running gentoo.

If you dismiss the name-calling and those who suggest switching to their distribution of choice in order to tame the problem, some valuable information remains to be gleaned. Among other things, I found a better, cleaner and "distribution-pure" installation method for Red Hat 8.0. It has only a couple of gotchas. Regardless of the distribution you're using, you might find help for how to get MySQL/ODBC installed and working with OpenOffice.org scattered in that forum.

If you're running Red Hat 8.0, the steps I followed might work for you. They are:

  1. up2date mysql mysql-server and mysql-devel
  2. /usr/bin/mysql_install_db
  3. chown -R mysql.mysql /var/lib/mysql
  4. chmod -R 666 /var/lib/mysql
  5. /usr/bin/safe_mysql -u mysql &
  6. up2date unixODBC unixODBC-devel
  7. up2date --src MyODBC
  8. rpmbuild --rebuild /var/spool/up2date/MyODBC-2.50.39-7.src.rpm
  9. rpm -Uvh --force /usr/src/redhat/RPMS/i386/MyODBC-2.50.39-7.i386.rpm
  10. Edit /etc/odbc.ini and /etc/odbcinst.ini

For whatever reasons, whenever I tried it with all the packages listed in a single up2date, it didn't work. It may have been the way I was holding my mouth, or I might not have sanitized my system completely from one of the previous failed installations. Or possibly up2date doesn't handle things correctly when there are too many packages under the tree. Hopefully it will work for you, too. As was explained in the forum, the reason for having to rebuild MyODBC is that the latest binary RPM for MyODBC does not yet match changes made in unixODBC for security problems.

There were also complaints about my suggesting a conflict of interest at MySQL AB between making money and making MySQL easy to install and configure. I would like to clarify my position. I did not say MySQL AB was failing to document MySQL more adequately in order to make more money selling support. What I said was that there is a conflict between the goals of the business and free software. It may or may not influence behavior, but it certainly exists. The goal of a business is to make money. The larger the market, the more money that can be made. Writing better documentation might very well shrink the market interested in MySQL AB products and services.

Moving right along

Enough of what happened last week. This week is all about actually using the MySQL/OpenOffice connection, not cussing while installing it or fussing about the best way to do it. For my test, I decided to do something that I had done circa 1995/1996 using both MS Access and the Lotus Approach — that being to create a table of newsletter subscribers and to use it to a mailmerge.

OpenOffice.org provides an easy-to-use tool for designing database tables. Clicking on View->DataSources brings up the Data Explorer, which lets you view and work with links, queries and tables in your data sources. I clicked on the MySQL-test data source to expand it, then selected tables and clicked the plus sign. Then a right-click on tables produced the Table Design window you see below. A couple of minutes later, I had a simple name and address table for newsletter subscriptions.

OpenOffice Table Design window
Editor's note: The above image is reduced in size to allow it to display on this page. Click on this image to see the original.

Now on to the heart of the matter: printing address labels from the table just created. I admit to getting stuck on this step for more than a full day. Relying primarily on the Help system, I followed the steps outlined but had no success. In addition to the steps being anti-intuitive, they also have the disadvantage of just not working. The best I could with them was to print every other label. I know, I know. You can say the glass is half-empty or half-full. But I wanted them all.

I asked for help using IRC in both the #openoffice.org and the #mysql channels on irc.freenode.net. But there was no help. Not even a suggestion. I Googled and read search results, but I couldn't find a mention of this problem (or even a similar one). Worse, I didn't know if I was dealing with a MySQL problem, an ODBC problem or an OpenOffice.org problem.

Finally, I joined OpenOffice.org so that I could search existing bugs. They track issues, not bugs, but it didn't help. Finally I decided to search the users mailing list archive. When I did, the problem became as plain as the nose on my face. The problem is that the Help system docs are wrong. Period. They don't work.

Success? Success!

I did find instructions there that work. I want to mention a small "gotcha" before proceeding. When I first began trying to print the address labels, I got a MySQL error message for "Too many connections." I learned that each label on the page requires a connection of its own. I did two things to resolve the problem. It's possible that only one of them was required. First, I added this line to the mysqld section of /etc/my.cnf:

set-variable = max_connections=200

After killing the running processes, I did an su mysql before restarting.0 I had found a note in a Google search from a MySQL developer who suggested not starting mysqld as root might solve the "Too many connections" problem. I restarted as user mysql instead of root passing a user argument on the command line. After the changes, database access worked. More experimentation is required to figure out which one actually cured the ill.

Here are the instructions on how to print the labels.

  1. Choose File->New->Labels
  2. Select the Labels tab and pick the columns from the table you want printed on the label, left to right, line by line. Be sure to enter space between adjacent columns on the same line and to press Enter to continue on a new line.
  3. Select the Format tab and define the way you want the labels to print: the size, margins and number of columns and rows you want per page.
  4. Select the Options tab and unselect Synchronize
  5. Click on New Document
  6. Press F4 to select the data source
  7. Select the rows from the table that you want included by clicking on the button to the left of the leftmost column of the table display. Use the Shift and CTL keys as you normally do for selecting multiple items.
  8. Click on the "Data to Fields" icon on the database toolbar. Just to the left of this icon is a very similar one. You want the one on the right, which is immediately adjacent to the Form Letter icon. When you click it, the document fields will be populated from the database.
  9. Click on the Form Letter icon and follow the dialog to print the labels.

Final impressions? OpenOffice.org is more sophisticated and powerful than I realized. It can be made to work with various database-management systems, and it can do the mailmerge functions required by many SOHO types. Ease of use is not up to what I remember with either Access or Approach six or seven years ago, but I'm sure that it will continue to improve in that regard. The good news to me is that Linux is making strong strides not just in the enterprise, and not just on the desktop, but in a place where it seems to lag most of all: the SOHO market.

If you need database access and manipulation for your SOHO office environment, I would not recommend an immediate switch to OpenOffice.org. I would very strongly recommend that you begin beta tests of it — perhaps with more than one DBMS — to see if it meets your needs. Exercise it fully. You don't want to get to the tail-end of an implementation only to find another "gotcha" that makes the whole thing unsuitable. As with all software, there are bound to be bugs in both the code and the documentation. I'll continue my own investigations and compare my experiences with both PostgreSLQ and the OpenOffice.org default, dBase.

More Stories By Joe Barr

Joe Barr is a freelance journalist covering Linux, open source and network security. His 'Version Control' column has been a regular feature of Linux.SYS-CON.com since its inception. As far as we know, he is the only living journalist whose works have appeared both in phrack, the legendary underground zine, and IBM Personal Systems Magazine.

Comments (6) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
lawrence harriott 02/24/05 09:58:59 AM EST

Oh, I am using Mandrake 10.1, OOo1.1.4, MySQL3.51. I installed the unixODBC drivers that come with linux.

lawrence harriott 02/24/05 09:55:35 AM EST

This is one of the few places where success with OOo and MySQL have happened. After a sleepless week of working at this problem, with precious but little help I wound up here.

My problem is that from early on I have been able to create tables in MySQL using OOo but up to now cannot see any when I access my data sources. I connect to the database but the tables in there will not show up no matter what I do.
So far I have added myslef to the mysql group, made the test database permission read/write to all, checked the table with isql and mysql (it is there and I can do what i want with it).

If anyone had solved this problem please, please, let me know how to correct this.

Thanks
Lawrence

Leslie Viljoen 03/25/04 07:03:58 AM EST

I used this article to set up MySQL/OpenOffice connectivity under Mandrake 9.1 - in about 30 minutes.
I needed to urpmi unixODBC-gui-gtk and MySQL had already been installed earlier (but I hadn't done anything with it).
Apart from that I just put in the odbc.ini and odbcinst.ini lines and everything worked.

Thank-you for the tips!

Leslie

Muraille Paul 12/07/03 04:09:29 PM EST

Maybe a look at the Database FAQ (http://dba.openoffice.org/FAQ/FAQ.html#dataupdate)
might help you. There's one question that reads:
"I can access my tables with OOo, but I can't update or insert any data - the access seems to be read-only." However, it seems that it was written for OOo 1.0 and you may need more drilling (in the answer, they say there's a turnaround for 1.1 beta2 and they explain it on another page).
As to me, I have OOo 1.1 (French version), mySQL 4.0.15 (installed together with Apache, see EasyPHP 1.7) and I downloaded the mySQL ODBC component from mySQL AB available on the mySQL website. After configuring the ODBC driver through the control panel, I ran OOo, then configured a datasource for a local mySQL database named 'test' as the 'root' user and everything was ok. So if you haven't done so yet, test your connection as the 'root' user. If it works, your problem may be a user's read/write problem as explained in the FAQ.

Hope this helps,

Paul

Joel Stone 10/27/03 07:42:42 PM EST

Gosh, I wouldn't have a clue about how to make any of that work on windoze, sounds like quite a mess... would yu be willing to consider the linux OS? Perhaps use crossover office to operate the handful of pesky legacy windoze apps for now?

Mike Darrett 10/27/03 12:21:58 PM EST

Hello,

I'm trying to run OpenOffice.org 1.1 with MySQL on Windows 98. (Yes yes I know, but some things like eBay's Mister Lister, not to mention TaxCut, only work on Windows.)

I've followed the McCreesh article, and cannot enter data into a newly-made table. (I've installed MyODBC instead of unixODBC.) I can create a new table, but I can't seem to edit the table at all; all record options are grayed out.

Any suggestions?

Thanks

Mike

@ThingsExpo Stories
In his session at @ThingsExpo, Arvind Radhakrishnen discussed how IoT offers new business models in banking and financial services organizations with the capability to revolutionize products, payments, channels, business processes and asset management built on strong architectural foundation. The following topics were covered: How IoT stands to impact various business parameters including customer experience, cost and risk management within BFS organizations.
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...
The question before companies today is not whether to become intelligent, it’s a question of how and how fast. The key is to adopt and deploy an intelligent application strategy while simultaneously preparing to scale that intelligence. In her session at 21st Cloud Expo, Sangeeta Chakraborty, Chief Customer Officer at Ayasdi, will provide a tactical framework to become a truly intelligent enterprise, including how to identify the right applications for AI, how to build a Center of Excellence to ...
SYS-CON Events announced today that Elastifile will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Elastifile Cloud File System (ECFS) is software-defined data infrastructure designed for seamless and efficient management of dynamic workloads across heterogeneous environments. Elastifile provides the architecture needed to optimize your hybrid cloud environment, by facilitating efficient...
There is only one world-class Cloud event on earth, and that is Cloud Expo – which returns to Silicon Valley for the 21st Cloud Expo at the Santa Clara Convention Center, October 31 - November 2, 2017. Every Global 2000 enterprise in the world is now integrating cloud computing in some form into its IT development and operations. Midsize and small businesses are also migrating to the cloud in increasing numbers. Companies are each developing their unique mix of cloud technologies and service...
SYS-CON Events announced today that Golden Gate University will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Since 1901, non-profit Golden Gate University (GGU) has been helping adults achieve their professional goals by providing high quality, practice-based undergraduate and graduate educational programs in law, taxation, business and related professions. Many of its courses are taug...
SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
SYS-CON Events announced today that DXWorldExpo has been named “Global Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Digital Transformation is the key issue driving the global enterprise IT business. Digital Transformation is most prominent among Global 2000 enterprises and government institutions.
From 2013, NTT Communications has been providing cPaaS service, SkyWay. Its customer’s expectations for leveraging WebRTC technology are not only typical real-time communication use cases such as Web conference, remote education, but also IoT use cases such as remote camera monitoring, smart-glass, and robotic. Because of this, NTT Communications has numerous IoT business use-cases that its customers are developing on top of PaaS. WebRTC will lead IoT businesses to be more innovative and address...
21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
Recently, WebRTC has a lot of eyes from market. The use cases of WebRTC are expanding - video chat, online education, online health care etc. Not only for human-to-human communication, but also IoT use cases such as machine to human use cases can be seen recently. One of the typical use-case is remote camera monitoring. With WebRTC, people can have interoperability and flexibility for deploying monitoring service. However, the benefit of WebRTC for IoT is not only its convenience and interopera...
When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
SYS-CON Events announced today that Secure Channels, a cybersecurity firm, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Secure Channels, Inc. offers several products and solutions to its many clients, helping them protect critical data from being compromised and access to computer networks from the unauthorized. The company develops comprehensive data encryption security strategie...
An increasing number of companies are creating products that combine data with analytical capabilities. Running interactive queries on Big Data requires complex architectures to store and query data effectively, typically involving data streams, an choosing efficient file format/database and multiple independent systems that are tied together through custom-engineered pipelines. In his session at @BigDataExpo at @ThingsExpo, Tomer Levi, a senior software engineer at Intel’s Advanced Analytics ...
WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, will introduce two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a...
SYS-CON Events announced today that App2Cloud will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. App2Cloud is an online Platform, specializing in migrating legacy applications to any Cloud Providers (AWS, Azure, Google Cloud).
When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
Internet-of-Things discussions can end up either going down the consumer gadget rabbit hole or focused on the sort of data logging that industrial manufacturers have been doing forever. However, in fact, companies today are already using IoT data both to optimize their operational technology and to improve the experience of customer interactions in novel ways. In his session at @ThingsExpo, Gordon Haff, Red Hat Technology Evangelist, shared examples from a wide range of industries – including en...
Detecting internal user threats in the Big Data eco-system is challenging and cumbersome. Many organizations monitor internal usage of the Big Data eco-system using a set of alerts. This is not a scalable process given the increase in the number of alerts with the accelerating growth in data volume and user base. Organizations are increasingly leveraging machine learning to monitor only those data elements that are sensitive and critical, autonomously establish monitoring policies, and to detect...
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. Jack Norris reviews best practices to show how companies develop, deploy, and dynamically update these applications and how this data-first...