Connecting libreoffice calc to existing sqlite database file

Jonathan_Ryshpan · May 19, 2012, 10:57pm

I want to examine an existing sqlite database file. When I attempt the
obvious:

     1. Start LibreOffice
     2. Click Database
     3. Select "Open an existing database file"
     4. Click "Open"
     5. Choose the desired file from the browser window

I get the message "Please choose Connect to an existing database' to
connect to an existing database instead"

A little research implies that I have to start a back end program to
make the desired file available to LibreOffice as a running interface,
rather than as a file. But it's not clear what this program would be.
Is such a program available for Fedora-16 (on x86_64 hardware), which is
what my system is? If so, what is it? Something on a repository would
be best; but I can build it, if necessary.

Thanks - jon

Miroslaw_Zalewski · May 19, 2012, 11:48pm

Hi Jonathan

Sorry about pointing to another thread, but you may find this useful. Poster
there managed to connect OpenOffice with sqlite file. I believe the same should
work for LO.
Link:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=40&t=9905

As far as I can tell, you will need (aside from LO, of course) unixodbc and
libsqliteodbc packages. At least that's how they are called in Debian; I don't
know about Fedora, but perhaps they have similar names.

HTH

Villeroy · May 20, 2012, 9:23am

Base opens only one particular type of file: the Base document (*.odb). This
file format is basically an archive with info about how to access a certain
tabular data source, where to find it, which protocol to use, which file
filter, which vendor specific driver (analog to a file system driver).
Additionally, you can store your own query definitions to pull meaningful
row sets out of the tables and you can store Writer documents in the .odb
archive (forms for input, reports for output).

Jonathan_Ryshpan · May 21, 2012, 4:34pm

Thanks very much for the pointer. I attempted to follow the
instructions, installing the SQLite ODBC Driver, but I have installation
problems. (If you configure wrong, an attempt to access a database will
lock up your system so badly that you need to reboot).

My particular system runs KDE, which requires iodbc for many
applications, so that is what I am using for the database interface.
Iodbc has two kinds of configuration files in two places, namely:
        /etc/odbc.ini
        /etc/odbcinst.ini
        ~/.odbc.ini
        ~/.odbcinst.ini (maybe not used)
The iodbc web site has info about ~/.odbc.ini, and by implication
about /etc/odbc.ini, which contains (I believe) default info; but it has
nothing about /etc/odbcinst.ini. Can someone enlighten me about the
relation about these files and what they are used for?

jon

Jonathan_Ryshpan · May 21, 2012, 9:40pm

I have been able to establish a connection between libreOffice base and
my database, using sqliteodbc and unixODBC (which are the names on
Fedora). I have not been able to use libiodbc, which serves the same
function as unixODBC and is the default driver on KDE, probably because
of documentation problems; libiodbc runs fine on all the KDE
applications that require it, but not for libreOffece and me.

But the database doesn't look like what I had expected it to: sqlite
databases contain within themselves the names and data types of all the
entries, but when libreOffice opens the database, there is no such data
visible. Am I missing something. A dump of the database using
"$ sqlite3 <archive>.sdb .dump" looks fine. Is this what I should expect,
or is something wrong, or am I missing something?

Thanks - jon

Villeroy · May 21, 2012, 10:43pm

Am 21.05.2012 23:40, Jonathan Ryshpan wrote:

blah

Ask your question on a SQLite forum with some _technical_ information about your operating system, SQLite version of the creating application, driver version, configuration file, connection URL of the client (Base menu:Edit>Database>Properties).

Tom_Davies · May 22, 2012, 12:29pm

Hi
I think posting to both lists is a good idea. Hopefully someone can sort this and it's definitely something that a few of us would be interested in hearing the answer to. We don't often hear about that particular back-end but it sounds about perfect for quite a few scenarios.
Good luck and regards from
Tom

Simon_Cropper1 · May 23, 2012, 12:04am

Hi Guys,

Just chimed into this conversation. Not sure if this may help.

I have managed to get sqlite to work with libreoffice on Ubuntu. Didn't do much with it as it was primarily a case of verifying it could be done. I am still looking for a easy method of populating the sqlite tables and analyzing the data, so this project has stalled somewhat.

I did take notes however so I could work on the method at a later date.

Here are my notes for what they are worth. Note I tested this using an earlier version of Ubuntu and libreoffice but it should still work.

From memory, where most people become unstuck is they don't read the feedback provided when 'making' the odbc driver. Errors appear if you are missing a dependency -- read all messages carefully, install any dependencies, rerun the make until it completes the install : IT HAS WORKED when the drivers are registered.

If this step does not work you need to go to the sqlite forums and ask why.

Anyway I hope the following notes help

****** MY NOTES, IN PREP FOR TUTORIAL *******

Most of software in Ubuntu 10.04 can not directly access SQLite Databases and it is necessary to install unixODBC plus the SQLite Driver.

Installation of the unixODBC package <http://www.unixodbc.org/> can be easily done from the Ubuntu Software Centre. The website provides a link to the appropriate driver maintained by Carl Weirner.

Installation of the SQLite ODBC Driver <http://www.ch-werner.de/sqliteodbc/> is a little more involved.

The website provides a link to the appropriate linux download <http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.88.tar.gz>. Download this file and extract the directory to a folder.

Open a terminal within the sqliteodbc-0.88 directory and run the following commands.

./configure
sudo make install

Check that the drivers have been registered in /etc/odbcinst.ini. The configuration settings required for this file are outlined in the ../sqliteodbc-0.88/README file. My installation did not require any changes as the odbcinst.ini file was correctly configured.

In order to have Ubuntu see you odbc database change the ~/.odbc.ini file so it includes the following data.

      [mysqlitedb]
      Description=My SQLite test database
      Driver=SQLite3
      Database=/path/to/the/sqlite/database.sqlite3
      # optional lock timeout in milliseconds
      Timeout=2000

You should now be able to see the database in LibreOffice Base or other packages that can use ODBC drivers.

      (c) Simon Cropper 2012
      Creative Commons Attribution-ShareAlike 3.0 Unported
      http://creativecommons.org/licenses/by-sa/3.0/

iplaw67 · May 23, 2012, 9:56am

Hi,

My particular system runs KDE, which requires iodbc for many
applications, so that is what I am using for the database interface.
Iodbc has two kinds of configuration files in two places, namely:
/etc/odbc.ini
/etc/odbcinst.ini

These are the system DSN and ODBC configuration files. You generally
need root/sudo access to change these files.

~/.odbc.ini
~/.odbcinst.ini (maybe not used)

These are the hidden, user DSN and ODBC configuration files. These files
are used as a fallback, or in addition, if nothing is defined in the
system DNS/ODBC configuration files.

The iodbc web site has info about ~/.odbc.ini, and by implication
about /etc/odbc.ini, which contains (I believe) default info; but it has
nothing about /etc/odbcinst.ini. Can someone enlighten me about the
relation about these files and what they are used for?

odbcinst.ini defines the name of the ODBC drivers present on the system,
and the locations/libraries to be used when an ODBC connection protocol
is invoked by a third party application, see the examples below that I
have on my Macbook :

[ODBC Drivers]
Actual SQL Server = Installed
Actual Open Source Databases = Installed
Actual Oracle = Installed
Actual Access = Installed
MySQL ODBC 5.1 Driver = Installed

[Actual SQL Server]
Driver = /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/atsqlsrv.so
Setup =

[Actual Open Source Databases]
Driver = /Library/ODBC/Actual Open Source
Databases.bundle/Contents/MacOS/atopnsrc.so
Setup =

[Actual Oracle]
Driver = /Library/ODBC/Actual Oracle.bundle/Contents/MacOS/atoradb.so
Setup =

[Actual Access]
Driver = /Library/ODBC/Actual Access.bundle/Contents/MacOS/ataccess.so
Setup =

[MySQL ODBC 5.1 Driver]
Driver = /usr/local/lib/libmyodbc5.so
SETUP = /usr/local/lib/libmyodbc3S.so

odbc.ini defines the connection parameters to access a given data source
name (DSN), see my example below for my Macbook :

[ODBC Data Sources]
Actual_MAMP = Actual Open Source Databases
myodbc = MySQL ODBC 5.1 Driver
IPDB_MAMP = MySQL ODBC 5.1 Driver
ipdatabase = MySQL ODBC 5.1 Driver

[ODBC]
Trace = 0
TraceAutoStop = 0
TraceFile =
TraceLibrary =

[Actual_MAMP]
Driver = /Library/ODBC/Actual Open Source
Databases.bundle/Contents/MacOS/atopnsrc.so
Description = Actual Driver to MAMP
Database = ipdatabase
Server = localhost
Port = 8889
IncludeViews = Yes
UserID = alex
UseKeychain = Yes

[myodbc]
Driver = /usr/local/lib/libmyodbc5.so
Description = Generic myodbc connection
SERVER = localhost
PORT = 3306

[IPDB_MAMP]
Driver = /usr/local/lib/libmyodbc5.so
Description = MyODBC Connection via Mamp port
Server = 127.0.0.1
Port = 8889

[ipdatabase]
Driver = /usr/local/lib/libmyodbc5.so
Description = IP management db
Server = 127.0.0.1
Port = 3306

Entries defined in /etc/ are system-wide, i.e. in theory available to
every user declared on the system, entries put in the local user file
are only available to that particular user.

Alex

Jonathan_Ryshpan · May 25, 2012, 2:16pm

I have been attempting to send this posting for a while, and have only
recently noticed that the list does not accept attachments. This has
(apparently) caused it to be silently rejected. I have appended what
had been attached to the end of this posting.

Sorry for the delay.

... the database doesn't look like what I had expected it to: sqlite
databases contain within themselves the names and data types of all
the entries, but when libreOffice opens the database, there is no such
data visible. Am I missing something. A dump of the database using
"$ sqlite3 <archive>.sdb .dump" looks fine. Is this what I should expect,
or is something wrong, or am I missing something? On Tue, 2012-05-22

Am 21.05.2012 23:40, Jonathan Ryshpan wrote:
>
>blah
>

Ask your question on a SQLite forum with some _technical_ information
about your operating system, SQLite version of the creating application,
driver version, configuration file, connection URL of the client (Base
menu:Edit>Database>Properties).

Wow! This is enough to singe my beard. Nevertheless I am grateful for
all replies, even beard singeing ones.

I fear that I put in too much technical detail, rather than too little.
As you see, I am a complete newbie to LibreOffice base (oobase), and am
only trying to find out whether I have succeeded in getting oobase to
open my database properly. I don't think a posting to an sqlite forum
would be useful, since this is really a question about oobase.

If oobase has connected properly to the database, it is useless for my
purpose and I will give up on it and do what needs done with a bunch of
shell scripts. If oobase has not opened the database properly, I will
persevere.

Here is the technical info that Mr. Säger desires:

All my systems run Fedora-16. The database was generated on an i686
system. LibreOffice runs on an x86_64 system. The database is
contained in a file named wview-archive.sdb generated on the i686
system and copied to the x86_64 system.

The database was generated by wview, which can be downloaded at
        http://www.wviewweather.com/
My running version of wview is accessible (in case you want to know
the weather here) at:
        http://oaklandweather.no-ip.org/index.html/
The archive file is not visible from the web, but extracts from it
are at:
        http://oaklandweather.no-ip.org/index.html/Archive
The version of sqlite is included in
        sqlite-devel-3.7.7.1-1.fc16.<......>.rpm
The library is:
        /usr/lib/libsqlite3.so.0.8.6
The drivers are in:
        unixODBC-2.2.14-13.fc15.x86_64
and as specified in its initialization file (which follows)
        $ cat ~/.odbc
        [ODBC Data Sources]
        mysqlitedb=SQLite

        [mysqlitedb]
        Driver=/usr/lib64/libsqlite3odbc.so
        Description=My SQLite test database
  Database=/home/jonrysh/src/wview/archive/nullWindSpeed/wview-archive.sdb
        # optional lock timeout in milliseconds
        Timeout=2000
The start of a dump of the database is appended to this message.
I.e. the output of
        $ sqlite3 archive.sdb .dump

If anyone else wants to know more about my struggles with oobase and
sqlite, email me or post to the list.

All the best - jon

======================= archive.txt =======================
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE archive (
        dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
        usUnits INTEGER NOT NULL,
        interval INTEGER NOT NULL,
        barometer REAL,
        pressure REAL,
        altimeter REAL,
        inTemp REAL,
        outTemp REAL,
        inHumidity REAL,
        outHumidity REAL,
        windSpeed REAL,
        windDir REAL,
        windGust REAL,
        windGustDir REAL,
        rainRate REAL,
        rain REAL,
        dewpoint REAL,
        windchill REAL,
        heatindex REAL,
        ET REAL,
        radiation REAL,
        UV REAL,
        extraTemp1 REAL,
        extraTemp2 REAL,
        extraTemp3 REAL,
        soilTemp1 REAL,
        soilTemp2 REAL,
        soilTemp3 REAL,
        soilTemp4 REAL,
        leafTemp1 REAL,
        leafTemp2 REAL,
        extraHumid1 REAL,
        extraHumid2 REAL,
        soilMoist1 REAL,
        soilMoist2 REAL,
        soilMoist3 REAL,
        soilMoist4 REAL,
        leafWet1 REAL,
        leafWet2 REAL,
        rxCheckPercent REAL,
        txBatteryStatus REAL,
        consBatteryVoltage REAL,
        hail REAL,
        hailRate REAL,
        heatingTemp REAL,
        heatingVoltage REAL,
        supplyVoltage REAL,
        referenceVoltage REAL,
        windBatteryStatus REAL,
        rainBatteryStatus REAL,
        outTempBatteryStatus REAL,
        inTempBatteryStatus REAL
);
INSERT INTO "archive" VALUES(1337285700,1,5,29.903,29.903,29.89414,70.099998,64.300003,47.0,64.0,NULL,NULL,0.0,NULL,0.0,0.0,51.855492,NULL,64.300003,0.0,947.0,8.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337286000,1,5,29.9,29.9,29.89114,70.199997,64.5,47.0,65.0,4.0,157.5,9.0,45.0,0.0,0.0,52.466148,64.5,64.5,0.0,946.0,8.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337286300,1,5,29.898001,28.888617,29.903215,70.099998,64.800003,48.0,64.0,3.0,157.5,10.0,67.5,0.0,0.0,52.328781,64.800003,64.800003,0.0,943.0,8.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337286600,1,5,29.9,28.8908,29.905462,70.199997,65.199997,48.0,63.0,3.0,270.0,8.0,112.5,0.0,0.0,52.278957,65.199997,65.199997,0.0,942.0,8.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337286900,1,5,29.898001,28.889185,29.903801,70.199997,66.0,48.0,62.0,2.0,112.5,5.0,90.0,0.0,0.0,52.598766,66.0,66.0,0.0,941.0,7.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337287200,1,5,29.893,28.884844,29.899336,70.400002,66.300003,48.0,63.0,3.0,315.0,6.0,315.0,0.0,0.0,53.318062,66.300003,66.300003,0.0,942.0,7.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337287500,1,5,29.893999,28.88623,29.900761,70.199997,66.900002,48.0,61.0,3.0,45.0,7.0,22.5,0.0,0.0,53.003407,66.900002,66.900002,0.0,939.0,7.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337287800,1,5,29.893,28.885727,29.900244,70.199997,67.0,48.0,60.0,3.0,337.5,6.0,292.5,0.0,0.0,52.647053,67.0,67.0,0.0,936.0,7.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337288100,1,5,29.892,28.885132,29.899632,70.400002,66.900002,48.0,61.0,3.0,157.5,7.0,22.5,0.0,0.0,53.003407,66.900002,66.900002,0.0,932.0,7.7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337288400,1,5,29.893,28.886364,29.9009,70.400002,67.0,49.0,58.0,3.0,112.5,7.0,112.5,0.0,0.0,51.726032,67.0,67.0,0.022,929.0,7.6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337288700,1,5,29.893,28.886597,29.901138,70.400002,67.099998,48.0,60.0,3.0,315.0,9.0,337.5,0.0,0.0,52.74094,67.099998,67.099998,0.0,926.0,7.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337289000,1,5,29.893,28.886805,29.901352,70.400002,67.5,48.0,59.0,3.0,0.0,8.0,0.0,0.0,0.0,52.658451,67.5,67.5,0.0,918.0,7.4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337289300,1,5,29.889999,28.88414,29.898613,70.599998,67.5,48.0,57.0,3.0,337.5,6.0,337.5,0.0,0.0,51.721531,67.5,67.5,0.0,915.0,7.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337289600,1,5,29.886999,28.881439,29.895834,70.599998,67.699997,48.0,60.0,3.0,112.5,7.0,112.5,0.0,0.0,53.304253,67.699997,67.699997,0.0,909.0,7.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337289900,1,5,29.889999,28.884537,29.899019,70.599998,68.5,48.0,56.0,4.0,45.0,10.0,22.5,0.0,0.0,52.173141,68.5,68.5,0.0,903.0,7.1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337290200,1,5,29.889999,28.884808,29.899298,70.699997,68.699997,48.0,55.0,3.0,45.0,8.0,337.5,0.0,0.0,51.869949,68.699997,68.699997,0.0,891.0,7.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337290500,1,5,29.889,28.884102,29.898571,70.699997,68.199997,48.0,55.0,4.0,67.5,10.0,45.0,0.0,0.0,51.405518,68.199997,68.199997,0.0,887.0,6.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337290800,1,5,29.889,28.884275,29.898752,70.699997,67.400002,48.0,54.0,5.0,90.0,11.0,112.5,0.0,0.0,50.167797,67.400002,67.400002,0.0,881.0,6.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337291100,1,5,29.886,28.881449,29.895842,70.599998,66.599998,48.0,59.0,4.0,90.0,9.0,90.0,0.0,0.0,51.815159,66.599998,66.599998,0.0,874.0,6.7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337291400,1,5,29.888,28.883368,29.897818,70.599998,67.400002,48.0,57.0,3.0,67.5,8.0,67.5,0.0,0.0,51.628231,67.400002,67.400002,0.0,860.0,6.6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337291700,1,5,29.891001,28.886328,29.900862,70.699997,67.5,48.0,55.0,4.0,67.5,9.0,67.5,0.0,0.0,50.75526,67.5,67.5,0.0,854.0,6.4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337292000,1,5,29.888,28.883493,29.897947,70.699997,67.199997,48.0,57.0,4.0,45.0,9.0,67.5,0.0,0.0,51.441616,67.199997,67.199997,0.021,844.0,6.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337292300,1,5,29.886999,28.882559,29.896984,70.900002,67.099998,48.0,59.0,4.0,45.0,11.0,45.0,0.0,0.0,52.283669,67.099998,67.099998,0.0,837.0,6.2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337292600,1,5,29.886999,28.882582,29.897009,70.900002,67.0,48.0,55.0,5.0,90.0,9.0,112.5,0.0,0.0,50.290749,67.0,67.0,0.0,827.0,6.1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337292900,1,5,29.886999,28.882595,29.897022,71.099998,66.300003,47.0,58.0,4.0,67.5,11.0,67.5,0.0,0.0,51.071461,66.300003,66.300003,0.0,820.0,5.9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337293200,1,5,29.884001,28.879656,29.893999,71.099998,65.699997,47.0,57.0,6.0,90.0,11.0,90.0,0.0,0.0,50.041874,65.699997,65.699997,0.0,807.0,5.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337293500,1,5,29.884001,28.879574,29.893915,70.900002,65.599998,47.0,58.0,6.0,90.0,11.0,112.5,0.0,0.0,50.416821,65.599998,65.599998,0.0,799.0,5.6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337293800,1,5,29.882999,28.878525,29.892836,70.900002,65.699997,47.0,59.0,5.0,45.0,10.0,90.0,0.0,0.0,50.971767,65.699997,65.699997,0.0,787.0,5.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337294100,1,5,29.881001,28.876524,29.890778,70.900002,65.400002,47.0,57.0,4.0,90.0,10.0,90.0,0.0,0.0,49.761894,65.400002,65.400002,0.0,779.0,5.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337294400,1,5,29.879,28.874506,29.8887,71.099998,65.400002,47.0,60.0,3.0,90.0,9.0,90.0,0.0,0.0,51.144642,65.400002,65.400002,0.0,765.0,5.1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337294700,1,5,29.874001,28.869596,29.883652,71.099998,66.099998,47.0,61.0,2.0,225.0,6.0,90.0,0.0,0.0,52.250721,66.099998,66.099998,0.0,754.0,5.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337295000,1,5,29.872999,28.868597,29.882624,71.199997,66.900002,47.0,55.0,3.0,22.5,7.0,0.0,0.0,0.0,50.197842,66.900002,66.900002,0.0,747.0,4.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337295300,1,5,29.874001,28.869583,29.883638,71.400002,67.199997,47.0,58.0,3.0,0.0,6.0,22.5,0.0,0.0,51.913036,67.199997,67.199997,0.0,734.0,4.7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337295600,1,5,29.872,28.867683,29.881683,71.599998,67.5,47.0,56.0,2.0,22.5,7.0,0.0,0.0,0.0,51.242188,67.5,67.5,0.019,720.0,4.5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337295900,1,5,29.870001,28.865801,29.879749,71.800003,68.099998,47.0,54.0,3.0,67.5,6.0,90.0,0.0,0.0,50.816624,68.099998,68.099998,0.0,711.0,4.3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337296200,1,5,29.868999,28.86491,29.878832,71.900002,68.300003,47.0,53.0,3.0,45.0,7.0,67.5,0.0,0.0,50.497425,68.300003,68.300003,0.0,697.0,4.1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337296500,1,5,29.868,28.864027,29.877922,72.099998,68.5,46.0,54.0,3.0,45.0,5.0,67.5,0.0,0.0,51.187351,68.5,68.5,0.0,681.0,4.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337296800,1,5,29.868,28.864117,29.878016,72.300003,68.5,46.0,54.0,3.0,45.0,9.0,22.5,0.0,0.0,51.187351,68.5,68.5,0.0,669.0,3.8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337297100,1,5,29.868999,28.865168,29.879097,72.400002,67.900002,46.0,52.0,4.0,90.0,11.0,67.5,0.0,0.0,49.615364,67.900002,67.900002,0.0,658.0,3.7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "archive" VALUES(1337297400,1,5,29.864,28.860388,29.87418,72.400002,67.099998,46.0,53.0,6.0,90.0,11.0,90.0,0.0,0.0,49.387642,67.099998,67.099998,0.0,649.0,3.6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0,0.0,799.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
COMMIT;

iplaw67 · May 25, 2012, 3:28pm

Hi Jon,

If oobase has connected properly to the database, it is useless for my
purpose and I will give up on it and do what needs done with a bunch of
shell scripts. If oobase has not opened the database properly, I will
persevere.

I took your dump, copied, and saved it to a text file with an sql
extension then imported it via the browser based SQLiteManager scripts
on my MAMP stack (Mac OSX). This gave me a table called "archive" and I
can see your data just fine using this technique.

My next step will be to try and set up and ODBC entry for use via LO on
the Mac. Will keep you posted.

I noticed that most of your fields are defined as REAL, however, I'm not
entirely sure how those are dealt with via ODBC, and particularly,
within the context of LibreOffice.

Which objects/fields in particular are you not seeing in your tables via
LO ?

Alex

Joep_L_Blom · May 25, 2012, 9:53pm

Jonathan,
Your table looks suspiciously like the table I use getting the data from WeatherDisplay, using their wdmysql-exe program (in windows). I have this table in a mysql database and that is perfectly well to handle using "Mysql Query Browser (a Linux program) but I assume there are several Windows programs doing the same I assume you use Windows)..
Joep

NoOp · May 26, 2012, 9:57pm

I have been attempting to send this posting for a while, and have only
recently noticed that the list does not accept attachments. This has
(apparently) caused it to be silently rejected. I have appended what
had been attached to the end of this posting.

Sorry for the delay.

... the database doesn't look like what I had expected it to: sqlite
databases contain within themselves the names and data types of all
the entries, but when libreOffice opens the database, there is no such
data visible. Am I missing something. A dump of the database using
"$ sqlite3 <archive>.sdb .dump" looks fine. Is this what I should expect,
or is something wrong, or am I missing something? On Tue, 2012-05-22

Am 21.05.2012 23:40, Jonathan Ryshpan wrote:
>
>blah
>

Ask your question on a SQLite forum with some _technical_ information
about your operating system, SQLite version of the creating application,
driver version, configuration file, connection URL of the client (Base
menu:Edit>Database>Properties).

Wow! This is enough to singe my beard. Nevertheless I am grateful for
all replies, even beard singeing ones.

I fear that I put in too much technical detail, rather than too little.
As you see, I am a complete newbie to LibreOffice base (oobase), and am
only trying to find out whether I have succeeded in getting oobase to
open my database properly. I don't think a posting to an sqlite forum
would be useful, since this is really a question about oobase.

If oobase has connected properly to the database, it is useless for my
purpose and I will give up on it and do what needs done with a bunch of
shell scripts. If oobase has not opened the database properly, I will
persevere.

Here is the technical info that Mr. Säger desires:

All my systems run Fedora-16. The database was generated on an i686
system. LibreOffice runs on an x86_64 system. The database is
contained in a file named wview-archive.sdb generated on the i686
system and copied to the x86_64 system.

The database was generated by wview, which can be downloaded at
        http://www.wviewweather.com/
My running version of wview is accessible (in case you want to know
the weather here) at:
        http://oaklandweather.no-ip.org/index.html/
The archive file is not visible from the web, but extracts from it
are at:
        http://oaklandweather.no-ip.org/index.html/Archive
The version of sqlite is included in
        sqlite-devel-3.7.7.1-1.fc16.<......>.rpm
The library is:
        /usr/lib/libsqlite3.so.0.8.6
The drivers are in:
        unixODBC-2.2.14-13.fc15.x86_64

You might want to use a GUI configure tool. On debian it is unixodbc-bin
- Graphical tools for ODBC management and browsing

$ ODBConfig

Makes it much easier to configure.

On Fedora I think it might be unixODBC-gui-qt
<https://admin.fedoraproject.org/pkgdb/acls/list/?searchwords=odbc>

and as specified in its initialization file (which follows)
        $ cat ~/.odbc
        [ODBC Data Sources]
        mysqlitedb=SQLite

        [mysqlitedb]
        Driver=/usr/lib64/libsqlite3odbc.so
        Description=My SQLite test database
  Database=/home/jonrysh/src/wview/archive/nullWindSpeed/wview-archive.sdb
        # optional lock timeout in milliseconds
        Timeout=2000

My initial tests (successful as I can see all the date in the tables)
used the places.sqlite files for Firefox & SeaMonkey. (They are easy to
look at with an sqlite viewer to verify). I also have the old microsoft
Nwind.mdb as well.

Mine is installed on Ubuntu/Debian - (I'd need to fire up Fedora in a VM
to test that distro). Here is my .odbc.ini file (configured with OBDConfig):

$ cat /home/gl/.odbc.ini
[SeaMonkeyPlaces.sqlite]
Description = SQLite3
Driver = SQLite3
Database = /home/gl/.mozilla/seamonkey/<snipped>.default/places.sqlite
Timeout = 100000
StepAPI = No
ShortNames = No
NoCreat = No
SyncPragma = NORMAL
LoadExt =

[NWTest]
Description = mdbtools
Driver = mdbtools
Database= /home/gl/Documents/MyDocuments/Nwind.mdb

[PlacesFirefox]
Description = SQLite3
Driver = SQLite3
Database = /home/gl/.mozilla/firefox/<snipped>.default/places.sqlite
Timeout = 100000
StepAPI = No
ShortNames = No
NoCreat = No
SyncPragma = NORMAL
LoadExt =

Note the difference in the SQLite drivers (I simply use use SQLite3).

I'll give your database a try this weekend & also try to find my
notes/links on how I got mine working (should be a post in this list IIRC).

The start of a dump of the database is appended to this message.
I.e. the output of
$ sqlite3 archive.sdb .dump

If anyone else wants to know more about my struggles with oobase and
sqlite, email me or post to the list.

All the best - jon

....

NoOp · May 26, 2012, 10:04pm

...

Mine is installed on Ubuntu/Debian - (I'd need to fire up Fedora in a VM
to test that distro). Here is my .odbc.ini file (configured with OBDConfig):

$ cat /home/gl/.odbc.ini
[SeaMonkeyPlaces.sqlite]
Description = SQLite3
Driver = SQLite3
Database = /home/gl/.mozilla/seamonkey/<snipped>.default/places.sqlite
Timeout = 100000
StepAPI = No
ShortNames = No
NoCreat = No
SyncPragma = NORMAL
LoadExt =

[NWTest]
Description = mdbtools
Driver = mdbtools
Database= /home/gl/Documents/MyDocuments/Nwind.mdb

[PlacesFirefox]
Description = SQLite3
Driver = SQLite3
Database = /home/gl/.mozilla/firefox/<snipped>.default/places.sqlite
Timeout = 100000
StepAPI = No
ShortNames = No
NoCreat = No
SyncPragma = NORMAL
LoadExt =

Note the difference in the SQLite drivers (I simply use use SQLite3).

I'll give your database a try this weekend & also try to find my
notes/links on how I got mine working (should be a post in this list IIRC).

Found the links:
<http://kiwinc.itgo.com/ian/TechBlog.html#OpenOffice_SQLlite>
<http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Using_SQLite_With_OpenOffice.org#ODBC-setup_of_new_database>
and
<https://en.wikibooks.org/wiki/MySQL/Databases_manipulation>

HTH

NoOp · May 27, 2012, 12:09am

...

I'll give your database a try this weekend & also try to find my

...

SQlite database created by:
1. Opening the archive .txt file in Calc & then saving as a .csv. Note:
I modified the first header slightly from 'Timestamp' to 'Date Time' so
that I'd have headers for both columns.

2. Open SQLite Manger & imported the .csv. Saved as an SQLite file.

3. Registered the new SQLite3 file using ODBConfig.

4. Opened LO 3.5 Base (Database from the menu), selected 'Connect to an
existing database', choose ODBC, and select Weather2. Table shows all
data from the archive file.

Files are here if anyone cares to view/use.

http://www.2shared.com/file/k8C-0SXm/Weather.html
(Weather.odb)
http://www.2shared.com/file/Fiddx6IL/weatherNoOp.html
(SQLite file)
http://www.2shared.com/file/1pj44lW9/weather.html
weather.csv
http://www.2shared.com/file/T9pvKz3y/weather.html
weather.ods