Propojení OpenOffice.org s databází

16. 3. 2004 | Petr Bravenec

Články - Propojení OpenOffice.org s databází  

Jednoduchý postup pro využití dat z SQL databází v dokumentech OpenOffice.org. Instalace ovladače, navázání spojení, práce s tabulkami.

Úvod

Ve svém zaměstnání jsem byl postaven před úkol propojit výrobní informační systém s kancelářským prostředím. I když se používá v kancelářích především Miscrosoft Office, tomuto sofwareovému balíku se věnovat nebudu, a pozornost obrátím především na OpenOffice.org. Informační systém u nás běží nad databází PostgreSQL, s jinými databázemi nemám zkušenosti. Bližší informace o ostatních databázích je možné najít na internetu - odkazy jsou v samostatné části tohoto článku. Chcete-li se připojit k Nepatrně Blbější Ale Mnohem Rychlejší SQL Databázi (koleduju si o přeshubu, protože uživatelů této databáze je mnohem více než uživatelů PostgreSQL), najdete driver vestavěný přímo v OpenOffice.org a není třeba jej instalovat.

Odkazy

http://jdbc.postgresql.org

PostgreSQL JDBC Drivers. Zde získáte hotový JDBC driver pro vaši verzi Javy a databázového serveru.

http://www.fankhausers.com/postgresql/jdbc/

PostgreSQL and JDBC FAQ

http://dba.openoffice.org/drivers/postgresql/index.html

Driver PostgreSQL napsaný originálně pro OpenOffice.org. Neztrácejte s ním čas. Je téměř nepoužitelný. Odkaz uvádím pouze kvůli varování před zklamáním a marnou snahou dosáhnout rozumných výsledků.

http://dba.openoffice.org/FAQ/specific.html

OpenOffice Database Access - Tips'n'tricks

http://www.unixodbc.org

unixODBC - asi funguje, ale překlad a nastavení je poněkud složité

Instalace

Moje experimentování ukázalo, že zřejmě nejpoužitelnější způsob propojení OpenOffice.org s databází PostgreSQL je přes JDBC. I když je v OpenOffice.org napsaný driver speciálně pro komunikaci s PostgreSQL, neosvědčil se mi. Při použití nativního driveru pro PostgreSQL jsou problémy s datumovými položkami a s reálnými čísly a možná i mnoho dalších problémů, na které jsem nepřišel, protože jsem se nedokázal přenést ani přes neschopnost driveru pracovat s datumy.

V dalším textu předpokládám, že OpenOffice.org, PostgreSQL i Java už jsou nainstalované a funkční. Všechny tři balíky bývají celkem běžnou součástí linuxových distribucí, takže se omezím pouze na popis instalace JDBC driveru.

Driver je pro každou databázi jiný. Při pátrání po JDBC driver hledejte driver konkrétně pro vaši verzi databáze a Javy. V mém případě je to soubor pg74.1jdbc3.jar. Ten překopírujte někam, kam ukazuje proměnná CLASSPATH, případně na takové místo v instalaci Javy, které je určené pro externí knihovny. Než jsem začal psát tento článek, bylo potřeba zkopírovat soubor pg74.1jdbc3.jar sem:

cp pg74.1.jdbc3.jar /usr/java/j2sdk1.4.1_02/jre/lib/ext/postgres.jar.

Poté, co jsem kvůli Mozille nainstaloval novější verzi Javy, musel jsem driver nainstalovat takto:

cp pg74.1.jdbc3.jar /usr/java/j2re1.4.2_03/lib/ext/postgres.jar

V principu jde pouze o to zkopírovat driver někam, kde Java hledá knihovny, nejlépe přímo do adresář, který je určený na externí moduly (lib/ext). Nezapomeňte samozřejmě na to, že soubor musí mít nastavená příslušná přístupová práva: -rw-r--r--.

Tím by měla být celá instalace hotová. Aby se dal driver JDBC používat, musí se ještě nakonfigurovat jak databázový server, tak OpenOffice.org.

Nastavení databáze PostgreSQL

Aby byl databázový server schopen poslouchat na portu TCP/5432 a komunikovat s klienty po síti, musí být TCP/IP komunikace povolená. Jednou z možností je nastartovat databázový server s parametrem -i, čímž se povolí síťová komunikace.

Jiná možnost je upravit konfigurační soubor postgresql.conf, doplnit do něj řádky:

tcpip_socket = true port = 5432

Než začneme s dalšími experimenty, bylo by dobré založit pokusnou databázi. Chcete-li přistupovat do existující databáze s daty, na nichž vám aspoň trochu záleží, pak bych doporučoval jako bezpečnostní opatření vytvořit uživatele, říkejme mu "office", s právy pouze pro čtení. OpenOffice.org totiž otevírá tabulky i pro zápis a v prostředí officové tabulky je až příliš jednoduché přepsat nežádoucím způsobem data.

Budete-li ukládat do databáze znaky v češtině, je nutné zadat při zakládání databáze i její kódování. JDBC driver v OpenOffice.org jinak protestuje a není možné získat z tabulek žádná data. U databáze PostgreSQL se musí zadat kódování databáze při jejím založení:

createdb -E Latin2 pokusy createuser office sql template1 <<! alter user office with unencrypted password 'heslo'; !

Ať už povolíte síťovou komunikaci databázového serveru tak či onak, musíte ještě nakonfigurovat práva pro síťový přístup v souboru pg_hba.conf (předpokládám síť 10.1.1.0/24 - doplňte vlastní hodnoty). V konfiguračním souboru pg_hba.conf lze zvolit, jestli bude při připojení k databázi vyžadované heslo. Chcete-li povolit přístup k databázi bez hesel i z ostatních počítačů v síti, nejen z databázového serveru, použijte místo slova "password" slovo "trust":

host pokusy office 127.0.0.1 255.255.255.255 trust host pokusy office 10.1.1.0 255.255.255.0 password

Vlastníkem vytvořené databáze nemusí být právě uživatel office. Uživatel office dokonce nemusí vůbec existovat jako linuxový účet (či jako účet jiného systému, na kterém provozujete databázový stroj). Nicméně při vytvoření nové databáze je jednodušší, pokud uživatel office existuje i v systému.

Pro potřeby tohoto článku jsem si vytvořil novou databázi a aby nebyla úplně prázdná, vytvořil jsem v ní jednoduchou tabulku a naplnil ji daty:

sql pokusy <<! create table telefonni_seznam (   radek int4 primary key,   prijmeni text not null,   jmeno text not null,   cislo text); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo)   values (1, 'Vopička', 'Franta', '789 333 443'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo)   values (2, 'Slepička', 'Franta', '789 333 143'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo)   values (3, 'Bobeček', 'Josef', '789 233 143'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo)   values (4, 'Blonďatá', 'Marie', '789 233 143'); grant all on telefonni_seznam to office; !

Připojujete-li se k existující databázi, je potřebné dát uživateli office alespoň minimální práva na tabulky, ke kterým chcete z OpenOffice.org přistupovat. Chcete-li do tabulek vkládat a používáte-li datový typ serial, nezapomeňte ani na příslušné sekvence:

grant select on databazova_tabulka to office; grant update on databazova_tabulka to office; grant insert on databazova_tabulka to office; grant select on databazova_tabulka_klic_seq to office; grant update on databazova_tabulka_klic_seq to office;

Nastavení datových zdrojů

Máme-li nastavenou databázi a nainstalovaný JDBC driver, můžeme se pokusit o propojení OpenOffice.org s daty. Do konfiguračního formuláře se dostaneme z menu přes Nástroje->Zdroje dat...

Ve formuláři klikněte na tlačítko "Nový zdroj dat" na vrcholku seznamu zdrojů dat. Tlačítko je nenápadně umístěné a tím, že jsem vás na ně upozornil, jsem vám ušetřil spoustu práce a přemýšlení, jak se v OpenOffice.org připojit k různým databázím.

Na kartě "Obecné" připojení pojmenujte, zvolte typ databáze (JDBC) a vyplňte URL zdroje dat. URL může vypadat například takto:

jdbc:postgresql://dbserver/pokusy

V URL se říká, že se má použít interface JDBC, driver PostgreSQL, připojovat se budeme k serveru dbserver k databázi pokusy.

Na kartě JDBC vyplňte třídu JDBC ovladače. Ta má být pro databázi PostgreSQL "org.postgresql.Driver". Další položkou, kterou je třeba vyplnit, je uživatelské jméno. V našem příkladě použijte jméno "office". Zaškrtnout musíme ještě případné vyžádání hesla. Office se sám od sebe na heslo neptá a vyžaduje-li databáze přístup pomocí hesla, nedovede se OpenOffice.org připojit, není-li tato volba zaškrtnutá. Tím je veškeré nezbytné nastavení hotovo a můžeme se pokusit o připojení k databázi. Přepnutím na kartu "Tabulky" se OpenOffice.org pokusí připojit k databázi a vytvořit seznam tabulek.

Nástroj pro správu datových zdrojů je sice primitivní, ale je poměrně mocný a nebezpečný. Nechci samozřejmě nikoho navádět, ale označte některou tabulku a klikněte na obrázek s červeným křížkem. Pak se podívejte, jak byla nastavená v databázi přístupová práva pro uživatele office a jestli tabulka ještě existuje a zauvažujte nad tím, o jak důležitá data jste jediným klikancem přišli. Právě jednoduchost, s jakou lze tímto nástrojem tabulky vytvářet, modifikovat a likvidovat, je důvodem pro maximálně restriktivní nastavení přístupových práv z OpenOffice.org. V žádném případě nedávejte běžným uživatelům práva pro zápis nebo modifikace tabulek. Naštěstí lze v PostgreSQL snadno vytvořit pravidla i pro zápisy do view, naprogramovat funkce pro kontrolu dat a zakázat přístupy do skutečných tabulek s daty.

V OpenOffice.org lze databázové tabulky jediným kliknutím myši i vytvářet, nejen mazat. Vytvářet lze ale jen velmi primitivní tabulky bez vazeb na své okolí. Mně tento způsob tvorby tabulek nikdy nevyhovoval, protože přes OpenOffice.org nelze zadat většinu vlastností, kterými moje tabulky disponují (cizí klíče, kontroly mezí, unikátní indexy a podobně).

Propojení databázových tabulek s dokumenty OpenOffice.org

Podařilo-li se vám připojit k databázi a vylistovat seznam tabulek, nic už vám nebrání otevřít databázovou tabulku a naimportovat data například do tabulkového kalkulátoru.

Ještě než začnete, rád bych varoval před chybou, která mě stála spoustu času a námahy. Množství tabulek jsem vytvářel v MS Excelu 2000 s použitím MS Query. Jste-li stejně jako já zvyklí na import dat do Excelu, nacvičený postup vás zavede přes položku v menu Data->Průvodce daty do jámy pekelné. Tudy cesta nevede. Nástroj je určený pouze na tvorbu kontingenčních tabulek a pokud jste alespoň minimálně zběhlí v SQL dotazech, doporučuji použít raději uložené SQL dotazy či view - tedy zpracovávat data na straně databázového serveru. O uložení dat ví databázový server mnohem více než si tabulkový kalkulátor vůbec dovede představit a dokáže data zpracovat s neporovnatelně větší efektivitou a mnohem, mnohem rychleji.

V OpenOffice.org existuje mnohem přímočařejší nástroj, než je MS Query v Office 2000 (Možná, že v novějších verzích MS Office existují podobné nástroje jako v OpenOffice.org. Nevím. Novější office od Microsoftu jsem dosud neměl příležitost používat). Otevřete si novou tabulku v tabulkovém kalkulátoru a stiskněte klávesu F4 nebo zvolte v menu položku Zobrazit->Zdroje dat. Okno s tabulkou se rozdělí a v jeho horní části se otevře nástroj pro práci s databázemi.

Data lze přímo v nástoji nejen prohlížet, ale i upravovat či mazat a vkládat do tabulek nové věty. Není problém změnit data v tabulkách k nepoznání i pouhým omylem. To je další důvod pro řádné nastavení přístupových práv a striktní oddělení jednotlivých uživatelů.

Budete-li zkoušet měnit data v ukázkové tabulce telefonni_seznam, pravděpodobně nenarazíte na žádný zádrhel. Jakmile ale začnete experimentovat s vlastními tabulkami, možná zjistíte, že některé tabulky prostě nelze změnit, i kdybyste se na hlavu postavili, a OpenOffice.org vám odmítne prozradit, proč se chová tak nevypočitatelně. Je to kvůli primárním klíčům - aby byly databázové údaje v OpenOffice.org modifikovatelné, musí mít tabulka vytvořený unikátní primární klíč. OpenOffice.org jinak nedovede rozeznat jednotlivé věty v tabulce od sebe a než by riskoval nespokojenost uživatele s tím, že oprava jedné věty přepíše půl databázové tabulky, raději zakáže změny úplně.

Ale článek jsem psal hlavně kvůli importu dat do dokumentů OpenOffice.org. Zvyklý na oklikové postupy z MS Office 2000 jsem marně klikal na tabulku pravým tlačítkem, bloudil v kontingenčních tabulkách a proklínal tvůrce OpenOffice.org z tak šíleného programu. Řešení je přitom jednoduché a zcela přímočaré: tabulku uchopte myší za prázdné levé horní pole v nadpisech a přetáhněte na požadované místo v dokumentu. Data importovaná do tabulkového kalkulátoru nevyžadují žádné další operace. Importujete-li ale data do textového editoru, máte ještě možnost zvolit způsob zobrazní dat (tabulka, pole, text a spoustu dalších formátovacích atributů).

Data jednou importovaná do dokumentu už nemají přímou vazbu na databázi. Změníte-li některé údaje, změny se do databáze nepromítnou. Neznamená to ovšem, že vazba na databázi zmizela úplně. Oblasti s databázovými dotazy lze zobrazit v nástroji Data->Vybrat Oblast a následně obnovit data z databáze volbou Data->Obnovit Oblast;

Omezení

Je-li v tabulce definováno více datových oblastí, nedokázal jsem obnovit všechny oblasti najednou - vždy bylo potřeba každou datovou oblast označit a obnovit zvlášť. Možná, že na to existuje podobně jednoduchý fígl jako s přetažením tabulky do dokumentu myší - já jsem na něj zatím nepřišel.

OpenOffice.org nedovede importovat některé typy dat. Nepoužívám samozřejmě všechny možné datové typy v PostgreSQL. V databázi mám uložená spíše data ekonomického významu a nikdy jsem nepoužil různé geometrické či internetové datové typy. Narazil jsem jen u datového typu interval. Tam lze naštěstí řešit situaci přepočtem například na hodiny přímo v databázi.

Další omezení, tentokrát mnohem nepříjemnější, je praktická nemožnost práce s primárními klíči typu serial při modifikaci dat. Tento datový typ se při insertu běžně nevkládá a obsah datové položky tvoří sama databáze - Office se o vygenerovaných datech nedoví. Při jednoduchém importu do dokumentů se ale tento problém nijak neprojevuje. Starosti začíná dělat až při práci s formuláři, které ale nejsou tématem tohoto článku. Narazit na toto omezení můžete při postupech popsaných v tomto článku jen při změnách dat v nástroji "Zdroje dat".

Závěrem

V článku jsem předvedl pouze nejjednodušší způsob propojení databáze s OpenOffice.org. I když je v článku zmíněna pouze databáze PostgreSQL, práce s libovolnou jinou SQL databází na principu klient-server se nebude příliš lišit. Nebude ani příliš záležet na způsobu připojení - připojení přes JDBC, ODBC či přes nativní driver se bude lišit jen v detailech.

V OpenOffice.org lze vytvářet i formuláře přímo propojené s databází. Takové formuláře pak lze využít i jako programový prostředek pro sběr dat. I když jsem nedovedl své experimenty v této oblasti až do finále a nakonec jsem žádné smysluplné formuláře nikdy nevytvořil a nepoužívám, podařilo se mi nasbírat pár zkušeností, které by mohly v budoucnu přinést pokračování v článcích s touto tématikou.

Diskuse k tomuto článku

[Leoš Literák]

Ahoj,

ten zmineny postup kopirovani jaru do adresare ext neni uplne nejvhodnejsi. Tim se totiz natahne pri kazdem spousteni JVM (javy), at je potreba ci nikoliv. A kdybyste napriklad pouzivali jinou aplikaci, ktera by take pouzivala stejny ovladac, ale v jine verzi, zadelali byste si na problemy.

Spravne reseni je nastavit si CLASSPATH tak, aby odkazaovala na ten jar. A nastavit si ji nikoliv globalne, ale pro jednotlive aplikace.

Pokud ale vezmu v uvahu, ze typicky uzivatel javu nepouziva a kdyz ano, tak maximalne pro jednu dve aplikace, pak je zde zminene reseni uplne bezproblemove a nejjednodussi.

[Jakub Hegenbart]

Znamená to tedy, že se načítají automaticky všechny třídy z ext? Myslel bych, že když i v takovém případě musím použít Class.forName(String) pro registraci driveru, tak se asi nenačítají...nebo třídy JDBC driverů mají výjimku?

[Peter Bodnar]

ja som svojho casu (pred rokom) pouzival na pristup do postgresu ODBC driver a nemal som s nim najmensie problemy najprv trebalo nastavit ODBC priamo v linuxe, to bol trochu problem, lebo bola mizerna dokumentacia potom boli aj trochu problemy s kniznicami (aspon teda vtedy, musel som rucne dokopirovat postgresove kniznice a porobit linky) ale potom to slapalo omnoho rychlejsie ako JDBC

[Petr Bravenec]

...nemal som najmensie problemy... to bol trochu problem... boli aj trochu problemy... Tak boli problemy alebo neboli problemy?

S ODBC jsem taky jednu chvíli pracoval. Ale přeložení a nastavení ODBC v Linuxu je na samostatný článek a energie vložená do zprovoznění ODBC mi ve srovnání s jednoduchostí JDBC přijde neúměrná. No a co, že JDBC pracuje pomaleji? Ať se taky chvíli snaží počítače

[BoodOk]

Zase kompilovani. Proc? ODBC staci nainstalovat z balicku a pres odbcmanager nastavit. Nezda se mi to slozite. Mluvim o unixODBC v dnesni podobe.

[Van Vren]

Moc by mě zajímaly formuláře v OO s přímým napojením s databází, tedy v článku zmiňovaný zběr dat. Pro ženský v kanceláři by byla intuitivnější práce v prostředí podobném office ... a pro mne jednodušší vytvořit a měnit. V běžném provozu je potřeba každou chvíli drobné změny a všecko to dělat php-apache-mysql není úplně to pravé ořechové. Máte někdo nějaké zkušenosti? nebo linky?

[Petr Bravenec]

Momentálně si s tím hraju a třídím poznatky tak, aby se z toho dal napsat další článek. Naposledy jsem s formulářema dělal někdy před rokem a to už je dost dlouhá doba, abych pozapomínal spoustu detailů. Plánovaný článek ještě nemá napsaný ani řádek, ale už se mi rýsuje alespoň osnova.

Upozorňuju ale předem, že od samého začátku tvořím formuláře na míru databázi PostgreSQL. V MySQL by vám mohly chybět triggery, referenční integrita, kontrola dat přímo v databázi a read-write view.

[Valoun]

malinkatá poznámka: mySQL má referenční integritu (viz. InnoDB tabulky), jak je to s triggery a kontrolou dat nevím, to už je otázka pro ostatní čtenáře.

[Jakub Hegenbart]

Ano, má, ale jen tehdy, pokud si člověk odřekne relativně kvalitní FTS (a protože to je jediný důvod, proč bych kdy do MySQL šel, automaticky to pro mě znamená, že MySQL referenční integritu nemá)

[Dalibor Smolík]

Ja toto propojeni dat pouzivam uspesne i u databaze MySQL. Nejprve upravim soubory /etc/odbc.ini a /etc/odbcinst.ini, a u MDK 9.2 doinstaloval kihovny - libmyodbc.so a vcetne devel (http://rpm.pbone.net), v OO přidal nový zdroj dat - název MySQL-test, vybrat ODBC, URL zdroje dat -
ODBC:MySQL-test, zadat uzivatelske jmeno.

[arnost]

mne u unixodbc a mysql nesla rozchodit varianta, kdy mysql bezelo na jinem serveru nez na localhostu.

[Jiří Mlíka]

Zdravím všechny,

používám kombinaci OpenOffice.org + ODBC + MySQL. Zpočátku se to jevilo jako dobré, ale přišel jsem na pár problémů:

1) Máte-li v tabulce datový typ FLOAT nebo DOUBLE, OO.org ořízne desetinná místa. Lze obejít datovým typem DECIMAL.

2) Používáte-li v SQL dotazu výpočet, jehož výsledkem je desetinné číslo, je rovněž oříznuto. Lze obejít změnou datového typu výsledku na textovou hodnotu (CAST). Pro přenos do tabulky to příliš nevadí, ale komplikuje to dotazy.

3) Návrhář/editor SQL dotazů v OO.org je poměrně stupidní. Při ukládání dotazu provádí kontrolu. "Vhodně" zformátovaný text dotazu odmítne pro chybu uložit, přestože je dotaz zcela správně a OO.org/MySQL jej klidně provede. Naštěstí lze tuto pochybnou kontrolu vypnout.

[Ivo Panacek]

Ahoj, uz jsem si s databazi taky "hral", ale narazim na problem pri zmene j2sdk (= novy adresar). ENV promenne jsou spravne, ale nejak nemoho najit, kde se v OOo meni (a kontroluje) nastaveni $JAVA_HOME nebo ekvivalentu. Ted prave jsem totiz presel na j2sdk1.4.2_04 a databazove zdroje jsou nedostupne, protoze se mu nedari najit jdbc drivery, ty ale v $JAVA_HOME/jre/lib/ext mam. Netusite ?

[David Mudrak]

Mě pomohlo Nástroje->Volby->Bezpečnost->ClassPath. Přidat cestu k .JAR archívu a restartovat OO.

[Tomas]

OpenOffice.org@WinXP + ODBC + Oracle9i@*nix funguje uplne rovnako.

[Martin]

Ahoj, mám WinXP pro, EasyPHP 2.0b1, OOo 2.2. Nainstaloval jsem ODBC connector verze 5.00.10 beta.

Pak jsem šel do Ovládacích panelů -> Nástroje pro zprávu -> Datové zdroje (ODBC) -> karta uživatelské DNS -> tlačítko Přidat -> vybrat MySQL Connector/ODBC v5 -> Dokončit.

Dále jsem si otevřel program BASE, zvolil jsem Připojit k databázi, zvolil jsem MySQL -> Připojit se pomocí ODBC -> další -> tlačítko Procházet -> zvolit MySQL ODBC Driver -> OK -> Další -> vyplnil jsem uživatelské jméno a heslo, otestoval spojení, které bylo úspěšné -> dokončit. Proč když si dám Nástroje -> Vztahy, tak mi to napíše: "Databáze nepodporuje relace"?

Ještě mám další dotaz, jak načtu data z databáze do programu Base?