java stored procedure vs sql stored procedure

Question

now java stored procedures are written in java as the name suggests, therefore they exists on client side. SQL stored procedures exist on the server. I know of advantages in having sql stored procedures:- One that i can think of is reduced network overheads because you only have to send the parameters instead of the whole sql statement.

But i cannot think of any use of having java stored procedures over sql stored procedure . Could someone please help me on this ?

What is a Java stored procedure? Can you show the code? As far as I know, then name stored procedure is specific to queries stored on the Database. — Oded
– Oded, Commented Jul 12, 2012 at 18:07
Oracle allows for stored procedures written in Java. They are executed in a JVM inside Oracle itself. — Thorbjørn Ravn Andersen
– Thorbjørn Ravn Andersen, Commented Jul 12, 2012 at 18:09
And are you sure your Java stored procedures are 'client side'? Most RDBMSs allow SPs to be defined in a number of different languages. Additionally, allowing any actual client-side code to directly touch your database (as would be required for a SP) is a HUGE security problem. — Clockwork-Muse
– Clockwork-Muse, Commented Jul 12, 2012 at 18:09
@ThorbjørnRavnAndersen - I see. Like SQL/CLR with SQL Server. — Oded
– Oded, Commented Jul 12, 2012 at 18:10

Peter Lawrey · Accepted Answer · 2012-07-12 18:11:20Z

1

One big advantage of using Java code only is that its easier to release and version. If you use stored procedures its not as easy to upgrade and roll back versions or run multiple versions concurrently.

answered Jul 12, 2012 at 18:11

Peter Lawrey

535k83 gold badges770 silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Peter Lawrey Over a year ago

It depends on how much you can cache the data. The more you can cache, the better it is to use Java, the less you can cache the better it is to keep the data in the database. Note: you can send SQL to the database without using any stored procedures.

klijo Over a year ago

In java stored procedures, shouldn't the SQL statement be sent to the database?

Peter Lawrey Over a year ago

If the data is cached in Java, the code can execute entirely in Java.

klijo Over a year ago

Yeah but for new ones it needs to send the sql query to the server. Apart from this drawback are there anymore ?

Thorbjørn Ravn Andersen Over a year ago

The code for the stored procedures and the scripts to install it, can and should be versioned. Essentially you should be able to start from zero with what's in your version control system.

|

Dave Newton · Accepted Answer · 2012-07-12 18:09:44Z

0

It's written in Java, which some people prefer to SQL. It's just another way of consolidating the languages used in a project.

answered Jul 12, 2012 at 18:09

Dave Newton

161k27 gold badges263 silver badges310 bronze badges

Collectives™ on Stack Overflow

java stored procedure vs sql stored procedure

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related