Documentation/WollMux/Using databases

    From The Document Foundation Wiki
    Other languages:

    Download JDBC driver

    A jdbc driver is required to access a database. Download Oracle JDBC driver. The driver should not be placed on a network drive, but locally.

    Include JDBC driver

    System-wide

    The JDBC driver can be included system-wide via a departmental LibreOffice preconfiguration.

    Entry DataAccess.xcu

    <node oor:name="JDBC">
       <node oor:name="DriverClassPaths">
         <node oor:name="oracle.jdbc.driver.OracleDriver" oor:op="replace">
           <prop oor:name="Path">
             <value>file:///<path of.jar file>/<name.jar></value>
           </prop>
         </node>
       </node>
     </node>

    INFO For a general functional test, the DataAccess.xcu can also be created in your own profile (in the directory ~/.config/libreoffice/4/user/registry/data/org/openoffice/Office).

    User-wide

    Include for a single user

    Invoke LO Base

    Base client

    From the console K-Menu → System Administration → Console (Terminal) → Enter: soffice -base or sbase

    Windows

    • Windows Start menu → Programs → LibreOffice → LibreOffice Base.
    • Command prompt → Enter: <LibreOffice installation path>\program\sbase

    Create the required LO-Base database once

    1. Invoke LO Base
    2. Select database
      Select appropriate database. --> "Next>" button

    3. Set up database connection
      Enter database connection information. --> Button "Next>"

      The button Test Class can be used to test the corresponding JDBC driver class.
    4. Set up user authentication
      Enter user for database access. If password is required set checkbox. -->Button "Next>"

      Use the button Connection Test to test the connection to the database.
    5. Finish and continue
      The database should not be logged in and LO Base should be terminated --> "Finish" button
    6. Save LO data source

    Deploy LO Base database

    Deploy LO Base database on the web server

    To allow the LO Base database to be used by multiple users, it is recommended to put the .odb file on the web server.

    Register LO Base database via WollMux

    In order for a LO Base database to be used, it must be registered via a Register section. For example, a data source can be registered in referat.conf.

    Example with the previously created oracle.odb:

    datasources(
      Register(
       NAME "MyOOoDataSource" # <Name_of_OOo_DataSource>
       URL "http://<Path>/oracle.odb" # <URL_of_odb_file>
       REFRESH "true" # <true_or_false>
      )
    )

    Further information:

    Setting up a LO data source

    To access an external database with WollMux, a LO data source must be defined in referat.conf' that points to the registered LO Base database. Likewise, a separate .conf configuration file can be created, which is included via a %include "<name>.conf" entry in referat.conf.

    Example with DB schema to bind to "Select Recipient" dialog:

    datasource(
      Data source(
        NAME "MyOOoDataSource" # <Name_of_WollMux_Data_Source>
        TYPE "ooo"
        SOURCE "oracle" # <Name_of_the_registered_in_OOo_data_source
         TABLE "<name_of_the_table_or_view>"
        USER "<username/user>"
        PASSWORD "<Password>"
        SQL_SYNTAX "oracle" # <optional syntax identifier, supported: ansi, oracle, mysql >
        Schema("<DB column with name>" "<DB column with street>" "<DB column with zip code>" "<DB column with city>")
        Key("<DB column with name>")
        # If a schema() section is specified, a key section must also be specified.
      )
    )

    see Setting up a LO data source

    Connect Select Recipient dialog

    The Select Recipient dialog can be extended via the configuration file address-select-recipient.conf e.g. by an extra tab. As an example, the toy stores in the address-selection-referat.conf, whose entry can be copied and adapted. The file address_selection-referat.conf is not overwritten by the xupdate script.

    Example customization for toy stores:

    MyOOoDataSource( # Name of the data source with type ooo.
           TITLE "<title>"
           CLOSEACTION "back"
           TIP "Select a name as recipient"
    
           Intro(
             (LABEL "You can search for name components or street names" TYPE "label" )
             (TYPE "glue" )
           )#intro
    
           Search(
             (TYPE "textfield" ID "search" ACTION "search" AUTOFILL "")
             (LABEL "search" TYPE "button" HOTKEY "S" ACTION "search")
           )#Search
    
           Search strategy(
             MyOOoDataSource()
             MyOOoDataSource(<DB column with name> "${searchquery1}*" ) # The DB columns must be entered in the data source definition at "Schema" )
             MyOOoDataSource(<DB column with name> "${suchrequest1} ${suchrequest2}" )
             MyOOoDataSource(<DB column with name> "${suchrequest1} ${suchrequest2} ${suchrequest3}*" )
             MyOOoDataSource(<DB column with postcode> "${suchrequest1}" )
             MyOOoDataSource(<DB column with name> "${searchrequest1} ${searchrequest2} ${searchrequest3}" )
             MyOOoDataSource(<DB column by name> "${suchrequest1} ${suchrequest2}" )
             MyOOoDataSource(<DB column by name> "${searchrequest1}" )
           )#search strategy
    
           Column conversion(
             ReceiverRow1(VALUE("<DB column by name>"))
             ReceiverRow2(VALUE("<DB column with street>"))
             RecipientRow3(CAT(VALUE("DB column with postal code>") " " VALUE("<DB column with city>")))
           )
    
           Search result(
             (LABEL "search result" TYPE "label")
             (TYPE "listbox" ID "search result" LINES "10" ACTION "select"
               DISPLAY "${receiverLine1}") #Relates already to result after column conversion
           )#Search result
    
           Preview( #refers already to result after column conversion
             (LABEL "Preview" TYPE "label")
             (LABEL "Receiver row 1" DB_SPALTE "Receiver row1" TYPE "textfield" READONLY "true")
             (LABEL "ReceiverLine2" DB_SPALTE "ReceiverLine2" TYPE "textfield" READONLY "true")
             (LABEL "ReceiverLine3" DB_SPALTE "ReceiverLine3" TYPE "textfield" READONLY "true")
             (TYPE "glue")
           )#Preview
    
           Footer(
             (LABEL "cancel" TYPE "button" HOTKEY "A" ACTION "back")
             (TYPE "glue")
             (LABEL "select" TYPE "button" HOTKEY "S" ACTION "select")
           )#Foot area
    ) #MyOOoDataSource

    Additional information: