This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Java Stored Procedure in SQL Server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java Stored Procedure in SQL Server" Watch "Java Stored Procedure in SQL Server" New topic
Author

Java Stored Procedure in SQL Server

Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
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

Joined: Oct 15, 2004
Posts: 8
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

Joined: Feb 13, 2004
Posts: 244
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

Joined: Feb 13, 2004
Posts: 244
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

Joined: Oct 27, 2004
Posts: 35
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.


nilesh<br />neilindallas@hotmail.com
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Java Stored Procedure in SQL Server