[Libreoffice-bugs] [Bug 144588] Base ODBC connection for Sqlite Inadequately Documented for Windows

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=144588

--- Comment #2 from flywire <flywire0 at gmail.com> ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210922/0e9b4027/attachment.htm>