Thread: modification required to pass Sun's CTS
The cts calls executeUpdate on a function with out parameters. I know the API says that executeUpdate is only to be used when no results are expected, but this is the way the test is. Any thoughts on changing executeUpdate to allow results to be returned? Dave Dave Cramer davec@postgresintl.com www.postgresintl.com ICQ #14675561 jabber davecramer@jabber.org ph (519 939 0336 )
Dave Cramer wrote: > The cts calls executeUpdate on a function with out parameters. > > I know the API says that executeUpdate is only to be used when no > results are expected, > but this is the way the test is. > > > Any thoughts on changing executeUpdate to allow results to be returned? That's almost certainly the wrong thing to do. executeUpdate is quite specific about throwing exceptions when a resultset is returned. I think the confusion arises from JDBC not considering an OUT parameter to be a "result". i.e. you can have a function with OUT parameters that returns a resultset, but equally you can have one that doesn't return a resultset; and the values in the resultset (if any) are separate to the returned OUT parameter values. Given that the backend OUT parameter support is implemented as a resultset, maybe it makes sense to hide the resultset of a function with OUT parameters entirely, since it's really an implementation artifact? Then executeUpdate shouldn't need changes. Does the CTS have the reverse case, i.e. requiring a function that has both OUT parameters and a resultset? Seems like that'd be hard to support at all with the current scheme.. -O
Oliver,
This is what we have to pass.
rsSch.createTab("Numeric_Tab",sqlp,conn);
msg.setMsg("get the CallableStatement object");
cstmt = conn.prepareCall("{call Numeric_Proc
(?,?,?)}");
msg.setMsg("Register the output parameter");
cstmt.registerOutParameter
(1,java.sql.Types.NUMERIC,15);
cstmt.registerOutParameter
(2,java.sql.Types.NUMERIC,15);
cstmt.registerOutParameter
(3,java.sql.Types.NUMERIC,15);
msg.setMsg("execute the procedure");
cstmt.executeUpdate();
msg.setMsg("invoke getBigDecimal method");
oRetVal = cstmt.getBigDecimal(1);
String sRetStr = rsSch.extractVal
("Numeric_Tab",1,sqlp,conn);
msg.setMsg("extracted MAX_VAL from
Numeric_Tab");
maxBigDecimalVal = new BigDecimal(sRetStr);
msg.addOutputMsg("" + maxBigDecimalVal, "" +
oRetVal);
if( (oRetVal.compareTo(maxBigDecimalVal) ==
0) )
{
msg.setMsg("getBigDecimal returns
the Maximum value ");
}
else
{
msg.printTestError("getBigDecimal()
did not return the Maximum value", "test getBigDecimal Failed!");
}
On 1-Jun-05, at 5:37 PM, Oliver Jowett wrote:
> Dave Cramer wrote:
>
>> The cts calls executeUpdate on a function with out parameters.
>> I know the API says that executeUpdate is only to be used when no
>> results are expected,
>> but this is the way the test is.
>> Any thoughts on changing executeUpdate to allow results to be
>> returned?
>>
>
> That's almost certainly the wrong thing to do. executeUpdate is
> quite specific about throwing exceptions when a resultset is returned.
>
> I think the confusion arises from JDBC not considering an OUT
> parameter to be a "result". i.e. you can have a function with OUT
> parameters that returns a resultset, but equally you can have one
> that doesn't return a resultset; and the values in the resultset
> (if any) are separate to the returned OUT parameter values.
>
> Given that the backend OUT parameter support is implemented as a
> resultset, maybe it makes sense to hide the resultset of a function
> with OUT parameters entirely, since it's really an implementation
> artifact? Then executeUpdate shouldn't need changes.
>
> Does the CTS have the reverse case, i.e. requiring a function that
> has both OUT parameters and a resultset? Seems like that'd be hard
> to support at all with the current scheme..
>
> -O
>
>
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )
Dave Cramer wrote:
> cstmt = conn.prepareCall("{call Numeric_Proc
> (?,?,?)}");
>
> msg.setMsg("Register the output parameter");
> cstmt.registerOutParameter
> (1,java.sql.Types.NUMERIC,15);
> cstmt.registerOutParameter
> (2,java.sql.Types.NUMERIC,15);
> cstmt.registerOutParameter
> (3,java.sql.Types.NUMERIC,15);
>
> msg.setMsg("execute the procedure");
> cstmt.executeUpdate();
Ok, so that is exactly the case I was talking about, a procedure that
doesn't return a value or resultset, but has OUT parameters. We should
mask the resultset we get from the backend in this case as it's just an
implementation detail of our OUT mechanism. (i.e. executeUpdate() does
not complain, executeQuery() does complain, etc)
-O
Oliver,
OK, so if I understand this correctly, we allow the OUT parameter
result set if it is a callablestatement ?
I don't think we can actually return two resultsets ie one for the
out parameters, and yet another if the function returns a resultset.
I don't believe the
backend has this facility.
On 1-Jun-05, at 9:34 PM, Oliver Jowett wrote:
> Dave Cramer wrote:
>
>
>> cstmt = conn.prepareCall("{call
>> Numeric_Proc (?,?,?)}");
>> msg.setMsg("Register the output parameter");
>> cstmt.registerOutParameter
>> (1,java.sql.Types.NUMERIC,15);
>> cstmt.registerOutParameter
>> (2,java.sql.Types.NUMERIC,15);
>> cstmt.registerOutParameter
>> (3,java.sql.Types.NUMERIC,15);
>> msg.setMsg("execute the procedure");
>> cstmt.executeUpdate();
>>
>
> Ok, so that is exactly the case I was talking about, a procedure
> that doesn't return a value or resultset, but has OUT parameters.
> We should mask the resultset we get from the backend in this case
> as it's just an implementation detail of our OUT mechanism. (i.e.
> executeUpdate() does not complain, executeQuery() does complain, etc)
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
Dave Cramer wrote:
> OK, so if I understand this correctly, we allow the OUT parameter
> result set if it is a callablestatement ?
No. I guess I'm not being clear.
If you have a function that does not return anything, but has OUT
parameters, then CallableStatement.executeUpdate() should succeed and
CallableStatement.executeQuery() should fail. That is, the fact that we
use a SELECT to get at the OUT parameters is an *implementation detail*
-- from the point of view of the JDBC {call} escape, the function is
*not* returning a resultset.
So the driver's implementation of OUT parameters should also make sure
to suppress the resultset generated by the SELECT. From the point of
view of the JDBC client, execution of the {call} escape *does not return
a resultset*. That means that executeUpdate() doesn't complain about it,
and executeQuery() *does* complain about it, and you can't get access to
any resultset via getResultSet() etc. If you want to get the OUT
values, you use the appropriate parameter-getting methods on
CallableStatement.
> I don't think we can actually return two resultsets ie one for the out
> parameters, and yet another if the function returns a resultset. I
> don't believe the
> backend has this facility.
Yes, that's what I said in my earlier email.
-O