Publish error on Select SQL

cancel
Showing results for 
Search instead for 
Did you mean: 
jsimonet
Active Member II

Publish error on Select SQL

Jump to solution

Hello All,

I am having issues with the AAAR_Publish. It is apparently unable to launch the select statement which I could launch using an Pgadmin with same login.

It seems that the login being used by the AAAR_Publish is not correct...for the extract I had to open the transformations using spoon and then set the connection credentials correctly.

For this one I tried to open the transformation but don't knwo where to check. Can someone help me? Is it a connection issue or something else...

Many thanks

1 Solution

Accepted Solutions
jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

This is not the correct way to do it you have to set the connections as follows in "[Your pdi installation]\data-integration\simple-jndi\jdbc.properties". Change user name, password, ports,dns and ip accordingly

SampleData/type=javax.sql.DataSource
SampleData/driver=org.h2.Driver
SampleData/url=jdbc:h2:file:samples/db/sampledb;IFEXISTS=TRUE
SampleData/user=PENTAHO_USER
SampleData/password=PASSWORD
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_DataMart.
AAAR_DataMart/type=javax.sql.DataSource
AAAR_DataMart/driver=org.postgresql.Driver
AAAR_DataMart/url=jdbcSmiley Tongueostgresql://[Your DNS name or IP]:5433/AAAR_DataMart
AAAR_DataMart/user=postgres
AAAR_DataMart/password=admin

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_Kettle.
AAAR_Kettle/type=javax.sql.DataSource
AAAR_Kettle/driver=org.postgresql.Driver
AAAR_Kettle/url=jdbcSmiley Tongueostgresql://[Your DNS name or IP]/AAAR_Kettle
AAAR_Kettle/user=postgres
AAAR_Kettle/password=admin

15 Replies
Moderator
Moderator

Re: Publish error on Select SQL

Jump to solution

Hi Jeremie,

The publish task uses a different login/password and IP for Alfresco
This is because you could want to extract informations from one (or more) Alfresco instances and publish reports on different Alfresco instances (or one only).

Please check at the following link to understand where to setup the connection parameters to the target Alfresco instances.

http://fcorti.com/alfresco-audit-analysis-reporting/aaar-faq/#q3 

jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

Hello Francesco,

The connections to pentaho and alfresco are ok. However the connection to the Postgres sql database is not working correctly it seems:

11:18:56,507 ERROR [PdfExportTask] Failed
org.pentaho.di.core.exception.KettleException:
There was an unexpected error processing report 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.prpt' to produce file 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.pdf' with processor: PDF.
Failed at query: select
a.id as instance_id,
a.desc as instance_name
from
dm_dim_alfresco a
order by
a.desc asc

Is there a place where you specify the Database credentials. Because if I use PGadmin I can connect to database?

Or is it another issue?

Moderator
Moderator

Re: Publish error on Select SQL

Jump to solution

Mmm... this is caused from the report itself, not able to connect to the DB for some reasons.

You can solve editing the report using Pentaho Report Designer and check that the report is using the AAAR_DataMart connection with valid settings.

ATTENTION: The valid settings ARE NOT the Report Designer settings but the Pentaho Data Integration settings. Because you will use the report through the ETL, so in the PDI environment.

Report should use the JNDI connection but you can try replacing the "explicits" connection settings for your environment.

jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

The report seems to be re-created with wrong credentials...no way to change it through report designer. But its confirmed, connection credentials are all wrong. Will try to re-check the connections which I really messed up as far as I can....

jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

Hello Francesco,

I wrongly understood that I should modify the file generated in the Temp folder using Report Designer. But rather I had to modify the report from the repository itself and save it there using the RD. Its working ok now. Up to the point where it is FTPing the file. 

2017/05/17 11:46:33 - Report - Starting entry [Carica file con FTP]
2017/05/17 11:46:33 - Carica file con FTP - Using PDIFTPClient override for exists() fix.
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Connection refused: connect
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : java.net.ConnectException: Connection refused: connect

I am investigating. Surely FTP is not set well...

If you have a clue where to change let me know. thanks.

Jeremie

Moderator
Moderator

Re: Publish error on Select SQL

Jump to solution

Yes, I was thinking we were not on the same page... but happy to hear you solved.

To change settings about FTP check into the dm_dim_reports table.

I'll be easy to understand how to setup the credentials.

You are close to the solution...

jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

Yes its working now. Issue was I defined ftp port as 21 on top of my global alfresco file and port 1121 at the bottom of my alfresco file. First occurrence took precedence. Remove top entry restarted. Process is in progress...ftp OK. Yaye!

Waiting for the process to finish.

jsimonet
Active Member II

Re: Publish error on Select SQL

Jump to solution

Hello,

I had to change each and every report to set the connection. I did something wrong during the installation surely. Also one should not forget to publish the Report once it has been modified. The connection to modify here is the Custom one which is set to Oracle and JNBI by default. I hope this can help other people having the same issue. 

The A.A.A.R. Manual installation by Vicenzo Sorrentino also helped me a lot to debug connection issues.

The Publish process is still in progress. Will let you know when it succeeds...

Moderator
Moderator

Re: Publish error on Select SQL

Jump to solution

Good job!
And thank you for sharing the solution that worked for you.