Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/26/2023 04:59 AM
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 "
I've tried removing semicolon as well. still the issue persists.
Please assist with it.
10/26/2023 06:49 AM
10/27/2023 03:15 AM
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.
10/27/2023 07:29 AM
Share screenshot of config
10/27/2023 07:32 AM
@rushikeshvartak : which config screenshot do you want me to share? the connection or the analytic report?
10/29/2023 07:04 PM
Report and connection both
10/29/2023 11:07 PM
11/01/2023 03:33 AM
@rushikeshvartak Any update on it please?
11/03/2023 08:48 PM
Does this query runs properly in DB
11/06/2023 01:30 AM
@rushikeshvartak yes, it runs fine in db
11/29/2023 06:10 AM
@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
11/29/2023 06:17 AM
Hi @Saathvik , @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
10/27/2023 11:50 AM
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?
10/29/2023 11:08 PM
Hi @Saathvik : 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.
10/30/2023 10:47 AM
@th_jyothi : Can you please share the logs during the issue timeframe?
11/01/2023 03:33 AM
Hi @Saathvik 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.
11/01/2023 10:54 PM
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
11/03/2023 12:31 AM
i'm getting the same error even when i try
select networkid from saphr_changelog where action='3'
11/05/2023 08:26 PM
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
11/29/2023 07:56 PM
This is working as expected for us for oracle as external DB
12/04/2023 09:23 AM
Hi @rushikeshvartak ,
As per my screenshots, could you please check and let me know if i'm missing anything.
thank you in advance.
12/04/2023 05:47 PM
Please confirm with preview does it work ?
12/04/2023 11:13 PM
Yes, it's working in preview state. I see results similar to the ones in DB.
01/30/2024 05:32 AM
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.
01/30/2024 02:26 PM
01/30/2024 06:21 AM
Ideally you should avoid pulling data from external connections and show report which can be additional overhead on system