Bug 144588 - Base ODBC connection for Sqlite Inadequately Documented
Summary: Base ODBC connection for Sqlite Inadequately Documented
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
7.2.1.1 rc
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-09-18 12:43 UTC by flywire
Modified: 2021-09-22 11:50 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description flywire 2021-09-18 12:43:04 UTC
Comments relate to BG64-BaseGuide.pdf

> Accessing external databases

> MySQL connection via ODBC
> To connect via ODBC, you must of course have ODBC software installed.
> Details of how to do this are not given here.

This is totally unintelligible for ODBC, especially Windows. Maybe present the four steps briefly first before explaining details for various options for the different drivers.

*** A separate section is needed for ODBC, including how to get drivers - http://ch-werner.de/sqliteodbc/ Sqlite would be a good example to save confusion with MySQL. See https://ask.libreoffice.org/t/connecting-base-to-sqlite-in-windows/68168/12 

The type of info required and a sample database is covered in https://www.sqlitetutorial.net/


> Query enhancement using SQL Mode

*** This doesn't explain how say sqlite works with Base. A sqlite example would be good and relatively easy to set up.


Version: 7.2.0.3 (x64) / LibreOffice Community
Build ID: 2a7ea282da28d665a7dc086360567b4aea27bf08
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: threaded


I'd welcome the opportunity to review changes to the docs.
Comment 1 Julien Nabet 2021-09-19 11:40:40 UTC
You may be interested in reading https://wiki.documentfoundation.org/Documentation
to find a way to contribute.
Comment 2 flywire 2021-09-22 02:43:06 UTC
SQLite is a self-contained, serverless, zero-configuration, SQL database engine contained in a single file. It is arguably the most widely used database engine used today, and Base can connect to SQLite using ODBC.

External databases must be prepared before the Database Wizard can access them.

Preparing for the database wizard requires:
1. A SQLite database
2. Installed ODBC driver
3. Configure database ODBC connection

SQLite database

Only existing databases can be connected so new databases must be created. If SQLite3 is installed run `SQLite3 test.db ".databases"`. Alternatively, just create an empty file with the database name eg in a Windows shell `type nul > demo.db` or linux `> test.db`.

Install ODBC driver

Install ODBC driver from http://ch-werner.de/SQLiteodbc/

eg for Windows, install http://ch-werner.de/SQLiteodbc/SQLiteodbc_w64.exe
If you’re using 32 bit software on Win64 you should install the Win32 driver, too.

A sample SQLite database can be downloaded and extracted for testing: https://www.SQLitetutorial.net/wp-content/uploads/2018/03/chinook.zip
Test at https://inloop.github.io/SQLite-viewer/

Configure database ODBC connection

1. In windows Run (Windows key + R) `C:\Program Files\LibreOffice\program\odbcconfig.exe` to bring up the ODBC Data Source Administrator.
2. In User DSN (Data Source Name): Add (User Data Source)
3. Select the SQLite3 ODBC Driver (if it's not displayed then it's not installed)
4. Browse to the database then give it a name, it can be reconfigured later if required

Database Wizard

Steps:
1. Select database - Connect to an existing database: ODBC
2. Set up ODBC connection - Browse and Choose a data source eg chinook
3. Set up user authentication - Set up the user authentication if any and Test connection
4. Save and proceed to create database - Name Base database

-----

Documentation opportunities:
1. https://help.libreoffice.org/7.2/en-US/text/shared/explorer/database/dabawiz00.html?&DbPAR=BASE&System=WIN
2. BG64-BaseGuide.pdf, Chapter 2 Creating a Database, Accessing external databases
3. GS72-GettingStarted.pdf, Chapter 8 Getting Started with Base, Accessing other data sources
4. https://wiki.documentfoundation.org/Documentation; https://wiki.documentfoundation.org/Tutorials; https://wiki.documentfoundation.org/Documentation/HowTo; https://wiki.documentfoundation.org/Faq/Base

Notes:
1. This Windows ODBC example needs revising to cover linux and macOS.
2. This is a documentation bug but UI communicates and supports new SQLite databases (which are not stored on a server) poorly. Description and option could be changed to: "...connect to an external database" and update second page for ODBC (and likely connection types) to "Only existing databases can be connected so new databases must be created.".
3. Better still, allow Database Wizard to create a new SQLite database file and configure database ODBC connection.
Comment 3 Alex Thurgood 2021-09-22 07:32:54 UTC
(In reply to flywire from comment #2)

> Install ODBC driver
> 
> Install ODBC driver from http://ch-werner.de/SQLiteodbc/
> 
> eg for Windows, install http://ch-werner.de/SQLiteodbc/SQLiteodbc_w64.exe
> If you’re using 32 bit software on Win64 you should install the Win32
> driver, too.
> 

Remember that the above needs to work for all platforms supported by the LibreOffice project.

I just tried the first link and get a 404 Not Found error.
The sqliteodbc of the URL should be in lower case.

I installed the latest available driver for macOS. Note that this package is only currently available for Intel processors. In other words, there is no ODBC driver for Mac Silicon as of today (22/09/2021).

After having set up and configured a USER DSN pointing to an existing SQLite3 db, I try to create an ODB connection using the ODBC driver.

This fails to load because the LibreOffice app bundle as a signed application on macOS only searches for libiodbc in /usr/lib, which is the wrong directory (and actually forbidden for signed apps).

As a result, the setting up of and SQLite3 ODB file using ODBC with Christian Werner's driver is currently not possible.
Comment 4 flywire 2021-09-22 09:12:41 UTC
(In reply to flywire from comment #2)
> Notes:
> 1. This Windows ODBC example needs revising to cover linux and macOS.

Process is very variable by system so only document the system you have access to. Sample macOS Guide: https://www.andrewheiss.com/blog/2016/02/10/libreoffice-base-sqlite-odbc-osx/

(In reply to Alex Thurgood from comment #3)
> The sqliteodbc of the URL should be in lower case.

Install ODBC driver

Install ODBC driver from http://ch-werner.de/sqliteodbc/

eg for Windows, install http://ch-werner.de/sqliteodbc/sqliteodbc_w64.exe
If you’re using 32 bit software on Win64 you should install the Win32 driver, too.

A sample SQLite database can be downloaded and extracted for testing: https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
Tests it's ok at https://inloop.github.io/sqlite-viewer/
Comment 5 flywire 2021-09-22 09:15:26 UTC
The Database Wizard steps depend on the type of database.

[Inaccurate wizard text should be changed, eg suggest: "connect to a database stored on server" -> "connect to external database". Consider order and use of Open vs Browse button.]


A. Open Existing [Base] Database File
1. Select database - Open an existing database file

Details...

B. Create a new [Embeded] database
1. Select database - Create a new database: Embedded database
2. Save and proceed to create database - Give Base database a name

Details...

C. Use external database
Note: New databases must be created before connecting.
1. Select database - Connect to an existing database
2. Set up [external] connection - Browse and Choose a data source
3. Set up JDBC connection - MySQL only
4. Set up user authentication - Supported External database types only
5. Save and proceed to create database - Give Base database a name

Details...
Comment 6 Alex Thurgood 2021-09-22 09:49:29 UTC
Unfortunately, it seems unlikely that anyone is going to change the documentation/inline help for you to review until/unless it can be made to work on all OSes.

To my knowledge, the built-in help or even the online HTML linked help, does not provide for a differentiation of the help supplied which is dependent on the OS. I'm not even sure that the help build system is even capable of providing such differentiated help.  

The user guides try to include the different approaches where the writers know that there is a difference.

The problem you referred to in the Base Guide Handbook would be one to take up with the author of the BaseGuide. 

The guides are produced independently from the LO product help, but there's nothing to stop you from writing your own mini-guide for example, and submitting it to the documentation project:

https://documentation.libreoffice.org/en/english-documentation/
Comment 7 Alex Thurgood 2021-09-22 09:59:44 UTC
Also note that the product help for LibreOffice clearly attempts to steer users away from using ODBC as a method for gaining access to datasources, e.g.:

/Applications/Libreoffice.app/Contents/Resources/help/fr/text/shared/explorer/database/dabawiz02odbc.html?&DbPAR=BASE&System=MAC

In which it clearly tells users to use JDBC instead on Linux and Solaris (macOS doesn't get a mention), and tells users to use ADO on Windows, implying that they should only connect to Access databases...

All in all, not particularly helpful or useful...
Comment 8 flywire 2021-09-22 10:43:18 UTC
(In reply to Alex Thurgood from comment #7)
> Also note that the product help for LibreOffice clearly attempts to steer
> users away from using ODBC as a method for gaining access to datasources,
> e.g.:
> 
> /Applications/Libreoffice.app/Contents/Resources/help/fr/text/shared/
> explorer/database/dabawiz02odbc.html?&DbPAR=BASE&System=MAC
> 
> In which it clearly tells users to use JDBC instead on Linux and Solaris
> (macOS doesn't get a mention), and tells users to use ADO on Windows,
> implying that they should only connect to Access databases...
> 
> All in all, not particularly helpful or useful...

???

Your link doesn't open. I assume it is https://help.libreoffice.org/7.2/en-GB/text/shared/explorer/database/dabawiz02.html?System=WIN&DbPAR=BASE&HID=dbaccess/ui/generalpagewizard/datasourceType#bm_id763267 but that doesn't support those comments.

1. Database is SQLite
2. Community recommendation is ODBC connection (https://ask.libreoffice.org/t/connecting-base-to-sqlite-in-windows/68168)
3. Wizard text is nonsense for SQLite

btw, there's no suggestion of different help text for each OS