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.
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
Yes, it works. Thanks for your answer.