Analytic report creation with External DB source is failing

[th_jyothi]

Hi Team,

I'm trying to retrieve records from External DB for which i created a connection with external db details and the connection was successful.

Now i'm trying to create an analytic report by giving above created new connection in External Connection field. 

Even though i'm using a simple select query as shown in the screenshot, it is throwing "

ORA-00933: SQL command not properly ended"

I've tried removing semicolon as well. still the issue persists.

Please assist with it.

[Dhruv_S]

Hi @th_jyothi 

Could you please share the query you are using in analytics. 

Regards,

Dhruv Sharma

[th_jyothi]

i'm using like below:

select networkid from saphr_changelog where action=3;

tried above query multiple ways as below

select networkid from saphr_changelog where action='3';

select sa.networkid from saphr_changelog sa where sa.action=3;

select networkid from saphr_changelog where action=3 (removed semi colon at the end)

None of them works.

[rushikeshvartak]

Share screenshot of config

[th_jyothi]

@rushikeshvartak : which config screenshot do you want me to share? the connection or the analytic report?

[rushikeshvartak]

Report and connection both

[th_jyothi]

@rushikeshvartak Please find the screenshots attached.

[th_jyothi]

@rushikeshvartak  Any update on it please?

[rushikeshvartak]

Does this query runs properly in DB

[th_jyothi]

@rushikeshvartak yes, it runs fine in db

[sk]

@th_jyothi : Did you find any solution? We are also facing similar issue when we use external connection.

Though we are connecting to Oracle DB, in logs we are see it is treating the query as MySQL DB and giving error SQL command not properly ended error. So we assume it an issue with functionality

[Dhruv_S]

Hi @sk , @th_jyothi 

Thanks for reaching out regarding this issue. It has been raised as a bug and being worked on by Engineering team. We will keep you posted.

Regards,

Dhruv Sharma

[sk]

Would you be able to share logs around that timeframe?

Are you getting error while creating analytics? or while running?

Did it give any result on preview?

[th_jyothi]

Hi @sk : I'm getting the error while creating itself. unable to create the anaylytics due to that error.

Yes, i'm able to see the results in preview.

[sk]

@th_jyothi : Can you please share the logs during the issue timeframe?

[th_jyothi]

Hi @sk  Please find the logs generated at the time i tried to create the analytics.

Please note that i've had to replace our client name in the log file. except that the logs are as it is.

[Dhruv_S]

Hi @th_jyothi 

Please try with below query without semicolon.

select networkid from saphr_changelog where action='3'

"2023-11-01T10:21:40.401+00:00","ecm","","","","2023-11-01T10:21:40.209428775Z stdout F 2023-11-01 10:21:40,209 [http-nio-8080-exec-2] DEBUG services.SaviyntGuardRailService - Final query for processing select count(*) as count from (select networkid from saphr_changelog where action=3) as result"
....Error in getResultSet : "
... java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended"

Regards,

Dhruv Sharma

[th_jyothi]

i'm getting the same error even when i try
select networkid from saphr_changelog where action='3'

[Dhruv_S]

Hi @th_jyothi 

Thanks for sharing the logs and observations. The logs shared mentions the SQL command not properly ended error but doesn't give more details. There seems to be some issue at connector level which needs to be checked in detail by our support/engineering teams. If you have raised a support ticket before coming to forums, please update the ticket with the logs and the connection details there and our team will take it for further investigation.

We appreciate your patience and co-operation. Please let us know if you have further queries.

Regards,

Dhruv Sharma

[rushikeshvartak]

This is working as expected for us for oracle as external DB

[th_jyothi]

Hi @rushikeshvartak ,

As per my screenshots, could you please check and let me know if i'm missing anything.

thank you in advance.

[rushikeshvartak]

Please confirm with preview does it work ?

[th_jyothi]

Hi @rushikeshvartak 

Yes, it's working in preview state. I see results similar to the ones in DB.

[Dhruv_S]

Hi @th_jyothi 

Below is the update from Engineering team's analysis on the JIRA opened for this issue.

Analytics creation is not supported for Oracle.
The query prepared to get the count of records is not supported by Oracle.
It is not a bug and may be taken as an enhancement post further analysis/business use case and business criticality.

Regards,
Dhruv Sharma
If the response is helpful, please click close the thread, Accept as Solution and kudos it.

[sk]

@Dhruv_S : what are the supported databases for external connection on analytics creation?

[rushikeshvartak]

Ideally you should avoid pulling data from external connections and show report which can be additional overhead on system