[
https://issues.apache.org/jira/browse/DERBY-2927?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13476088#comment-13476088
]
Dag H. Wanvik edited comment on DERBY-2927 at 10/15/12 11:50 AM:
-----------------------------------------------------------------
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 work
- 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.
-
was (Author: dagw):
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 work
- 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: 10.2.2.0
> 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, derby-2927-diag.stat
>
>
> 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
