topic Re: Put results of a mysql query into DataList in Alfresco Archive

Put results of a mysql query into DataList

soulcollector — Wed, 12 Oct 2016 13:02:05 GMT

Hello. I'm completely new to Alfresco, but i have a decent knowledge about MySQL databases and queries.Using Alfresco Community, i have the following problem. The agency i'm working for has a customers database; it has these columns:NameCompanyBirthdateCustomer sinceSectorWhat i should do is to load

Re: Put results of a mysql query into DataList

afaust — Wed, 12 Oct 2016 13:17:47 GMT

1) I can always suggest people new to Alfresco and custom data models read this excellent tutorial by Jeff Potts. It is geared more towards generic document types, but the basic principles are the same for data lists (where to deploy / how to configure the UI). Specifically, you absolutely have to configure the Share forms for any sort of reasonable display of data lists in the UI - without that, Alfresco will default to the only sane alternative: show every field, even the technical fields you might not care about.

2) In most Alfresco use cases (90%) you typically never query a MySQL database directly. The data list UI component has not been designed to be used ti display data from an arbitrary DB source. This is what typically requires custom UIs. Also, quite a few people consider it an anti-pattern to use Alfresco for handling relational data as it is primarily geared towards semi-structure, content-related information management.

It is technically possible to query databases directly and I have done so in various projects, but this always requires custom development (dependant on the use case).

There are also community addons that allow linking content data to relational metadata, e.g. the Metadata DB Connector addon.

Re: Put results of a mysql query into DataList

soulcollector — Wed, 12 Oct 2016 13:25:35 GMT

The main problem is that without reading the db, or finding a way to import it inside the dataList, i'll have to copy rows one by one hardtyping! Now i'll check that tutorial and be back there when i'll have tried. Temporarily, thank you.

Re: Put results of a mysql query into DataList

jpotts — Wed, 12 Oct 2016 22:37:32 GMT

Thanks for the link to the tutorial, Axel Faust!

Daniele Pani it sounds like you are trying to read data from an external database into Alfresco. What isn't clear is whether or not this is a one-time import. If it is, I'd write a little Java class or Groovy script that reads the data from SQL and then writes it to the data list using CMIS.

If, however, what you are trying to do is enhance Alfresco Share with a UI for managing the data in your SQL database, there are a lot of ways to do that, but one would be to create a new Share page (look for tutorials/docs on Aikau) and then have Share tier web scripts written in Java that are interacting with your external SQL database via JDBC. In this case I would follow Axel's good advice and not copy the data into Alfresco at all--just leave it in its relational database and use Share as more of a window into that data.

A cheaper/lower-tech way to do it would be if you have an existing app that allows you to manage that data you could just iframe it into a Share dashlet.

Re: Put results of a mysql query into DataList

soulcollector — Thu, 13 Oct 2016 08:14:49 GMT

Thank you. It's a one-time import, or i think it would be the best idea considering the nature of this business.

Re: Put results of a mysql query into DataList

soulcollector — Wed, 19 Oct 2016 10:35:46 GMT

Hello, i'm sorry to bother you again. I followed part 1 and 2 of your tutorial; they work as expected in the alfresco launched by command lines. But when i try to install the amps in my alfresco install, it prompts a 404 error.

I got the amps from content-tutorial-repo\target and content-tutorial-share\target, put them in amps folder inside alfresco's install folder and ran apply_amps.bat. I even tried to run mvn install on content-tutorial-share but nothing changed. Where did i make a mistake? Thank you

Re: Put results of a mysql query into DataList

jpotts — Wed, 19 Oct 2016 13:55:32 GMT

Just to be precise, the content-tutorial-repo/target AMP goes in the amps

folder but the content-tutorial-share/target AMP goes in the amps_share

folder.

When you ran apply_amps did you validate that both AMPs were installed

successfully and that the MMT did not output any errors?

Does your $TOMCAT_HOME/logs/catalina.out log show any errors?

On Wed, Oct 19, 2016 at 5:36 AM, soulcollector <community@alfresco.com>

Re: Put results of a mysql query into DataList

soulcollector — Thu, 20 Oct 2016 08:44:50 GMT

Solved. I had to uninstall duplicate amps as well (using the alfresco-mmt.jar) on both alfresco.war and share.war. Indeed, sometimes alfresco services aren't stopping when i try to do it, so when i install/uninstall amps strange things are happening.