db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Coetzee (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2927) Procedures can't return values other than ResultSets
Date Fri, 13 Jul 2007 08:21:04 GMT

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

Jacques Coetzee commented on DERBY-2927:
----------------------------------------

Thanks for the quick reply.

Unfortuantely I can't send you a full code segment (non-disclosure), but
I'll send you a small example:

This is what I want to do
callableStatement = connection.prepareCall({?=call getProc(?,?,?,?,?,?)});
Note that "myProc" is a procedure not a function

This is a snippet
callableStatement.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
cstmt.setString(2, "someValue");
...
callableStatement.execute();
...
int returnCode = callableStatement.getInt(1);
It is this returnCode that throws the exception
System.out.println(cstmt.getString(2));
...
end

Now remember that I said "myProc" is a procedure with all the parameters
being of type INOUT and it does NOT return a ResultSet.
Now it's fairly obvious where the problem comes in, the signature of a JAVA
"stored procedure" looks like this:

public static void method-name ( SQL-arguments, ResultSet[] result-set-array )
throws SQLException

I need the procedure to return primitive type Integer, and obviously that's
not possible with a function with a VOID return type. Now I would have been
able to get around this problem if functions in Derby had the ability to
take parameters of type OUT or INOUT.

Here is a extract of the SQL-server stored procedure that I'm trying to
create/mimic in Derby:

CREATE  procedure getProc(
  @aquiringCode            CHAR (11) OUTPUT,
  @something                   CHAR (15) OUTPUT,
  @something                          CHAR (4) OUTPUT,
  @something                CHAR (8) OUTPUT,
  @something              CHAR (40)  OUTPUT,
  @something                               VARCHAR (255) OUTPUT
) as begin
  declare @returnCode INTEGER
  if (@aquiringCode is null) begin
    return -1
...
if everything is successfull and all OUTPUT parameters a populated :
    return 1
end

Now this works fine with my current code. But I need to replicate it in
Derby.

Sorry for the small snippets, please let me know if this satisfies your
request, if not I'll see if I can elaborate more.

THANKS for your help
Jacques Coetzee





> 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: 10.2.2.0
>         Environment: Java 1.4.2
> Windows XP SP 2
>            Reporter: Jacques Coetzee
>             Fix For: 10.0.2.2
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message