[Solved] Connecting to SQlite database

[zeke]

I am trying to connect to a SQlite database created in Bibus. I want to register this database so I can view it from within Ooo.
As best I can tell, the way to do this is to:
Tools>options>Base>databases

Then click the 'NEW' button. The problem is that when I browse for the database, the only option available in the filter line is ODB.

It seems like there should be some other filters available?

I am running Ooo 2.4.1 on Mandriva 2008.1 and the SQlite database is created with Bibus

I can insert a link to a citation from Bibus, so the UNO connection is working. I have the ODBC driver installed, and if I go to
File>new>database I can go through the motions of connecting to an existing ODBC database, but because I can't get it registered, I can't see it on the connect to: dialog box.


My thanks in advance, and GREAT work (I am very excited about the forthcoming version 3.0!!!)

[Villeroy]

Tools>Options...OOoBase>Databases

[zeke]

Right. Thanks.

That is [almost] as far as I have gotten.
When I do that, I get a dialog box that lists 2 existing databases, one for my email client (evolution) and an empty bibliography database. Both are ODF format.

So if I click "NEW", I get another dialog box that pops up - "Create Database Link" with 2 fields, Database file and Registered Name. The only possible action is to click "Browse"
This opens yet another dialog box - a typical Open File style.

I can browse and find any ODF database on my system, but that is it.

The only filter available is ODF. No SQlite, ODBC, etc.....
So I can't seem to link to a the SQlite database because Ooo doesn't recognize any other formats except ODF?

[Villeroy]

First, OOo databases (zip archives with suffix .odb) are the only native file format which has nothing to do with the ODF standard. A database document is a bunch of configuration settings about how to connect to some source of tabular data (several file formats and server connections through odbc and jdbc). There is one special type of datasource where the data are embedded in the odb, served by OOo's integrated hsqldb server. Additionally, a odb can have queries (SQL SELECT strings) and embedded Writer documents (forms and reports).
http://wiki.services.openoffice.org/wik ... Office.org indicates that sqlite can be accessed through odbc. The wiki refers to outdated OOo1.
Create an odbc source on system level as shown in the wiki.
File>New>Database...
[X] Connect to existing...
Type: ODBC
follow the instructions
[X] Register database
Store database

I found this in the other forum: http://www.oooforum.org/forum/viewtopic.phtml?t=73735

[zeke]

Thanks again Villeroy.

Sorry for the confusion. My first post should have read "the only option available in the filter line is ODF." rather than ODB (oops!)

I have never used databases so I am trying to understand all of this, at least enough to integrate bibus and Ooo.

I believe that I have the proper drivers installed, as I can use the SQlite database with no problems in Bibus, and I can pipe the citations to the top-level Ooo Writer document.
It seems that I am having a problem understanding how to register a database that isn't ODF

If I do the steps you list:
File>New>Database...
OK
[X] Connect to existing...
Type: ODBC
OK
follow the instructions
This is where I run into trouble.... Page 2 of the wizard pops up and there is a blank asking "Name of the ODBC data source on your system"
If I click "Browse" a new window opens that says "Choose data source" but the window below is empty and the only options are OK, Cancel, and Help
Clicking OK or cancel returns me to the previous (page 2) wizard
[X] Register database
Store database

[Villeroy]

Refer to the linked wiki. Most databases expose themselves through a mechanism called "odbc", since they require a log-in and may contain gigabytes of data. Odbc is not about opening a database file, it is a way to connect to a source of data and request portions of data from that source.

[Some data] <suitable odbc-driver to access data> [odbc-pool with named connections] <Base document--->Writer/Calc

odbc is the bridge that makes [Some data] transparently availlable for the other end of the connection (OOo, Bibus, MSAccess, a set of scripts, any other software beeing able to use the odbc-layer)

[zeke]

Ahhhh, many mnay thanks..that made things much more clear.

I have been able to get farther, I found out I can type in the blank dialog box, but it appears I still don't quite have the proper drivers installed. I will try to get my driver resolved on Monday when I go back to work.

Thanks and have a great weekend!

[zeke]

Ok, so I have used the sqlitebrowser to verify that my Bibus database exists and can be read in sqlite format. It can.

I have also verified that the unixODBC driver is installed (unixODBC)


Yet, Ooo gives me the following error when I try to connect to the bibus database:

[unixODBC][Driver Manager] Data source name not found, and no default driver specified

Thanks again for your help....

[Villeroy]

So you have unixODBC and the sqlite driver. I think they come with some documentation if the above mentioned sources don't suffice. As far as I remember, you have to set up something like: "Let there be a datasource called 'Inventory', accessible through the installed odbc driver for sqlite, located in some file xyz.sql". Then you can access the odbc-source called 'Inventory' from any odbc-capable database tool. There is a GUI for unixODBC similar to Window's odbc-sources in the system panel. You can also write odbc configuration without GUI.

[zeke]

OK, I am getting closer.

I now know for sure that I have the sqliteodbc driver described in the Ooo forum thread installed. I have the proper lib files in the proper folder (/usr/local/lib) and have created two files:

/etc/odbc.ini contains:
[SQLite]
Description=SQLite
Driver=/usr/local/lib/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so
Threading=2

~/.odbc.ini contains:

[mysqlitedb]
Description=BIB
Driver=SQLite
Database=/home/brett/.bibus/brettsqlite
# optional lock timeout in milliseconds
Timeout=2000

now when I go to File>New>Database>Connect to existing [ODBC] database and click on the browse button, I see two entries where it was previously blank - one is SQLite and the other is mysqlitedb. These entries are coming from the .ini files above because I can change the names in the files and see the changes in the Ooo dialog box.

If I select either entry I still get the same error message:
[unixODBC][Driver Manager] Data source name not found, and no default driver specified

Based on this wiki
http://dba.openoffice.org/drivers/sqlit ... html#intro

I have copied the zip files linked on that page into my ~/.ooo-2/user/uno-connections folder as described. Yet I still can't seem to get Ooo to recognize the sqlite/ODBC driver.

[Villeroy]

Sorry, this is beyond my scope since I don't use SQLite, Bibus or anything related to odbc. I attached a candle-light to this thread, hoping that someone more experienced takes notice.

[zeke]

Many thanks!

Hopefully someone will stumble by......

[zeke]

I have it working now,

I had to remove blank lines from the files listed below, and then run
ODBCConfig

From there, I added a DSN entry for my database (browsed to it from the dialog box) with the SQLite3 driver. After that, it was simply adding a new database as described in the wiki.

My sincere thanks to Christian Werner for creating the drivers and patiently guiding me throught the process over the past week!