Connecting LO Base to an SQL DB

users
#21

Hello LibO experts,
and congratulations IanW!

It has been very interesting to follow this discussion getting Base working with MySQL.
Unfortunately -- if I got it right - is Ian's OS a Linux (Ubuntu?)

Would it be possible to get the same proceeding described as a step-by-step instruction for Windows7 (64bit)?
> in plain english (no IT-jargong)
> starting from the very beginning defining the different programs (versions) and components (ODBC, JDBC, JRE, etc when needed, included) to be installed

Since Andreas Säger (one of LibO db-experts) last winter and spring repeatedly pointed out that LibO/Base is not to be considered as a stand-alone database program I have several times tried to create the connection LibO/Base to SQLite and to MySQL (I prefer SQLite as lighter than MySQL), but without success.

I am sure that such an instruction could increase the acceptance and usability of LibO and
I am sure that we are many that would be happy.
Regards
Pertti Rönnberg

#22

Hi Pertti

Noted on your comments!! Unfortunately I'm running PCLinuxOS here and NOT Windows!! (I'm sure you will be told to move by someone!!). So I don't think it's any use me sending you the steps I went through. I did however have the same situation here and over the past 8 months or so this was the fourth attempt at getting it working.

In the past I gave up after several days of "thrashing" about and went back to Base/HSQL until it crashed on me again and so on and so on....

This time I decided to hold out and work it through to the very end!! There were many things to learn and lots of Hoops to jump through but we got there in the end thanks to the aforementioned Members of the Forum (thanks again Guys!!)

I'm sure somebody WILL pop up and try and help you out - just stay with it, one step at a time, and you'll get there in the end.

VERY early days here but so far it looks good and I have a good feeling about Base/MySQL. I'm stuck at the moment though trying to extract my data from the old DB!!! Whatever I try it just crashes on me..... (Grrrrrrr).

The saga continues and I will be VERY glad to be shot of Base/HSQL!!!

All the best

IanW
Pretoria RSA.

#23

The way I extracted my data was to right-click on each table and copy, and
then open a new spreadsheet and paste. Once in the spreadsheet you can then
save as CSV for easy importing into MYSQL.

#24

Hello Pertti,
Just for "kicks": I used to personally know some of the "old" IBMers who
actually "invented" SQL, in particular DB2. That's a long time ago though...
I have LO running on Linux-Debian, Linux-Mint, and Windows/Vista (32 Bit).
The MySQL server runs on Linux, the clients run on all of these systems
(locally and LAN- or broadband-Internet-attached).
As I mentioned before, I have taken to using the native MySQL connector
preferably. It is faster and in my view somewhat less "buggy". Besides,
I keep reading in the mail lists that LO intends to "minimise" the
dependence on Java. The native connector comes included in the Windows-LO
version as far as I remember. So all you have to do is define your connection
for it (connect to a MySQL-database and then specify that you want to use the
native connector (the other possibilities on the respective dialog box being
JDBC or ODBC)).
The LO-Base definitions on the client side are basically the same as for the
Linux-version. I used to run the MySQL server also on Windows but have not done
so for a long time. If I remember correctly, my.cnf is called my.ini (or maybe
mysql.ini) in "true" Windows fashion. Again, the definitions in that file
should not be THAT different from the Linux situation (except - maybe -
the stuff concerning sockets/pipes).
Regards from Austria
Heinrich

#25

It might not be so difficult. See if the attached file will help.

--Dan

#26

Base as a stand alone refers to embedded databases (the database data is contained in the database file. This type of database can lose data.
      However, this does not mean that you have to use MySQL, Presgresql, or Oracle, ... Nor do you have to install a JDBC or ODBC driver for Base to connect to the data. Base includes its own JDBC driver. Everything you need is included in the installed Base files.
      The last section of Chapter 3 of the Base Guide discusses how to extract the data from an embedded database and connect to this data using Base. (The data becomes an external data source. I don't have time right now to upload this to the LO Wiki, but I will be doing it in about 6 hours or so. Warning: it is a draft copy, but you are free to study it and make any suggestions or comments about it.

--Dan

#27

Thank you Dan, IanW and Heinrich
for your kind answering and encouraging!
The other day when some spare time I shall study your advice and try again.

Is it not fantastic that we like this can discuss things all across the word!!!
Pertti Rönnberg (Finland)

#28

http://wiki.documentfoundation.org/Documentation/Publications

      This is the link to the LibreOffice guides. The Base section contain links to the first 3 chapters of the Base Guide. Chapter 1 has been published. Chapter 2 & 3 are still in draft form.

--Dan (Eastern US)

#29

Hi :slight_smile:
I think quite a few of us owe a huge thanks to Dan for being a total star.  Certainly i do.  Chapter 3 is larger than 3 chapters of most other guides and it's even larger than the current iteration of one entire guide!  (Although i think the Maths Guide might grow a bit for the 3.7.x).  Also Dan seems to have fixed Chapter 2 of the Base Guide.

There are a few people working on a Base Handbook, translating it from the German.  A couple of people translated the French Faq's Base section into English.  But all that is quite a lot different from researching and writing a hefty guide from scratch!

So, top marks to Dan from me, if that counts for anything (sadly it doesn't).  Please can people who do know about Base try reading the 2nd and 3rd chapters?  That might help the docs team get it through the proof-reading stage.  Also it would be interesting to hear from people who know nothing about database programs to hear if it helped or made sense.

Of course there are different possible approaches for writing a guide and this isn't a good time to try to steer the guide in a different direction.  Between the handbook, the Faq and the guide most people will hopefully be able to find a way into understanding Base even if they haven't been aware of using database programs before.

Again, a million thanks to Dan from me. 
Regards from
Tom :slight_smile:

#30

Ian Whitfield wrote:

Thanks Guys!! (Girvin, Henirich, Dan, Mas and maybe others!!)

You have all added some pieces to the Jigsaw and I'm pleased to say......

*I now have Base working with a MySQL Database!!!! (at last!!)*

<snip>

IanW
Pretoria RSA.

Ian,
Glad to hear you got it working.

For importing your data, you might check the mysqlimport command. I used it a long time ago when I needed to get data from some MS Access files. In Access, I was able to save the data as a CSV file and then import the CSV file into my MySQL database tables. Here are two scripts I used to perform that task:
This one I called msqnewimport which deletes any data in the table before loading the CSV file

    #!/bin/sh
    # Import a ".csv" data file into a mysql table of the same name.
    # Original database data is deleted first. This creates new data.
    # Usage: msqnewimport <database> <<tablename>.csv>

#31

You are welcome Ian , glad you were able to get it resolved.