That's probably it - thank you, but what is the point of permanently storing the stream on disk when the %Stream.FileBinary object is no longer in use since the code has finished executing.
I figured it out, however I am not sure why this is the case:
Firstly the permissions were in order, and I did not change anything since the full permission to the entire table was granted.
When I added a WHERE clause with tbl.SomeBoolean = ?, it would prompt the -99 SQLCODE error, I then attempted to use tbl.SomeBoolean LIKE (?) which worked, I then tried tbl.SomeBoolean = (?) which ALSO worked.
My conclusion: when filtering by boolean (bit) columns using dynamic queries (%SQL.Statement) parentesis must be surrounded by the value, I don't know why this is and it seems strange to only be needed when dealing with bit columns.
https://docs.intersystems.com/irisforhealthlatest/csp/documatic/%25CSP.D...
Am I missing something? I am saving the data inside response.Data to the fileDestinationPath, which works but it also saves to the database stream folder and never gets purged.
I understand I have to use TmpBinary instead but what is the point of keeping a copy in the stream folder when the code has finished executing? the files are also not named in a way that makes them recognizable other than their extension and file size.
Basically the file is saved to both the location I set at fileDestinationPath and the stream folder where the database file resides.