Retrieve (in database) vs Read Database generates error message?

DocMusher

Dear RM friends,
In exactly the same setting, when using Retrieve (in database), I get an error message which I do not get when using the normal Read Database operator. What could be the reason?
Thanks
Sven

MartinLiebig

Hi @DocMusher,
can you check what the log says? Thats usually more telling.

Cheers,
Martin

BalazsBarany

Hi @DocMusher,

also the error message location is misleading. The In-Database Processing extension generates a SQL statement and executes it at once. So it can't know what operator caused the error. Therefore, the problem could be with any of the operator settings (or a bug in the extension).

Regards,
Balázs

DocMusher

This is the log file I get: 

Mar 4, 2020 11:17:51 AM INFO: No filename given for result file, using stdout for logging results!

Mar 4, 2020 11:17:51 AM INFO: Process //Local Repository/processeslocal/Chipsoft/Saturationindatabase starts

Mar 4, 2020 11:17:51 AM WARNING: Incorrect syntax near '0'.

Mar 4, 2020 11:17:51 AM SEVERE: Process failed: Submitted command failed: Incorrect syntax near '0'.

Mar 4, 2020 11:17:51 AM SEVERE: Here:

Mar 4, 2020 11:17:51 AM SEVERE:           Process[1] (Process)

Mar 4, 2020 11:17:51 AM SEVERE:            subprocess 'Main Process'

Mar 4, 2020 11:17:51 AM SEVERE:              +- In Database Nest[1] (In Database Nest)

Mar 4, 2020 11:17:51 AM SEVERE:            subprocess 'SQL Steps'

Mar 4, 2020 11:17:51 AM SEVERE:        ==>         +- Retrieve (In Database)[1] (Retrieve (In Database))

with the connection working perfectly.
Cheers
Sven

BalazsBarany

Hi Sven,

which version of In-Database Processing are you using? Mine always logs the issued SQL statement. That's what we're interested in (you could redact it if it has sensitive table or column names, but we should see the structure). 

Regards,
Balázs

DocMusher

Hi Balazs, running 9.6. There is no issued SQL statement logged. How to continue?
Thanks
Sven

BalazsBarany

Hi Sven,

I was asking for the In-Database Processing extension version. I guess that could be different. I have 9.5 and there is no newer version in the Marketplace.
Did you change the process parameter "logverbosity"? Can you look into your database logs if you don't get the SQL from RapidMiner?

I don't have access to the source code of the extension, so I can only make semi-educated guesses on how it works.

Regards,
Balázs

DocMusher

Indeed I noticed that 9.5 was mentioned in the xml and I stupidly changed it to 9.6 followed by confirming the green V which is not the right approach. I can take a look at the database logs asap but today I am fully booked. Thanks for now and I come back on this subject soon. 
Sven

DocMusher

Could this access issue for in database nest operations be related with the fact that my applicationIntent is readonly?

BalazsBarany

Hi Sven,

I don't know but getting the attempted SQL statement is really the best way for us to help you. In-Database Processing is probably generating a wrong SQL statement (or a correct one that's not understood by your database), and you would help future users by telling us the details so the developers can fix it. 

As I wrote, the processes I've tried are putting the SQL into the process log. Please try creating a minimal example process and look if that's the case. Then work step by step on reproducing the problem. Or alternatively get the statement and/or the error message from the database server.

Regards,
Balázs

DocMusher

Hi Balazs
No sql is detected in the process log. 
I only get Mar 6, 2020 12:48:23 PM WARNING: Incorrect syntax near '0'.

Mar 6, 2020 12:48:23 PM SEVERE: Process failed: Submitted command failed: Incorrect syntax near '0'.

Mar 6, 2020 12:48:23 PM SEVERE: Here:

Mar 6, 2020 12:48:23 PM SEVERE:           Process[1] (Process)

Mar 6, 2020 12:48:23 PM SEVERE:            subprocess 'Main Process'

Mar 6, 2020 12:48:23 PM SEVERE:              +- In Database Nest[1] (In Database Nest)

Mar 6, 2020 12:48:23 PM SEVERE:            subprocess 'SQL Steps'

Mar 6, 2020 12:48:23 PM SEVERE:        ==>         +- Retrieve (In Database)[1] (Retrieve (In Database))

As far as I was able to look for error messages at server level, you need to have sysadmin roles which I do not have.
To be continued. 
Thanks anyway!!!!!!