Base: Connecting to SQLite3 .db and Working With it

users
#1

Having built and installed unixODBC and sqlite3odbc I want to open a
sqlite3 database (*.db) within Base.

   From the menu item File -> New -> Database I chose to connect to an
existing database. When I select ODBC for the connector, the desired .db
file is recognized and selected. No need for user authentication so I skip
that step.

   Step 4 is 'Save and Proceed' which is where my choices do not produce the
result I expect.

   The top portion of the dialog box has 2 radio buttons: do I want to
register the database with Base? I assume so, but the help button moves the
open firefox to that virtual desktop and presents a page with no
information. What are the effects of registering and not registering the
database with Base?

   The bottom portion of the dialog box has 2 check boxes, one to open the
database for editing, the other to create tables. Only the first one is
invoked.

   Than, when I clicked the 'Finish' button I get a LO save dialog with the
default name of 'New Database' (with a space that needs to be removed, no
less!). If I give it a name, does the new *.odb contain the schema and data
of the existing sqlite3 .db? I apparently cannot use File -> Open on the .db
file; it produces a Write page with hash symbols on it.

   To summarize: please tell me how to connect to the existing sqlite3 .db
file and then how to access it in the future to work with it.

Rich

#2

It is a good idea to register the database as then the data is accessible to the Writer and Calc programs under "View Data Source"

The DATABASE terminology confusion!!!!
The problem is the "Database" terminology. Unfortunately there are two times the word Database gets used, once when you create a database such as SQLlite or MySQL, etc. and once when you create a 'front end" to your database.

LibreOffice Base can be a database!! but in your case the database is SQLlite and the LibreOffice Base is just a way of working with the data from the SQLlite database.
When you save the newly created connection to the database by giving your Base file a name you are in fact creating the front end, NOT a database!!
So once you save this "front end" file and open it you will find all your SQLlite tables, views, procedures, etc. displayed in the front end and you can then edit or work with the data in the SQLlite database.
If your connection to the database is good you can now control the input and extracion of data using your Base front end.

Hope this all makes sense?
SO go ahead register your "database" (which in fact is registering the Base front end) and when asked to save the new file you can give it any name you like as you are saving the "front end" to your PC.

For instance I have a real database on a MySQL server called "sanmadb" , the front end (in Base) on my laptop is called "Sanma Central Database".
The accountants base front end is called "Sanma Finances" but both front ends connect and get their data from the same MySQL database ("sanmadb").

#3

It is a good idea to register the database as then the data is accessible
to the Writer and Calc programs under "View Data Source"

Tony,

   Thought this to be the case.

LibreOffice Base can be a database!! but in your case the database is
SQLlite and the LibreOffice Base is just a way of working with the data
from the SQLlite database. When you save the newly created connection to
the database by giving your Base file a name you are in fact creating the
front end, NOT a database!!

   Wondered about this so I went ahead and created a file with the .odb
extension.

open it you will find all your SQLlite tables, views, procedures, etc.
displayed in the front end and you can then edit or work with the data in
the SQLlite database. If your connection to the database is good you can
now control the input and extracion of data using your Base front end.

   Ah, but here's the problem: Base is looking for libsqliteodbc.so which is
specific to SQLite2. Since SQLite3 has been out a few years now, I looked
for a way to tell Base the library is called libsqlite3odbc but could not
find a configuration option.

   On the one hand, I can make a soft link from /usr/local/lib/sqlite3odbc.so
to /usr/local/lib/sqliteodbc.so, but that won't work for the users on
Windows.

   If someone points me to the way of telling Base that the library is for
SQLite3, I'll make the fix and move on with the project.

Many thanks,

Rich

#4

Rich,

  Ah, but here's the problem: Base is looking for libsqliteodbc.so which is
specific to SQLite2. Since SQLite3 has been out a few years now, I looked
for a way to tell Base the library is called libsqlite3odbc but could not
find a configuration option.

  On the one hand, I can make a soft link from
/usr/local/lib/sqlite3odbc.so
to /usr/local/lib/sqliteodbc.so, but that won't work for the users on
Windows.

  If someone points me to the way of telling Base that the library is for
SQLite3, I'll make the fix and move on with the project.

Which version of LO are you using ? A distrib provided one, a TDF
download ? I don't know whether there is a difference in the library
versions that the various package maintainers / TDF chooses for unixODBC
when they build LO. There is certainly a build switch.

Alex

#5

Alex,

   LO-4.3.5, unixODBC-2.3.2, and sqliteodbc-0.9991, and sqlite-3.8.8 on
Slackware-14.1. I use the build scripts provided on SlackBuilds.org, except
for sqliteodbc which I built with the usual configure; make; make install.

   LO is simply repackaged from the source .rpm into a Slackware
.txz file. Therefore, the version is that downloaded from TDF's LO site.

   AFAIK, each component is the latest version. I've not examined the source
tarball to see if changes are needed, after all, it is a packaged .rpm not
raw source.

Thanks,

Rich

#6

Some progress has been made. The sticking point now is Base not seeing the
DSN or driver. Deleted the existing .odb and started over; here are the
steps and the results:

   1. File -> New -> Base.
   2. Connect to an existing database; choose ODBC from dropdown list. Next.
   3. Set up connection to the ODBC database (which I thought established
     the DSN): 'Browse' offers two choice: SQLite and the SQLite3 .db
     file name. I select the latter. Next.
   4. Ignore user authentification. Next.
   5. Save and proceed: accept defaults of registering database and opening
   the database for editing. Finish.
   6. Assign an .odb filename and Save.

   See this error message:

   "The connection to the data source '<filename>' could not be established.
   "[unixODBC][Driver Manager] Data source name not found, and no driver
specified."

   I thought the DSN was specified in step 3, above. In /etc/unixodbc.ini is
[SQLITE3]
Description = SQLite3 ODBC Driver
Driver = /usr/local/lib/libsqlite3odbc.so
Setup = /usr/local/lib/libsqlite3odbc.so
Threading = 2

   What am I doing incorrectly here?

Rich

#7

You may need to go to "LibreOffice > Tools > Options > LibreOffice Base > Connections" and select the "ODBC Driver", under the "Connection Pool" dialogue.
Select the driver and click the "Enable pooling for this driver" checkbox.

This MAY solve the problem. No promises!

Tony Bray
tonybsa@mac.com
MacBook Pro 15 inch Mid 2009
2.8 GHz Intel Core 2 Duo, 4 GB RAM,
Mac OS X 10.9.5.

#8

Tony,

   I've done this several times, but the only drivers visible are for sun
(now oracle) star. See attached .png.

   Clicking on the pool check box makes them visible. I select the one for
ODBC, click to make it pooled, and save the results. Unfortunately, it makes
no difference. When the .odb is opened and I try to edit tables or create
forms the same error message box as before appears. Sigh.

Thanks,

Rich

#9

Rich Shepard schreef op 19/01/2015 om 20:40:

LO-4.3.5, unixODBC-2.3.2, and sqliteodbc-0.9991, and sqlite-3.8.8 on
Slackware-14.1. I use the build scripts provided on SlackBuilds.org, except
for sqliteodbc which I built with the usual configure; make; make install.

  Some progress has been made. The sticking point now is Base not seeing the
DSN or driver. Deleted the existing .odb and started over; here are the
steps and the results:

  1. File -> New -> Base.
  2. Connect to an existing database; choose ODBC from dropdown list. Next.
  3. Set up connection to the ODBC database (which I thought established
      the DSN): 'Browse' offers two choice: SQLite and the SQLite3 .db
      file name. I select the latter. Next.

did you tested the connection ? (button right under)

#10

Apparently, attachments are stripped off. But there are only the sun
drivers visible and no button to add others.

Rich

#11

Yes I was aware of this but thought that just "maybe" the oracle drivers would work for you.
Sorry all out of other suggestions.

Tony Bray
tonybsa@mac.com
MacBook Pro 15 inch Mid 2009
2.8 GHz Intel Core 2 Duo, 4 GB RAM,
Mac OS X 10.9.5.

#12

Tony,

   With the help of one subscriber to the unixODBC mail list we may have
found the problem source. Results of two tests:

[rshepard@salmo ~]$ dltest /usr/local/lib/libsqlite3odbc.so SQLConnect
SUCCESS: Loaded /usr/local/lib/libsqlite3odbc.so
SUCCESS: Found SQLConnect

and

[rshepard@salmo ~]$ isql -v no-mdf-banquetdb
[S1000][unixODBC][SQLite]connect failed
[ISQL]ERROR: Could not SQLConnect

   'isql' is the CLI for interative use of unixODBC. Perhaps Base uses this
interface and that's why it fails.

Rich

#13

Your tables, relations, indices and data are still in the SQLite.db file
and handled by the ODBC driver. The so called Base document is just a
configuration file with optional queries, forms and reports. It is
bridge between SQLite and office documents.
With a registered database you can use database data in arbitrary office
documents for mail merge, label printing, bibliographies and as a data
source of calculation models.

In Calc or Writer hit F4 and you see the registered databases. With
unregistered databases you can only use the forms and reports that are
embedded in that "database document".

#14

Andreas,

   Thank you for the explanation.

   The issue needing to be resolved is why Base cannot connect to the SQLite
.db through the .odb file even though it sees it. Both uxixODBC and
sqlite3odbc _seem_ to be correctly configured, something needs to be
tweaked.

   One of the unixODBC developers has been helping; perhaps tomorrow (in my
timezone) it will finally be resolved.

Regards,

Rich

#15

Good to know that there is a solution.
Perhaps the documentation team can find time to add this information to the Base User Guide or Help or somewhere.

Tont

#16

Shot in the dark.

Are you using UTF-8?

I don't know much about your problem, but Googling shows there may be a relationship.

See https://www.sqlite.org/c3ref/open.html

Hope this helps

#17

Apparently there's no visible explanation for the problem. So, I'll write
the application in Python/wxPython which I know works well with sqlite3.

Thanks to everyone for trying to solve this conundrum,

Rich

#18

I want to add a little image in cell A2 if A1=1, like (in A2) "IF(A1=1;add image;"").
Or I could add an image in A2 and have a formula (in A2) like " IF(NOT(A1 = 1); hide image;show image)".
Prefer the first one.

In cell styles I do not find any way to add background images as in page style, so I can't use conditional formatting.
I would prefer a solution, without scripts or macros.
Is it possible?

Kolbjoern

#19

Hi :slight_smile:
If the problem is the lack of a connector then writing a connector in
Python so that you can use Base might be more useful than developing
your own database program to use the sqlite backend - especially if
you OpenSource the connector and upload it to the Extensions website.
It'd mean more people able to keep it updated and less work setting it
up, hopefully!

Anyway, good luck and regards from
Tom :slight_smile:

#20

Tom et al.,

   There's actually a better, easier, quicker solution. I downloaded the
hsqldb user guide (not the Base Handbook) and learned that I can write the
schema and read it into a database then set up Base to use it. The guide
includes the supported DDL syntax so I can convert the primary/foreign keys
and column/table constraints as needed.

   Should have looked first for this option.

Rich