Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

problem with query syntax in flexmonster-config.json

Answered
Andrey asked on August 5, 2020

Hi !
When tried to use Flexmonster Data Server to connect to Oracle DB I came across an error with starting my server.
The query that I use in flexmonster-config.json looks like this:

"DataSources": [{
"Type": "database",
"DatabaseType": "oracle",
"ConnectionString": "Data Source=ORCL;User Id=user;Password=mypassword;",
"Indexes": {
"index_database": {
"Query": "Select WP_MDP, WP_SLS, (WP_SLS*WP_MDP)/NULLIF((1-WP_MDP),0) WP_MD from (select * from F_Sls_Op_Plus_Wp_Small where rownum<10000)"
}
}
}],

When starting a server from console I get the error message:

ERROR|Flexmonster.DataServer.Core.PrepopulatingCacheService|Cannot load data from database executing query "Select WP_MDP, WP_SLS, (WP_SLS*WP_MDP)/NULLIF((1-WP_MDP),0) WP_MD from (select * from F_Sls_Op_Plus_Wp_Small where rownum<10000)".
Please check query. Details: Specified cast is not valid.
2020-08-06 00:26:13.0609|FATAL|Microsoft.AspNetCore.Hosting.Diagnostics|Application startup exception
2020-08-06 00:26:13.0858|FATAL|Flexmonster.DataServer.Program|Data prepopulating failed

The query is actually valid because it works in Oracle.
After some tests I found that the problem is caused by the division sign “/” in the third column of my query.

(WP_SLS*WP_MDP)/NULLIF((1-WP_MDP),0) WP_MD

If I replace “/” with “*”,”+” or “-“, everything’s fine.
I also tried to escape with “\” :

(WP_SLS*WP_MDP)\/NULLIF((1-WP_MDP),0) 

but with no success.

The fmlog file doesn’t provide enough info to debug, so could you please advice how to tackle this ?
Thanks in advance,
Andrey.

2 answers

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster August 10, 2020

Hi Andrey,
 
Thank you for posting your question.
 
We’ve done some research on this and ran into the following Stack Overflow thread: https://stackoverflow.com/questions/23935716/specified-cast-is-not-valid-when-populating-datatable-from-oracledataadapter-f
 
It seems like the issue does not lie in Flexmonster – instead, it’s about how Oracle DB & C# treat decimal places in number types differently.
 
We hope this helps.
 
Best regards,
Mykhailo

Public
Andrey August 13, 2020

Yes, it works. Thanks for your answer.

Please login or Register to Submit Answer