db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-2927) Procedures can't return values other than ResultSets
Date Fri, 12 Oct 2012 16:51:03 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2927?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13475149#comment-13475149

Dag H. Wanvik commented on DERBY-2927:

This behavior is not per SQL 2003, cf. this quote from section 4.27.1 "SQL-invoked routines":

"An SQL-invoked procedure is an SQL-invoked routine that is invoked from an SQL <call statement>.
An SQL-invoked procedure may have input SQL parameters, output SQL parameters, and SQL parameters
that are both input SQL parameters and output SQL parameters. The format of an SQL-invoked
procedure is specified by <SQL-invoked procedure> (see Subclause 11.50, “<SQL-invoked
routine>”). ... "


"An SQL-invoked function is an SQL-invoked routine whose invocation returns a value. Every
parameter of an SQL-invoked function is an input SQL parameter, one of which may be designated
as the result SQL parameter. ..."

>From this it seems clear that a) function can't take OUT or INOUT parameters, and b) procedures
can't return values except through OUT or INOUT parameters.

> Procedures can't return values other than ResultSets
> ----------------------------------------------------
>                 Key: DERBY-2927
>                 URL: https://issues.apache.org/jira/browse/DERBY-2927
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>         Environment: Java 1.4.2
> Windows XP SP 2
>            Reporter: Jacques Coetzee
>              Labels: derby_triage10_10
> First off, this is not really a bug, it's more of a restriction.
> As per the needs of my inhouse framework I need a procedure to be able to return a value
other than
> a ResultSet (I need a integer). The reason I need to use a procedure; is because I'm
using INOUT parameters.
> When call my procedure, it needs to look like this:
> { ? = call myProcedure(?,?,?,?,?,?)}
> Where the first ? represents a status-code, ie a failure = -1, and a successfull would
be 1.
> All the other procedure paramters are INOUT.
> If I where to use normal scripting methods in any other DB, for instance DB2 or Oracle
> then I would be able to do this. It seems that the problem has to do with using
> JAVA "Stored" Procedures.
> Is it possible to either make procedures return primitive types, OR
> for Functions to be able to use OUT and INOUT paramters?
> I've got High Hopes for Derby, but it's issues like this that might just
> sway me to use a different DB.
> I appreciate any feedback you can give me.

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

View raw message