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 Mon, 15 Oct 2012 11:42:04 GMT

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

Dag H. Wanvik commented on DERBY-2927:

Regressions showed once instance of an old error message now receiving the improved on in
this sample of lang/outparams.sql (old harness test):

Expected exception on prepare of '? = call returnsNothing()': java.sql.SQLException: 'RETURNSNOTHING'
is a procedure but it is being used as a function.

Uploading a new version of the patch, derby-2927-diag-2.

For the record, here is a detailed list of the fixes I made to LangProcedureTest to make it

- Updated the class comment
- removed three negative test cases which are no longer relevant: we do support LOB, CLOB
and LONG VARCHAR arguments now
- fixed up lots of weird looking formatting (systematic, probably from conversion tool) of
"s.execute" being split over two lines
- converted a positive case to a negative one: DERBY-3652 made a non-compliant resolution
fail, cf. comment inserted mentioning that issue
- changed lots of cases of missing double quotes around routine arguments returned from metadata:
apparently, these were not quoted earlier.
- changed a reference to "IR" to "IR1"; there was no IR1.
- we have tightened up type checking of routine arguments since this test was live, so I converted
a wrong argument to a call of IR2.
- in checkCommitWithMultipleResultSets, changed the expected # of locks from 3 to 2 in location
"lock count after next on first rs", same as Kathey saw before, and I think its correct.
- in another too long argument case, I introduced a negative test in addition to converting
the old one to a positive test: 2nd argument to PT1.
-  in test testSQLControl, I had to make several adjustments to schemas to reference the right
objects, and introduce an extra DROP table to avoid a later 2nd iteration CREATE if it not
fail. Possibly the old test ran with different default schemas, or those we lost in translation.
I believe the test does what it should now.
> 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
>         Attachments: derby-2927-diag-2.diff, derby-2927-diag-2.stat, derby-2927-diag.diff,
> 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