Re: [libreoffice-users] Connecting LO Base to an SQL DB [SOLVED]

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>
   #
   mysqlimport --local --delete --fields-terminated-by=,
   --fields-enclosed-by='"' --lines-terminated-by='\r\n' \
   -p -u <user> $1 $2

This one I called msqimport and it appends the CSV file data to the 
existing data in the table.

   #!/bin/sh
   # Import a ".csv" data file into a mysql table of the same name.
   # Original database data is preserved.The new data is appended.
   # Usage: msqimport <database> <<tablename>.csv>
   #
   mysqlimport --local --fields-terminated-by=,
   --fields-enclosed-by='"' --lines-terminated-by='\r\n' \
   -p -u <user> $1 $2

Replace <user> with your MySQL user name.
For more information on mysqlimport, see the manpage.

You may be interested in the MySQL database backup process as well.  
MySQL has a program, mysqldump, that writes an SQL file which can be 
used to restore a database later.    For example:

   mysqldump --user=root -p --opt <database name> > <backup filename>.sql

To restore, use:

   mysql --user=root -p <database name> < <backup filename>.sql

Note the "<" and ">" redirection characters.  These programs use stdio 
for output and input, respectively.

It has been my experience that recreating forms and reports is a time 
consuming problem when changing database clients.  I am very reluctant 
to change a database client for this very reason.  It has also been my 
experience that for reports, Base's Oracle Report Builder (ORB) is 
problematic.  For small tables, ORB works fairly well and generates 
reports at a reasonable speed.  For large tables, however, it bogs 
down.  I have a table with over 5000 records and still growing.  My 
latest try with this table and LO 3.5.7/ORB 1.2.1 generated over 500 
writer pages.  That bogged ORB down, although it is working better with 
LO 3.5.7 than LO 3.5.3.  3.5.3 was so slow as to be unusable.  I have 
even tried a 3rd party report builder called DataVision, which has its 
own set of problems, is immature, and seemingly no longer active (last 
release was in 2008).  There are also proprietary (and not free) report 
builders out there that are reported to be mature and work fine. (No pun 
intended).
Hope this helps.
Girvin





--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted