Is there any alternative to stored procedures, secure and fast as well as stored procs. i know only Hibernate. Is there any other technologies like that?
+3
A:
You can do dynamic SQL as secure and fast as stored procedures can be, it just takes some work. Of course, it takes some work to make stored procedures secure and fast also.
Lance Roberts
2008-10-06 17:32:19
Guess there's some stored procs fanboys out there.
Lance Roberts
2008-10-06 17:36:33
No kidding. Lance, I think your answer might depend a lot on the DB engine you are talking about.
Flory
2008-10-06 17:40:53
Yes, I agree. I tried to keep any detail out of the answer, so as not to start some kind of flamewar.
Lance Roberts
2008-10-06 17:43:21
Dynamic sql is not as secure as a properly written stored proc. You can limit the direct access to tables by using stored procs (as long as they in turn do not use dynamic sql) which helps you protect your database from internal users. (The ones who most frequently steal data or commit fraud.)
HLGEM
2008-10-06 19:15:21
@HLGEM: I'd have to see an example of this to believe it. After all, I can grant select, insert, update and delete as individual privileges to individual users. Not sure how much more secure things can be.
S.Lott
2008-10-06 20:25:36
@S.Lott: your stored procedure can implement a rule that means a particular user can only update a subset of rows, or can only delete rows that are in status "EXPIRED" or whatever. That's why they are tighter for security than granting DELETE on the table.
WW
2009-08-13 03:40:50
I've never worked with any of these before. I am actually now getting into this. How many of these libraries are out there. I am only familiar with SubSonic. Are these considered ORM?
Saif Khan
2008-10-06 17:52:26
+4
A:
Hibernate is an object/relational persistence service.
Stored procedure is a subroutine inside a relational database system.
Not the same thing.
If you want alternative to Hibernate, you can check for iBatis for Spring
Daok
2008-10-06 17:33:35
+5
A:
Yes. you can use dynamic sql, but I personally like stored procedures better.
1) If you're using MS SQL Server, it will generate a query plan which should enable the stored procedure to execute faster than simple dynamic sql.
2) It can be easier an more effective to fix a bug in a stored procedure, expecially if your application calls that procedure in several spots.
3) I find it's nice to encapsulate database logic in the database rather than in embedded sql or application config file.
4) Creating stored procedure into the database will allow sql server to do some syntax, and validation checks at design time.
Jeremy
2008-10-06 17:36:35
Dynamic SQL can also get query plans, it's a myth (true at one time in the past), that only stored procs get the query plans.
Lance Roberts
2008-10-06 17:38:00
Dynamic SQL gets a query plan, but if your dynamic sql changes at all due to parameter values, the query plan will have to be rebuilt, which would be a performance hit.
Jeremy
2008-10-06 18:26:20
Yes, you're right, just like using parameter values can affect stored procs. I liked stored procs also, but for my usual needs I just wish they were more dynamic!
Lance Roberts
2008-10-06 22:40:44
Databases don't have logic - applications do. Coding without SQL or using a DB neutral approach such as NHibernate Criteria or HQL and keeping that logic in the DAO classes makes sense from a OO coders perspective. This and Lance's observations would justify a down vote.
Simon Gibbs
2008-10-09 10:40:56
Putting essential business logic in the application makes no sense at all from a database specialist's perspective. Databases tend to be accessed from multiple sources. Essential rules must be at the database level or you are going to have data integrity problems.
HLGEM
2009-09-01 17:25:10
+1
A:
If you are working in a .Net envorionment check out SubSonic.
Simple to use and extremely powerful.
+1
A:
@Jeremy I dont know what idiot voted you down. +1 from me
@Poster. Parameterized SQL (it can be Dynamic but not necessarily) will execute with the same safety and efficiency as stored Procs.
StingyJack
2008-10-06 17:54:50
I think Lance didn't understand my comment on stored procedure query plans.
Jeremy
2008-10-06 18:28:09
Parameterized sql might execute with the same safety as stored procs, but unly in terms of mitigating a sql injection attack. You have to give the user direct access to the table for the sql to work, so in effect you're giving the application/user the ability to run any sql against the table.
Jeremy
2008-10-06 20:13:23
With a stored procedure, you give the user/application access to only that procedure, so no other sql can be run against the table other than that stored proc, which is far safer.
Jeremy
2008-10-06 20:14:12
I didn't vote you down (Jeremy), just thought your (1) point needed clarification.
Lance Roberts
2008-10-06 22:42:41
+6
A:
Stored procedures are a place to put code (SQL) which executes on the database, so I understand the question to mean
"is there any other way to package up the code which runs on the database?"
There are several answers:
- There is nothing else that is quite the same as a stored procedure, but there are alternatives which you might consider.
- You could write all your SQL as strings inside your client code (java or whatever)
- This has various problems (loss of encapsulation, tight coupling -> harder maintenance), however, and is not a good idea.
- You could use an ORM such as NHibernate, which inserts a layer between your client logic and the database. The ORM generates SQL to execute on the database. With an ORM, it is harder to express complex business logic than in a stored procedure (sweeping generalisation!).
- A kind of halfway house is to define your own data access layer (DAL) in java (or watever you're using) and keep it separate from the main body of client code (separate classes / namespaces / etc.), so that your client makes calls to the DAL, and the DAL interprets these and sends SQL to the database, returning the results from the database back to the client.
AJ
2008-10-07 09:50:41
A:
Hmm, seems to me that the obvious alternative to stored procedures is to write application code. Instead of, say, writing a store procedure to post a debit every time a credit is posted, you could write application code that writes both.
Maybe I'm being too simplistic here or missing the point of the question.
Jay
2009-08-14 17:01:46
+3
A:
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary.
Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement
..from Wikipedia
I think you need to read this article and reframe your question. Hibernate has nothing to do with stored procs.
pinku
2009-09-18 15:36:46
+1
A:
It'd help a little more if you said why you are looking for alternatives, what about stored procs do you not like?
Some databases (eg. PostgreSQL) also allow you to write stored procedures in different languages. So if you really want to you can write them in Python or Java or the like, instead of SQL.
Colin Coghill
2009-09-21 00:29:57