I use LinuxMint 11 Gnome 64, LO 3.5.3.2
Install MySQL server and admin stuff. Using Software Manager
- mysql-server --this is a metapackage for all the required server programs for latest build
mysql-admin --this is a GUI admion tool for mysql
mysql-query-browser --used to enter queries and scripts directly
libreoffice-mysql-connector --needed to connect directly to MySQL without ODBC or JDBC wrapper.
Open mySQL Administrator and enter the following:
- hostname = localhost
port = 3306
username = root
password = [blank]
MySQL Administrator - Next Steps
- create password for root account
create new user - same as my normal user
Create a new Schemata- Right click in Schemata area
Select create schema
Name it GVaccounts
- Right click in Schemata area
- Now I want to connect to this new schema from LibreOffice Base and save as a new Database
Open LibreOffice Base
Connect to an existing database
Select MySQL and press next
Asks for ODBC or JDBC Connectivity.... OK time to install the libreoffice-mysql-connector package from software manager to see if I can get native support
Awesome, restarted base and now get a third option to Connect directly! Choose that one
Database name: GVaccounts
Server/Port = localhost 3306
Saved ODF file as GVaccounts in appropriate directory
got an error that could not connect (using password = no).... need to figure out how to enter password
Do this in Edit/Database/Properties
Transfer Tables -- Method 1 - Copy and Paste (I did not Use, but would work fine)
This method may be faster than the method I used, but does require that you pay close attention to Field formats, writing down the format for each field, then selecting the proper format as part of the Import procedure. You will also may need to set your key fields after you import the data. The steps are the same as the Data Import - Copy and Paste into Existing Table section below
- ... except after you right click in the Table area of the new MySQL connected base file,
you type the name of the new table you are creating,
then you select to import definition and data
then you need to setup your field data types
and setup your keys (maybe after importing data)?
I quickly tested this method and it seems to work fine, but I personally imported my table definitions from the sql dump instead.
- Now that I can connect to MySQL database, copy the tables over
Open the new MySQL pointing database side by side with old copy of my database and attempt to drag and drop tables- I get the message "unknown database root@localhost when logged to server by root
I get the message "create command denied by User..." when logged to server by my new user- seems like it is not pointing to specific database... also the system database mysql shows in the Tables
I may need to change Server parameter from localhost to something that specifies the exact database...... looking into it
OR... the Database name I give of "GVaccouts" is missing something
as mentioned, I see GVaccounts and mysql databases
So I create a table in the GVaccounts database - seems like it is not pointing to specific database... also the system database mysql shows in the Tables
- Tried to create a new connection to a specific database using another port, but did not work
OpenOffice Bug reports including copying tables into Base connected to a MySQL database not working properly... This exactly describes my problem.
http://wiki.services.openoffice.org/wik ... k_properly
There are some report of dragging to copy working with JDBC based connection (not native), but the above report suggest this is also broken. Here is the report where one users stated success. http://user.services.openoffice.org/en/ ... php?t=6924
Not sure I want to also setup a jdbc connectoion to MySQL while I am not sure it will work either, so I think I will try the next approach on my radar... - I get the message "unknown database root@localhost when logged to server by root
Because dragging and dropping tables was not working, try a more round about method of Exporting from the old database.
- Export database
- Open my old database
Tools/SQL
Enter command- SCRIPT '/home/UserName/pathiwant/database.sql'
Status reports "Command successfully executed." --seemed way to fast for the amount of data I have.
- Right click file and select Open with Text Editor (gedit)
Amazing, Table create statements exist for each table, plus Insert statements for all data! Looks great so far.
- keep database.sql file open in text editor
Open MySQL Query Browser
copy a create table statement over and execute- getting a syntax error, studying syntax
- CACHED (from CREATE CACHED TABLE) apparently not allowed
Remove quotes from around table and field names... not correct
longvarchar is not an acceptable data type
primary key must be specified as separate item, separated by comas e.g. ", PRIMARY KEY(field), "
- Official: http://dev.mysql.com/doc/refman/5.6/en/ ... table.html
Openoffice base data formats: http://wiki.services.openoffice.org/wik ... Data_Types
MySQL data formats: http://dev.mysql.com/doc/refman/5.6/en/data-types.html
- Did not work:
CREATE CACHED TABLE "CategoryDef"("Category" VARCHAR(50) NOT NULL PRIMARY KEY,"CategoryDetails" LONGVARCHAR)
Worked:
CREATE TABLE CategoryDef(Category VARCHAR(50) NOT NULL, PRIMARY KEY(Category), CategoryDetails VARCHAR(255))
I will change to ANSI quotes mode via MySQL Administrator as follows.- Open MySQL Administrator, connecting to the correct data source
Navigate to Startup Paramaters, then the Advanced tab
check the box "Use ANSI sql" about 2/3 way down page
click the save changes button at the bottom right of the window- I get an editing right error
bottom of form tells me my config file is at /etc/mysql/my.cnf
this means I need root privelages to make this change
look at menu item to figure out how to launch from command line
gksudo /usr/bin/mysql-admin- make the change described above. I suggest when you get to the check box you first click revert changes, then check the ANSI sql box, then save changes. (when I was scrolling down, I accidentally changed another value on the form when I scrolled over it... and the value scrolled to another value). The revert button changes anything back to how it was until you hit save.
- I get an editing right error
- paste another of the CREATE TABLE statements which was output from Base into it.
remove the CACHED from CREATE CACHED TABLE
fix any mis-mached data types (this time there were none)
Query browser now accepts how the primary key is defined, so no need to change it
Success!
- remove CACHED from between CREATE TABLE
changed LONGVARCHAR to VARCHAR(255)
changed GENERATED BY DEFAULT AS IDENTITY (START WITH 0) to AUTO_INCREMENT
- Open MySQL Administrator
Select Catalogs, then the schemata desired
Select a table, then choose the button "Edit Table"
On Table Options Tab, in the Sotrage Engine area, choose InnoDB from the drop down box.
Click Apply Changes, then execute.
Repeat for each table.
CREATE VIEW statements... not sure yet if I need these views with MySQL backend. I will insert them for now... by copying one CREATE VIEW statement at a time into Query Browser.- casewhen does not exist in MySQL, converted statement to CASE WHEN... ELSE...END statements
You can see the new views in MySQL administrator via Catalogs/YourSchemata/Views tab
And I can see the Views in Base in the Table browser section
- Open my old database
Copy and Paste (append) into Existing Table Base (old) to Base (new)
- After trying to copy whole tables including definitions without success, I gave up on the Copy from One Base (embedded-old) to the other Base (MySQL connected-new). Turns out I gave up too soon. Discovered you can Copy and Paste if you have the Table Definition and only Append the Data.
I did have a data definition conflict on one table which I couldn't figure out how to resolve, so ended up copying data over for that table via the next method.
This is much like the following procedure, but much quicker.
Steps- Verify your table definitions exist properly in the new Database/MySQL
In the old Base program Right click a table and select copy
In the new Base program Right click the corresponding table and select paste- Select Append data
Ensure the table name is correct as SchemaName.TableName
click next then make sure all the source table fields are selected and the orders fo the fields are the same
Click Create and verify your data was imported.
Success!
- Select Append data
- Verify your table definitions exist properly in the new Database/MySQL
Thanks to: http://user.services.openoffice.org/en/ ... =5&t=38908
- Register your old database as a data source in LibreOffice
- Tools/Options/LibreOffice Base/Databases/New
Give the data source a name and point to the file
Import data into spreadsheet- Open a Blank spreadsheet (or new tab)
Press F4 or select View/Datasources
In the new data section above the spreadsheet, Navigate to the Table of the old database you want to import
When the table data shows to the right, click in the upper left box of the data to select all data (or select all or a subset any other way)
Copy data down to the spreadsheet by copy/paste or by dragging
- Highlight the data in the spreadsheet, including headers
Navigate to you new MySQL connected database
Right click on the Table name you want to append this data into and choose paste
Choose "Append data" and "Use first line as column names", verify the table name is correct as YourSchemaName.TableName and press Next
Make sure the box is selected for the Source table field of each field you want to import (each one in this case)
Make sure the field order between the Source and Destination tables are the same, use the arrows to modify if needed (shouldn't be necessary if you created tables exactly the same)
Select the Create button, then open the table to verify your data is there (may need to wait a sec for large amounts of data)
Success!
- Tools/Options/LibreOffice Base/Databases/New
http://user.services.openoffice.org/en/ ... 29#p236529
- Open the .sql dump file in a text editor
Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
Copy and paste into a blank document in a text editor of your choice. I am using LibreOffice Writer 3.5
Find and replace to place semi-colons at the end of each line.- Find statement:
- If you are certain that your data has no end parenthesis within it ")", then you can simply use that.
Some text editors allows you to use "&" for end of line, mine did, but skipped lines. Finding on this was enabled via selecting "regular expressions" find
if your last field was always text you could use ') or NULL)
My example:- position at the top of the document
Edit/find and replace- ensure "regular expressions" is not selected in "more options" section
Search for = )
Replace with = );
select replace all
- ensure "regular expressions" is not selected in "more options" section
- If you are certain that your data has no end parenthesis within it ")", then you can simply use that.
Select File/New Script tab
Copy and paste your modified INSERT INTO statements into the New Script tab
Click on Execute button
Open your database and verify the records have been inserted.
Success! - Find statement:
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html
syntax: http://dev.mysql.com/doc/refman/5.6/en/load-data.html
- Open the .sql dump file in a text editor
Locate a desired set of INSERT INTO statements (perhaps a single table, perhaps all your tables).
Copy and paste into a blank document in a text editor of your choice. I am using LibreOffice Writer 3.5
File/Save as text format (.txt)
Open mysql command line (open terminal in Linux)- open terminal (Linux)
mysql -u DBUser -h [localhost or servername or ip] -p
"-h localhost" is optional on local machine
- connect YourDB
This would work well if you had data in a spreadsheet then saved it as a delimited txt output file.
I could modify my SQL dump to remove the extra stuff with find and replace, but what's the point... could load into MySQL Query browser just as easily... see next section- for fun, I did this... but didn't follow through to complete success (unsure how to quote a single quote successfully) The statement I got to, then decided it was unnecessary effort and no more fun was this:
LOAD DATA LOCAL INFILE '/home/chenier/Documents/GreenView/GVaccount/DataTemp/HouseDef2.txt' INTO TABLE HouseDef FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY ' LINES TERMINATED BY '\r\n';
error on the FIELDS ENCLOSED BY
- open terminal (Linux)
http://forge.mysql.com/wiki/MySQL_User_ ... _Tutorials
- This is much like the section above, but with exporting data into txt file via spreadsheet save as instead of directly from sql dump.
- Using the txt file with the INSTERT INTO commands I created above, I changed the name to Name.sql
I then added semi-colons at the end of each statement and used the following command from the command line to successfully import data: (again, requiring to add semi-colons for large amounts of data is not awesome.)- mysql -u chenier -p GVaccounts < /home/chenier/Documents/GreenView/GVaccount/DataTemp/HouseDef3.sql
Success!
Alternate approach... without adding semi-colons: See the following help- explains how to modify the delimiter used http://dev.mysql.com/doc/refman/5.6/en/ ... ining.html
Input conventions talk about delimiter and line feed character using CRLF http://dev.mysql.com/doc/mysqltest/2.0/ ... tions.html
http://dev.mysql.com/doc/connector-net/ ... miter.html
The command I was trying, but unable to work properly:- mysql -u chenier -p --delimiter CRLF GVaccounts < /home/chenier/Documents/GreenView/GVaccount/DataTemp/HouseDef.sql
OTHER DATABASE ITEMS
Queries, Forms and Reports Importing
- Open the old database and the new database side by side
Drag and drop (or copy/paste) each object you want to keep from one to the other
This is not a comprehensive list, just the items I ran across
- Queries
- Table Names - change to include Schema name --see below
Top statement -> Replace with Limit statement at end
http://www.w3schools.com/sql/sql_top.asp
- Change Table Names whenever referenced
- Form Properties/Data tab => Content and Filter properties
Change table name to include Schema name in front- e.g. Filter to GVaccounts.Filter
Remove Double quotes from around Table name whenever exist
- e.g. Filter to GVaccounts.Filter
- Form Properties/Data tab => Content and Filter properties
- Table Names - change to include Schema name --see below
- In the new Database, Create modules to correspond to those in the old database
- Tools/Macros/Organize Macros/LibreOffice Basic
In your database, navigate to the correct location and select new
Rename the macro calls if you need to
Changes within the macros- none yet...
- Tools/Macros/Organize Macros/LibreOffice Basic
REFERENCES
- LibreOffice Base FAQ - lots of info https://wiki.documentfoundation.org/Faq#Base
MySQL Connector/OpenOffice.org -- Thorough reference
http://dev.mysql.com/doc/refman/5.6/en/ ... r-ooo.html
and similar reference from mysql web site: http://forge.mysql.com/wiki/Connector_OpenOffice
OpenOffice native MySQL reference
http://wiki.services.openoffice.org/wik ... ection_URL
Lists connection URL as: sdbc:mysql:mysqlc:[host[:port]]/database
And gives details
C connection API for MySQL (extra info)
http://dev.mysql.com/doc/refman/5.6/en/ ... nnect.html
X-driver (Openoffice Wrapper) connection info: (extra info)
http://www.openoffice.org/api/docs/comm ... ml#connect
OpenOffice Bug reports including copying tables not working propertly
http://wiki.services.openoffice.org/wik ... k_properly
OpenOffice Bugs - general for MySQL Native Driver
http://wiki.services.openoffice.org/wik ... SQL_Native
Importing MySQL Dumpfile (something.sql)
http://www.cyberciti.biz/faq/import-mys ... -database/
http://crazytoon.com/2007/11/28/mysql-h ... ll-script/
MySQL statement syntax, enabling ANSI quotes!: http://dev.mysql.com/doc/refman/5.6/en/identifiers.html
explains and lists modes: http://dev.mysql.com/doc/refman/5.6/en/ ... -mode.html
MySQL INSERT INTO syntax guide: http://dev.mysql.com/doc/refman/5.6/en/insert.html
Techotopia guide about INSERT INTO http://www.techotopia.com/index.php/Ins ... L_Database
Export sql dump file from Base
This one is presented simply enough: http://www.oooforum.org/forum/viewtopic.phtml?t=32333
example given: SCRIPT 'C:\xtrasns.sql' in the Tools/SQL section, then open file and past individual statements rather than the whole thing for importing
Exporting OpenOffice Files into other OpenOffice Programs via Copy and Paste or F4, once you have registered your database... not useful for importing data into Base, but very useful for outputing Query results to spreadsheets, etc.
http://www.ehow.com/how_5914884_export- ... table.html
Installing MySQL on Ubuntu: http://blog.sudobits.com/2011/09/25/how ... ntu-11-10/
MySQL postinstallation setup and testing (general) http://dev.mysql.com/doc/refman/5.5/en/ ... ation.html
Getting Started with MySQL http://dev.mysql.com/tech-resources/art ... intro.html
MySQL Administrator Documentation: http://dev.mysql.com/doc/administrator/en/
MySQL Workbench GUI tool..... hmmm http://www.mysql.com/products/workbench/
This product looks sharp and is said to replace both MySQL Administrator and MySQL Query browser plus much more including visual Database Design..... Worth checking into sometime. Workbench area has SQL Development, plus Data Modeling and Server Administration. Visual Database Design allows Table layout and connections representation. SQL Editor is just that but with code snippet saving and reusing, visual SQL code help and cues. Workbench also has reverse engineering of existing databases, change management and database documentation.