Need a special offer?Find out if your project fits.
+

PostgreSQL Pivot tables

Answered
Elia asked on July 30, 2020

Hi,
I'm brand new to Flexmonster,
Anybody can share a working code for retrieving a PostgreSQL query, and passing it to a felxmonster web app?
I couldn't find any specific example in the documentation, they're all specific to OLAP cases.
Thanks!

14 answers

Public
Milena Pechura Milena Pechura Flexmonster July 30, 2020

Hi, Elia!
 
Thank you for writing to us.
 
Our team would like to recommend connecting to a PostgreSQL database using Flexmonster Data Server.
Please have a look at the tutorial. The 6th step shows the retrieving query sample.
 
If you would like to implement your own server, we kindly suggest using custom data source API – our custom communication protocol.

Please let us know if it works for you and in case any further questions arise.
 
Best regards,
Milena

Public
Elia July 30, 2020

Hi Milena, 
thank you so much for the quick reply.
The Flexmonster Data Server is actually perfect for our requirements, and I can easily make it work on localhost.
But, I don't know how to make it work on a cloud runtime (my app is run on Heroku) 
Any suggestion?
thanks!

Public
Milena Pechura Milena Pechura Flexmonster July 31, 2020

Hello, Elia,
 
We would like to suggest deploying Flexmonster Data Server to the cloud application platforms that support executable files (for example, AWS EC2) or to Heroku by using Docker.
 
Another option is embedding Flexmonster Data Server dll in your app and deploying as one project.
Please note that it is possible if the application is using .NET Core.
 
Please let us know if it works for you.
 
Regards,
Milena

Public
Elia August 4, 2020

HI Milena,
Thank you so much for the support, it's almost solved.
I've succesfully deployed Flexmonster Data Server to an EC2 instance (following this tutorial)
It works great when I call the endpoint from http (HTTP). But, my web app redirects all requests to HTTPS, and that's when the Data Server fails.
I just need to enable HTTPS (please note that my EC2 instance already allows all https requests)
Following this article, I've tried different settings in my flexmonster-config.json file, but none seem to work. For example:

{
    "DataSources": [{
        "Type": "database",
        "DatabaseType": "postgresql",
        "ConnectionString": "xxxx",
        "Indexes": "SELECT * FROM xxxx",
    }],
    "Security":
    {
        "Authorization":
        {
            "Enabled": false
        },
        "CORS":
        {
            "AllowOrigin": "*"
        },
    },
    "DataStorageOptions":
    {
        "DataRefreshTime": "60"
    },
    "Port": "9500",
    "HTTPS": "true"
}

I've tried to move "HTTPS":"true" in and out the security element, type it as "HTTPS":true, HTTPS=true etc, with no result.
In my template, I'm calling my EC2 endpoint as following

var pivot = new Flexmonster({
container: "output",
componentFolder: "{% static 'flexmonster/node_modules/flexmonster/' %}",
toolbar: true,
report: {
dataSource: {
type: "api",
url: "https://ec2-xxxxx.xxxx.amazonaws.com:9500",
index: table_query,
}
},

 

Here's the logs of the failed requests for reference

{'HTTP_HOST': 'xxxx.herokuapp.com', 'HTTP_CONNECTION': 'close', 
'HTTP_UPGRADE_INSECURE_REQUESTS': '1',
'HTTP_USER_AGENT': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36',
'HTTP_ACCEPT': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9',
'HTTP_SEC_FETCH_SITE': 'none', 'HTTP_SEC_FETCH_MODE': 'navigate',
'HTTP_SEC_FETCH_USER': '?1', 'HTTP_SEC_FETCH_DEST': 'document',
'HTTP_ACCEPT_ENCODING': 'gzip, deflate, br',
'HTTP_ACCEPT_LANGUAGE': 'en,it;q=0.9,fr;q=0.8,en-US;q=0.7',
'HTTP_COOKIE': 'csrftoken=xxxx; sessionid=xxxx', 'HTTP_X_REQUEST_ID': 'xxxx',
'HTTP_X_FORWARDED_FOR': '46.6.166.39', 'HTTP_X_FORWARDED_PROTO': 'https', 'HTTP_X_FORWARDED_PORT': '443',
'HTTP_VIA': '1.1 vegur', 'HTTP_CONNECT_TIME': '2', 'HTTP_X_REQUEST_START': '1596540679766', 'HTTP_TOTAL_ROUTE_TIME': '0'}

Any tip on how to solve?

Public
Milena Pechura Milena Pechura Flexmonster August 5, 2020

Hi, Elia!
 
We are glad to hear that our recommendations helped you.
 
Please note that the article you referenced is related to Flexmonster Accelerator, not to the Data Server.
 
Our team would like to suggest browsing the security and authorization guide, which contains the HTTPS connection configuration.
 
Please let us know if it works for you.
 
Kind regards,
Milena

Public
Milena Pechura Milena Pechura Flexmonster August 12, 2020

Hello, Elia,
 
We were wondering if our response helped you with your question. Did you solve the issue with HTTPS? Is the connection successfully configured?
 
Please let us know if it works for you and in case any questions arise.
 
Best regards,
Milena

Public
Elia August 21, 2020

Hi Milena,
sorry for the slow reply, I've been stucked with this for a while.
I'm running the flexmonster endpoint on an EC2 instance
I could successfully enable SSL, but I still I have a certificate error (see screenshots below)
I've downloaded the certificate and saved locally, but I can't retrieve it, as I can't strip "currentuser" or "localmachine" from the certificate path.
Alternatively, I'm trying to autorize the certificate directly in the EC2 options, but I'm blocked there as well.
Have you seen a similar case before? Anyu advice on how to solve?

Public
Elia August 21, 2020

Option 3 would be to run Flexmonster data server directly on Heroku (there won't be any security concern between Heroku app). I'm not really sure how to do it, and there's nothing in the documentation. Do you have examples of how to do that?

Public
Vera Didenko Vera Didenko Flexmonster August 25, 2020

Hello, Elia,
 
Thank you for your response and for providing screenshots of the issue.
 
1. About the certificate problem:
The certificate can be added with either Path-Password Object or Subject-Store Object.
With this in mind, we kindly recommend one of the following solutions:
 
1.1) Placing the downloaded certificate in the store location and setting "Store": "My":
In your HTTPS configuration, we have noticed that the Subject-Store Object approach is used. 
Please kindly note that in this case, the certificate needs to be placed in the certificate store (the location where certificates are stored on your machine).
For Windows, the Store should be referred to as "My" instead of specifying a path, for example:

"HTTPS": {
"Enabled": true,
    "Certificate": {
"Subject": "localhost",
"Store": "My",
"Location": "LocalMachine",
"AllowInvalid": true
  }
}

 
1.2)  Using the Path-Password Object approach:
An alternative solution is to use the Path-Password Object. Here you can simply specify the path to your certificate and the password (the password can be set as blank if not required), for example:

"HTTPS": {
"Enabled": true,
"Certificate": {
"Path":"path_to_certificate/yourCertificate.pfx",
"Password":""
}
},

 
Please let us know if one of these approaches works for you.
 
2. As for running Flexmonster Data Server directly on Heroku:
Aside from being available as an EXE, the Flexmonster Data Server is also provided as a DLL. You can create a simple .NET Core API and include the Flexmonster Data Server DLL in your project. Then you could deploy your .NET Core API to Heroku using Docker.
 
For more details about the Flexmonster Data Server DLL please see the following guide.
This guide also contains a sample .NET Core project with the Flexmonster Data Server embedded.
Also, we kindly suggest checking out the following tutorial on how to deploy your .NET Core API to Heroku using Docker.
 
 
 
Please let us know if this helps. If any questions arise, please don't hesitate to reach out.
Looking forward to your reply.
 
Kind regards, 
Vera

Public
Vera Didenko Vera Didenko Flexmonster August 31, 2020

Hello, Elia, 
 
How have you been? 
 
Our team would like to kindly take an interest in whether you found our previous response helpful. 
Did it work to authorize the certificate and connect to Flexmonster?
 
Looking forward to your feedback.
 
Kind regards, 
Vera

Public
Elia August 31, 2020

Hi Vera,
thank you so much for your detailed reply, and really sorry for the slow response.
Unfortunately I had to put this on my backlog, as other more urgent priorities arose. I hope to be able to look into it this week, and get back to you.
Sorry again for the inconvenience

Public
Vera Didenko Vera Didenko Flexmonster August 31, 2020

Hello, Elia, 
 
Thank you for the update. 
 
If you run into any difficulties, please feel free to reach out.
We would be happy to hear if everything worked.
 
Kind regards,
Vera 

Public
Vera Didenko Vera Didenko Flexmonster October 16, 2020

Hello, Elia, 
 
Hope you're doing well.
 
Just checking in to ask if you've managed to resolve the initial issue.
Did it work to connect to Flexmonster?
Please let us know if there is still anything we can assist you with here.
 
Looking forward to your feedback. 
 
Kind regards, 
Vera

Public
Elia October 20, 2020

Hi Vera, thank you for following up on this.
In the end, more burning priorities came up, and we didn't have the capacity to move forward with the flexmonster project.
It's in our backlog, and we'll try to resume it as soon as we'll have more bandwitdh
Bests,

Please login or Register to Submit Answer