.
|
LibreOffice Base
Video Tutorial
HomePage
LibreOffice Base - Links :
- Video 6 - Creating a Form
Create a form for our employee table to view each employee individually, and how to quickly find a record you are looking for.
- Video 62 - Standalone Switchboard
We learn how to run the forms we created straight from our desktop as a standalone form. Note: Macros will NOT work in standalone forms.
- Video 79 - E-mail from LibreOffice
We look at the possibilities of sending an e-mail from LibreOffice. (The idea of e-mailing the invoices completed in the previous tutorial)
At this moment, LibreOffice cannot e-mail a document out by itself, it can open Thunderbird and set a LibreOffice document up as an e-mail or as an attachment(in various formats). LibreOffice has started an e-mail feature; this can be seen by clicking on the "Mail Merge Wizard..." feature in LibreOffice Writer. This functionality is not (yet) working
- Video 80 - Install MySQL
In this video we see how to install MySQL.
Over the next few videos, we are going to look at how to connect LibreOffice Base to a MySQL database. This video will show you the options I chose when installing MySQL on my computer.
- Video 81 - Connect with ODBC
We see how to use the ODBC connector to connect LibreOffice Base to a MySQL Database
The first thing we do is delete the MySQL installer file (no-longer needed). Then open a Writer document to see if the MySQL connector extension is installed.
Where to download the LibreOffice MySQL connector extension is then shown as well as how to install it.
A new MySQL database is created using the MySQL server 5.6 Command Line Client.
A new LibreOffice Base database is created and connected to the new MySQL database using the ODBC connector.
Tables are created for the MySQL database using LibreOffice Base.
Data is entered into the MySQL tables from LibreOffice Base.
A relationship is created using LibreOffice Base
Forms are created using LibreOffice Base, and we see that Subforms do not appear to work for the ODBC connector, but simple forms do work.
- Video 82 - Connect Directly with problem
We create another MySQL database and connect directly to the database. We are able to successfully create tables for this database, proving LibreOffice is connecting properly, but we can't view the tables, so we can't create any data for the tables or create any forms using LibreOffice. In the past, I have gotten this to work properly, on this computer, but for some reason, right now, I c annot view my tables.
- Video 83 - Connect Directly
On a different computer, I connect directly to MySQL and we see how to create tables, add data, and create a form (with a subform) by connecting to MySQL directly.
- Video 84 - Connect with JDBC
We see how to connect LibreOffice Base to a MySQL database using JDBC (Java Database Connectivity)
When we first attempt to connect LibreOffice to a MySQL database using the JDBC connector, we get the error “The JDBC driver could not be loaded” for com.mysql.jdbc.driver.
We see how to solve this problem by identifying the mysql-connector-java-5.1.34-bin.jar file, then in LibreOffice Tools, Advanced, Class Path, adding that folder and getting the proper file name into the open file area.
Having resolved the problem, we then go on with the same tests we did with the Direct and ODBC connections showing the Java connection seem to work best of the three connection types.
- Video 85 - Multiple MySQL Users
We add Multiple users to MySQL and give different authority to each user.
First we add a user from the MySQL Command Line Client, this user can only view a specific table on a database.
Then we use the MySQL Workbench, Users and Permissions to add a MySQL user with full access to any database that starts with the first three letters of odb.
We add one more user using the MySQL Workbench, Users and Permissions to give a user the authority to view a specific database.
- Video 86 - MySQL Workbench (1)
We take a first look at MySQL Workbench a tool that can help us manage our LibreOffice Base MySQL databases.
We see we can view all of our LibreOffice MySQL databases as well as see information about the database or information about a table on the database.
We see how to update a MySQL table using MySQL Workbench – and that it creates SQL to apply the change.
We also use MySQL Workbench to update information on a MySQL table and that MySQL Workbench creates SQL to apply our update.
We also see MySQL Workbench has a button to reformat SQL, similar to the way I would pause the video and reformat the SQL in the LibreOffice Base SQL videos.
- Video 87 - MySQL Workbench (2)
We learn how to reverse engineer a MySQL Database using MySQL Workbench.
We reverse engineer a MySQL database, which means create a model and an EER (Enhanced Entity Relationship) Diagram from an active MySQL database (schema).
We look at how to add a field to the model as well as add a table, then we synchronize that model with the table on the database.
We also see how to save our model to our desktop.
- Video 88 - mysql_searchform (1)
We create a new MySQL database called mysql_searchform and all the tables for it.
We create a mysql workbench model called mysql_searchform.
We create tables for our new model similar to the tables we used in LibreOffice Base tutorial (57) Employe search Form pt2.
We then forward engineer a database from our model to create the new MySQL database mysql_searchform
- Video 89 - Create .csv files
We create .CSV backup files from the odb57 LibreOffce Base tables to upload into the new MySQL tables.
We need to edit the data a little bit to have it load in properly to the MySQLfiles. We need to delete the Header Records and change the Date Format to be YYYY-MM-DD.
- Video 90 - Upload .csv files
We update MySQL tables with the .csv files we created in the last video.
We see the SQL used to upload a .csv file into a MySQL table using MySQL Workbench.
When we upload the employee table, everywhere MySQL Workbench finds a NULL for the End Date, a date of 0000-00-00 is forced in. This date will not work with LibreOffice Base and we need to create some SQL to reset the zero date to null.
We also learn we need to turn off the MySQL Safe Update feature by using SET SQL_SAFE_UPDATES=0; before running a MySQL UPDATE.
- Video 91 - MySQL Relationships
We create some relationships for the MySQL tables using MySQL Workbench.
We see how to create a basic 1 to many relationship and set the foreign key parameters when the primary key is deleted (Cascade, Set to Null, No Action).
Then we look at how to change the colors of the tables and add layers to group like tables (for visual model use only – no effect on MySQL performance)
We finish by applying the relationship changes to the database.
- Video 92 - MySQL Pictures
We add pictures into the MySQL employee table.
We look at the MySQL employee table and see what type of blob (Binary Large Object) we should use to store our pictures (Tiny Blob, Blob, Medium Blob, or LongBlob)
Then we add a picture into the MySQL database using MySQL Workbench.
We end by creating a LibreOffice Base database form that will display the picture from the MySQL database and we will add another picture to the MySQL table using that same LibreOffice Base form.
- Video 93 - MySQL Searchform
We start to create the LibreOffice Base form to search the for employees on the MySQL database.
We start creating the LibreOffice base document by connecting it to a MySQL database using a JDBC connection.
We connect to the filter table to the form and the employee table to the subform.
We add a header, arrange and format fields on the form, and add a refresh button.
- Video 94 - MySQL Searchform
We complete the LibreOffice Base form, that searches for employees on a MySQL database, that we started in the previous video.
First we add a drop-downs to the Department and Group text boxes in the search criteria. Then we change the Department-ID and Group-ID to Department and Group in the subform and reformat the Start Date in the subform.
We add the variable link fields for the SQL join and add the SQL from odb57 into the SQL command area.
We test the form and find that most of the SQL is good, but the MySQL SQL is different than the HSQLDB SQL for upper case and concatenate. We need to use UCASE instead of UPPER to get our data to the upper case and CONCAT() in place of || to concatenate the fields together.
|
|
