The general distinction between (stored) procedures and (stored) functions can be summarized like this:
- Are called using JDBC CallableStatement
- Have no return value
- Usually support OUT parameters
- Can be used in SQL statements
- Have a return value
- Usually don’t support OUT parameters
But there are exceptions to these rules:
- DB2, H2, and HSQLDB don’t allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
- H2 only knows functions (without OUT parameters)
- Oracle functions may have OUT parameters
- Oracle knows functions that mustn’t be used in SQL statements for transactional reasons
- Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/freaky, depending on your taste
- The Sybase jconn3 JDBC driver doesn’t handle null values correctly when using the JDBC escape syntax on functions
In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT’s / arrays are involved.
Java Platform, Enterprise Edition is a widely used platform for enterprise server programming in the Java programming language.
This book covers exciting recipes on securing, tuning and extending enterprise applications using a Java EE 6 implementation.The book starts with the essential changes in Java EE 6. Then they will dive into the implementation of some of the new features of the JPA 2.0 specification, and look at implementing auditing for relational data stores.They will then look into how they can enable security for their software system using Java EE built-in features as well as using the well-known Spring Security framework. They will then look at recipes on testing various Java EE technologies including JPA, EJB, JSF, and Web services.Next they will explore various ways to extend a Java EE environment with the use of additional dynamic languages as well as frameworks.At the end of the book, they will cover managing enterprise application deployment and configuration, and recipes that will help you debug problems and enhance the performance of your applications.