April 23, 2021 at 5:35 pm
I have a requirement to make a call to a Java class from stored procedure. How do I get the return value and pass it to a local variable inside the stored procedure? -Vic
April 23, 2021 at 5:44 pm
If possible, I would avoid doing this.
April 23, 2021 at 5:49 pm
Thanks Ken. What's the reason of avoiding? I have to explain to my team 😉
April 23, 2021 at 8:08 pm
I take the view that, where possible, it is best to keep .Net, Java etc in the middle tier and leave the database server to do what it is best at.
April 23, 2021 at 8:33 pm
I second Ken's approach. I like having my application logic at the application layer and my database logic in the database.
I would much rather have 2 stored procedures (pre java calls and post java calls) and have the java "magic" happen outside the database. That way I don't need to have java installed on my SQL Servers and I don't need to keep track of java versions on my database servers. Plus that gives more resources I can dedicate to SQL. If memory serves, the external script stuff in SQL operates outside of the native SQL resources (CPU and memory), so I need to make sure that the server has enough overhead room for the Java stuff. Same thing with SSRS and SSIS. I prefer to have all of the memory (well, MOST as the OS needs some memory) on my server allocated to SQL and not need extra room for non-SQL processes that may overflow the amount I set aside and page to disk which is a whole different beast to tackle.
Just my 2 cents; YMMV.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2021 at 8:43 pm
Thanks Gents! Your suggestions are making a lot of sense and I am on the same page.
I am migrating Oracle to Mssql and there is this requirement. The call route actually starts from a Job agent scheduled -> Calls a stored procedure (caller) -> calls Java to create zip file -> returns file size value to (caller) then carry on and assign the return value to a local variable.
I suggested an option to have a stand-alone app to do the job (Timer and TimerTask). Other option is sp caller -> calls Java -> Java will persist file size on a table. Caller sp then queries from table and carry on with the steps but there might be timing issue.
April 23, 2021 at 9:53 pm
Another option (others may shoot this down as a bad idea) would be to turn on filestream access on the folder and have the SQL side mostly pulled out of the equation. If I remember right (been a while since I used filestream), filestream should allow you to query the filesystem to get the file size along with other metadata about the file.
So you could have "something" call the java and SQL would automatically "know" the file size and that the file exists.
And (yet) another option would be to have a 3rd party application (custom developed likely, but maybe off the shelf/) that could be run as a windows task or a service that would make a call to the stored procedure, take the results and make a call to Java, take those results and call a second stored procedure. This approach (replacing windows task with SQL Agent Job) may be able to be handled by an SSIS package.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2021 at 10:09 pm
Haha, funny I am thinking of SSIS as we speak.
April 26, 2021 at 9:48 pm
I did apply your suggestion. Sql server calls the java class but instead of return a value it will just handle the file on local folder. But when java is doing Read/Write to the file, it gets permission issue - Access is denied. I have the folders/file complete control from any user.
On the other hand, if java runs as stand-alone, all goes well. Any clues?
June 25, 2022 at 5:08 am
This was removed by the editor as SPAM
January 12, 2024 at 1:32 pm
This was removed by the editor as SPAM
March 19, 2024 at 4:55 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply