*Warning

(for adventurous eaters only)


Libreoffice Base Sqlite

broken image


Open a Base file of the database type that you want. Either create a new Base file using the Database Wizard, or open any existing Base file that is not read-only. Open the Calc file that contains the data to be imported to Base. You can open a.dbf dBASE file or many other file types. The average Base user is not informed enough to assess all options before beginning a Base project, so the default must be flexible enough to meet all of their expectations (ala MS Access). HSQLDB 2.x provides that flexibility. SQLite does not. Base is enhanced by the SQL standards-compliance found with HSQLDB as this speeds the learning process.

Tutorials‎ > ‎

LibreOffice Base: Connect to any kind of database

Although LibreOffice Base works with hsqldb (*.odf) files by default, you can, in fact, use it as a common GUI tool to connect to any kind of database, all you need is the right JDBC driver to do the job. Here is how you do it:
Open LibreOffice main app (with white icon), go to Tools->Options->Libre Office->Advanced and on the right-hand side, click the Class Path button on the right-side of the dialog:

In the dialog that comes up, add the path to your JDBC driver jar files (for example, sqlite-jdbc-3.7.2.jar) for whatever databases you want to connect. In a section below, I'll list down where to get these JDBC drivers for all kinds of databases.
Once this is done, click OK and exit from this configuration dialog, and then click File->New->Database. Select 'Connect to an existing database' radio button, and then select JDBC from the dropdown. Click Next and then enter the connection parameters and JDBC Driver class as shown below:

Click the 'Test Class' button to ensure that everything is good, and in the dialog that comes next, also test the database connection. After that, just file this new database which will be just a link to your actual database, and you are good to go! You can thus use LibreOffice Base as a proper GUI client tool (such as Toad or SQLDeveloper) to connect to just about any kind of database. The only condition is that the Java JDBC driver is available for that database (and it usually is, since Java is a widely used programming language).
Here is where you can obtain JDBC drivers for popular databases:
SQLite:
https://bitbucket.org/xerial/sqlite-jdbc
http://www.ch-werner.de/javasqlite/
MySQL:
https://dev.mysql.com/downloads/connector/j/
MariaDB:
https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/
MongoDB:
https://docs.mongodb.com/ecosystem/drivers/java/

Microsoft SQL-Server:
https://msdn.microsoft.com/en-us/library/mt484311(v=sql.110).aspx
Microsoft Access:
http://ucanaccess.sourceforge.net/site.html (Also refer to this)
Oracle:
http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html


References:
http://stackoverflow.com/questions/14640907/where-to-download-sqlitejdbc
http://askubuntu.com/questions/10635/how-can-i-install-sqlite-to-be-used-with-openoffice
http://askubuntu.com/questions/276725/how-do-i-get-libreoffice-base-to-access-ms-access-2003-data

Do you also dislike it to manually convert Excel tables into PostgreSQL? Not having real fun in exporting to CSV, then manually writing the SQL header and importing the stuff in PG? This article here explains how to convert a table into an SQL database via OpenOffice. Essentially you open the Excel (or whatever) table with OpenOffice-Calc, select and copy the full table or a subset to the clipboard, and simply paste it into OpenOffice-Base with the right mouse button. A wizard pops up which let's you define the type of each column and a primary key. Save & done. Can't be easier!

  • 1Usage of OpenOffice-Base with various drivers
    • 1.2PostgreSQL (OpenOffice.org 2.x)
  • 2Converting Excel, CSV, .. to PostgreSQL/MySQL/.. via OO-Base

Usage of OpenOffice-Base with various drivers

  • See Database drivers page for overview. There are drivers for PostgreSQL, SQLite and MS-Access mdb files. Also you can check an expanded list of ODBC drivers for Oracle, SQL Server, PostgreSQL, MySQL, SQLite.

PostgreSQL (LibreOffice.org 3.x)

The version LibreOffice 3.5 introduces a new PostgreSQL driver: http://www.libreoffice.org/download/3-5-new-features-and-fixes/Alternative PostgreSQL ODBC driver perfectly works with the latest LibreOffice.

PostgreSQL (OpenOffice.org 2.x)

Installation of PostgreSQL driver (MS-Windows and Linux)

  1. download postgresql-sdbc-X.Y.Z.zip driver (take the 'recommended' file) and save the file to your disk.
  2. Start OpenOffice, choose 'Tools/Package Manager'. Click on the add button and select the downladed file (DO NOT EXTRACT IT !) in the upcoming File dialog. The driver gets installed now, afterwards, the file is listed in the dialog with state enabled. In case you have installed an older version of the driver, remove it now. Close the dialog.
  3. Restart openoffice (even the quickstarter on windows must be shut down !!!).
  4. Done (did you really close openoffice? Please do.)

Usage of PostgreSQL driver (MS-Windows and Linux)

After starting OpenOffice.org again, use: File -> New -> Database to enter the DB dialog.

  • Connect to existing database (select driver): postgresql
  • Connection settings: We have to define the server ('host') name and the name of the database, probably also the user name in case you have to connect with a different user name. To find out, ask your DB administrator or use in a terminal 'psql -l ' or, if the database is on a remote server, 'psql -h host.mydomain.org -l'. An entry may look like this:
  • User name (the login name): yourname
  • Finish brings you to a 'Save as' dialog for the connection, saves these settings as file. This file you can use later to re-establish the connection (instead of defining all again).
  • Then click on 'Tables' in the next screen to see the existing PG tables in the database. Usually the 'public' scheme contains the user tables.
  • The 'Queries' button takes you to the query mode, it contains a graphical query designer.

Installation of PostgreSQL driver (MacOSX)

Please use the JDBC driver.

There are not mac OSX version of official SDBC driver, you can connect your postgreSQL database using the JDBC driver. But there is unofficial PosgreSQL ODBC driver for MAC OS X which you can download for free.


1)Download the JDBC driver at :PosgreSQL
(select the driver for your pgsql version)for example with postgreSQL-8.2 (postgresql framework installation - until 20/3/2008) we need to download this driver:


2) Open Neooffice- preferences:

Libreoffice Base Sqlite Download

select :

click on :

click on :

select the downloaded driver

click on :

3) Come back to :

In the database configuration window:

at the point : connect to an existing database (collega a un database esistente), select the option :


click on :

insert the url to your pgsql database (in this example we have host=localhost, dbname=grassdb):

class driver JDBC :

click on :

if oll is ok :

click on :

Libreoffice base sqlite windows

go to the next step

insert :

click on :


http://img300.imageshack.us/img300/9965/immagine6ck5.png


insert :

click on :

if all is ok :

click on :

click on

Libreoffice Base Sqlite

to save the database

database successfully created:

MySQL (OpenOffice.org 2.x)

  • Connection via JODBC driver: http://kienlein.com/pages/mysql-jdbc-howto-en.html
  • Connection via MySQL ODBC driver

DBF files (OpenOffice.org 2.x)

No installation needed, the driver is included in the standard version of OpenOffice.org.

Usage:

  • File -> New -> Database -> 'Connect to an existing database' -> dBASE
  • -> NEXT button
  • Set path to dBASE files (Browse if needed: only the path to the right directory)
  • -> NEXT button
  • -> FINISH button, this asks you to enter a new name for the ODF file which stores the connection (call it 'test' or else)

Now the graphical user interface appears. In the lower part the tables are listed which are available in above selected path.

Filezilla client download 64. Download FileZilla Client 3.53.1 for Windows (64bit x86) The latest stable version of FileZilla Client is 3.53.1. Please select the file appropriate for your platform below.

  • to change a DBF definition: right-click the table of interest from the list and select 'Edit'. Then you can modify column definitions and save
  • to modify table content: double-click the table

Libreoffice Base Sqlite Ubuntu

MS-Access mdb files (OpenOffice.org 2.x)

  • http://dba.openoffice.org/drivers/mdb/index.html Note that the driver is somewhat behind the mdbtools.sf.net development.
  • Connect through ODBC based connection: http://www.openoffice.org/FAQs/ms-access/ms-access.html . For Linux, you need unixODBC to be installed (your preferred distribution most likely contains this package). Get also the MDB Tools (preferably fresh from CVS since the 0.5 release is old; compile with unixodbc support enabled). Then follow instructions at http://www.gdal.org/ogr/drv_pgeo.html (or add them here).

SQLite files (OpenOffice.org 2.x)

  • OO-Base SQLite driver (driver still in alpha state)
  • Tested and working SQLite ODBC driver. Connection string and installation guide of SQLite ODBC driver https://www.devart.com/odbc/sqlite/docs/using_odbc_driver.htm
  • SQLite ODBC Driver (wrapped SQLite library into an ODBC driver). Installation:

After installation of 'sqliteodbc', add the driver to /etc/odbcinst.ini (either add next lines or create as new file if the file does not yet exist):

Now the SQLite driver is available for ODBC. The next step is to add the definition(s) of the database you want to connect to. Wechat 2fa. Add an ODBC Data Source Name (DSN) to your definition file at $HOME/.odbc.ini (replace 'user' and 'mymapset' with the correct entries):

Libreoffice Base Sqlite

Converting Excel, CSV, .. to PostgreSQL/MySQL/.. via OO-Base

  • Bringing data into OpenOffice 2.0's database: You can for example open an Excel file with OpenOffice, select contents, then open a DB connection in OpenOfficeBase and paste the table into it. The wizard allows to define the type of each column before creating the table. Using the right mouse button on a column name, you can define it as primary key. Important: If generating a PostgreSQL table, prefix the new table name with 'public.' (example: public.table1) to avoid that the user name is used as schema name.

Step by step DB import procedure (MS-Windows and Linux)

Here we show how to get a spreadsheet table into a SQL database.

1. Mark and copy to clipboard the relevant table parts in the Excel/CSV/whatever table (OO-Calc):

Open the original table with OpenOffice-Calc. For CSV files, they need the .csv extension.

(see also larger screenshot)

2. Open OO-Base and establish a connection to the database (see above for driver installation):

Go File -> New -> Database to launch OO-Base.

Libreoffice Base Sqlite
(see also larger screenshot) (see also larger screenshot) (see also larger screenshot)

3. In the table wizard, paste (right mouse button) earlier selected OO-Calc spreadsheet into the table section:

First select 'table' from left menu, then paste into it:

Libreoffice base sqlite download
(see also larger screenshot)

4. A wizard opens, define a table name:

When connecting to PostgreSQL, be sure to write a prefix for the schema, usually 'public.':

(see also larger screenshot)
Libreoffice base sqlite 2019

go to the next step

insert :

click on :


http://img300.imageshack.us/img300/9965/immagine6ck5.png


insert :

click on :

if all is ok :

click on :

click on

to save the database

database successfully created:

MySQL (OpenOffice.org 2.x)

  • Connection via JODBC driver: http://kienlein.com/pages/mysql-jdbc-howto-en.html
  • Connection via MySQL ODBC driver

DBF files (OpenOffice.org 2.x)

No installation needed, the driver is included in the standard version of OpenOffice.org.

Usage:

  • File -> New -> Database -> 'Connect to an existing database' -> dBASE
  • -> NEXT button
  • Set path to dBASE files (Browse if needed: only the path to the right directory)
  • -> NEXT button
  • -> FINISH button, this asks you to enter a new name for the ODF file which stores the connection (call it 'test' or else)

Now the graphical user interface appears. In the lower part the tables are listed which are available in above selected path.

Filezilla client download 64. Download FileZilla Client 3.53.1 for Windows (64bit x86) The latest stable version of FileZilla Client is 3.53.1. Please select the file appropriate for your platform below.

  • to change a DBF definition: right-click the table of interest from the list and select 'Edit'. Then you can modify column definitions and save
  • to modify table content: double-click the table

Libreoffice Base Sqlite Ubuntu

MS-Access mdb files (OpenOffice.org 2.x)

  • http://dba.openoffice.org/drivers/mdb/index.html Note that the driver is somewhat behind the mdbtools.sf.net development.
  • Connect through ODBC based connection: http://www.openoffice.org/FAQs/ms-access/ms-access.html . For Linux, you need unixODBC to be installed (your preferred distribution most likely contains this package). Get also the MDB Tools (preferably fresh from CVS since the 0.5 release is old; compile with unixodbc support enabled). Then follow instructions at http://www.gdal.org/ogr/drv_pgeo.html (or add them here).

SQLite files (OpenOffice.org 2.x)

  • OO-Base SQLite driver (driver still in alpha state)
  • Tested and working SQLite ODBC driver. Connection string and installation guide of SQLite ODBC driver https://www.devart.com/odbc/sqlite/docs/using_odbc_driver.htm
  • SQLite ODBC Driver (wrapped SQLite library into an ODBC driver). Installation:

After installation of 'sqliteodbc', add the driver to /etc/odbcinst.ini (either add next lines or create as new file if the file does not yet exist):

Now the SQLite driver is available for ODBC. The next step is to add the definition(s) of the database you want to connect to. Wechat 2fa. Add an ODBC Data Source Name (DSN) to your definition file at $HOME/.odbc.ini (replace 'user' and 'mymapset' with the correct entries):

Libreoffice Base Sqlite

Converting Excel, CSV, .. to PostgreSQL/MySQL/.. via OO-Base

  • Bringing data into OpenOffice 2.0's database: You can for example open an Excel file with OpenOffice, select contents, then open a DB connection in OpenOfficeBase and paste the table into it. The wizard allows to define the type of each column before creating the table. Using the right mouse button on a column name, you can define it as primary key. Important: If generating a PostgreSQL table, prefix the new table name with 'public.' (example: public.table1) to avoid that the user name is used as schema name.

Step by step DB import procedure (MS-Windows and Linux)

Here we show how to get a spreadsheet table into a SQL database.

1. Mark and copy to clipboard the relevant table parts in the Excel/CSV/whatever table (OO-Calc):

Open the original table with OpenOffice-Calc. For CSV files, they need the .csv extension.

(see also larger screenshot)

2. Open OO-Base and establish a connection to the database (see above for driver installation):

Go File -> New -> Database to launch OO-Base.

(see also larger screenshot) (see also larger screenshot) (see also larger screenshot)

3. In the table wizard, paste (right mouse button) earlier selected OO-Calc spreadsheet into the table section:

First select 'table' from left menu, then paste into it:

(see also larger screenshot)

4. A wizard opens, define a table name:

When connecting to PostgreSQL, be sure to write a prefix for the schema, usually 'public.':

(see also larger screenshot)

5. Select the columns you want to transfer:

(see also larger screenshot)

6. Define primary key (right mouse button on desired column) and all column types:

WARNING: If you make a mistake in defining the column types, all rows after the first failing conversion will be silently ignored. Verify later that all rows arrived in the final SQL table.

(see also larger screenshot)

7. Save and enjoy. The table is now written into your SQL database.

Note that the conversion can take some time.

You can now connect the table to GRASS, R-stats, .. as you like.

Step by step DB import procedure (MacOSX)

There are not difference between osx and linux.
Look for 'Step by step DB import procedure (MS-Windows and Linux)'.

Retrieved from 'http://grasswiki.osgeo.org/w/index.php?title=Openoffice.org_with_SQL_Databases&oldid=21993'




broken image
PreviousNext
Cookie Use
We use cookies to improve browsing experience, security, and data collection. By accepting, you agree to the use of cookies for advertising and analytics. You can change your cookie settings at any time. Learn More
Accept all
Settings
Decline All
Cookie Settings
Necessary Cookies
These cookies enable core functionality such as security, network management, and accessibility. These cookies can’t be switched off.
Analytics Cookies
These cookies help us better understand how visitors interact with our website and help us discover errors.
Preferences Cookies
These cookies allow the website to remember choices you've made to provide enhanced functionality and personalization.
Save