Advanced Topic: Connecting to a Microsoft Access Database - Alfresco Federation Services - 3.2 - 3.2 - Ready - Alfresco - external - Alfresco/Alfresco-Federation-Services/3.2/Alfresco-Federation-Services/Configure/Connectors/JDBC/Advanced-Topic-Connecting-to-a-Microsoft-Access-Database - 2025-03-04

Alfresco Federation Services
Platform
Alfresco
Product
Alfresco Federation Services
Release
3.2
License

To use an Access database you’ll need a JDBC Driver.

You will need to install the JARs in the 3sixty-admin web app. Find Manipulating an Access database from Java without ODBC on Stack Overflow to see the JARS you will need:

Place them into 3sixty-admin/WEB-INF/lib.

After installing the JARs you should be able to start Federation Services and use the JDBC connector.

Example JDBC to BFS Oracle Integration

This process walks you through setting up an integration from a JDBC Repository to a BFS Output system.

  1. Create a JDBC Auth Connector:

    For Oracle there are a couple of things to note:

    1. The username must be all caps
    2. The SID/Schema must be all caps as well
    3. The username can be different from the Schema name, but obviously needs permissions to that Schema.
    4. Note the Driver class
  2. Create a JDBC Discovery Connector:

    Set the authentication connector to be the auth connector created above. For Oracle, the above will get all objects in your database.

    Troubleshooting:

    Remove any ojdbc jar file from the 3Sixty-admin/WEB-INF/lib directory and put in the ojdbc jar for your Oracle version. For Oracle 12c as an example that would be ojdbc8.jar.

    The schema pattern should be set to the schema you want. Oracle seems to hang if you leave that field blank.

    Table types can be a comma delimited list of the following:

    1. TABLE
    2. VIEW
    3. SYSTEM TABLE
    4. GLOBAL TEMPORARY
    5. LOCAL TEMPORARY
    6. ALIAS
    7. SYNONYM
  3. Create an integration connector for JDBC:
  4. Create an integration connector for BFS.
    Note: BFS does not have an authentication connector.
  5. Create a job with the repo connector as your JDBC integration connector and the output connector as your BFS integration connector.
  6. Edit the BFS Tab:

    Chose your output folder path that must exist ahead of time. Check the Include Un-Mapped Properties check box. Leave the rest as the default like the above picture.

  7. Edit the JDBC Tab:

Some things to note about this configuration:

  • You should test your query using SQL Developer. This will allow you to get your query correct before testing it in Federation Services
  • Notice how ABC_PLAN_id is in quotes in the query? That’s because in this instance that field won’t work unless its in quotes. Both SQL Developer and Federation Services will throw and error.
  • The ID Field must be unique and is used as the file name. See the image below to see how this shows up in BFS Output
  • The query should list the fields you want. Select * is not intended to work.
  • The query cannot end with a ; or it will fail.
  • Fetch Size can be modified to improve performance

This is what the BFS Output looks like for this query. It returns one row, and the id was the number 1.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE propertiesSYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>--No Comment---</comment>
<entry key="ABC_PLAN_id">1</entry>
<entry key="folderpath"></entry>
<entry key="PLAN_YEAR_NBR">12.45</entry>
<entry key="type">cm:content</entry>
<entry key="PLAN_YEAR">2004</entry>
<entry key="separator">,</entry>
</properties>

The above xml is an example of the properties that were output for this job.

You’ll notice the fields come over as is with no prefix. PLAN_YEAR won’t have a type associated with it. When you see this, you can fix it by modifying your query like the following:

select “ABC_PLAN_id” as “mytype.abc_plan_id”, plan_year as “mytype.plan_year”, plan_year_nbr as “mytype.plan_year_nbr” from abc_plan

The new query results in a xml file that looks like the following:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE propertiesSYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>--No Comment---</comment>
<entry key="folderpath"></entry>
<entry key="mytype.plan_year_nbr">12.45</entry>
<entry key="mytype.abc_plan_id">1</entry>
<entry key="type">cm:content</entry>
<entry key="mytype.plan_year">2004</entry>
<entry key="separator">,</entry>
</properties>

But the file is named: default_document_name.doc.metadata.properties.xml

This is because we changed the ID Field in the query, but not in the ID Field in the form. Now if we update it like the following:

You’ll see the file name is back to: 1.metadata.properties.xml

Now that you have this working with BFS you can create your mappings to match what you want in the output system. You can map mytype in this case to the output type you want. You can then map the mytype fields to the fields of the new type.

  • Run the JDBC to BFS Job to finish the integration process