Should we discard stored procedures and start database calls from java programs

Question

I struggle to use stored procedures in our company. There are a few people who say that they are bad and we should not use them. We use DB2 in the i-series.

Please help me with the argument of storing stored procedures in my company.

Answer 1

OK I will favor stored procedures.

At first, if you use them exclusively, they greatly simplify database refactoring, since you can use the dependencies stored in the database to find out what the change will affect (well, in any case, SQL Server cannot talk about other datbases) .

Secondly, if you just need to change the query, they are much easier to deploy.

They are also easier to configure, as they can be easily called without launching the application.

If you have complex logic, you maintain some performance by not sending it all over the network to the database server. It may not seem like a big win, but if a complex query is run thousands of times a day, it can add up.

Safety is also extremely important. If you do not use storage procedures, you must set permissions at the table or view level. This opens up a database for internal fraud. Yes, parameterized queries reduce the risk of sql injection, but this is not the only threat that you need to protect. If you have personal or financial data, and you do not use stored procs (and those that do not have dynamic SQl), and restrict your users to be able to do something only through proc, your system is in extreme danger from internal employees that can steal data or bypass internal controls to steal money. Check out the internal controls in accounting standards to see why this is a problem.

ORMs also tend to write only bad SQL code, especially if the query is complex. In addition, when people start using them instead of stored processes, I find that people who have never used stored processes have a poor understanding of how to get data from a database and often get invalid data. Using ORM is okay if you already understand SQL and can determine when to rewrite auto-generated code into something that works best. But too many users do not have the skills to write complex code, because they never learned the basics.

Finally, since you already saved the procs for your application, getting rid of them altogether is a way to introduce new bugs because you need to generate new code.

Answer 2

You won’t like it, and I’m probably going to be forgotten, but I’m staying with your company.

Stored procedures used to provide many benefits (security, performance, etc.), but with parameterized queries and better query optimization, stored procedures really just add another level of overhead to your application and provide you with another place that you need update / change code.

I prefer to keep everything in one place, so when I need to edit the code, I can go to one place and make my changes there.

If you want to learn more about arguments for migrating from stored Prcoedures, check out this CodingHorror article:

The coding horror: who needs stored procedures?

... and I just noticed that the article is from 2004. I must believe that since then the databases have become better, which means that today it will be even more true than then.

Answer 3

Doing all the work on JDBC essentially means that you are inserting a network layer between you and the database. In general, this means that the data is more "remote" and comes to you more slowly. Stored procedures can work directly with data inside the database, and the resulting speed difference may surprise you.

Please note that you can write stored procedures in any IBM i language, including Java, in case this is a matter of programming skills. In addition, you have access to the FULL machine, and not to some internal databases. Here, AS / 400 is so different from any other database product that the experience of other databases is simply, in my opinion, not applicable.

I would recommend the Midrange mailing lists as they have the highest concentration of AS / 400 programming skills that I know of.

Answer 4

This is one of those problems Marmita. if you are primarily a database programmer, you will think that stored procedures should be used widely. If you are an application programmer - say, Java or an .Net encoder - you will most likely say that they should be completely avoided.

Not that this is consistent with application programmers who want to write their own SQL queries. No, these days they tend to want to abstract away everything related to ORM's confusing services. This is not easy to understand than stored procedures, but are available in the same IDE, so they require less context switching.

There are two big things in favor of stored procedures. Firstly, people who know PL / SQL are likely to be familiar with Oracle databases (T-SQL and SQL Server, etc.), and therefore will seek to write better programs for this database (defined as programs who take advantage of the platform features and are adapted to its functionality) than people who do not.

Secondly, the data is saved. Application developers like to talk about "database independence," but application independence is really important. Front panels come and go, but the data model lasts forever. Over the past ten years, Java applications have been written as applets, servlets, JSP, Tiles and Faces, with add-ons in JavaScript, Groovy, AJAX and JSON, connecting to the database using manual JDBC, EJB (v1, 2,3), TopLink, Hibernate and IBatis ... forgive me if I missed a few. Applications whose interface is a shell on top of the stored procedure level are easier to upgrade to the latest and greatest applications, where business logic needs to be rewritten every time. And they will also work better.

Ultimately, however, applications that interact directly with the database are likely to leave. Everything will talk to the service bus, and it will decide where to get the data. Of course, stores in which the database is viewed through a well-designed API of stored procedures can easily go into this bold new world than the places that all of their ORM logic will need to extract.

Answer 5

They are useful when you have a multi-level set of applications. For example, one basic database with web services that offers atomic operations (which are stored procedures) and an ESB or a set of applications that consume these WSs. In the case of a single application / solo case, the idea is to save the code in one place, like the others.

But it's good that only me.

Answer 6

I am a longtime Java developer who recently came across several projects that heavily used stored procedures that put the use of stored procedures in a really bad light for me.

Having said that, I am reluctant to make the statement that stored procedures are bad as a variant of the system design, because in reality it depends on a specific project and what certain stored procedures are trying to execute.

My preference is to avoid any kind of stored procedure for simple CRUD operations (it might seem ridiculous for some to have stored procedures handle these operations, but I came across several systems that did this). The result is a lot of code that needs to be written (and tested and supported) on the Java side in order to manage these procedure calls from what I observed. It's better to just use Hibernate (or some other ORM library) to handle these kinds of operations ... if not for any other reason, but tend to reduce the amount of code that needs to be supported. It can also lead to problems when trying to reorganize or make any significant changes to the system, since you not only need to deal with class / table changes, but also stored procedures that also handle CRUD operations. And this can be even more aggravated if you are in a situation where developers cannot make changes to the database on their own or there is some kind of formal process for coordinating changes between the two parts of the system.

On the other hand, having stored procedures that require limited interaction with Java code (basically, you just start a call to one with a few arguments) and are executed in semi-autonomous mode, this is not terrible, I came across several situations (especially where we transferred or imported data to the system), where using a stored procedure was much better than writing a bunch of Java code to handle functionality.

I assume that the real answer here would be that you should study that each storage procedure in the system is currently underway and evaluate them on a case-by-case basis to determine if it is possible to handle the operation in Java or the databases data. Some may work very well in Java (either with the ORM library or actual handwritten code), some may not work. In any case, the goal should always be to make the system understandable and easy to maintain for everyone, and not just whether the stored procedures are good or bad in themselves.