Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file
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. On Mon, 2012-05-21 at 14:40 -0700, Jonathan Ryshpan wrote: ... 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 On Tue, 22 May 2012 at 00:43 +0200, Andreas Säger wrote: 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:EditDatabaseProperties). 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.fc16rpm 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
Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file
On 25-05-12 16:16, Jonathan Ryshpan wrote: 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. On Mon, 2012-05-21 at 14:40 -0700, Jonathan Ryshpan wrote: ... 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 $ sqlite3archive.sdb .dump looks fine. Is this what I should expect, or is something wrong, or am I missing something? On Tue, 2012-05-22 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file
On 22/05/12 22:29, Tom Davies wrote: 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 :) --- On Mon, 21/5/12, Jonathan Ryshpanjonr...@pacbell.net wrote: From: Jonathan Ryshpanjonr...@pacbell.net Subject: Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file To: users@global.libreoffice.org Date: Monday, 21 May, 2012, 17:34 On Sun, 2012-05-20 at 01:48 +0200, Mirosław Zalewski wrote: 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=40t=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. 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 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/ -- Cheers Simon Simon Cropper - Open Content Creator / Website Administrator Free and Open Source Software Workflow Guides Introduction http://www.fossworkflowguides.com GIS Packages http://www.fossworkflowguides.com/gis bash / Pythonhttp
Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file
On Sun, 2012-05-20 at 01:48 +0200, Mirosław Zalewski wrote: 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=40t=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. 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Connecting libreoffice calc to existing sqlite database file
On Mon, 2012-05-21 at 09:34 -0700, Jonathan Ryshpan wrote: On Sun, 2012-05-20 at 01:48 +0200, Mirosław Zalewski wrote: 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=40t=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. Thanks very much for the pointer. I attempted to follow the instructions, installing the SQLite ODBC Driver, but I have installation problems. 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
