question on IN parameter values and streams ?

[Sunitha Kambhampati <ks...@gmail.com>]

I have a question about the binding of parameters using the stream api's 
in JDBC

If you set the value for a parameter marker of a preparedstatement to a 
stream by using the stream api's like setBinaryStream, what should be 
the expected behavior on subsequent executions of the preparedstatement. 

e.g
.....
ps.setBinaryStream(2,mystream, streamLength);   //mystream is a user 
supplied stream
for (int i = start; i < start + rows; i++) {
           ps.setInt(1, i);
           count += ps.executeUpdate();
       }

Since the value set is a stream, the first execution will read off the 
stream and the stream will be drained. In case of Derby, if the 
preparedstatement is an insert, subsequent inserts will actually throw 
an error as the stream will not have enough data. 

I looked at the jdbc 3.0 spec, api and the tutorial book (edition 2) but 
didnt find anything specific to streams and how they should be treated 
when binding. 
____________

1) Is it ok to expect the user  to supply the stream each time when 
using the stream api's provided we document it clearly.
2)or should we check to see if the user supplied stream supports the 
markSupported() and reset the stream each time we finish reading it.
3) or store the data from the stream on disk for multiple re-use
4) materialize the stream so it can be used multiple times

I think #1 seems fine to me compared to all the other options unless of 
course the standard has something specific to say.

Thoughts/comments ?

Thanks,
Sunitha.

ps: using stream api with update statement is probably another issue.

Re: question on IN parameter values and streams ?

["Lance J. Andersen" <La...@Sun.COM>]

i am talking to the EG about clarifying this in the spec.

-lance

Daniel John Debrunner wrote:

>Sunitha Kambhampati wrote:
>
>  
>
>>I have a question about the binding of parameters using the stream api's
>>in JDBC
>>
>>If you set the value for a parameter marker of a preparedstatement to a
>>stream by using the stream api's like setBinaryStream, what should be
>>the expected behavior on subsequent executions of the preparedstatement.
>>e.g
>>.....
>>ps.setBinaryStream(2,mystream, streamLength);   //mystream is a user
>>supplied stream
>>for (int i = start; i < start + rows; i++) {
>>          ps.setInt(1, i);
>>          count += ps.executeUpdate();
>>      }
>>
>>Since the value set is a stream, the first execution will read off the
>>stream and the stream will be drained. In case of Derby, if the
>>preparedstatement is an insert, subsequent inserts will actually throw
>>an error as the stream will not have enough data.
>>I looked at the jdbc 3.0 spec, api and the tutorial book (edition 2) but
>>didnt find anything specific to streams and how they should be treated
>>when binding. ____________
>>
>>1) Is it ok to expect the user  to supply the stream each time when
>>using the stream api's provided we document it clearly.
>>2)or should we check to see if the user supplied stream supports the
>>markSupported() and reset the stream each time we finish reading it.
>>3) or store the data from the stream on disk for multiple re-use
>>4) materialize the stream so it can be used multiple times
>>
>>I think #1 seems fine to me compared to all the other options unless of
>>course the standard has something specific to say.
>>
>>Thoughts/comments ?
>>    
>>
>
>
>Good question, first just worry about the functional behaviour as seen
>by the JDBC client, not the implementation. So really 2,3,4 are the
>same, the parameter value set by a stream is re-used for the next execution.
>
>The only item I see in the JDBC 3.0 spec is the last paragraph of 13.2.2
>which basically says parameters are re-used. Nothing special called out
>about streams.
>
>There is a potential clue in the javadoc for
>PreparedStatement.clearParameters()
>"In general, parameter values remain in force for repeated use of a
>statement"
>
>Maybe we could take that "In general" to mean, "except for streams".
>
>I think at least option 1) is a safe solution that is forwards
>compatible with allowing a value set by a stream value to be reused. The
>error could be better by clearing the 'parameter is set' flag after the
>execution if the value was set by a stream.
>
>Dan.
>
>  
>

Re: question on IN parameter values and streams ?

[Daniel John Debrunner <dj...@debrunners.com>]

Sunitha Kambhampati wrote:

> I have a question about the binding of parameters using the stream api's
> in JDBC
> 
> If you set the value for a parameter marker of a preparedstatement to a
> stream by using the stream api's like setBinaryStream, what should be
> the expected behavior on subsequent executions of the preparedstatement.
> e.g
> .....
> ps.setBinaryStream(2,mystream, streamLength);   //mystream is a user
> supplied stream
> for (int i = start; i < start + rows; i++) {
>           ps.setInt(1, i);
>           count += ps.executeUpdate();
>       }
> 
> Since the value set is a stream, the first execution will read off the
> stream and the stream will be drained. In case of Derby, if the
> preparedstatement is an insert, subsequent inserts will actually throw
> an error as the stream will not have enough data.
> I looked at the jdbc 3.0 spec, api and the tutorial book (edition 2) but
> didnt find anything specific to streams and how they should be treated
> when binding. ____________
> 
> 1) Is it ok to expect the user  to supply the stream each time when
> using the stream api's provided we document it clearly.
> 2)or should we check to see if the user supplied stream supports the
> markSupported() and reset the stream each time we finish reading it.
> 3) or store the data from the stream on disk for multiple re-use
> 4) materialize the stream so it can be used multiple times
> 
> I think #1 seems fine to me compared to all the other options unless of
> course the standard has something specific to say.
> 
> Thoughts/comments ?


Good question, first just worry about the functional behaviour as seen
by the JDBC client, not the implementation. So really 2,3,4 are the
same, the parameter value set by a stream is re-used for the next execution.

The only item I see in the JDBC 3.0 spec is the last paragraph of 13.2.2
which basically says parameters are re-used. Nothing special called out
about streams.

There is a potential clue in the javadoc for
PreparedStatement.clearParameters()
"In general, parameter values remain in force for repeated use of a
statement"

Maybe we could take that "In general" to mean, "except for streams".

I think at least option 1) is a safe solution that is forwards
compatible with allowing a value set by a stream value to be reused. The
error could be better by clearing the 'parameter is set' flag after the
execution if the value was set by a stream.

Dan.