Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java Stored Procedure in SQL Server

 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can I write a Java Stored Procedure in SQL Server?

It supports and works on databases like Oracle, DB2, FirstSQL.

Rgds,

Seetesh
 
Imran Mirza
Greenhorn
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
no you can not write Java Stored Proceedure for SQL Server. The reason is SQL Server does not support Java as Oracle did.

Regards, Imran Mirza
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

Environment - Application is getting developed in JAVA and backend database is Oracle 9i.

Query - For doing the Batch processing in the mentioned environment two options are available, namely 'Java Stored Procedures' OR 'Oracle Stored Procedures'. From Maintenance and Performance point which option should be opted for and Why. We need the some technical write-up to support the same.

Rgds,

Seetesh
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

Can i deploy the same Java Stored Procedure across various databases or there is some majore change required?

Rgds,

Seetesh
 
nilesh Katakkar
Ranch Hand
Posts: 35
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How is your application going to pass data to the backend for batch processing ?

I personally prefer PL/SQL in Oracle over java stored procs. PL/SQL unlike Java is tightly integrated with SQL and was developed for that sole purpose. If DB independence is not a requirement... you will benefit from SP's. Unless you are doing one insert / update from java client at a time you won't see any benefit over SPs. All SQL statements fired from Java are dynamic by nature. PL/SQL can take advantage of explicit cursors where appropriate. Plus if you are planning to invoke your procedure from Client (middleTier.. lets say using execute batch) then calling a Oracle Stored Proc will be advantegeous.. as there wont be context switch from PL/SQL to Java for every statement you execute. From maintenance point of view - if DBA's are going to maintain it then , I think they will like simple PL/SQL. You dont need to build SQL statememnts in Strings dynamically. SQL in JDBC code isn't really readable compared to PL/SQL.

Though one cannot generalize any rules -
1. Use PL/SQL for SQL intensive stuff
2. Use java where PL/SQL isnt the natural choice - Sending Email, FTP, getting access to OS related things.
3. If you cant do it in Java then use C external routine.

Thats what we follow in our projects.

With 9iR2 you can also natively compile your SP in oracle if you are doing computive intensive tasks.
 
Seetesh Hindlekar
Ranch Hand
Posts: 244
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Nilesh,

could u ellaborate on

3. If you cant do it in Java then use C external routine

Or any other alternatives

Rgds,

Seetesh
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic