OO Base/SQLite

[John Ralls]

On Dec 25, 2012, at 8:21 PM, spotty albino <jehelsby at iinet.net.au> wrote:

> Greetings 
> 
> I hope everyone has had a Merry Christmas :)
> 
> I have a question about using LibreOffice Base to connect to the GNUcash
> database so I can build my own reports in Base. I realise similar questions
> have been asked before. Unfortunately I have no experience with databases
> and everything I have read in the Nabble and FAQ goes over my head.
> 
> I'm using Gnucash 2.4.11 and Mac OS 10.6.
> 
> Base can connect to a database using MySQL, PostgreSQL, ODBC, JDBC amongst a
> few other methods. I understand PostgreSQL is not recommended as the libdbi
> is no longer easily obtainable for OSX. I've read that SQLITE is recommended
> since it is simple and does not require much maintenance. 
> 
> My question are;
> 
> -Is SQLITE right for me?
> -How do I go about setting up SQLITE to interface with Gnucash?
> -How do I got about setting up LibreOffice Base to interface with SQLITE?
> 
> Please note I have limited experience with the Terminal, although I'm keen
> to learn. I do not wish to write to the gnucash databse. 

Yes, I think SQLite would work well for you. I haven't tried connecting to a Gnucash SQLite file with LibreOffice base, mostly because I didn't think of it -- it's an excellent idea, so long as you do it read-only and don't need (or can figure out) the data stuffed into Slots.

There's no problem getting the pgsql dbi sources. They're in the same dbd-drivers package along with the rest. I don't include the pgsql or mysql drivers because a) the dmg is quite large enough already and b) I'd have to install mysql and pgsql on my build mac and you'd have to install the same versions on yours, built with the same settings and with the same SDK, and that's too hard.

To use SQLite, just use File>Save As and select SQLite3 from the combo box at the top of the dialog box -- it says "XML" by default.

AFAIK, all you need to do in Base is open the file. Make sure that Gnucash doesn't have it open: SQLite has no locking whatsoever, and having the file open with two programs, even if one of them never writes, is asking for trouble.

Regards,
John Ralls